32.3. Making Binary Backups

This section describes methods that you can use to make binary-format database or table backups.

32.3.1. Making Binary MyISAM Backups

To make a binary backup of a MyISAM table, copy the .frm, .MYD, and .MYI files that MySQL uses to represent the table. When you do this, the table must not be in use by other programs (including the server) during the copy operation. If you stop the server while copying the table, there will be no problem of server interaction. If you leave the server running, use an appropriate locking protocol to prevent server access to the table. For example, to copy the Country table in the world database, lock the table and flush any pending changes like this:

 mysql> USE world; mysql> LOCK TABLES Country READ; mysql> FLUSH TABLES Country; 

Then (with the table still locked) use your operating system's file copy command to copy the table files. After the copy operation completes, release the lock on the table:

 mysql> UNLOCK TABLES; 

The preceding strategy works on Unix. On Windows, file-locking behavior is such that you might not be able to copy table files for tables that are locked by the server. In that case, you must stop the server before copying table files.

Another way to make binary MyISAM backups is to use mysqlhotcopy, which does the locking and flushing for you. See Section 32.3.3.1, "mysqlhotcopy."

To recover a MyISAM table from a binary backup, stop the server, copy the backup table files into the appropriate database directory, and restart the server. If you want to use the files with another MySQL server, the requirements that must be satisfied are described in Section 32.3.4, "Conditions for Binary Portability."

32.3.2. Making Binary InnoDB Backups

A binary backup operation that makes a complete InnoDB backup (a backup of all tables in the InnoDB tablespace) is based on making exact copies of all files that InnoDB uses to manage the tablespace.

To make an InnoDB binary backup, use the following procedure:

1.	Stop the server for the duration of the copy operation. The tablespace must not be in use when copying the tablespace files.
2.	Make sure that the server shut down without error. Binary InnoDB backups require a clean shutdown to be certain that the server has completed any pending transactions.
3.	Make a copy of each of the following components: The .frm file for each InnoDB table. The tablespace files. This includes the files for the shared tablespace. It also includes the .ibd files if you have configured InnoDB to use per-table tablespaces. The InnoDB log files. Any InnoDB configuration options, such as those stored in option files. The configuration options are required in case you need to restore the backup from scratch. In that case, you'll need to know how the tablespace and log files were created originally.
4.	Restart the server.

Another way to make binary InnoDB backups is to use InnoDB Hot Backup. See Section 32.3.3.2, "InnoDB Hot Backup."

To recover an InnoDB tablespace using a binary backup, stop the server, replace all the components that you made copies of during the backup procedure, and restart the server.

If you want to use a binary InnoDB backup to copy your InnoDB tables to another server, the requirements that must be satisfied are described in Section 32.3.4, "Conditions for Binary Portability." Note that the necessity of copying the tablespace files as a group means that for recovery operations you'll need to replace any existing tablespace files on the destination server. You cannot add one tablespace to another using a binary backup.

An alternative to making a binary backup is to dump table contents in text format (for example, with mysqldump). This technique can be useful for copying individual InnoDB tables from one server to another or if the conditions for binary portability are not satisfied. It can also be used to add tables from one tablespace to another: Run mysqldump to dump the tables into a text file, and then load the file into the destination server using mysql.

32.3.3. Other Binary Backup Tools

The programs described in this section, mysqlhotcopy and InnoDB Hot Backup, are special-purpose programs that can be used with particular storage engines to help you make binary backups.

32.3.3.1 mysqlhotcopy

The mysqlhotcopy script copies tables to a backup directory. It is a Perl script and requires the DBI module to be installed. It runs on Unix and NetWare. mysqlhotcopy works for MyISAM tables but not InnoDB tables.

mysqlhotcopy connects to the local MySQL server, locks the tables so that the server will not change them, flushes the tables to make sure that any pending changes are written to disk, and then copies the table files. When it has finished the copy operation, it unlocks the tables.

mysqlhotcopy must be run on the server host so that it can copy table files while the table locks are in place. It must be run while the server is running so that it can connect to the server to lock and flush the tables.

Operation of mysqlhotcopy is fast because it copies table files directly rather than backing them up over the network. It's also more convenient than issuing statements to the server to lock and flush the tables, because it handles those operations for you.

mysqlhotcopy has many options, which you can see by invoking it with the --help option. The following examples present some simple ways to use mysqlhotcopy:

• Back up the world database to a directory named world in the /var/archive directory:

   shell> mysqlhotcopy world /var/archive Locked 3 tables in 0 seconds. Flushed tables (`world`.`City`, `world`.`Country`, `world`.`CountryLanguage`) in 0 seconds. Copying 10 files... Copying indices for 0 files... Unlocked tables. mysqlhotcopy copied 3 tables (10 files) in 0 seconds (0 seconds overall). 

  
  

• Back up only the tables in the world database whose name contains Country:

   shell> mysqlhotcopy world./Country/ /var/archive Locked 2 tables in 0 seconds. Flushed tables (`world`.`Country`, `world`.`CountryLanguage`) in 0 seconds. Copying 6 files... Copying indices for 0 files... Unlocked tables. mysqlhotcopy copied 2 tables (6 files) in 0 seconds (0 seconds overall). 

  
  

32.3.3.2 InnoDB Hot Backup

The InnoDB Hot Backup program (ibbackup) is a commercial product available from Innobase Oy. It can back up InnoDB tables while the server is running without disturbing normal database activity. It's available for Unix and Windows.

32.3.4. Conditions for Binary Portability

Binary portability is important if you want to take a binary backup that was made on one machine and use it on another machine that has a different architecture. For example, using a binary backup is one way to copy databases from one MySQL server to another.

For MyISAM, binary portability means that you can directly copy the files for a MyISAM table from one MySQL server to another on a different machine and the second server will be able to access the table.

For InnoDB, binary portability means that you can directly copy the tablespace files from a MySQL server on one machine to another server on a different machine and the second server will be able to access the tablespace. By default, all InnoDB tables managed by a server are stored together in the tablespace, so portability of the tablespace is a function of whether all individual InnoDB tables are portable. If even one table is not portable, neither is the tablespace.

MyISAM tables and InnoDB tablespaces are binary portable from one host to another if two conditions are met:

• Both machines must use two's-complement integer arithmetic.

• Both machines must use IEEE floating-point format, or else the tables must contain no floating-point columns (FLOAT or DOUBLE).

In practice, those two conditions pose little restriction. Two's-complement integer arithmetic and IEEE floating-point format are the norm on modern hardware.

A third condition for InnoDB binary portability is that you should use lowercase names for databases and tables. This is because InnoDB stores these names internally (in its data dictionary) in lowercase on Windows. Using lowercase names allows binary portability between Windows and Unix. To force the use of lowercase names, you can put the following lines in an option file:

 [mysqld] lower_case_table_names=1 

If you configure InnoDB to use per-table tablespaces, the conditions for binary portability are extended to include the .ibd files for InnoDB tables as well. (The conditions for the shared tablespace still apply because it contains the data dictionary that stores information about all InnoDB tables.)

If the conditions for binary portability are not satisfied, you can copy MyISAM or InnoDB tables from one server to another by dumping them using some text format (for example, with mysqldump) and reloading them into the destination server.