Linux server - backup production database

Question

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!

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

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

Answer 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