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

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.

Submitted by falko (Contact Author) (Forums) on Wed, 2006-09-27 18:25. ::

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:

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...