MySQL Backup And Recovery With mysql-zrm On Debian Sarge - Page 4

Want to support HowtoForge? Become a subscriber!
 
Submitted by falko (Contact Author) (Forums) on Wed, 2006-09-27 18:23. ::

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.


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.