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

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:

–quick, -q

Doesn’t buffer the results in memory, rather fetches the
rows one at a time. For large tables, you may need to do this (if they can’t
fit in the available memory)


Adds a DROP TABLE statement before each CREATE TABLE. This
ensures that when restoring, if the tables already exist, they will first be
dropped and not able to interfere with the restoration.


Puts a LOCK TABLES statement before each table dump, and
an UNLOCK TABLE statement, causing the INSERT statements to be processed much
more quickly, as the key buffer is only flushed once per table, after the

–extended-insert, -e

Uses the multiline INSERT syntax. Faster (because the index
buffer is flushed after each INSERT, although this doesn’t help if you
already have –add-locks) and also more compact.

–lock-tables, -l

Locks all tables on the server before starting the dump.
MySQL uses a READ LOCAL lock, which allows concurrent inserts.


The fastest restore, equivalent to all the above options.

–all-databases, -A

Dumps all databases

–databases, –B

Dumps the specified of databases


Dumps a list of tables (this overwrites the –databases

-u username -ppassword -h host

Allows you to specify the username, password or an
alternate host to localhost


Allows you to dump results returned according to a
specific condition.

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
is the correct equivalent.


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

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


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

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

Ian Gilfillan
Ian Gilfillan
Ian Gilfillan lives in Cape Town, South Africa. He is the author of the book 'Mastering MySQL 4', published by Sybex, and has been working with MySQL since 1997. These days he develops mainly in PHP and MySQL, although confesses to starting out with BASIC and COBOL, way back when, and still has a soft spot for Perl. He developed South Africa's first online grocery store, and has developed and taught internet development and other technical courses for various institutions. He has majors in Programming and Information Systems, as well as English and Philosophy. For 5 years he was Lead Developer and IT Manager for Independent Online, South Africa's premier news portal. However, he has now 'retired' from fulltime work, and is hoping that his next book will be more in the style of William Blake and Allen Ginsberg.

Latest Articles