Backing Up MySQL

There are several ways to backup MySQL data, however, automation certainly makes the process easier. Insuring your backed up data is stored remotely further supports your business continuity in the event of a need to restore data onto new systems based on hardware failure, etc.

Having spent some time murking through my network (not needing to reinvent the wheel to research an efficient backup procedure), I stumbled onto some backup scripts from PJ Doland.

PJ (Patrick) is a web designer in the Washington DC area with former ties to the CATO Institute web site and was willing to share two scripts he is using. I liked these scripts as they are simple, and include the functionality to automate dropping the backups on a remote server for business continuity.

The first is for use as a standard MySQL backup and is found below:


#!/bin/bash

#####################################
### MySQL Configuration Variables ###
#####################################

# MySQL Hostname
DBHOST='localhost'

# MySQL Username
DBUSER='root'

# MySQL Password
DBPASSWD='password'

#####################################
### FTP Configuration Variables #####
#####################################

# FTP Hostname
FTPHOST='www.example.com'

# FTP Username
FTPUSER='username'

# FTP Password
FTPPASSWD='password'

# Local Directory for Dump Files
LOCALDIR=/path/to/local/directory/

# Remote Directory for Offsite Backup
REMOTEDIR=/path/to/remote/directory/

# Prefix for offsite .tar file backup
TARPREFIX=db1

#####################################
### Edit Below If Necessary #########
#####################################

cd $LOCALDIR
SUFFIX=`eval date +%y%m%d`

DBS=`mysql -u$DBUSER -p$DBPASSWD -h$DBHOST -e"show databases"`

for DATABASE in $DBS
do
if [ $DATABASE != "Database" ]; then
FILENAME=$SUFFIX-$DATABASE.gz
mysqldump -u$DBUSER -p$DBPASSWD -h$DBHOST $DATABASE | gzip --best > $LOCALDIR$FILENAME
fi
done

chmod 400 $LOCALDIR*.gz

tar -cf $TARPREFIX-$SUFFIX.tar $SUFFIX-*.gz

ftp -n $FTPHOST < quote USER $FTPUSER
quote PASS $FTPPASSWD
cd $REMOTEDIR
put $TARPREFIX-$SUFFIX.tar
quit
END_SCRIPT

rm -f $TARPREFIX-$SUFFIX.tar

exit 0

It is a straightforward script, uses mysqldump and tar/gzips the files prior to using ftp to move the data to a remote server. This script and the one below can easily be modified to use scp if you prefer running the transfer over ssh.

The second script is updated to use mysqlhotcopy and is specifically for use with ISAM/MYISAM tables only. This script below will NOT work with InnoDB tables.


#!/bin/bash

### Configuration Variables

DBHOST='localhost'
DBUSER='root'
DBPASSWD='password'
FTPHOST='ftp.example.com'
FTPUSER='username'
FTPPASSWD='password'
LOCALDIR=/path/to/local/
REMOTEDIR=/path/to/local/
TARPREFIX=db1

### Do not edit anything below this line

cd $LOCALDIR
SUFFIX=`eval date +%y%m%d`

DBS=`mysql -u$DBUSER -p$DBPASSWD -h$DBHOST -e"show databases"`

for DATABASE in $DBS
do
if [ $DATABASE != "Database" ]; then
FILENAME=$SUFFIX-$DATABASE.tar.gz
mysqlhotcopy -u $DBUSER -p $DBPASSWD $DATABASE $LOCALDIR
tar -czf $LOCALDIR$FILENAME $LOCALDIR$DATABASE
rm -rf $LOCALDIR$DATABASE
rm -rf $LOCALDIR$DATABASE-replicate
fi
done

chmod 400 $LOCALDIR*.tar.gz

tar -cf $TARPREFIX-$SUFFIX.tar $SUFFIX-*.tar.gz

ftp -n $FTPHOST < quote USER $FTPUSER
quote PASS $FTPPASSWD
cd $REMOTEDIR
put $TARPREFIX-$SUFFIX.tar
quit
END_SCRIPT

rm -f $TARPREFIX-$SUFFIX.tar

exit 0

It is recommended to use 700 permissions on these shell scripts to limit them to root user as they do contain substantial information on user name, password, and path to multiple servers once they are updated with your information.

