MySQL Backup and Recovery

This article discusses options for setting up a MySQL Backup.

We all understand that our data and database schema are valuable and that they should be backed up on a regular basis. Loss of data can be crippling to your business, so a good backup system is critically important. This article will take a look some of the important things you should look for in a recovery system and hopefully point you in the direction of a setup that will work for you.

So, what are the most important things to consider when choosing how you will perform a MySQL backup of your databases?

Let's assume that we are administering a web application under a moderate or heavier load. We will require a solution that can be automated, and can safely and accurately backup our system as it is in use, handling table locking issues automatically.

In addition we want MySQL backup files stored in the format and location of our choosing with the option to compress them in order to save disk space.

The system must also provide quick and accurate recovery in the event of an emergency. Keep in mind that the solution you choose must also be appropriate for your operating system and the type of storage engine you have chosen for your data.

Using mysqldump

Using mysqldump to provide all the functionality listed above in an automated way requires a small amount of scripting, but allows you to make a tailor-made solution that fits your specific needs. mysqldump works in tandem with the MySQL server, so it takes care of table locking to prevent data corruption.

It can be called from a Perl script or shell script on a Linux or Unix box, or run from a batch file in Windows. Consider the following shell script, named backup.sh, run on a Linux box via crontab:

#/bin/sh mysqldump --opt --user=joe --password=mypass dbname | gzip -9 ›
/home/joe/db_backups/policy_`date +%d-%m-%y.gz`

This script connects to the mysql server with the username joe, who must have administrative privileges for the database “dbname”. By default, mysqldump creates a text file containing the necessary CREATE TABLE statements to recreate the database as it existed at the moment the backup took place.

This file is piped through the gzip utility which compresses it, and this compressed file is placed in a subdirectory of joe's home directory called db_backups, and named dbname_10-10-19.gz, if the date is the 10th of October, 2019.

To automate the execution of this script on a Linux server so that it runs, say, every morning at 3:00am, you could create a crontab entry that looks something like this:

In the event of a problem, a backup file could be used from the command line in the following manner to recreate the database:

[joe@localhost ~]$ gunzip dbname_10-10-07.gz
[joe@localhost ~]$ mysql -u joe -p dbname ‹ dbname_10-10-07›

On the first line the file is decompressed, then the decompressed file is used to create the database.

In Windows the commands could be placed in a batch file named backup.bat, and might look something like this:

@echo off
echo Running dump...
c:\‹\bin\mysqldump -u joe -p mypass --result-file="c:\‹path›\dbname.%DATE:~0,3%.sql" dbname
echo Done!

This file could be executed from a command prompt, or its execution could be automated using the at utility, the DOS equivalent of cron. Your entry would look something like this:

at 03:00 /every:M,T,W,Th,F,S,Su c:\‹path›\mysql-backup.bat

There are many more options available with the mysqldump utility. The example presented here is a hopefully useful but rather general solution for executing backups. For instance, you do not have to create your backup files in the default sql format. They can be created as cvs files, or in a number of other formats.

For more information on this very useful command consult the MySQL documentation.

Mysqlhotcopy

mysqlhotcopy is a utility that ships with MySQL.

It differs from mysqldump in that it produces a binary copy of a database rather than a set of instructions to recreate it. Before getting into the specifics of how to use mysqlhotcopy though, we should note that there are some important caveats.

First of all, it runs on Unix, Linux and NetWare - not on Windows. If your server is running on a Unix installation and you want to use mysqlhotcopy you need to have Perl installed with the DBI module since mysqlhotcopy is a Perl script.

And last but not least, mysqlhotcopy does not work on InnoDB tables. So if your application uses InnoDB tables you need to use another MySQL backup option.

Having said all this, mysqlhotcopy has a few things to recommend it. For one, it is fast. It copies files directly rather than backing them up over the network. It is run when the server is up (in fact the server must be up for mysqlhotcopy to work), and it locks and flushes tables automatically. mysqlhotcopy can be very simple to run, but has also has many options for the more adventurous among you.

A very simple example might look like this from the command line:

[joe@localhost ~]$ mysqlhotcopy -u joe -p mypass dbname /var/spool/backups

This command would place a backup of the database dbname in the directory /var/spool/backups. This command could also be automated by placing it in a shell script and executing it via crontab (See the previous example). To restore a database from a mysqlhotcopy backup file, simply copy the file into the appropriate location in the data directory of the server.

MySQL Administrator

This program is actually a fairly complete graphical administration environment for MySQL, and you might want to take some time to look over the other functionality. For our MySQL backup purposes here though, you can access the MySQL backup section from the left hand menu as shown in the screen shot below.

In the database column is a list of all the databases available to the user. To choose a database to back up, simply click on it. As you can see, each table in the database may be displayed and tables can be selected individually if you do not need all the tables backed up. Each MySQL backup configuration you create must have a unique name and is saved as a project. You can also import projects saved from another computer, or export a project to another computer over a network or to a removable device. Backups can be started manually from the “Start Backup” link at the bottom, or you can click on the “Schedule Backup” tab at the top to schedule a backup using crontab.

Below is a view of the Advanced Options section. As you can see, the program is able to handle backups of both MyISAM and InnoDB tables. When backing up MyISAM files, the “Lock All Tables” option is probably the best choice. It locks all the tables of the database simultaneously. This ensures that the tables are consistent with one another.

The “Normal Backup” option locks each table individually. This method is likely to cause fewer delays to anyone using the system at the time of the backup, but this convenience is generally not worth the chance of having backups in which tables may contain data which is not concurrent.

MySQL Administrator produces MySQL backup files that consist of SQL commands in plain text files. In this way it is similar to the default behavior of mysqldump. At the bottom there are options available to tweak this output.

For example, the “No CREATEs” option removes CREATE DATABASE statements in case you want to copy the database tables to an existing database with a different name.

The “Add DROP TABLE” includes DROP TABLE statements that are necessary to update an existing table from a backup file. The table is first deleted, then recreated.

The “Schedule Backup” section, below, provides a gui interface to automate your backups as cron jobs. As you can see, you can schedule a backup for any day of the week or any combination of days at a given time. Under the “Options” section you can designate a directory where the backups will be stored, and a prefix for the file names if desired. If you need to change or delete a backup schedule, there are buttons for this at the bottom.

To restore a database or table from MySQL Administrator, click on the “Restore Backup” tab in the left menu. A list of available backup files will appear at bottom left. When you click on the name of a file, MySQL Administrator processes it, preparing it for restoration. After clicking on “Restore Backup”, the database is recreated from the file.

So there you have it. With a little work you can effect a customized, automated MySQL backup system with mysqldump or mysqlhotcopy and crontab on Unix, or with mysqldump on Windows.

If you prefer the point and click method, you can download the freely available MySQL Administrator and have a full-featured graphical administration environment.

The important thing is that you do a MySQL backup, and do it before it is too late.

Return to MySQL DBA