Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
    
If your question is answered please indicate so by selecting the most helpful answer. Thanks. –  Josh Dec 31 '10 at 18:58

3 Answers 3

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.

share|improve this answer
    
I will try incorporate your suggestion on my current backup script. –  Rui Gonçalves Feb 4 '11 at 17:32

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.

share|improve this answer
    
+1 for a sensible answer (although somewhat lacking in specifics) note that the master and slave MySQL instances can reside on the same phyiscal host (but must use different data dirs and sockets). –  symcbean Jul 30 '10 at 14:11
    
I need a more immediate solution, and so, I will use for now a more simple approach just to grant a basic full backup. –  Rui Gonçalves Feb 4 '11 at 17:30

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.