rsnapshot Remote MySQL Backup Shell Script

by on April 15, 2009 · 18 comments

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:

backup.example.com ==> rsnapshot server with RAID protected disks.
mysql.example.com ==> Remote mysql server
webserver.example.com ==> Remote Apache webserver

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:

snapshot_root	/.raiddisk/rsnapshots/

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:

  1. ### start db backup for remote server called mysql.example.com ###
  2. # dump all databases at remote server itself
  3. backup_script /usr/bin/ssh root@mysql.example.com "/root/rsnapshot.mysql" unused1/
  4. # Copy all databases from remote server to local server and rsnapshot will move it to /.raiddisk/rsnapshots/$level/mysql/ directory (where, $level can be hourly, monthly etc).
  5. backup_script /usr/bin/scp -r root@mysql.example.com:/tmp/rsnapshot/mysql/ /.raiddisk/rsnapshots/tmp/ mysql/

Here is sample configuration for both web and mysql server:

  1. #
  2. # Backup mysql.example.com
  3. #
  4. backup_script /usr/bin/ssh root@mysql.example.com "/root/rsnapshot.mysql" unused1/
  5. backup_script /usr/bin/scp -r root@mysql.example.com:/tmp/rsnapshot/mysql/ /.raiddisk/rsnapshots/tmp/ mysql/
  6. #
  7. # Backup webserver.example.com
  8. #
  9. backup root@webserver.example.com:/etc/ webserver.example.com/
  10. backup root@webserver.example.com:/var/www/ webserver.example.com/
  11. backup root@webserver.example.com:/root/ webserver.example.com/
  12. backup root@webserver.example.com:/var/spool/ webserver.example.com/
  13.  

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

Shell Script

  1. #!/bin/bash
  2. # A UNIX / Linux shell script to backup mysql server database using rsnapshot utility.
  3. # -------------------------------------------------------------------------
  4. # Copyright (c) 2007 Vivek Gite <vivek@nixcraft.com>
  5. # This script is licensed under GNU GPL version 2.0 or above
  6. # -------------------------------------------------------------------------
  7. # This script is part of nixCraft shell script collection (NSSC)
  8. # Visit http://bash.cyberciti.biz/ for more information.
  9. # -------------------------------------------------------------------------
  10. # Tested under RHEL / Debian / CentOS / FreeBSD oses
  11. # Must be Installed on remote MySQL Server
  12. # -------------------------------------------------------------------------
  13. # Last update: Sun Jul 5 2009 : Added mysql ping support and binary checking
  14. # -------------------------------------------------------------------------
  15. ### SETUP MYSQL LOGIN ###
  16. MUSER='YOUR-MySQL_USERNAME'
  17. MPASS='YOUR-MySQL_PASSWORD'
  18. MHOST="127.0.0.1"
  19.  
  20. ### Set to 1 if you need to see progress while dumping dbs ###
  21. VERBOSE=0
  22.  
  23. ### Set bins path ###
  24. GZIP=/bin/gzip
  25. MYSQL=/usr/bin/mysql
  26. MYSQLDUMP=/usr/bin/mysqldump
  27. RM=/bin/rm
  28. MKDIR=/bin/mkdir
  29. MYSQLADMIN=/usr/bin/mysqladmin
  30. GREP=/bin/grep
  31.  
  32. ### Setup dump directory ###
  33. BAKRSNROOT=/tmp/rsnapshot/mysql
  34.  
  35. #####################################
  36. ### ----[ No Editing below ]------###
  37. #####################################
  38. ### Default time format ###
  39. TIME_FORMAT='%H_%M_%S%P'
  40.  
  41. ### Make a backup ###
  42. backup_mysql_rsnapshot(){
  43. local DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
  44. local db="";
  45. [ ! -d $BAKRSNROOT ] && ${MKDIR} -p $BAKRSNROOT
  46. ${RM} -f $BAKRSNROOT/* >/dev/null 2>&1
  47. [ $VERBOSE -eq 1 ] && echo "*** Dumping MySQL Database ***"
  48. [ $VERBOSE -eq 1 ] && echo -n "Database> "
  49. for db in $DBS
  50. do
  51. local tTime=$(date +"${TIME_FORMAT}")
  52. local FILE="${BAKRSNROOT}/${db}.${tTime}.gz"
  53. [ $VERBOSE -eq 1 ] && echo -n "$db.."
  54. ${MYSQLDUMP} -u ${MUSER} -h ${MHOST} -p${MPASS} $db | ${GZIP} -9 > $FILE
  55. done
  56. [ $VERBOSE -eq 1 ] && echo ""
  57. [ $VERBOSE -eq 1 ] && echo "*** Backup done [ files wrote to $BAKRSNROOT] ***"
  58. }
  59.  
  60. ### Die on demand with message ###
  61. die(){
  62. echo "$@"
  63. exit 999
  64. }
  65.  
  66. ### Make sure bins exists.. else die
  67. verify_bins(){
  68. [ ! -x $GZIP ] && die "File $GZIP does not exists. Make sure correct path is set in $0."
  69. [ ! -x $MYSQL ] && die "File $MYSQL does not exists. Make sure correct path is set in $0."
  70. [ ! -x $MYSQLDUMP ] && die "File $MYSQLDUMP does not exists. Make sure correct path is set in $0."
  71. [ ! -x $RM ] && die "File $RM does not exists. Make sure correct path is set in $0."
  72. [ ! -x $MKDIR ] && die "File $MKDIR does not exists. Make sure correct path is set in $0."
  73. [ ! -x $MYSQLADMIN ] && die "File $MYSQLADMIN does not exists. Make sure correct path is set in $0."
  74. [ ! -x $GREP ] && die "File $GREP does not exists. Make sure correct path is set in $0."
  75. }
  76.  
  77. ### Make sure we can connect to server ... else die
  78. verify_mysql_connection(){
  79. $MYSQLADMIN -u $MUSER -h $MHOST -p$MPASS ping | $GREP 'alive'>/dev/null
  80. [ $? -eq 0 ] || die "Error: Cannot connect to MySQL Server. Make sure username and password are set correctly in $0"
  81. }
  82.  
  83. ### main ####
  84. verify_bins
  85. verify_mysql_connection
  86. backup_mysql_rsnapshot


4000+ howtos and counting! If you enjoyed this article, join 45000+ others and get free email updates!

Click here to subscribe via email.

  • Yousef

    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

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

  • gn

    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.

  • joe

    Nice how-to.

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

  • Aymen

    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.

  • Chris

    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.

  • Chris

    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.

  • Angel Abad

    Hi! Good article, but

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

    link is broken.

  • Anonymous

    It’s there, but for some reason it’s blocking wget’s user agent. :) To fix that, try:

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

  • Killshot

    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.

  • Killshot

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

  • Vivek Gite

    wget issue has been fixed.

  • Vivek Gite

    wget issue has been fixed.

    Thanks!

  • Stephan Jansen

    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.

  • Az

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

  • nixCraft

    @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
    
  • Dom

    @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]

  • Erwin

    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?

Previous Script:

Next Script: