Documentation Home
MySQL Backup and Recovery
Related Documentation Download this Excerpt
PDF (US Ltr) - 378.5Kb
PDF (A4) - 377.5Kb
EPUB - 120.1Kb
HTML Download (TGZ) - 78.8Kb
HTML Download (Zip) - 85.7Kb

MySQL Backup and Recovery  /  Backup and Recovery  /  Database Backup Methods

1.2 Database Backup Methods

This section summarizes some general methods for making backups.

Making a Hot Backup with MySQL Enterprise Backup

Customers of MySQL Enterprise Edition can use the MySQL Enterprise Backup product to do physical backups of entire instances or selected databases, tables, or both. This product includes features for incremental and compressed backups. Backing up the physical database files makes restore much faster than logical techniques such as the mysqldump command. InnoDB tables are copied using a hot backup mechanism. (Ideally, the InnoDB tables should represent a substantial majority of the data.) Tables from other storage engines are copied using a warm backup mechanism. For an overview of the MySQL Enterprise Backup product, see MySQL Enterprise Backup Overview.

Making Backups with mysqldump or mysqlhotcopy

The mysqldump program and the mysqlhotcopy script can make backups. mysqldump is more general because it can back up all kinds of tables. mysqlhotcopy works only with some storage engines. (See Section 1.4, “Using mysqldump for Backups”, and mysqlhotcopy — A Database Backup Program.)

For InnoDB tables, it is possible to perform an online backup that takes no locks on tables using the --single-transaction option to mysqldump. See Section 1.3.1, “Establishing a Backup Policy”.

Making Backups by Copying Table Files

For storage engines that represent each table using its own files, tables can be backed up by copying those files. For example, MyISAM tables are stored as files, so it is easy to do a backup by copying files (*.frm, *.MYD, and *.MYI files). To get a consistent backup, stop the server or lock and flush the relevant tables:


You need only a read lock; this enables other clients to continue to query the tables while you are making a copy of the files in the database directory. The flush is needed to ensure that the all active index pages are written to disk before you start the backup. See LOCK TABLES and UNLOCK TABLES Syntax, and FLUSH Syntax.

You can also create a binary backup simply by copying all table files, as long as the server isn't updating anything. The mysqlhotcopy script uses this method. (But note that table file copying methods do not work if your database contains InnoDB tables. mysqlhotcopy does not work for InnoDB tables because InnoDB does not necessarily store table contents in database directories. Also, even if the server is not actively updating data, InnoDB may still have modified data cached in memory and not flushed to disk.)

Making Delimited-Text File Backups

To create a text file containing a table's data, you can use SELECT * INTO OUTFILE 'file_name' FROM tbl_name. The file is created on the MySQL server host, not the client host. For this statement, the output file cannot already exist because permitting files to be overwritten constitutes a security risk. See SELECT Syntax. This method works for any kind of data file, but saves only table data, not the table structure.

Another way to create text data files (along with files containing CREATE TABLE statements for the backed up tables) is to use mysqldump with the --tab option. See Section 1.4.3, “Dumping Data in Delimited-Text Format with mysqldump”.

To reload a delimited-text data file, use LOAD DATA INFILE or mysqlimport.

Making Incremental Backups by Enabling the Binary Log

MySQL supports incremental backups: You must start the server with the --log-bin option to enable binary logging; see The Binary Log. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you performed a backup. At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained in Section 1.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS, mysqldump --flush-logs, or mysqlhotcopy --flushlog. See mysqldump — A Database Backup Program, and mysqlhotcopy — A Database Backup Program.

Making Backups Using Replication Slaves

If you have performance problems with your master server while making backups, one strategy that can help is to set up replication and perform backups on the slave rather than on the master. See Chapter 2, Using Replication for Backups.

If you are backing up a slave replication server, you should back up its and files when you back up the slave's databases, regardless of the backup method you choose. These information files are always needed to resume replication after you restore the slave's data. If your slave is replicating LOAD DATA INFILE statements, you should also back up any SQL_LOAD-* files that exist in the directory that the slave uses for this purpose. The slave needs these files to resume replication of any interrupted LOAD DATA INFILE operations. The location of this directory is the value of the --slave-load-tmpdir option. If the server was not started with that option, the directory location is the value of the tmpdir system variable.

Recovering Corrupt Tables

If you have to restore MyISAM tables that have become corrupt, try to recover them using REPAIR TABLE or myisamchk -r first. That should work in 99.9% of all cases. If myisamchk fails, see Section 1.6, “MyISAM Table Maintenance and Crash Recovery”.

Making Backups Using a File System Snapshot

If you are using a Veritas file system, you can make a backup like this:

  1. From a client program, execute FLUSH TABLES WITH READ LOCK.

  2. From another shell, execute mount vxfs snapshot.

  3. From the first client, execute UNLOCK TABLES.

  4. Copy files from the snapshot.

  5. Unmount the snapshot.

Similar snapshot capabilities may be available in other file systems, such as LVM or ZFS.

Download this Excerpt
PDF (US Ltr) - 378.5Kb
PDF (A4) - 377.5Kb
EPUB - 120.1Kb
HTML Download (TGZ) - 78.8Kb
HTML Download (Zip) - 85.7Kb
User Comments
  Posted by jeroen playak on November 29, 2009
I wrote a PHP script that creates gzipped backups (one per table), so you'll always have all tables backed up, without having to backup tables that haven't changed. Details and download: . Especially handy if you're uploading the backup files to a service like Amazon S3 with duplicity or similar.

  Posted by Vlatko Šurlan on July 4, 2010
There is an interesting script sample here:
showing a way to dump mysql databases directly into gzip and then into ssh connection, thus creating a tarball archive that never resided on the server hard drive. This can be a handy way to ensure that backup does not fill up the server hard drive and can help you backup your server even when it is near 100% full.
  Posted by Lloyd Standish on July 17, 2010
FLUSH TABLES WITH READ LOCK is extremely convenient for making a full backup of all MyISAM databases. However, the database locks are released as soon as the mysql session ends, which is why this backup-methods page suggests that FLUSH TABLES WITH READ LOCK be executed in one shell, while the backup command is run from a separate shell.

Unfortunately, this won't work when the backup is executed from a shell script, such as during scheduled backups via cron.

To hold the lock on all databases while running an automated backup script, the mysql SYSTEM command can be used to execute a backup script before the mysql session ends, like this:

echo "FLUSH TABLES WITH READ LOCK; SYSTEM /path/to/helper/; UNLOCK TABLES;" | mysql -u <user> -p <password>

The file /path/to/helper/ would have the actual backup command. For example, I have:

rsync -vza --delete /var/lib/mysql | tee /root/crontasks/mysqlbackup.log

  Posted by Dan W on January 19, 2011
A modification of Lloyd's example is to use a snapshot filesystem so that the database doesn't have to be offline for an extended period of time. This way the database is offline for a few seconds at most. ie:

echo "FLUSH TABLES WITH READ LOCK; SYSTEM /path/to/helper/; UNLOCK TABLES;" | mysql -u <user> -p <password>
rsync -arv /snapshot/var/lib/mysql /path/to/backupstorage/

The file /path/to/helper/ would have the commands to create the snapshot partition:

/usr/sbin/lvcreate --size 28G --snapshot --name snap /dev/VolGroup00/LogVol00
mount /dev/VolGroup00/snap /snapshot

The file /path/to/helper/ would have the commands to remove the snapshot partition:

umount /snapshot
/usr/sbin/lvremove /dev/VolGroup00/snap

Sign Up Login You must be logged in to post a comment.