MySQL backups

One of the most
frequently requested topics for this column is, not surprisingly, backups. Not
surprising because disks still fail, and developers still forget WHERE clauses
and nuke entire tables. I am not ashamed to admit that I did just that a few
days ago. Everyone destroys something every now and again, a timely reminder
that we are human and perhaps an untimely reminder of the importance of
backups. The table I destroyed sat on a friend’s server, was previously backed
up 6 months earlier, and the binary logging was a mess, but luckily it was a
trivial table (a list of Open Source
Software versions I keep for my own interest) that I could recreate easily.
So, with the topic still fresh in my mind, into the detail.

Dumps or raw file copies

There are two main
methods for backing up MySQL databases – an SQL dump and a raw file copy. SQL
dumps have the advantage of being easy to use and flexible, as the SQL
statements can be manipulated as required using standard text tools, so it is
easy to restore only certain records or tables. However, they are larger, and
often much slower to create and restore than raw file copies, which simply copy
the binary files. SQL dumps include mysqldump, SELECT INTO (and LOAD DATA INTO
to restore). Later we look at mysqldump, which is easier to use and more
flexible than SELECT INTO. Raw dumps include mysqlhotcopy, the BACKUP statement
and directly copying the files yourself. Later we look at BACKUP and mysqlhotcopy.
Finally, we look at my preferred method for larger systems, which is to use
replication along with mysqldump, so as not to impact on the live server, but
to have a backup that is flexible and easy to work with.

mysqldump – the tried and trusted

mysqldump is a command
line utility that creates a text file containing the SQL statements needed to
recreate the database. It is a robust method, but slow to run as well as
restore, and also results in large dump files. Here is the basic statement:

mysqldump dbname tablename > filepath_filename.sql

Quick and
easy. Here is what a resulting file could look like, opened in a text editor.

As you
can see, it is a simple text file containing the SQL statements. Which means
restoring is extremely easy – here is how:

%mysql dbname < filepath_filename.sql

As
mentioned before, when working with large tables, mysqldump is time-consuming
to generate, and time-consuming to restore from. By default, each INSERT
statement appears on its own, meaning when restoring, the index buffer is
flushed each time, but there are ways to substantially speed things up. You can
see the full list of mysqldump options on the MySQL site, but
here are a few important ones:

The BACKUP statement

The BACKUP statement is a
fairly simple form of backup that only works with MyISAM tables, and has been
deprecated by MySQL. You should only use it for small, low volume tables, if at
all. BACKUP works as follows:

BACKUP TABLE tablename1 [, tablename2...] TO 'filepath_filename'

This copies the
definition (.frm) and data (.MYD) files. The indexes will be rebuilt when
restoring, which is done as follows:

RESTORE TABLE tablename1 [, tablename2...] FROM 'filepath_filename'

One common trap to look
out for is that slashes in the filepath on Windows need to be escaped, as the
backslash is the MySQL escape character. For example: BACKUP TABLE
tablename1 TO ‘c:db_backups’ will fail, as BACKUP TABLE tablename1 TO
‘c:\db_backups’ is the correct equivalent.

mysqlhotcopy

mysqlhotcopy is a Perl
script that physically copies files. MyISAM tables are stored as files, and can
therefore be backed up and restored simply by copying the data, index and
structure files. This is much quicker than mysqldump, but does not work for InnoDB
tables, which are not stored individually as files. When copying files, you
need to ensure that either the server is offline, or the tables are locked to
ensure a consistent backup. mysqlhotcopy does all of this for you by obtaining
a read lock on all the tables to be backed up, then copying the tables and
releasing the lock. It is a good option for small, live databases, but not for
larger, heavily used databases. mysqlhotcopy works as follows:

%mysqlhotcopy dbname filepath

There are
a number of prerequisites to running mysqlhotcopy:

• It’s a Perl
  script, so you need to be able to execute Perl scripts on your server.

• It requires
  the following Perl classes: Getopt::Long, Data::Dumper, File::Basename, File::Path,
  DBI, Sys::Hostname

• You need write
  access to the directory to which you are copying.

• You need
  SELECT and RELOAD privileges

To
restore from mysqlhotcopy, simply copy the files back to the data directory:

$ cp filepath_filenames datadir_filepath

A full list of mysqlhotcopy
options are available on the MySQL site.
One option, —-noindices, allows a smaller and quicker backup by excluding
indexes. Once you restore, you will need to repair the indexes by either
running myisamchk -rq, or REPAIR TABLE from within MySQL.

InnoDB Hot Backup

InnoDB Hot Backup is a
commercial tool for making online backups to InnoDB tables (as well as MyISAM
tables), See the InnoDB site
for details. To run it you pass two arguments – the MySQL config file, and the InnoDB
Hot Backup config file, specifying the destination of the backups:

% ibbackup path_to_mysql_configfile path_to_ibbackup_config_file

To restore, simply use
the restore option (MySQL needs to be offline while you restore):

% ibbackup --restore path_to_ibbackup_config_file

Replication

On its own, replication
provides a basic level of protection against hardware failure. It does not
protect against user stupidity though, as a reckless DELETE statement will
still be replicated onto the second server. However, it does allow you to run
backups without affecting a live server. You can then easily take the secondary
server offline to perform a raw copy, or run a CPU intensive mysqldump without impacting
on users. It is important to backup all the replication files, such as master.info
and the relay logs and index. My
earlier column delves into the intricacies of replication.

Restoring with the binary logs

Once you have restored
the backups (which can perhaps be 24 hours out of date), there is still more
you can do. If your binary logs are still intact (and I recommend turning
binary logging on and keeping backups of these too), you can use them to
restore to just before your data loss. I have already written an article on
that topic, which you can view
here.

While I may wish you would
never get to test your backup regime, some things are up there with death and
taxes. So be prepared, choose an appropriate form of backup for your needs, and
make sure you test it before you get any nasty surprises (such as finding out
it took 6 hours to restore a critical server as I once experienced). Good luck!

»


See All Articles by Columnist Ian Gilfillan