Using mysqlhotcopy to Back Up

The mysqlhotcopy utility is a Perl script to ease backups. It's still in beta (check the latest documentation to see if this is still the case by the time you read this), so it may not work properly in all situations. It's fast and convenient to use, and it works by locking and flushing the tables and then copying the files to the directory you specify (see Table 11.3). It can only copy files to elsewhere on the server. The syntax is as follows:

% mysqlhotcopy databasename backup_directory_path 

Table 11.3 describes the options available to mysqlhotcopy.

Table 11.3: mysqlhotcopy Options

Option	Description
-?, --help	Displays a help screen and exits.
-u, --user=#	Username for connecting to the server.
-p, --password=#	Password for connecting to the server.
-P, --port=#	Port to use when connecting to local server.
-S, --socket=#	Socket to use when connecting to local server.
--allowold	If the files already exist, mysqlhotcopy usually aborts. This option appends _old tothe filenames and continues operating.
--keepold	The renamed files from --allowold are usually deleted after the operation. This option leaves them there.
--noindices	This option does not include the index files in the backup, which speeds the process. After restoring the files, the indexes can be rebuilt with myisamchk -rq.
--method=#	Allows you to specify whether to use cp or scp to copy the files.
-q, --quiet	Only errors are outputted.
--debug	Enables debugging.
-n, --dryrun	Outputs messages, but does not do the actions.
--regexp=#	Copies all databases with names that match the regular expression.
--suffix=#	Gives a suffix for the names of copied databases.
--checkpoint=#	Inserts checkpoint entries into specified database table.
--flushlog	Flushes the logs once all the tables are locked.
--tmpdir=#	Allows you to specify a temporary directory.

mysqlhotcopy gets its options from the client and mysqlhotcopy groups in the option files.

To restore a backup made with mysqlhotcopy, replace the files in the data directory, as if you'd made the direct copies yourself.

There are a number of prerequisites to meet in order to run mysqlhotcopy:

• You need to be able to run Perl scripts on your database server.

• mysqlhotcopy depends on the following Perl classes in order to run: Getopt::Long, Data::Dumper, File::Basename, File::Path, DBI, and Sys::Hostname.

• You need write access to the directory to which you're trying to back up.

• You need select privileges on the databases you are backing up.

• In order to flush the table, you need reload privileges.