≡ Menu

rsnapshot Remote MySQL Backup Shell Script

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 comments… add one }
  • Erwin November 12, 2012, 4:02 pm

    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@192.168.1.56 ‘/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?

  • Dom November 9, 2012, 2:23 pm

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

    backup_script /usr/bin/ssh root@mysql.example.com “/root/rsnapshot.mysql” unused1/ +ssh_args=-p 3412
    backup_script /usr/bin/scp -r root@mysql.example.com:/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]]
    [user@]hostname [command]

  • nixCraft October 30, 2012, 7:29 am

    @Az, try the following if sshd running on 3412

    backup_script /usr/bin/ssh root@mysql.example.com "/root/rsnapshot.mysql"	unused1/	+ssh_args=-p 3412
    backup_script /usr/bin/scp -r root@mysql.example.com:/tmp/rsnapshot/mysql/ /.raiddisk/rsnapshots/tmp/ mysql/	+ssh_args=-p 3412
    
  • Az October 23, 2012, 4:23 am

    Seems like rsnapshot.conf remote script doesn’t work for non standard port for ssh.

  • Stephan Jansen November 18, 2011, 3:29 pm

    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.

  • Vivek Gite June 25, 2011, 12:32 am

    wget issue has been fixed.

  • Killshot June 13, 2011, 8:45 pm

    @Angel Abad,

    There’s a bug currently on the website end that’s blocking wget unless you put in another user agent. Try:

    wget -U=Hi http://bash.cyberciti.biz/dl/408.sh.zip

    That worked for me any way.

  • Killshot June 13, 2011, 8:44 pm

    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.

  • Angel Abad May 4, 2011, 9:34 am

    Hi! Good article, but

    wget http://bash.cyberciti.biz/dl/408.sh.zip

    link is broken.

  • Chris November 23, 2010, 7:21 pm

    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.

  • Chris November 9, 2010, 4:04 pm

    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.

  • Aymen October 6, 2010, 10:39 am

    This doesn’t work for me.
    the problem is:
    /usr/bin/rsnapshot hourly: ERROR: backup_script /usr/bin/ssh root@10.100.1.24 “/root/rsnapshot.mysql” returned 255
    help pls.
    thanks.

  • joe October 26, 2009, 8:38 am

    Nice how-to.

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

  • Yousef September 24, 2009, 10:36 pm

    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.

    • Olivier October 9, 2009, 10:13 pm

      Yousef , I’m not sure backuping the DB files instead the dump is a good idea.

    • gn October 14, 2009, 5:53 am

      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.

Security: Are you a robot or human?

Leave a Comment