4.8.7 mysqlhotcopy, Copying MySQL Databases and Tables

mysqlhotcopy is a Perl script that uses LOCK TABLES, FLUSH TABLES and cp or scp to quickly make a backup of a database. It's the fastest way to make a backup of the database or single tables, but it can only be run on the same machine where the database directories are. mysqlhotcopy works only on Unix, and it works only for MyISAM and ISAM tables.

mysqlhotcopy db_name [/path/to/new_directory]

mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

mysqlhotcopy db_name./regex/

mysqlhotcopy supports the following options:

-?, --help

Display a help screen and exit

-u, --user=#

User for database login

-p, --password=#

Password to use when connecting to server

-P, --port=#

Port to use when connecting to local server

-S, --socket=#

Socket to use when connecting to local server

--allowold

Don't abort if target already exists (rename it _old)

--keepold

Don't delete previous (now renamed) target when done

--noindices

Don't include full index files in copy to make the backup smaller and faster The indexes can later be reconstructed with myisamchk -rq..

--method=#

Method for copy (cp or scp).

-q, --quiet

Be silent except for errors

--debug

Enable debug

-n, --dryrun

Report actions without doing them

--regexp=#

Copy all databases with names matching regexp

--suffix=#

Suffix for names of copied databases

--checkpoint=#

Insert checkpoint entry into specified db.table

--flushlog

Flush logs once all tables are locked.

--tmpdir=#

Temporary directory (instead of /tmp).

You can use perldoc mysqlhotcopy to get more complete documentation for mysqlhotcopy.

mysqlhotcopy reads the groups [client] and [mysqlhotcopy] from the option files.

To be able to execute mysqlhotcopy you need write access to the backup directory, the SELECT privilege for the tables you are about to copy and the MySQL RELOAD privilege (to be able to execute FLUSH TABLES).

User Comments

--regex=

What sort of regex works here? I can;t get it to match anything

Since this is a perl script, I think this will be perl regular expressions as discribed in 'perldoc perlre'.

The manual says that for mysqlhotcopy to work, the user needs the RELOAD priviledge, but this priviledge can be only granted at the database level and not at a table level (at least in mysql 3.23.53).

You can set the grant by:
grant reload on *.* to user@host;

I couldn't get mysqlhotcopy to work with the privileges the documentation say are required (Select_priv,Reload_priv). I was able to strace the thread and see mysql was returning "access denied" because mysqlhotcopy was trying to do a LOCK query.

If you are getting "access denied", try giving the appropriate user "Lock_tables_priv" and do a "FLUSH PRIVILEGES".

(mysqlhotcopy v1.18)

Add your own comment.