Cheap VPS & Xen Server


Residential Proxy Network - Hourly & Monthly Packages

MySQL Backup And Recovery With mysql-zrm On Debian Sarge


This guide describes how to back up and recover your MySQL databases with mysql-zrm on a Debian Sarge system. mysql-zrm is short for Zmanda Recovery Manager for MySQL, it is a new tool that lets you create full logical or raw backups of your databases (regardless of your storage engine and MySQL configuration), generate reports about the backups, verify the integrity of the backups, and recover your databases. It can also send email notifcations about the backup status, and you can implement multiple backup policies (based on your applications and based on time (e.g. daily, weekly, etc.)).

I want to say first that this is not the only way of setting up such a system. There are many ways of achieving this goal but this is the way I take. I do not issue any guarantee that this will work for you!

 

1 Preliminary Note

mysql-zrm works on MySQL 4.1 and above, so I assume you already have a MySQL server installed on your Debian Sarge system, e.g. like this:

apt-get install mysql-client-4.1 mysql-common-4.1 mysql-server-4.1

This also installs the package libdbd-mysql-perl which is needed by mysql-zrm as mysql-zrm is written in Perl.

 

2 Installation

Zmanda has released an rpm package of mysql-zrm for rpm-based distributions like Fedora, RedHat, SuSE, CentOS, etc., but no package for Debian Sarge. So we must download the mysql-zrm source package from http://www.zmanda.com/downloads.html. Select the stable release (at the time of this writing it was 1.0.3) and download it to your /tmp directory:

cd /tmp
wget http://www.zmanda.com/downloads/community/ZRM-MySQL/1.0.3/Source/MySQL-zrm-1.0.3.tar.gz

Next we unpack the sources and go to the source directory:

tar xvfz MySQL-zrm-1.0.3.tar.gz
cd MySQL-zrm-1.0.3

Unfortunately the installation instructions in the INSTALL file only say that you can install the mysql-zrm rpm package if you are on an rpm-based distribution, but nothing more. Also, there’s no installation script and no installation instructions for the source package on the Zmanda web site, so I had to find out myself how to get mysql-zrm installed on my Debian Sarge system. This is how I did it:

chown root:root *
mv mysql-zrm /usr/bin
mv mysql-zrm-reporter /usr/bin
mv mysql-zrm-scheduler /usr/bin
gzip mysql-zrm.1
mv mysql-zrm.1.gz /usr/share/man/man1
gzip mysql-zrm.conf.5
mv mysql-zrm.conf.5.gz /usr/share/man/man5
gzip mysql-zrm-reporter.1
mv mysql-zrm-reporter.1.gz /usr/share/man/man1
gzip mysql-zrm-reporter.conf.5
mv mysql-zrm-reporter.conf.5.gz /usr/share/man/man5
gzip mysql-zrm-scheduler.1
mv mysql-zrm-scheduler.1.gz /usr/share/man/man1
mkdir /etc/mysql-zrm
mv *.conf /etc/mysql-zrm
mkdir -p /usr/lib/mysql-zrm/Data/Report/Plugin
mv Report.pm /usr/lib/mysql-zrm/Data
mv Base.pm /usr/lib/mysql-zrm/Data/Report
mv *.pm /usr/lib/mysql-zrm/Data/Report/Plugin
mkdir /var/log/mysql-zrm
gzip AUTHORS
gzip COPYING
gzip INSTALL
gzip README
mkdir /usr/share/doc/MySQL-zrm
mv * /usr/share/doc/MySQL-zrm
mkdir /var/lib/mysql-zrm
touch /etc/mysql-zrm/mysql-zrm-release

That’s it. The executable files have been moved to /usr/bin, the configuration files are in /etc/mysql-zrm, and we even have man pages for our executables (mysql-zrm, mysql-zrm-reporter, and mysql-zrm-scheduler), so if you are not sure about the usage of the executables, you can run

man mysql-zrm

man mysql-zrm-reporter

man mysql-zrm-scheduler

3 Basic Configuration

The main configuration file is /etc/mysql-zrm/mysql-zrm.conf. In this file we have to specify at least the MySQL-backup user (a MySQL user with all privileges, like root) and his password:

vi /etc/mysql-zrm/mysql-zrm.conf

user="root"
password="yourrootsqlpassword"

