Backup MySQL Databases by Cron

jhadley · #1 04-01-2007, 01:50 PM

This tutorial shows you how to backup MySQL databases by cron and have the backups sent to you by email, or have them uploaded by FTP. It is based on a script I found at another website though I can confirm it is fully working.

Change the commented variables in the following file and save it as backup.sh:

Code:

#!/bin/sh

# This script will backup one or more mySQL databases
# and then optionally email them and/or FTP them

# This script will create a different backup file for each database by day of the week
# i.e. 1-dbname1.sql.gz for database=dbname1 on Monday (day=1)
# This is a trick so that you never have more than 7 days worth of backups on your FTP server.
# as the weeks rotate, the files from the same day of the prev week are overwritten.
#/bin/sh /home/user/directory/scriptname.sh > /dev/null
############################################################
#===> site-specific variables - customize for your site

# List all of the MySQL databases that you want to backup in here, 
# each seperated by a space
# If not run by root, only one db per script instance
databases="mydbname"

# Directory where you want the backup files to be placed
backupdir=/home/mydomain/backups

# MySQL dump command, use the full path name here
mysqldumpcmd=/usr/bin/mysqldump

# MySQL Username and password
userpassword=" --user=myuser --password=mypasswd"

# MySQL dump options
dumpoptions=" --quick --add-drop-table --add-locks --extended-insert --lock-tables"

# Unix Commands
gzip=/bin/gzip
uuencode=/usr/bin/uuencode
mail=/bin/mail

# Send Backup?  Would you like the backup emailed to you?
# Set to "y" if you do
sendbackup="n"
subject="mySQL Backup"
mailto="me@mydomain.com"

#===> site-specific variables for FTP
ftpbackup="y"
ftpserver="myftpserver.com"
ftpuser="myftpuser"
ftppasswd="myftppasswd"
# If you are keeping the backups in a subdir to your FTP root
ftpdir="forums"

#===> END site-specific variables - customize for your site
############################################################

# Get the Day of the Week (0-6)
# This allows to save one backup for each day of the week
# Just alter the date command if you want to use a timestamp
DOW=`date +%w`

# Create our backup directory if not already there
mkdir -p ${backupdir}
if [ ! -d ${backupdir} ] 
then
   echo "Not a directory: ${backupdir}"
   exit 1
fi

# Dump all of our databases
echo "Dumping MySQL Databases"
for database in $databases
do
   $mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${DOW}-${database}.sql
done

# Compress all of our backup files
echo "Compressing Dump Files"
for database in $databases
do
   rm -f ${backupdir}/${DOW}-${database}.sql.gz
   $gzip ${backupdir}/${DOW}-${database}.sql
done

# Send the backups via email
if [ $sendbackup = "y" ]
then
   for database in $databases
   do
      $uuencode ${backupdir}/${DOW}-${database}.sql.gz > ${backupdir}/${database}.sql.gz.uu
      $mail -s "$subject : $database" $mailto < ${backupdir}/${DOW}-${database}.sql.gz.uu
   done
fi

# FTP it to the off-site server
echo "FTP file to $ftpserver FTP server"
if [ $ftpbackup = "y" ]
then
   for database in $databases
   do
      echo "==> ${backupdir}/${DOW}-${database}.sql.gz"
ftp -n $ftpserver <<EOF
user $ftpuser $ftppasswd 
bin
prompt
cd $ftpdir
lcd ${backupdir}
put ${DOW}-${database}.sql.gz
quit
EOF
   done
fi

# And we're done
ls -l ${backupdir}
echo "Dump Complete!"
exit

Upload backup.sh to your server, to any directory you want. A directory which is not web-accessible will stop your login information being seen by just anyone

.

You should chmod the file to 777:

Code:

chmod 777 backup.sh

If you uploaded this file from a Windows machine you will need to convert the file to Unix format. You should run the following command by SSH in the appropriate directory:

Code:

dos2unix backup.sh

If you don't have dos2unix installed, you can install it using yum if you have that:

Code:

yum install dos2unix

If you don't have yum, get it here.

You may want to test the script at this point to make sure it's doing what you want it to. Change to the appropriate directory and run this command:

Code:

./backup.sh

Once you're happy with it, enter it into the crontab to run daily (or whenever you want). Cron jobs vary a lot depending on the configuration of your system, so check Google for how to do it on your system. The command you will need to run by cron is:

Code: