Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 19, 2004

MySQL backups

By Ian Gilfillan

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:

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

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

-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 'c:\\db_backups' 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

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


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

MySQL Archives

Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM