MySQL Database backup script

This script I have here has saved me a number of times from embarrassment. Many a times I happened to have “accidently” dropped an entire database (in phpmyadmin) while wanting to drop only a few tables. Apparently the big-fat “drop” button at the top navigation is related to the database than the tables you have selected in the listing.

The script has the ability to dump your database tables and upload them to an ftp server or email the compressed sql file to your email address. I haven’t written this entirely on my own but with help from various people on discussion forums. Post back your comments and improvements if any.



#!/bin/sh

# Get the Day of the Week (Sun-Mon-Tue-Wed-Thu-Fri-Sat)
# This allows to save one backup for each day of the week
DOW=`date +%a`

# List all of the MySQL databases that you want to backup in here,
# each seperated by a space
databases="mphpugt_mambo mphpugt_phpcollab"

# Directory where you want the backup files to be placed
backupDir=/home/mphpugt/databaseBackup

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

# MySQL Username and password
userPassword=" --user=[replace with database username] --password=[replace with password]"

# MySQL dump options
dumpOptions=" --opt"

# 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
emailBackup="y"
subject="Database Backup"
maitTo="admin@techsatcomputers.com"


# Site-specific variables for FTP
ftpBackup="y"
ftpServer=techsatcomputers.com
ftpUser=backup@techsatcomputers.com
ftpPassword=[replace with password]
ftpDir=/


####################### End of Configuration #############################


# 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 db in $databases
do
  $mysqlDumpCMD $userPassword $dumpOptions $db > ${backupDir}/${DOW}_${db}.sql
done

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


# Send the backups via email
if [ $emailBackup = "y" ]
then
echo "Emailing Files to " $maitTo
   for db in $databases
   do
      $uuencode ${backupDir}/${DOW}_${db}.sql.gz ${DOW}_${db}.sql.gz > ${backupDir}/${DOW}_${db}.sql.gz.uu
      size=`wc -c < ${backupDir}/${DOW}_${db}.sql.gz`
      echo "Sending file: ${DOW}_${db}.sql.gz .....Total $size Bytes"
      $mail -s "$subject: ${DOW}_${db} (${size} Bytes)" $maitTo < ${backupDir}/${DOW}_${db}.sql.gz.uu
     rm -f ${backupDir}/${DOW}_${db}.sql.gz.uu
   done
fi

# FTP it to the off-site server
if [ $ftpBackup = "y" ]
then
echo "FTP-ing file to $ftpServer FTP server"
for db in $databases
do
echo "==> ${DOW}_${db}.sql.gz"
ftp -in <<EOF
open $ftpServer
user $ftpUser $ftpPassword
bin
hash
prompt
cd $ftpDir
lcd ${backupDir}
put ${DOW}_${db}.sql.gz
quit
EOF
done
fi

# And we're done
ls -l ${backupDir}
echo "Backup Complete!"
exit 
This entry was posted in MySql. Bookmark the permalink.

2 Responses to MySQL Database backup script

  1. sharynproctor says:

    Hi Jeffrey,
    What do I call the script?
    backup.sh

    or what?
    Where do I upload it?
    public_html or bellow?

    Thanks Heaps
    Shaz
    Sorry if obvious, I am a learner :)

    • Jeffery says:

      Hi sharynproctor,

      Yes you can call the script backup.sh and for security reasons put it below public_html. The script needs to be executed on the “shell” and not via a browser. If you have access to your hosting provider shell, then you can run it via a scheduler (eg cron). If you don’t have access to the shell ask your hosting provider if you can be given access to cron. Most hosting providers have some sort of a Control Panel do upload/add schedulers. Let me know how you go.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>