This section describes methods that you can use to make binary-format database or table backups. 32.3.1. Making Binary MyISAM BackupsTo 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 BackupsA 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:
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 ToolsThe 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 mysqlhotcopyThe 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:
32.3.3.2 InnoDB Hot BackupThe 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 PortabilityBinary 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:
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. |