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

Want to support HowtoForge? Become 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:

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

 

13 Links


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.
Submitted by Anthony (not registered) on Thu, 2013-04-11 18:14.

You mentioned that running didnt work right:

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

Well you need to make

--source-directory=<backup-directory>/<backup-set>/<incremental-backup> 

 That will result with alot of output if its a large db backup. So this is NOT referring to bin-log directory.

Source:

https://forums.zmanda.com/showthread.php?3272-cannot-open-index-file 

 

 

Submitted by music01 (not registered) on Wed, 2009-12-30 15:34.
for the incremental Backup using the ZRM Community edition, ensure that you have installed the zrm client rpm on the server  in which you need the incremental backup