How to backup your MySQL tables and data every night using a bash script and cron.

© copyright 01.Apr.2005 by Paul Bradley filed under MySQL


Please Note: I now offer an off site MySQL database backup service, if you need a reliable a backup solution for your on-line database, please take a look.

Summary:
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 :-

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.

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

 


If you have found this article helpful or useful please consider linking to it, emailing it to friends, or share it with others using social sites like del.icio.us, Stumble Upon or Twitter.

Paul Bradley

About the Author
Paul Bradley is a VB.NET software developer living and working in Cumbria. He has over 20 years programming experience. He also produces e-learning videos at Linux by Example

Other Popular Articles

Categories & Topics

Home · Apache · JavaScript · Perl · PDF · PHP · MySQL · MSSQL · TAR · Ubuntu Linux · Video · Visual Basic

Browse the complete article history, and if you like what you see; consider subscribing to the rss feed.