MySQL Backup and Recovery
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.
Thanks,
Srinivas
No comments:
Post a Comment