The mysqlhotcopy performs efficient backups of databases and tables. It works only for MyISAM and ISAM tables. mysqlhotcopy is a Perl script. It requires that you have DBI support installed (not surprising, given that it was originally written by Tim Bunce, one of the creators of DBI). mysqlhotcopy works on Unix and (as of MySQL 4.1.2) NetWare, but not Windows.
mysqlhotcopy connects to the server on the local host. It sends table flushing and locking statements to the server for each table to be copied, and then copies the table files to another location to make a backup. This ensures that outstanding table modifications have been flushed to disk and that the server won't try to further modify the table while it is being copied. (Essentially, mysqlhotcopy implements the protocol described in "Performing Database Maintenance with the Server Running," in Chapter 13. This protocol serves to tell the server to leave the designated tables alone while you're working directly with the table files.)
This program can be invoked in a number of ways. The general invocation syntax is as follows:
mysqlhotcopy [options] db_name[./regex/] [new_db_name | dir_name]
For example, to make a copy of the database db_name named db_name_copy under the data directory, use this command:
% mysqlhotcopy [options] db_name
To copy the db_name database to a directory named db_name under the /tmp directory instead, do this:
% mysqlhotcopy [options] db_name /tmp
More examples are provided in the online documentation, available with this command:
% perldoc mysqlhotcopy
Standard Options Supported by mysqlhotcopy
--debug --host --port --user
--help --password --socket
The --host option, if given, is intended only for specifying the name of the local host. Normally, mysqlhotcopy tries to connect to the local server using a Unix socket file. It connects over TCP/IP instead if you specify the actual name or IP number of the server using the --host option. The --port option may be used in this case to specify a port number other than the default.
Options Specific to mysqlhotcopy
Instead of renaming the target directory if it already exists, just add backup files to it. This option was introduced in MySQL 4.1.1.
If the target directory already exists, rename it by adding a suffix of _old. If the copy fails, the renamed directory is restored to the original name. If the copy operation succeeds, the renamed directory is deleted, unless the --keepold option is also given.
Write a checkpoint record to the given table, which should have been created in advance with this structure:
CREATE TABLE tbl_name
time_stamp TIMESTAMP NOT NULL,
src and dest are the source and destination database names, respectively, and msg indicates success or failure of the copy operation.
"No execution" mode. mysqlhotcopy reports what actions it would take to perform the command, without actually doing them. This is useful for checking whether mysqlhotcopy will do what you expect, particularly when you're learning how to use it.
Flush the logs after all the tables have been locked and before copying them. This has the effect of checkpointing them to the time of the copy operation.
If the previous target directory exists, rename it by adding a suffix of _old prior to making a new copy. This option implies --allowold.
The method to use for copying files. A value of cp uses the cp program. Experimental support for an scp method is also available. In this case, the copy_method value should be the entire scp command to use, and the destination directory must already exist. The scp method may result in your tables being locked for a much longer time than a local copy due to the extra time required to copy the files over the network. To avoid this problem, make the backup locally, and then copy it to the remote host after mysqlhotcopy finishes.
Don't copy index files. (If you need to use the backup files later to recover the tables, you can re-create the indexes by using the files with myisamchk --recover for MyISAM tables.)
Produce no output except when errors occur.
Before copying tables, issue SHOW MASTER STATUS and SHOW SLAVE STATUS statements and record the results in the given table, which should have been created in advance with this structure:
CREATE TABLE tbl_name
host VARCHAR(60) NOT NULL,
time_stamp TIMESTAMP NOT NULL,
log_file VARCHAR(32) NULL,
log_pos INT NULL,
master_host VARCHAR(60) NULL,
master_log_file VARCHAR(32) NULL,
master_log_pos INT NULL,
PRIMARY KEY (host)
The results from SHOW MASTER STATUS are recorded in the log_file and log_pos columns. This information provides replication coordinates for the binary logs; if the backup host is a replication master server, a slave should begin from these coordinates if it is initialized from the backup files as a slave of the master. The results from SHOW SLAVE STATUS are recorded in the master_host, master_log_file, and master_log_pos columns; they can be used if the backup host is a replication slave server and you want to initialize another slave of the same master from the backup files.
Copy all databases having names that match the given regular expression. The final argument of the command should be the directory where you want to copy the databases.
Reset the binary logs by issuing a RESET MASTER statement after all the tables have been locked and before they are copied.
Reset the information in the master.info file by issuing a RESET SLAVE statement after all the tables have been locked and before they are copied.
This option is used when making a copy of databases into the database directory. Each new database directory name is the same as the original with the given suffix added.
The pathname of the directory in which to create temporary files. The default is to use the directory named by the TMPDIR environment variable, or /tmp if that variable is not set.