These instructions worked for me in CentOS 4.0. It may very well work for you on Red Hat-like or other distributions. Please note that if you decide to use these instructions to back up and restore MySQL databases on your machines, you are doing so entirely at your very own discretion and that neither this site, sgowtham.com, nor its author is responsible for any/all damage – intellectual or otherwise.
Quite often, we find situations or circumstances where a (or a bunch of) MySQL database(s) need to be transferred from one machine to another. As this situation has surfaced quite many times for myself (and for sure, others), I wrote couple of scripts – to automate the process as much as possible. For the sake of discussion, let us suppose that the two machines are called raymond (source) and robert (destination). Let us further suppose that two databases – MyDB, UrDB – need to be moved from raymond (source) and robert (destination).
Step #1. Backing Them Up on raymond
I use the following BASH script, called mysql_backup.sh:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
#! /bin/bash # BASH script to backup MySQL databases # Create the temporary storage location export STORAGE="/tmp/MySQL/" mkdir -p $STORAGE echo "" >> $STORAGE/MySQL.log echo "MySQL Backup Started : `/bin/date -R`" >> $STORAGE/MySQL.log # Databases - space separated list export dbs='MyDB UrDB' # USER & PASSWORD - use MySQL <strong>root</strong> account credentials here, if you wish export db_user='MYSQL-USER' export db_pass='MYSQL-PASS' # Backup databases cd $STORAGE for db in $dbs do mysqldump --opt -u $db_user -p$db_pass $db > $db.dump bzip2 -9 $db.dump done echo "MySQL Backup Completed : `/bin/date -R`" >> $STORAGE/MySQL.log
So, when this script is executed under the system root account, one should find MyDB.dump.bz2 and UrDB.dump.bz2 under /tmp/MySQL.
Step #2. Transfer The Dumps To robert
I rely on scp (Secure Copy) and the syntax, while still logged in raymond, is as follows:
scp /tmp/MySQL/*.dump.bz2 robert:/tmp/
Enter the appropriate password and the time for file transfer varied based on the size of dumps as well as the speed of network connection between raymond and robert.
Step #3. Restoring Them On robert
After logging into robert, login as root. The following script, called mysql_restore.sh, will first create these databases and then import the dumps into them:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
#! /bin/bash # BASH script to restore MySQL databases # Databases - space separated list export dbs='MyDB UrDB' # USER & PASSWORD - use MySQL <strong>root</strong> account credentials here, if you wish export db_user='MYSQL-USER' export db_pass='MYSQL-PASS' cd /tmp # Create databases rm -f CREATE_DATABASES.sql touch CREATE_DATABASES.sql for db in $dbs do echo "CREATE DATABASE \`$db\` ;" >> CREATE_DATABASES.sql done mysql -h localhost -u $db_user -p$db_pass < CREATE_DATABASES.sql # Restore databases for db in $dbs do bunzip2 $db.dump.bz2 mysql -h localhost -u $db_user -p$db_pass $db < $db.dump done
That’s it. But it’s my personal practice to restart MySQL (on robert) after such restorations. On Red Hat/Fedora/CentOS distributions (or alikes), this can be accomplished via:
More Than Two Databases?
Not a problem. Just make sure that the line export dbs=’MyDB UrDB’ (in both scripts) contains all the MySQL databases you wish to transfer from one machine to another, EXCEPT mysql and information_schema.
How To Back Up and Restore mysql and information_schema ?
Personally, I would include these two databases in the line export dbs=’MyDB UrDB’ in mysql_backup.sh – so that line looks like:
export dbs='MyDB UrDB mysql information_schema'
But while restoring, I do them separately and NOT as part of mysql_restore.sh. One has to keep in mind that (almost) every MySQL installation has these two databases by default. As such, they don’t need to be created like others. The syntax to import them is as follows:
1 2 3 4 5
bunzip2 mysql.dump.gz2 mysql -h localhost -u root -p mysql < mysql.dump bunzip2 information_schema.dump.gz2 mysql -h localhost -u root -p information_schema < information_schema.dump
Enter the password when prompted (password for MySQL root account, not the normal root one).