CSC220 Lab 9: Automatic MySql Database Backup

--D. Thiebaut 14:22, 21 November 2010 (UTC)

---

The goal of this lab is to setup a simple cron-based scheme to automatically backup your database every 24 hours. It requires the use of mysqldump which is standard on most linux system

Where should you login?

• The computer you are going to login to will be the one that will be doing the automatic backup. Because the setup will be saved on that server, it is important to pick one that is reliable and won't be restarted in Windows mode by somebody else (which could be the case with any of the hadoop1xx machines). This means that beowulf or grendel should be our candidates. Unfortunately, mysqldump, the command we need to run to backup our databases is not installed on these machines.

• So pick one of the hadoop1XX machines to login to, but remember that these servers are not as reliable because they can be rebooted at any time.

Running MySqlDump from the Command Line

Main Reference

• http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Is it installed?

• Login to your 220a-xx account on one of the hadoop1XX servers
• Open a terminal (no need to ssh anywhere)
• run the following command

  mysqldump

• and verify that the command is installed and is giving you its USAGE.
• Figure out what the full path of the command is using which, and write it down:

  which mysqldump

Backing up your 220a-xx database

• Run the following command

  mysqldump --opt --host=xgridmac.xxxxx.xxx  --user=220a-xx  --password=xxxxxx  220a-xx  > 220a-xx_dump.sql

where you need to replace the xxx parts with the proper information.

• The --opt option is documented in dev.mysql.com, as follows:

Use of --opt is the same as specifying --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. All of the options that --opt stands for also are on by default because --opt is on by default.'

• Verify that you have a new file in your working directory, called 220a-xx_dump.sql. Take a look at it with less. You should recognize its format as that generated by phpmyadmin.

Backing-up plus Compression

• Because the sql dump is in text form, it will be huge if you have a lot of data in your database.
• You should compress the output. Bzip2 is a compression utility that is pretty efficient at squeezing the juice out of sql dumps:

 mysqldump --opt --host=xgridmac.xxxxx.xxx  --user=220a-xx  \
        --password=xxxxxx  220a-xx | bzip2 -c  > 220a-xx_dump.sql.bz2

• Look at the new file in your working directory, and compare the size of the dump.sql file to the dump.sql.bz2 file. On my system I get almost a 1-to-8 ratio in size between the compressed and uncompressed files.

Decompression

• First rename the 220a-xx_dump.sql file to something like 220a-xx_dump.sql.org

• Then simply decompress the bz2 file with bunzip2

 bunzip2   220a-xx_dump.sql.bz2

and you should have two dump.sql files of identical size in your working directory.

Restore Lost Data

• Use PhpMyAdmin to change one of your 220a-xx tables. Either remove a row, or delete the table completely (as long as you have a backup, you are safe to do this!)

• Then restore your database to the contents that is saved in your dump.sql file:

 mysql --host=xgridmac.xxxxx.xx --user=220a-xx  --password=xxxx    220a-xx < 220a-xx_dump.sql

where 220a-xx in bold is the name of the database you want to restore, and not your user name.

• Verify that your database has been restored to its state before your deletion.

Automatic Backup at Regular Intervals

• The trick is to use cron to run a bash script containing a call to the mysqldump command, and to execute the script at regular times.

Main Reference

• http://adminschoice.com/crontab-quick-reference

A Bash Script

• If you don't have a bin directory, create one now:

 mkdir ~/bin
 cd ~/bin

• create a bash script that will backup your database. Call it backupDB.sh

#! /bin/bash
/usr/bin/mysqldump  --opt \
            --host=xgridmac.xxxxx.xxx  \
            --user=220a-xx  \
            --password=xxxxxx  220a-xx | /usr/bin/bzip2 -c  \
            >  /Users/classes/220a/students/220a-xx/220a-xx_dump.sql.bz2

• Make it executable

 chmod +x  backupDB.sh

• Test it

 ./backupDB.sh

• Verify that you have a newer version of the dump file in your home directory.
• If everything is working fine, then move on to the next section.

Cron Job

• First make sure you are running in the bash shell:

 bash

• set emacs as the default editor for editing cron

 export EDITOR='emacs -nw'

• create a new cron entry

  crontab -e
  

• The format of a cron job entry is as follows:

*     *     *   *    *        command to be executed
-     -     -   -    -
|     |     |   |    |
|     |     |   |    +----- day of week (0 - 6) (Sunday=0)
|     |     |   +------- month (1 - 12)
|     |     +--------- day of        month (1 - 31)
|     +----------- hour (0 - 23) 
+------------- min (0 - 59)

• enter the following line, changing the time in the line below (3:30 a.m.) to be a minute or two in the future:

30 3 * * * /Users/classes/220a/students/220a-xx/bin/backupDB.sh

• Save the file and close emacs.

• wait for your cron job to run and verify that you get a new backup file in your directory.

Final Setup

• Once your setup is working you can setup your cron job to run every night or early morning so that you have a 24-hour backup of your data.