They can then be added to cron to run on a schedule of your preference.

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Dave Whitinger

    Why not just use rsync and copy over the /var/lib/mysql/* files? I used the mysqldump method until my database grew over 1G in size, at which point it wasn’t feasible anymore. Check out rsync over ssh for backups – works great!

  • http://www.practicalapplications.net bwarrene

    Good point on large tables. One note to add – if you are using a MySQL version prior to 4.1, mysqldump by default passes all data to memory when preparing the backup. If you use –opt it will prevent this – and perhaps allow more efficient backup of large databases without crunching your server’s ram.

    If you really want to jam your ram and have version 4.1 or better – you need to use –skip-opt as it is on by default now to improve large database backups.

  • PJ Doland

    Rsync-ing /var/lib/mysql/* would work provided MySQL wasn’t running at the time.

    You need the tables locked when you copy them.

  • PJ Doland

    You don’t want to just use rsync because you need to lock the tables.

    If you’re stopping mysql every time you backup that shouldn’t be a problem though.

  • Richard Thomas

    You can not reliably rsync a live database for so many reasons.
    1. If the database crashes your copy could be incomplete/corrupt.
    2. Your backup doesn’t take into account database or query failures. If you for example delete the wrong table your “backup” is useless.

  • http://www.revmedia.com dhecker

    I’ve been looking for something like this becuase my server uses the cpanel backup system, which drops all the html, etc. on a remote server but is a bit sloppy with the mysql output.

    My question is this: that script is perfect for a single database, but is there a way to have it go through ALL the databases on a particular server and dump them on a remote machine?? That would be a big winner for me!

    thanks, Dave

  • PJ Doland

    It *does* backup all the databases. That’s what the “show databases” query and the loop are for.

  • Anonymous

    You can use tar with ssh , option –rsh-command=CMD in tar and with public key autentication for ssh.
    example:
    USER=me
    HOST=remote-host
    FILENAME=backup.$HOST.$(date +%d%m%Y).bkp

    tar cjf $user@$HOST:$FILENAME.tar.bz2 –rsh-command=/usr/bin/ssh -T include-file.txt 2>&-

  • RockWell

    congratulations!
    This script functioning correct :)
    but i don

  • michael301080

    Wouldn’t it be possible to do an export of mysql structure and data to an *.sql file (as in phpmyadmin)? Could you make a *.php script for this? and only store the diffs for example? by editing the diffs you could control what will be restored after a crash. Does anyone have a comment on this? TIA

  • PJ Doland

    SQL dumps from the first script listed above compress so well with GZIP that storing diffs would be a pain (with disk space so cheap these days).

  • slamdunkinpool

    Best is to rsync just the mysql binary log files (–log-bin) instead of rsync’ing MySql tables directly.
    Write a script to keep track of logfile name and position.
    Its sad that mysql.com doesn’t have much information or examples showing how to use binary logs for backups.

  • slamdunkinpool

    At the time when I posted above comment I was just planning and thinking of my strategy as to how i am going to do backup with some flexibility to fullfill my clients needs.
    I have implemented and tested the above strategy and it works well and fast too.
    slamdunkinpool xxxreplace x with @xxxx yahoo xxdot herexx com

  • Chris L. Franklin -Pegasys.cc-

    I use rsync + ssh + crond to sync up my 13 mysql servers. Heres what I ended up doing

    rsync -pogtr /var/lib/mysql/System_Users $Server:/var/lib/mysql/ -e /usr/bin/ssh

    ssh $Server “mysqladmin flush-tables”

    You need to send the flush-tables or the new data will never get used, due to mysql query caching.

  • slamdunkinpool

    If it’s ok to stop mysql server during backup and then rsync’ng table would be totally safe.

    flush-tables
    stop mysql
    rsync
    start mysql

    Though this is not suitable for databases that are required to be up round the clock.

    Also if you want to sync only particular tables then use “mysqladmin flush-table [tableName, ..]” command.

  • http://www.rpgdesigns.net smorelli

    What do you save the script as? Other words what is the execution method? Perl, PHP, etc.

  • Ian

    A better way to do this so usernames and passwords aren’t in files, via ssh:
    http://secpriv.com/viewarticle.php?id=49

  • slamdunkinpool

    Ian, your soluntion is fine but you can use ‘scp’ or ‘rsync’ for better results instead of ‘sftp’.

  • KaRa

    Do we really need -pogt options when using rsync? I see no difference without them.

  • bangis

    Guys,

    I found this software. http://www.cpsitesaver.com

    It backs up MYSQL databases.

    Maybe that will help.

  • Joe Annoyed

    Could you please put the line breaks into the script? It would make it much more readable

  • Oliver Treend

    Just found this script. It looks really helpful.
    As Joe said, would it be possible to put the line-breaks in the code? I can’t understand it very well as it is.. :-/
    Thanks for sharing this with us! :-)