rsnapshot Remote MySQL Backup Shell Script

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 server

Download the following script and install on remote mysql server at /root/rsnapshot.mysql location. Setup executable permissions:
# cd /root
# wget
# unzip
# mv rsnapshot.mysql
# rm
# 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

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

Share this on:

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 rsnapshot@ ‘/home/rsnapshot/’ 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 “/root/rsnapshot.mysql” unused1/ +ssh_args=-p 3412
    backup_script /usr/bin/scp -r /.raiddisk/rsnapshots/tmp/ mysql/ +ssh_args=-p 3412


    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]]
    [user@]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. Nice how-to.

    It is good to consider each table to be dumped in separate file.
    That will save you time when restoring.

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

    Have a question? Post it on our forum!