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:
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. 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.
#!/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 <
#!/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 <
Related posts:
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!
April 30th, 2004 at 12:35 pm
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.
April 30th, 2004 at 3:56 pm
Rsync-ing /var/lib/mysql/* would work provided MySQL wasn’t running at the time.
You need the tables locked when you copy them.
April 30th, 2004 at 4:11 pm
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.
April 30th, 2004 at 4:19 pm
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.
April 30th, 2004 at 10:09 pm
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
May 2nd, 2004 at 12:37 am
It *does* backup all the databases. That’s what the “show databases” query and the loop are for.
May 4th, 2004 at 9:46 am
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>&-
Édson
May 5th, 2004 at 8:50 am
congratulations!
This script functioning correct :)
but i don´t make the uncompress the archives .gz
:(
what i can make?
thansk´s
RockWell
May 5th, 2004 at 2:16 pm
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
May 9th, 2004 at 6:56 am
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).
May 13th, 2004 at 12:54 pm
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.
May 30th, 2004 at 3:20 am
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
June 15th, 2004 at 3:48 am
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.
November 30th, 2004 at 11:55 am
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.
December 19th, 2004 at 9:17 pm
What do you save the script as? Other words what is the execution method? Perl, PHP, etc.
July 14th, 2005 at 2:22 pm
A better way to do this so usernames and passwords aren’t in files, via ssh:
http://secpriv.com/viewarticle.php?id=49
November 2nd, 2005 at 2:59 pm
Ian, your soluntion is fine but you can use ’scp’ or ‘rsync’ for better results instead of ’sftp’.
November 6th, 2005 at 7:38 pm
Do we really need -pogt options when using rsync? I see no difference without them.
March 31st, 2006 at 5:42 am
Guys,
I found this software. http://www.cpsitesaver.com
It backs up MYSQL databases.
Maybe that will help.
September 24th, 2006 at 3:04 am
Could you please put the line breaks into the script? It would make it much more readable
December 9th, 2007 at 8:45 am
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! :-)
April 23rd, 2009 at 7:43 am