mysqlhotcopy

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

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

• --addtodest

  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.

• --allowold

  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.

• --checkpoint=db_name.tbl_name

  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        VARCHAR(32),
      dest       VARCHAR(60),
      msg        VARCHAR(255)
  );
  

  
  

  src and dest are the source and destination database names, respectively, 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 by adding a suffix of _old 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. To avoid this problem, make the backup locally, and then copy it to the remote host after mysqlhotcopy finishes.

• --noindices

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

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

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