Creating one script to backup mysql automatically

This tutorial will show you how to write a simple bash shell script which will extract your database schema, compress the data and email you the backup. Utilising cron, this script can be configured to run in the early hours of the morning when your web server is least active.
After completing your database enabled web site, you need an automated method for backing up all that valuable data. Below is a bash shell script which can be used to backup all your clients databases using a nightly cron job.

Bash Shell Script (mysqlbackup)

#!/bin/sh
mysqldump -uroot -ppwd --opt db1 > /sqldata/db1.sql
mysqldump -uroot -ppwd --opt db2 > /sqldata/db2.sql

cd /sqldata/
tar -zcvf sqldata.tgz *.sql
cd /scripts/
perl emailsql.pl

A bash script is a text file containing commands that can be interpreted by the bash shell. Above is a cut down version of the original script which I keep in a directory called /myscripts/. This is important for when we look at adding the script to the cron tab.
The first line of the script tells the operating system (Unix) where to find the bash interpreter, you may need to change this line to work on your systems. The second and third lines call the MySQL utility mysqldump which is used to export the data, the output from this command is then piped into a text file.
For example the first mysqldump statement has 4 parameters passed to it :-

• -u = your MySQL username. (substitute root with your username)
• -p = your MySQL password. (substitute pwd with your password)
• --opt adds the most common and useful command line options, resulting in the quickest possible export. This option automatically includes --add-drop-table, --add-locks, --extended-insert, --quick and --use-locks.
• the database name to extract. (substitute db1 with your database name)
• the > /sqldata/db1.sql redirects all the output to a file called db1.sql in a directory /sqldata/. You can create the file in any directory you have rights to, however for consistency I would suggest naming the resulting .sql file the same as the database name.

You simply repeat this process for each database you want to backup. The following line changes directory to the /sqldata/ directory and performs a tar compression adding all the .sql files into one archive file called sqldata.tgz. After changing back to the scripts directory I finally run a Perl script (emailsql.pl) which attaches the sqldata.tgz archive to an email and forwards it to two offsite email accounts. Alternatively you could ftp the sqldata.tgz to an offsite machine.
After creating the script, you need to make it executable by CHMODing the file permissions to 700. At this point you should be able to test the script by entering /myscripts/mysqlbackup from the shell prompt.

The emailsql.pl script.

The example Perl script below shows how you can attach the archive to an email, and send it to your email inbox. This Perl script requires the MIME::Lite Module which you may need install on your server. (How to install Perl Modules).

#!/usr/bin/perl -w
use MIME::Lite;

$msg = MIME::Lite->new(
  From    => mysqlbackup@yoursite.co.uk,
  To      => you@yoursite.co.uk,
  Subject => sqldata.tgz MySQL backup!,
  Type    => text/plain,
  Data    => "Here are the MySQL database backups.");

$msg->attach(Type=>application/x-tar,
             Path =>"/sqldata/sqldata.tgz",
             Filename =>"sqldata.tgz");

$msg->send;

Adding the Script to Cron

Cron is a scheduling tool for Unix, it allows you to specify when a program or script should be run. To edit your current cron table, enter crontab -e from the system prompt. This will load your current cron table into your default text editor, when you save and exit your editor the crontab file will be loaded and ready for use.

0 2 * * * /myscripts/mysqlbackup
0 5 * * 0 /myscripts/reindex

The above example shows my current crontab. The file has two entries, one for each script I wish to run. The first entry tells cron to run the mysqlbackup script every morning at 2am. The second entry runs my search engine indexer every Sunday morning at 5am.
There are five fields for setting the date and time that a program should be run. The five time settings are in the following order.

• Minutes - in the range of 0 - 59
• Hour - in the range of 0 - 23
• Day of month - in the range 1 - 31
• Month - in the range 1 -12
• Day of week - in the range 0 - 6 (0 = Sunday)

Any field with a * means run every possible match, so for example a * in the day of month field will run the script every single day of the month at the specified time.

Additional References

• BASH - GNU Project
• BASH reference manual
• TAR - GNU Project
• MIME::Lite Module