If nothing else is specified, mysql-zrm assumes default values for all other settings. With this configuration, mysql-zrm would do backups of all databases, in raw format, it would keep the backups forever, and it wouldn’t send email notifications.

Raw format means, it backs up the databases as binary files that can be copied back to the database in case of loss of data, but it can cause problems if you copy these files between different MySQL versions.

The counterpart of the raw format is the logical format which creates text files with a plain SQL dump of your databases. These SQL dumps can be restored in almost all MySQL versions, and you could even do it manually, as shown here: http://www. kreationnext.com/faq/6_4_en.html

If you want to get email notifications to your email address example@example.com, add this to /etc/mysql-zrm/mysql-zrm.conf:

vi /etc/mysql-zrm/mysql-zrm.conf

mailto="example@example.com"

If you want to make backups in logical format and want to keep backups for seven days (instead of forever), add these lines to /etc/mysql-zrm/mysql-zrm.conf:

vi /etc/mysql-zrm/mysql-zrm.conf

# allowed values for backup-mode are "raw" and "logical"
backup-mode=logical
retention-policy=7D

If you want to make a backup of a MySQL replication slave, add this line to /etc/mysql-zrm/mysql-zrm.conf:

vi /etc/mysql-zrm/mysql-zrm.conf

replication=1

This will back up files that are important for a MySQL replication slave.

If you want to back up only the databases exampledb and anotherexampledb instead of all databases, add this to /etc/mysql-zrm/mysql-zrm.conf:

vi /etc/mysql-zrm/mysql-zrm.conf

databases=exampledb anotherexampledb

If you only need backups of the tables text, user, and page from the exampledb database, add this to /etc/mysql-zrm/mysql-zrm.conf:

vi /etc/mysql-zrm/mysql-zrm.conf

tables=text user page
database="exampledb"

Please note: the settings all-databases, databases, and tables/database are mutually exclusive!

The default /etc/mysql-zrm/mysql-zrm.conf comes with lots of comments that explain all configuration options. I’m using these settings for now:

vi /etc/mysql-zrm/mysql-zrm.conf

user="root"
password="yourrootsqlpassword"
mailto="example@example.com"
backup-mode=logical
retention-policy=7D
# all-databases=1 is optional, as it is the default setting
all-databases=1

4 Backup Examples

With mysql-zrm you can maintain multiple backups, called backup sets, e.g. a daily backup, a weekly backup, a backup for your osCommerce database, a backup for your vBulletin database, etc.

Now let’s create our first backup which we create in the backup set dailyrun:

mysql-zrm-scheduler –now –backup-set dailyrun –backup-level 0

This will immediatley back up all databases to the directory /var/lib/mysql-zrm/dailyrun, and it will also create the directory /etc/mysql-zrm/dailyrun. –backup-level 0 means: do a full backup (you can also do incremental backups (–backup-level 1), but I’ll say something about this later on – for now we only do full backups).

If you have enabled email notifications, you should have received an email with the status of the backup now. But you can also generate a report about the backup status on the command line like this:

mysql-zrm-reporter –where backup-set=dailyrun –show backup-status-info

The output will look like this:

          backup_set  backup_date                             backup_level  backup_status         comment
----------------------------------------------------------------------------------------------------------------------
            dailyrun  Tue 26 Sep 2006 07:57:47 PM CEST                   0  Backup succeeded      ----

You can also generate a few other reports with the following commands:

mysql-zrm-reporter –where backup-set=dailyrun –show backup-method-info
mysql-zrm-reporter –where backup-set=dailyrun –show backup-retention-info
mysql-zrm-reporter –where backup-set=dailyrun –show backup-performance-info
mysql-zrm-reporter –where backup-set=dailyrun –show restore-full-info
mysql-zrm-reporter –where backup-set=dailyrun –show restore-incr-info
mysql-zrm-reporter –where backup-set=dailyrun –show replication-info

Try each of them to see what information they provide.

Now we want to create a daily and a weekly backup that is run automatically by the system (i.e., without our interaction). This is how we can set up these backups:

mysql-zrm-scheduler –add –interval daily –backup-set dailyrun –backup-level 0
mysql-zrm-scheduler –add –interval weekly –backup-set weeklyrun –backup-level 0

(With the second command we have created a new backup set called weeklyrun.)

Now let’s check our scheduled backups:

mysql-zrm-scheduler –query

