Copy MySQL Database to New Database


#!/bin/bash

Dump Old-db
mysqldump –compress -u user -pPassword -h db.example.com –skip-quote-names –databases olddb > olddb.dump

Dump Stored Procedures  
mysqldump –compress -u user -pPassword -h db.example.com –routines –no-create-info –no-data –no-create-db –skip-opt olddb  > olddb_routines.dump

Create New User & Permissions  
CREATE USER ‘newuser’ IDENTIFIED BY ‘newPAssword’;
GRANT ALL ON newdb.* TO ‘newuser’@’%’;

Replace Both Dumps olddb(newdb), olduser(newuser)
perl -p -i.bak -e “s/DEFINER=`\w.`@`\d[0-3].[0-3]`//g” olddb_routines.dump

Create New db
cp olddb.dump olddb.sql
cp olddb_routines.dump olddb_routines_routines.sql

mysql -u user -pPassword -h db.example.com < olddb.sql
mysql -u user -pPassword -h db.example.com < olddb_routines_routines.sql

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s