October 19, 2004
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 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:
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!