The output looks like this:

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
0 3 * * * /usr/bin/mysql-zrm --action backup --destination /var/lib/mysql-zrm --backup-set dailyrun --backup-level 0
0 4 * * * /usr/bin/mysql-zrm --action purge --destination /var/lib/mysql-zrm
0 2 * * 0 /usr/bin/mysql-zrm --action backup --destination /var/lib/mysql-zrm --backup-set weeklyrun --backup-level 0

As you can see, the daily backup is run each day at 03:00h, and the weekly backup is run each Sunday at 02:00h.

(BTW, instead of

mysql-zrm-scheduler –query

you could as well run

crontab -l

to get the same information.)

If you want to start the daily backup at another time, e.g. 13:35h, you can do it like this:

mysql-zrm-scheduler –add –interval daily –backup-set dailyrun –start 13:35 –backup-level 0

To remove a scheduled backup from cron, you can do it like this:

mysql-zrm-scheduler –delete –interval weekly

This would remove the weekly backup run on Sundays at 02:00h from cron. If you want to remove the daily backup that is scheduled for 13:35h, you can do it like this:

mysql-zrm-scheduler –delete –interval daily –start 13:35

But you can as well run

crontab -e

to edit your cron jobs which is sometimes easier than dealing with mysql-zrm-scheduler.

 

5 Customized Reports, HTML Reports

In chapter 4 I have already mentioned what kind of reports are available. But you can also generate customized reports, i.e., you can specify which columns/information you want to see. For example,

mysql-zrm-reporter –fields backup-set,backup-date,backup-level,backup-status –where backup-set=dailyrun

generates a report for the backup set dailyrun that shows the columns backup-set, backup-date, backup-level, and backup-status:

          backup_set  backup_date                             backup_level  backup_status
------------------------------------------------------------------------------------------------
            dailyrun  Tue 26 Sep 2006 07:57:47 PM CEST                   0  Backup succeeded
            dailyrun  Tue 26 Sep 2006 07:58:08 PM CEST                   0  Backup succeeded
            dailyrun  Tue 26 Sep 2006 07:58:31 PM CEST                   0  Backup succeeded
            dailyrun  Tue 26 Sep 2006 08:24:04 PM CEST                   0  Backup succeeded

A list of all columns is available on http://mysqlbackup.zmanda.com/index.php/What_information_can_be_obtained_from_a_backup_report%3F.

mysql-zrm lets you also create HTML reports. Let’s assume you have a web server (e.g. Apache) installed on your system with the document root /var/www. Now run

mysql-zrm-reporter –show backup-status-info –where backup-set=dailyrun –type html –output /var/www/backup-status-dailyrun.html

