Recently I use mysql-zrm to backup a large databases from a client server. I can backup all databases or a set of database or maybe some of tables with one line linux command. Pretty easy to use.

You may imagine that mysql-zrm is a strong management tool of mysql, mysqldump and mysqlhotcopy.  The key benifits for me are : I can backup all databases at once with my databases are automatically created if a new client comes; mysql-zrm is able to save compressed data to different folder, so I can keep saved data up to seven days (you may do it one month or maybe one year); It also has a scheduler to do your task every certain time you want.

Installation:

1. login to your backup server

2. Download rpm from http://www.zmanda.com/download-zrm.php

3. If you don’t have Perl installed, run ” yum install perl ”

4. run ” rpm -ivh MySQL-zrm-2.0-1.noarch.rpm ”

5. “  vi /etc/mysql-zrm/mysql-zrm.conf ” update following parameters in file

backup-mode=logical [ ideally raw for myisam engine and logical for innodb. but I recommend using logical for default. Because when I use raw for default value, and if there is table with innodb engine exist, zrm will prompt me password for mysql@(server_ip) which have no idea about linux mysql user login ]

destination=/home/mybackup_dir  [ specify where backup files will be stored]

Start backup:

mysql-zrm –action backup –host xxx.xxx.xxx.xxx –user aabb –password ccdd  –backup-set abcd

where
–host is remote server IP for MySQL
–user is remote MySQL server  user login
–password is remote MySQL server  user login password
–backup-set is backup folder name

Result:

after running the command, you will get backup file in the path look like this

/home/mybackup_dir/abcd/20081128195236

Restore database to local server:

/usr/bin/mysql-zrm-restore –user=1122 –password=2233 –source-directory=/home/mybackup_dir/abcd/20081128195236
check out your backup server MySQL database.