Backing Up And Restoring MySQL Databases

Disclaimer

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 &amp; 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 &gt; $db.dump
  bzip2 -9 $db.dump
done
 
echo "MySQL Backup Completed  : `/bin/date -R`" &gt;&gt; $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 &amp; 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\` ;" &gt;&gt; CREATE_DATABASES.sql
done
 
mysql -h localhost -u $db_user -p$db_pass &lt; CREATE_DATABASES.sql
 
# Restore databases
for db in $dbs
do
  bunzip2 $db.dump.bz2
  mysql -h localhost -u $db_user -p$db_pass $db &lt; $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:

/etc/init.d/mysqld restart

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).