This creates the HTML file backup-status-dailyrun.html in your /var/www directory that you can now access in your browser (e.g. http://server1.example.com/backup-status-dailyrun.html):

1

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.

11 Incremental Backups

mysql-zrm can also do incremental backups, however I had some problems with it. First I had to configure MySQL to write its bin-logs to /var/lib/mysql (by editing /etc/mysql/my.cnf) because that’s where mysql-zrm expects them. Afterwards,

mysql-zrm-scheduler –now –backup-set dailyrun –backup-level 1

seemed to work, but

mysql-zrm –action parse-binlogs –source-directory=/var/lib/mysql –backup-set dailyrun

gave back an error:

Use of uninitialized value in concatenation (.) or string at /usr/bin/mysql-zrm line 1305.
INFO: mysql-zrm-version
ERROR: cannot open index file /var/lib/mysql/index No such file or directory

That’s why I think it’s better to do full backups for now instead of incremental backups.

 

12 Remote Backups

mysql-zrm lets you also do backups from remote MySQL servers over the network. However, this also caused a few problems for me.

12.1 First Try

In my first try I wanted to do a backup (in raw format) of a MySQL server on a remote SuSE 10.0 system. I edited /etc/mysql-zrm/mysql-zrm.conf and put the remote user, password, and hostname into it, then I ran

mysql-zrm-scheduler –now –backup-set dailyrun –backup-level 0

The result was this:

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
Use of uninitialized value in concatenation (.) or string at /usr/bin/mysql-zrm line 1305.
INFO: mysql-zrm-version
WARNING: Binary logging is off. Incremental and logical backup will not work.
INFO: backup-set=dailyrun
INFO: backup-date=20060927095528
INFO: backup-date-epoch=1159343728
INFO: mysql-version=4.1.13
INFO: backup-directory=/var/lib/mysql-zrm/dailyrun/20060927095528
INFO: backup-level=0
WARNING: Database test is empty and hence will not be backedup
WARNING: Database tmp is empty and hence will not be backedup
ERROR: Output of command: ‘mysqlhotcopy’ is
DBI connect(‘;host=192.168.0.163;mysql_read_default_group=mysqlhotcopy’,’root’,…) failed: Client does not support authentication protocol requested by server; consider upgrading MySQL client at /usr/bin/mysqlhotcopy line 182
ERROR: mysqlhotcopy command did not succeed.
Command used is mysqlhotcopy –quiet –user=root –password=***** –host=192.168.0.163 db_ispconfig mysql “/var/lib/mysql-zrm/dailyrun/20060927095528” > /tmp/4Z75iIAeo5 2>&1
Return value is 65280
INFO: backup-status=Backup failed
INFO: Backup failed
ERROR: /usr/bin/mysql-zrm did not finish successfully

I guess this happened because of different MySQL versions on both systems.

12.2 Second Try

In my second try I wanted to make a backup (again in raw format) of a MySQL database on a remote Debian Sarge server. Both the local and the remote system had the same MySQL version. I ran

mysql-zrm-scheduler –now –backup-set dailyrun –backup-level 0

and got the following errors:

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
Use of uninitialized value in concatenation (.) or string at /usr/bin/mysql-zrm line 1305.
INFO: mysql-zrm-version
INFO: backup-set=dailyrun
INFO: backup-date=20060927100653
INFO: backup-date-epoch=1159344413
INFO: mysql-version=4.1.11-Debian_4sarge7-log
INFO: backup-directory=/var/lib/mysql-zrm/dailyrun/20060927100653
INFO: backup-level=0
WARNING: Database test is empty and hence will not be backedup
ERROR: Output of command: ‘mysqlhotcopy’ is
Cannot open dir ‘/var/lib/mysql/web34_db1’: No such file or directory at /usr/bin/mysqlhotcopy line 293.
ERROR: mysqlhotcopy command did not succeed.
Command used is mysqlhotcopy –quiet –user=root –password=***** –host=192.168.0.110 mysql web34_db1 “/var/lib/mysql-zrm/dailyrun/20060927100653” > /tmp/yxFsViAlbm 2>&1
Return value is 512
INFO: backup-status=Backup failed
INFO: Backup failed
ERROR: /usr/bin/mysql-zrm did not finish successfully

It seemed to miss the directory /var/lib/mysql/web34_db1 (web34_db1 is one of the databases on the remote system) on the local system! So I created it:

mkdir /var/lib/mysql/web34_db1

and ran

mysql-zrm-scheduler –now –backup-set dailyrun –backup-level 0

again, and this time it worked, but I doubt that this is the way it is supposed to be…

12.3 Third Try

This time I tried to make a backup from the same remote Debian Sarge system as before, but in logical instead of raw format. I ran

mysql-zrm-scheduler –now –backup-set dailyrun –backup-level 0

and got the following error:

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
Use of uninitialized value in concatenation (.) or string at /usr/bin/mysql-zrm line 1305.
INFO: mysql-zrm-version
WARNING: Binary logging is off. Incremental and logical backup will not work.
INFO: backup-set=dailyrun
INFO: backup-date=20060927095501
INFO: backup-date-epoch=1159343701
INFO: mysql-version=4.1.13
INFO: backup-directory=/var/lib/mysql-zrm/dailyrun/20060927095501
INFO: backup-level=0
ERROR: Binary logging is off. Logical backup cannot be done
INFO: backup-status=Backup failed
INFO: Backup failed
ERROR: /usr/bin/mysql-zrm did not finish successfully

But this time it’s the normal behaviour because for remote logical backups MySQL needs to be configured with SSL, as described on http://mysqlbackup.zmanda.com/index.php/Do_I_need_to_make_changes_to_MySQL_database_configuration%3F. Unfortunately, the Debian Sarge MySQL packages come without SSL support:

mysqld –ssl –help

060927 12:26:09 [ERROR] mysqld: unknown option ‘–ssl’

I logged in to the MySQL shell:

mysql -u root -p

and ran

SHOW VARIABLES LIKE ‘have_openssl’;

and got the same result:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | NO    |
+---------------+-------+
1 row in set (0.01 sec)

No SSL support…

 

 

 

Comments

comments