MySQL Backup and Recovery

If your site manages it's data with MySQL, then you obviously need to make sure the data is safe. In this blog post, I will show how to create a daily backup automatically. I will also show a continuous data protection plan for MySQL databases. This blog post uses the previous backup server configured in my Secure Backup & Recovery with rsnapshot, rssh and OpenSSH article.
In order to understand this blog, let's define some important terms :

• Backup server's hostname : angel.company.com
• First MySQL server's hostname : jedi.company.com
• Second MySQL server's hostname : r2d2.company.com

Backup Server Setup (part 1 of 2)

The first thing we need to do on the backup server is to install the required software.

ssh angel.company.com
sudo yum -y openssh-clients rsnapshot mysql vim

Then configure a directory structure where the MySQL backups will be stored. Ideally, you want to create a seperate file system for this directory structure. And manage the file system under LVM2 so that you can increase it's size dynamically in the future. I'll skip the LVM2 setup for now.

sudo mkdir /export/backup/{conf,data,log,run,scripts}
sudo chown -R root:root /backup

Create two wrapper scripts to help the process.

sudo vim /export/backup/scripts/backup_runner.sh
sudo vim /export/backup/scripts/ssh_wrapper.sh

Make sure both scripts are executable and that they don't have any syntax errors in them.

sudo chmod a+x /backup/scripts/*.sh
sudo sh -n /export/backup/scripts/backup_runner.sh
sudo sh -n /export/backup/scripts/ssh_wrapper.sh

Configure both MySQL backup configuration files. WARNING : rsnapshot is very sensitive with spaces and tabs. DO NOT USE ANY SPACES IN THE CONFIGURATION FILE! You have been warned :)

sudo vim /export/backup/conf/rdbms.mysql.daily
sudo vim /export/backup/conf/rdbms.mysql.hourly

Make sure our backup log files don't consume too much disk space.

sudo vi /etc/logrotate.d/backup

And make sure our new logrotate configuration is still valid.

sudo logrotate -d /etc/logrotate.conf

Create the MySQL two backup scripts. Notice that in each of these two scripts, the variable MYSQL_HOST_LIST  is a space seperated list of all FQDN machines running MySQL. The beauty of this is that you can backup all your MySQL machines with a single script!

WARNING : be sure to change the user's password in both scripts!

sudo vim /export/backup/scripts/mysql_backup_daily.sh

sudo vim /export/backup/scripts/mysql_backup_hourly.sh

Protect those scripts because they hold the MySQL backup user's password.

sudo chown root:root /export/backup/scripts/mysql_backup_*.sh

sudo chmod 700 /export/backup/scripts/mysql_backup_*.sh

We now need to configure the MySQL clients with the proper database backup user.

MySQL Client Configuration

Connect to each MySQL machines in order to create the backup user in their mysql database. Again, don't forget to update the user's password in the SQL commands. Let's start by our first MySQL server.

ssh jedi.company.com

mysql -u root -p

mysql> create user 'backup'@'angel.company.com' identified by 'change_me';

mysql> grant all on *.* to 'backup'@'angel.company.com';

mysql> flush privileges;

mysql> exit;

exit

And now do the same with the other MySQL machine.

ssh r2d2.company.com

mysql -u root -p

mysql> create user 'backup'@'angel.company.com' identified by 'change_me';

mysql> grant all on *.* to 'backup'@'angel.company.com';

mysql> flush privileges;

mysql> exit;

exit

Backup Server Setup (part 2 of 2)

Back on the backup server, execute the mysql command to test if the new user can connect?

mysql -u backup -p -h jedi.company.com

mysql -u backup -p -h r2d2.company.com

Once that is done, we can configure root's crontab to execute both of these scripts.

sudo crontab -e

Once the backups are done, you will now have the following data in your data folder.

sudo ls -AlFR /export/backup/data/rdbms.mysql/

/export/backup/data/rdbms.mysql/:

total 20

drwxr-xr-x  3 root root 4096 Feb 22 13:30 daily.0/

drwxr-xr-x  4 root root 4096 Feb 22 14:00 hourly.0/

drwxr-xr-x  3 root root 4096 Feb 22 13:46 hourly.1/

/export/backup/data/rdbms.mysql/daily.0:

total 12

drwxr-xr-x 2 root root 4096 Feb 22 13:30 all_servers/

/export/backup/data/rdbms.mysql/daily.0/all_servers:

total 292

-rw------- 1 root root 139900 Feb 22 13:30 jedi.company.com.ALL.20130222.sql.gz

-rw------- 1 root root    985 Feb 22 13:30 jedi.company.com.information_schema.20130222.sql

-rw------- 1 root root 138348 Feb 22 13:30 jedi.company.com.mysql.20130222.sql.gz

-rw------- 1 root root   2247 Feb 22 13:30 jedi.company.com.net2ftp.20130222.sql.gz

/export/backup/data/rdbms.mysql/hourly.0:

total 16

drwxr-xr-x 2 root root 4096 Feb 22 14:00 all_servers/

drwxr-xr-x 2 root root 4096 Feb 22 13:46 prod/

/export/backup/data/rdbms.mysql/hourly.0/all_servers:

total 152

-rw------- 1 root root    985 Feb 22 14:00 jedi.company.com.information_schema.2013.02.22-14:00.sql

-rw------- 1 root root 138354 Feb 22 14:00 jedi.company.com.mysql.2013.02.22-14:00.sql.gz

-rw------- 1 root root   2254 Feb 22 14:00 jedi.company.com.net2ftp.2013.02.22-14:00.sql.gz

/export/backup/data/rdbms.mysql/hourly.0/prod:

total 152

-rw------- 2 root root    985 Feb 22 13:46 jedi.company.com.information_schema.2013.02.22-13:46.sql

-rw------- 2 root root 138356 Feb 22 13:46 jedi.company.com.mysql.2013.02.22-13:46.sql.gz

-rw------- 2 root root   2255 Feb 22 13:46 jedi.company.com.net2ftp.2013.02.22-13:46.sql.gz

/export/backup/data/rdbms.mysql/hourly.1:

total 12

drwxr-xr-x 2 root root 4096 Feb 22 13:46 prod/

/export/backup/data/rdbms.mysql/hourly.1/prod:

total 152

-rw------- 2 root root    985 Feb 22 13:46 jedi.company.com.information_schema.2013.02.22-13:46.sql

-rw------- 2 root root 138356 Feb 22 13:46 jedi.company.com.mysql.2013.02.22-13:46.sql.gz

-rw------- 2 root root   2255 Feb 22 13:46 jedi.company.com.net2ftp.2013.02.22-13:46.sql.gz

Recovery

Should you ever need to recover (and you should try this before you really have to use this!) simply use one of the SQL scripts generated. For example, if we need to restore the entire database on host jedi.company.com, we would do this :

mysql -u backup -p -h jedi.company.com < jedi.company.com.ALL.20130222.sql.gz

That is assuming the host was reinstalled as a result of a catastrophic failure or security beach. If you already have your databases on the host, make sure to drop them all before you do that.

HTH,

David