MySQL in a Nutshell, 2nd Edition

Name

mysqlhotcopy

Synopsis

mysqlhotcopy database [path]

Use this utility to make backup copies of databases while the server is active. It works only on MyISAM and ISAM tables. It makes a simple copy of each database directory and each table file. This results in a separate directory for each database and usually three files for each table: one for the schema, another for the data, and a third for the index. It places a read lock on all of the tables in the database while copying them. Here is an example of how you can copy a database with mysqlhotcopy:

mysqlhotcopy -u russell -p password workrequests /tmp/backup

Note that unlike other MySQL utilities, there is a space between the -p and the password. Next, specify the database (workrequests). Finally, give the path to write the backup directories. To restore databases or tables that were copied by mysqlhotcopy, just copy the table files to be restored to their original data directories.

mysqlhotcopy options

--addtodest: This option instructs the utility not to abort the session or to rename the backup directory, but to add new files to the directory.
--allowold: This option renames any existing backup directory with an _old suffix so that the copying may be completed. If the new copy is successful, the old directory is deleted. If it’s unsuccessful, the old directory is restored.
--checkpoint=database.table: This option saves logging information to the named database and table.
--chroot=path: This option is used to specify the base directory of the chroot in which the mysqld daemon is located, which should have the same directory of the --chroot option.
--debug: This option is used to enable debugging information.
--dryrun, -n: This option has the utility test the backup process without actually making a copy.
--flushlog: This option flushes logs after all tables are locked.
--help, -?: This option displays basic help information.
--host=host, -h host: This option specifies the name or IP address of the server for connection.
--keepold: This option instructs the utility when using the --allowold option not to delete the old directory if the copying is successful.
--method=method: This option sets the method used by the utility for copying files. The choices are cp or scp.
--noindices: This option copies only the headers of index files. Indexes may be rebuilt when restoring copies.
--password=password, -ppassword: This option provides the password to pass to the server. A space is permitted after the -p option, before the password.
--port=port, -P port: This option specifies the port number to use for connecting to the server.
--quiet, -q: This option suppresses all messages except for error messages.
--record_log_pos=database.table: This option is used to specify the database and table to record the log position and status of the master and slave servers when using replication.
--regexp=expression: This option provides a regular expression for determining which databases to copy based on the name.
--resetmaster: This option executes a RESET MASTER statement after tables are locked.
--resetslave: This option executes a RESET SLAVE statement after tables are locked.
--socket=filename, -S filename: This option provides the name of the server’s socket file.
--sufix=string: This option specifies the suffix for the copies of databases. The default is _copy.
--tmpdir=path: This option specifies the temporary directory to use. The default is /tmp.
--user=user, -u suser: This option specifies the username for connecting to the server.

Table of Contents for MySQL in a Nutshell, 2nd Edition

Name

Synopsis

mysqlhotcopy options

Table of Contents for
MySQL in a Nutshell, 2nd Edition