Blog Post RSS ?

Blogs » Open Source » Backing Up MySQL
 

Backing Up MySQL

by Blane Warrene

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 <

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 <

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.

Share and Enjoy:
  • Digg
  • del.icio.us
  • Facebook
  • Google Bookmarks
  • Ping.fm
  • Twitthis

Related posts:

  1. Backing Up Using Expect and Rsync
  2. Using WGET
  3. Installing New MySQL Admin
  4. How to Administer a Remote MySQL Database using SSH Tunneling
  5. How to Install MySQL

This post has 22 responses so far

Sponsored Links