Home / server / Questions / 165549
In Process
Rui Gonçalves
Asked: 2010-07-31 02:01:12 +0800 CST

Linux server - backup production database

  • 772

I had created a script to backup a production database. My initial approach was to backup the mysql directory itself (/var/lib/mysql) but it was discouraged by other users.

The script is very simple, once the shell script knowledge is very basic.

#!/bin/bash

# backup file 
backupFile="backup_$(date +%d%m%Y_%H%M).sql"

# create the backup file using mysqldump
/usr/bin/mysqldump -uuser -ppass db > /home/user/db_backups/$backupFile

# copy the backup file to the remote server
rsync -e ssh -varuzP /home/user/db_backups/ user@remoteserver:/home/user/backup/mysql

First of all, I would like to know your opinion about the script. Second, I would like also to know if rsync somehow checks for a possible file corruption during the transmission between the production server and the remote backup server.

Thanks in advance for the help, Best regards!

backup linux rsync database

3 Answers

  1. At least your script is simple and straigthforward.

    One way to check if the rsync was ok is to compare the md5sums.

    LOCALCOPY=`md5sum /home/user/db_backups/$backupFile`
REMOTECOPY=`ssh user@remoteserver md5sum /home/user/backup/mysql/$backupfile`

if [ $LOCALCOPY == $REMOTECOPY ]; then echo "Checksum OK"; else echo "Checksum ERROR. Eep."; fi

    Or something like that, didn't actually test that.

    • 2

  2. A mysqldump with large database should be avoided for performance and integrity reasons. You could setup a replica and do the backup on the slave.

    Searching on internet you will find a lot of articles about mysql backup.

    • 2

  3. The script looks good to me, but you may want to think about setting up a cycle system where old backups are overwritten (thus taking full advantage of rsync). As for corruption checking, I believe rsync will do this for you automatically if you use the --checksum option.

    Unless you plan on watching the output, or piping it to a file, I wouldn't advise using the --verbose option. Also, no need for -a and -r together:

    -a, --archive
    This is equivalent to -rlptgoD.

    See also the StackOverflow question: Ensuring data integrity of mysqldump <-> rsync

    • 1