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. 