mysqlhotcopy

The mysqlhotcopy performs efficient backups of databases and tables. It works only for MyISAM and ISAM tables. mysqlhotcopy is a Perl script and requires that you have DBI support installed. It does not currently work on Windows.

mysqlhotcopy connects to the server on the local host and 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 Chapter 13 for telling the server to leave the designated tables alone while you're working directly with the table files.)

mysqlhotcopy was introduced in MySQL 3.23.11.

Usage
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 the following 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 the following 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 will connect over TCP/IP instead if you specify the actual name of the server using the --host option. The --port option can be used in this case to specify a port number other than the default. The --host option was introduced in MySQL 3.23.52.

Options Specific to mysqlhotcopy
--allowold

If the target directory already exists, rename it using an _old suffix. 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.

--checkpoint=db_name.tbl_name

Write a checkpoint record to the given table, which should have been created in advance with the following structure:

CREATE TABLE tbl_name
(
time_stamp TIMESTAMP NOT NULL,
src VARCHAR(32),
dest VARCHAR(60),
msg VARCHAR(255)
);

src and dest are the source and destination database names, and msg indicates success or failure of the copy operation.

--dryrun, -n

"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.

--flushlog

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.

--keepold

If the previous target directory exists, rename it with an _old suffix prior to making a new copy. This option implies --allowold.

--method=copy_method

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.

--noindices

Don't copy index files. (If you need to use the backup files later to recover the tables, you can recreate the indexes by using the files with myisamchk--recover for MyISAM tables or isamchk--recover for ISAM tables.

--quiet, -q

Produce no output except when errors occur.

--record_log_pos=db_name.tbl_name

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 the following 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.

--regexp=pattern

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.

--resetmaster

Reset the binary update logs by issuing a RESET MASTER statement after all the tables have been locked and before they are copied.

--resetslave

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.

--suffix=str

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.

--tmpdir=dir_name

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 the variable is not set.