rsnapshot Remote MySQL Backup Shell Script

Posted on in Categories Backup, MySQL last updated July 5, 2009

rsnapshot is an open-source backup and recovery tool. It can create daily, weekly, hourly and monthly file system backup. It can also create local and remote MySQL server backup.

How do I use this script?

Our sample setup:

Configuration on remote mysql.example.com server

Download the following script and install on remote mysql server at /root/rsnapshot.mysql location. Setup executable permissions:
# cd /root
# wget http://bash.cyberciti.biz/dl/408.sh.zip
# unzip 408.sh.zip
# mv 408.sh rsnapshot.mysql
# rm 408.sh.zip
# chmod +x rsnapshot.mysql

Customize the script and setup login info.

Configuration for rsnapshot server

First, find out your snapshot root, enter:
# grep snapshot_root /etc/rsnapshot.conf
Output:

rsnapshot will store mysql backup at /.raiddisk/rsnapshots/tmp/ before moving to /.raiddisk/rsnapshots/hourly.0/mysql/ directory. Open your rsnapshot.conf file and add the following configuration:

Here is sample configuration for both web and mysql server:

Save and close the file. Test configuration:
# rsnapshot configtest
See rsnapshot tutorial for further details under RHEL / CentOS and Debian / Ubuntu Linux.

Shell Script

18 comment

  1. This is a great script. Works great, but there is one problem though: it returns an ‘error 1’ in the rsnapshot logfile.
    /usr/bin/rsnapshot daily: ERROR: backup_script /usr/bin/ssh -i /home/rsnapshot/.ssh/id_rsa [email protected] ‘/home/rsnapshot/mysql-backup.sh’ returned 1

    I slightly modified the script, by changing some mysqldump-switches:
    –add-drop-table –single-transaction –add-locks –extended-insert –quick

    Any idea where this error 1 comes from?

  2. @nixCraft, that isn’t working. I’m trying to get it work with non standard port for hours.

    backup_script /usr/bin/ssh [email protected] “/root/rsnapshot.mysql” unused1/ +ssh_args=-p 3412
    backup_script /usr/bin/scp -r [email protected]:/tmp/rsnapshot/mysql/ /.raiddisk/rsnapshots/tmp/ mysql/ +ssh_args=-p 3412

    Problem:

    /usr/bin/ssh
    usage: ssh [-1246AaCfgKkMNnqsTtVvXxYy] [-b bind_address] [-c cipher_spec]
    [-D [bind_address:]port] [-e escape_char] [-F configfile]
    [-I pkcs11] [-i identity_file]
    [-L [bind_address:]port:host:hostport]
    [-l login_name] [-m mac_spec] [-O ctl_cmd] [-o option] [-p port]
    [-R [bind_address:]port:host:hostport] [-S ctl_path]
    [-W host:port] [-w local_tun[:remote_tun]]
    [[email protected]]hostname [command]

  3. @Az, try the following if sshd running on 3412

  4. 1. /tmp/rsnapshot/mysql/ directory is world-readable -> no good place to store mysql dumps. You should at least “chmod 700” the dir.
    2. /tmp/rsnapshot/mysql/ directory is not removed after backup.

    Apart from the a good one.

  5. Great script, thanks a bunch! Two minor suggestions to add to this:

    – Put quotes around parameters that might contain spaces in the script. I used a pw generator and it put a space in it, and the script will fail unless you add quotes to them.

    – Have it ignore the information_schema database in the db loop. This will always produce an error (at least it does for me) and it’s a db that is recreated by MySQL that can’t be restored.

  6. Aymen, if you’re getting 255, that means one of the checks in the script failed. It checks the paths of all the binaries and then checks the mysql connectivity before it begins. Set it in verbose mode and run it locally.

  7. Excellent script, though there’s one feature I may have to code in myself –the ability to exclude a database. If I do a manual mysqldump of “the whole server” it doesn’t include information_schema, but this script tries to back it up, and fails.

    When I run it locally, I get this:
    Access denied for user ‘root’@’localhost’ to database ‘information_schema’ when using LOCK TABLES

    There was a bug in mysqldump (fixed in 2008) where it encountered the same error.

  8. Thanks.
    I think it is better if you back up the MySql data directory file (this method will not increase the load like mysqldump). For example, if you have a database called MyDB, just copy the data directory which is /var/lib/mysql/MyDB.
    I use this method for 3 years and it is working fine.

    1. Yousef, your approach has at least one big problem: You have to stop your MySQL to do this cold backup. Most of the administrators don’t have that kind of luxury. The other “problem” is that there might be unused “data” in your data files. Then you backup also those irrelevant bytes.

Leave a Comment