4.6.10 mysqlhotcopy — A Database Backup Program

Note

This utility is deprecated in MySQL 5.6.20 and removed in
MySQL 5.7

mysqlhotcopy is a Perl script that was
originally written and contributed by Tim Bunce. It uses
FLUSH TABLES,
LOCK TABLES, and
cp or scp to make a
database backup. It is a fast way to make a backup of the
database or single tables, but it can be run only on the same
machine where the database directories are located.
mysqlhotcopy works only for backing up
MyISAM and ARCHIVE tables.
It runs on Unix.

To use mysqlhotcopy, you must have read
access to the files for the tables that you are backing up, the
SELECT privilege for those
tables, the RELOAD privilege (to
be able to execute FLUSH
TABLES), and the LOCK
TABLES privilege (to be able to lock the tables).

shell> mysqlhotcopy db_name [/path/to/new_directory]

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

Back up tables in the given database that match a regular
expression:

shell> mysqlhotcopy db_name./regex/

The regular expression for the table name can be negated by
prefixing it with a tilde (“~”):

shell> mysqlhotcopy db_name./~regex/

mysqlhotcopy supports the following options,
which can be specified on the command line or in the
[mysqlhotcopy] and
[client] groups of an option file. For
information about option files used by MySQL programs, see
Section 4.2.6, “Using Option Files”.

User Comments

I've written some basic Java code that does something similar to the perl script, except with a lot less options. Maybe MySql could add it to the JDBC driver code or other, so that future downloads have this functionality, which would allow others to use it in Windows or UNIX without the need of PERL.

/* * Should always be localhost as files are assumed to be on the same server * that mysql is running on. On Unix use 127.0.0.1 and not localhost or else * you'll get Socket Exceptions */ private final String host = "127.0.0.1";

Hi andrew, First I would like to thank you for the code. But I dont understand the use of SourceIndexDir when you are asking for the username, password for the database as you can directly copy from one directory to another!!! I think, its a better option to create queries and find out the result sets and save the result sets as a back up, not copying the directories directly. Sorry if I am missing something over here.

On the other hand, if there is a way to find the directory location of where the index files to the MYISAM are via an SQL query, then you could alter code, and remove the sourceIndexDir field. This would add safely, and make the code smarter.

Mysqlhotcopy does not copy all of the directories from a raid set when the number of tables is greater than ten. The raid directories are numbered using hexadecimals but mysqlhotcopy only copies directories numbered with decimals. This was found on a Linux system. Raid directories may be numbered diferently on other systems like Windows.

While trying to copy all my databases to a new machine using mysqlhotcopy, I couldn't find anyway to do this in a single command, with or without the --regexp option. If it can be done, would someone please describe how.

This is how I finally did it in one line in bash:cd /tmp/mysqlhotcopies/ && mysqlhotcopy --flushlog --regexp '.*' . && for d in *; do { mysqlhotcopy --flushlog --addtodest $d /tmp/mysqlhotcopies; } done

When dumping too many databases alltogether, mysqlhotcopy may fail with DBD::mysql::db do failed: Can't find file: '...'To avoid this you should dump the databases separately, so instead of doing mysqlhotcopy --regexp='.+'.'.+' <dumpdir>do it in two or more steps, for instancemysqlhotcopy --regexp='^[a-m].+'.'.+' <dumpdir>mysqlhotcopy --regexp='^[n-z].+'.'.+' <dumpdir>

That way only tables in one database are locked when copying files. If you have some very large database and other smaller all are locked because it takes a long time to copy the big one although smaller could be copied instantly.

When using the --noindices option, or when taking a binary backup of a MyISAM table without the MYI index files, the myisamchk -rq option didn't work for me! It seems that you need to use the inbuilt REPAIR TABLE command with the USE_FRM switch to rebuild the index file in version 4.0.2+. This caught me out a little until I found:

I had troubles with Hotcopy backup script with a error like this DBD::mysql::db do failed: File '_path_to_file_' not found (Errcode: 24) at /usr/bin/mysqlhotcopy line 466. The solution was to increase the open_file_limit in the my.cnf and restart mysql service.