MySQL Backup And Recovery With mysql-zrm On Debian Sarge - Page 4
6 Verify Your Backups
Now we want to check the integrity of our backup set dailyrun. We can do it like this:
mysql-zrm --action verify-backup --backup-set dailyrun
The output should look like this:
Use of uninitialized value in concatenation (.) or string at /usr/bin/mysql-zrm line 1305. INFO: mysql-zrm-version INFO: Verification successful |
(You can ignore the warning in the first line, it's nothing serious.)
Next we want to check a specific backup within our backup set dailyrun. First we run
mysql-zrm-reporter -show restore-full-info --where backup-set=dailyrun
to find out which backups are available:
backup_set backup_date backup_level backup_directory
----------------------------------------------------------------------------------------------------------
dailyrun Tue 26 Sep 2006 07:57:47 PM CEST 0 /var/lib/mysql-zrm/dailyrun/20
060926195747
dailyrun Tue 26 Sep 2006 07:58:08 PM CEST 0 /var/lib/mysql-zrm/dailyrun/20
060926195808
dailyrun Tue 26 Sep 2006 07:58:31 PM CEST 0 /var/lib/mysql-zrm/dailyrun/20
060926195831
dailyrun Tue 26 Sep 2006 08:24:04 PM CEST 0 /var/lib/mysql-zrm/dailyrun/20
060926202404
|
As you can see, there are four backups in our backup set dailyrun. We want to check the last one which is in the directory /var/lib/mysql-zrm/dailyrun/20060926202404. This is how we can do it:
mysql-zrm --action verify-backup --backup-set dailyrun --no-quiet --source-directory /var/lib/mysql-zrm/dailyrun/20060926202404
The output should look like this:
Use of uninitialized value in concatenation (.) or string at /usr/bin/mysql-zrm line 1305. INFO: mysql-zrm-version INFO: Verification successful |
7 Data Recovery
Let's assume our database has crashed, and we have lost data. This is how we can restore our data from our MySQL backups. First we run
mysql-zrm-reporter -show restore-full-info --where backup-set=dailyrun
to find out which backups are available:
backup_set backup_date backup_level backup_directory
----------------------------------------------------------------------------------------------------------
dailyrun Tue 26 Sep 2006 07:57:47 PM CEST 0 /var/lib/mysql-zrm/dailyrun/20
060926195747
dailyrun Tue 26 Sep 2006 07:58:08 PM CEST 0 /var/lib/mysql-zrm/dailyrun/20
060926195808
dailyrun Tue 26 Sep 2006 07:58:31 PM CEST 0 /var/lib/mysql-zrm/dailyrun/20
060926195831
dailyrun Tue 26 Sep 2006 08:24:04 PM CEST 0 /var/lib/mysql-zrm/dailyrun/20
060926202404
|
We want to restore from our latest backup which is in /var/lib/mysql-zrm/dailyrun/20060926202404:
mysql-zrm --action restore --backup-set dailyrun --source-directory /var/lib/mysql-zrm/dailyrun/20060926202404
The output looks like this:
Use of uninitialized value in concatenation (.) or string at /usr/bin/mysql-zrm line 1305. INFO: mysql-zrm-version INFO: Restored database from raw backup: egroupware INFO: Restored database from raw backup: mysql INFO: Restore done in 14 seconds. MySQL server has been shutdown. Please restart after verification. |
The data has been restored, but the MySQL server has been shutdown. Therefore we must start it now:
/etc/init.d/mysql start
If you have made your backups in the logical format instead of the raw format, you can also recover your data like this.
8 Multiple Backup Policies
Until now, we had one global configuration in /etc/mysql-zrm/mysql-zrm.conf which applied to all our backup sets. Now let's assume we have a backup set osCommerce for our osCommerce database, another backup set vBulletin for our vBulletin database, etc.
It doesn't make sense to back up all our MySQL databases in the backup set osCommerce because that backup should only contain the osCommerce database, and the same applies to our vBulletin backup set. This is how we can solve this problem:
Each backup set has its own subdiretory in the /etc/mysql-zrm directory, e.g. the backup set osCommerce has the directory /etc/mysql-zrm/osCommerce. Now whenever mysql-zrm is run, it first checks the global configuration in /etc/mysql-zrm/mysql-zrm.conf, and then it checks the directory of the current backup set for the file mysql-zrm.conf whose settings override the global settings in /etc/mysql-zrm/mysql-zrm.conf. E.g., if the current backup set is osCommerce, mysql-zrm would first read the configuration from /etc/mysql-zrm/mysql-zrm.conf and afterwards the configuration from /etc/mysql-zrm/osCommerce/mysql-zrm.conf.
If we just want to backup the MySQL database osCommerce in the backup set osCommerce, we put the following into /etc/mysql-zrm/osCommerce/mysql-zrm.conf:
vi /etc/mysql-zrm/osCommerce/mysql-zrm.conf
databases=osCommerce |
9 Removal Of Old Backups
If you have not set retention-policy in your mysql-zrm configuration, the MySQL backups are kept forever which means your hard disk will get full over time. You can remove old backups simply by deleting the backup directory. For example, if you have a backup in /var/lib/mysql-zrm/dailyrun/20060926195831 and don't need it anymore, you can delete it like this:
rm -fr /var/lib/mysql-zrm/dailyrun/20060926195831
10 Log
mysql-zrm logs to the log file /var/log/mysql-zrm/mysql-zrm.log.
