MySQL 5.1 Reference Manual  /  ...  /  mysqlhotcopy — A Database Backup Program

4.6.9 mysqlhotcopy — A Database Backup Program

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 and NetWare.

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

Table 4.14 mysqlhotcopy Options

--addtodestDo not rename target directory (if it exists); merely add files to it
--allowoldDo not abort if a target exists; rename it by adding an _old suffix
--checkpointInsert checkpoint entries
--chrootBase directory of the chroot jail in which mysqld operates
--debugWrite debugging log
--dryrunReport actions without performing them
--flushlogFlush logs after all tables are locked
--helpDisplay help message and exit
--hostConnect to MySQL server on given host
--keepoldDo not delete previous (renamed) target when done
--methodThe method for copying files
--noindicesDo not include full index files in the backup
--passwordPassword to use when connecting to server
--portTCP/IP port number to use for connection
--quietBe silent except for errors
--regexpCopy all databases with names that match the given regular expression
--resetmasterReset the binary log after locking all the tables
--resetslaveReset the file after locking all the tables
--socketFor connections to localhost, the Unix socket file to use
--tmpdirThe temporary directory
--userMySQL user name to use when connecting to server

  • --help, -?

    Display a help message and exit.

  • --addtodest

    Do not rename target directory (if it exists); merely add files to it.

  • --allowold

    Do not abort if a target exists; rename it by adding an _old suffix.

  • --checkpoint=db_name.tbl_name

    Insert checkpoint entries into the specified database db_name and table tbl_name.

  • --chroot=dir_name

    Base directory of the chroot jail in which mysqld operates. The dir_name value should match that of the --chroot option given to mysqld.

  • --debug

    Enable debug output.

  • --dryrun, -n

    Report actions without performing them.

  • --flushlog

    Flush logs after all tables are locked.

  • --host=host_name, -h host_name

    The host name of the local host to use for making a TCP/IP connection to the local server. By default, the connection is made to localhost using a Unix socket file.

  • --keepold

    Do not delete previous (renamed) target when done.

  • --method=command

    The method for copying files (cp or scp). The default is cp.

  • --noindices

    Do not include full index files for MyISAM tables in the backup. This makes the backup smaller and faster. The indexes for reloaded tables can be reconstructed later with myisamchk -rq.

  • --password=password, -ppassword

    The password to use when connecting to the server. The password value is not optional for this option, unlike for other MySQL programs.

    Specifying a password on the command line should be considered insecure. See Section, “End-User Guidelines for Password Security”. You can use an option file to avoid giving the password on the command line.

  • --port=port_num, -P port_num

    The TCP/IP port number to use when connecting to the local server.

  • --quiet, -q

    Be silent except for errors.

  • --record_log_pos=db_name.tbl_name

    Record master and slave status in the specified database db_name and table tbl_name.

  • --regexp=expr

    Copy all databases with names that match the given regular expression.

  • --resetmaster

    Reset the binary log after locking all the tables.

  • --resetslave

    Reset the file after locking all the tables.

  • --socket=path, -S path

    The Unix socket file to use for connections to localhost.

  • --suffix=str

    The suffix to use for names of copied databases.

  • --tmpdir=dir_name

    The temporary directory. The default is /tmp.

  • --user=user_name, -u user_name

    The MySQL user name to use when connecting to the server.

Use perldoc for additional mysqlhotcopy documentation, including information about the structure of the tables needed for the --checkpoint and --record_log_pos options:

shell> perldoc mysqlhotcopy

Download this Manual
User Comments
  Posted by Andrew Bruno on November 4, 2005
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.

Here it is, use at own risk.


import java.sql.*;
import java.util.Calendar;

* A java representation of the perl mysqlhotcopy script
* Ref:
* <p>
* Some statistics for 28 files totaling 640MB:<br>
* <br>Windows Native: 104375 msecs
* <br>UNIX Native: 95520 msecs
* <br>
* <br>UNIX commons-io: 94657 msecs (1.5 mins)
* <br>WINDOWS commons-io: 96360 msecs (1.5 mins)
* <br>
* @TODO: Add a debug/verbose flag
* @TODO: Add more options
* @author Andrew Bruno
public class MySqlHotCopy

private String username = null;

private String password = null;

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

private String database = null;

private String url = null;

private String dirSourceIndex = null;

private String dirBackup = null;

private String copymode = null;

private static boolean error = false;

public MySqlHotCopy()

public MySqlHotCopy( String args[] )
username = args[0];
password = args[1];
database = args[2];
dirSourceIndex = args[3];
dirBackup = args[4];
copymode = args[5];
url = "jdbc:mysql://" + host + "/" + database;

* @param args
public static void main(String[] args)
if (args.length <= 0)
showUsageAndExit("Parameters missing", args);
if (args.length != 6)
showUsageAndExit("6 Parameters required", args);
MySqlHotCopy mySqlHotCopy = new MySqlHotCopy(args);

Connection conn = null;

conn = DriverManager.getConnection(mySqlHotCopy.url, mySqlHotCopy.username, mySqlHotCopy.password);
System.out.println("Database connection established");

Statement s = conn.createStatement();

s.executeQuery("SHOW TABLES");
ResultSet rs = s.getResultSet();

String lockSqlCommand = "LOCK TABLES ";
String flushSqlCommand = "FLUSH TABLES ";

while (
String tableName = (String) rs.getObject(1);
lockSqlCommand = lockSqlCommand + tableName + " READ";
flushSqlCommand = flushSqlCommand + tableName;

if (rs.isLast())
lockSqlCommand = lockSqlCommand + ";";
flushSqlCommand = flushSqlCommand + ";";
lockSqlCommand = lockSqlCommand + ", ";
flushSqlCommand = flushSqlCommand + ", ";

System.out.println("Lock Sql Command is " + lockSqlCommand);
System.out.println("Flush Sql Command is " + flushSqlCommand);

s.executeUpdate("FLUSH LOGS");
// s.executeUpdate("RESET MASTER");
// s.executeUpdate("RESET SLAVE");

long time = Calendar.getInstance().getTimeInMillis();

if (mySqlHotCopy.copymode.equals("nativedos"))
System.out.println("Using Native Dos mode to copy files");

String copyCommand = "cmd /c COPY /Y \"" + mySqlHotCopy.dirSourceIndex + "\" \"" + mySqlHotCopy.dirBackup + "\"";

System.out.println("DOS Copy Command = '" + copyCommand + "'");
Process process = Runtime.getRuntime().exec(copyCommand);

DataInputStream p_in = new DataInputStream(process.getInputStream());
BufferedReader d = new BufferedReader(new InputStreamReader(p_in));

String p_str;
while ((p_str = d.readLine()) != null)


if (process.exitValue() != 0)
System.out.println("Dos copy process exited with an error value of " + process.exitValue());
else if (mySqlHotCopy.copymode.equals("nativeunix"))

final String copyCommand = "/bin/cp -v " + mySqlHotCopy.dirSourceIndex + "*" + " " + mySqlHotCopy.dirBackup;

System.out.println("Using Native Unix mode to copy files");
String[] cmd = { "/bin/sh", "-c", copyCommand };

System.out.println("UNIX Copy Command = '" + copyCommand + "'");

// See
Process process = Runtime.getRuntime().exec(cmd, null, null);

DataInputStream p_in = new DataInputStream(process.getInputStream());
BufferedReader d_in = new BufferedReader(new InputStreamReader(p_in));

String p_str;
while ((p_str = d_in.readLine()) != null)

DataInputStream p_err = new DataInputStream(process.getErrorStream());
BufferedReader d_err = new BufferedReader(new InputStreamReader(p_err));

String p_err_str;
while ((p_err_str = d_err.readLine()) != null)

// OutputStreamWriter osWriter = new
// OutputStreamWriter(process.getOutputStream());
// PrintWriter out = new PrintWriter(osWriter);

// if ((process != null) && (process.exitValue() != 0))
// {
// System.out.println("UNIX copy process exited with an
// error value of " + process.exitValue());
// }

else if (mySqlHotCopy.copymode.equals("commonsiojava"))
System.out.println("Using Commons IO mode to copy files");

FileUtils.copyDirectory(new File(mySqlHotCopy.dirSourceIndex), new File(mySqlHotCopy.dirBackup));

showUsageAndExit("copymode " + mySqlHotCopy.copymode + " not supported", args);

time = Calendar.getInstance().getTimeInMillis() - time;

System.out.println("Copying of files took " + time + " milleseconds");

/* I dont think this is really needed */
s.executeUpdate("UNLOCK TABLES");


catch (Exception e)
System.err.println("Exception caught: " + e.getMessage());
error = true;
if (conn != null)
// System.out.println("Database connection terminated");
catch (Exception e)
{ /* ignore close errors */

if (error)



private static void showUsageAndExit(String errorString, String[] args)

if ((errorString != null) && (errorString.length() != 0))
System.err.println("Error Message: " + errorString);

if (args.length != 0)
System.err.print(MySqlHotCopy.class.getName() + " called with parameters ");
for (int i = 0; i < args.length; i++)
String string = args[i];
System.err.print(string + " ");

System.out.println("Usage: username password database dbindexdir dbbackupindexdir <copymode>");
System.out.println("where <copymode> is one of: ");
System.out.println("\t nativedos");
System.out.println("\t nativeunix");
System.out.println("\t commonsiojava");



  Posted by Tasin Reza on November 13, 2005
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.
  Posted by Jason Toh on November 23, 2005
Hi Tasin, i believe the username and password is neeeded to lock the database tables when performing a backup. Copying the index files is similiar to creating queries but with lesser effort. Cheers!
  Posted by Andrew Bruno on November 23, 2005
Guys, if you want to simply backup queries, then use mysqldump.

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.

  Posted by Kevin Mannion on April 4, 2006
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.

A bug report with a potential fix has been submitted.
  Posted by Kevin Zembower on August 2, 2006
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

Suggestions welcomed.

  Posted by Jacob Rief on November 8, 2006
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 instance
mysqlhotcopy --regexp='^[a-m].+'.'.+' <dumpdir>
mysqlhotcopy --regexp='^[n-z].+'.'.+' <dumpdir>

  Posted by icy flame on January 25, 2007
When dumping a db with many tables, a similar problem described above by Jacob Rief also occurs.

DBD::mysql::db do failed: File '_path_to_file_' not found (Errcode: 24) at /usr/bin/mysqlhotcopy line 466.

A similar work around using regexp to split up the number of tables to dump should work.

If there is a --all-databases option for this utility would be a more elegant solution.
  Posted by Marko Hrastovec on August 24, 2007
Instead of using --regexp and locking bunch of databases I rahter use this perl script which copies one database after another.


opendir (D, "/var/lib/mysql");
@f = readdir (D);
closedir (D);
foreach $file (@f)
$filename = "/var/lib/mysql/" . $file;
if (-d $filename && $file ne '.' && $file ne '..')
system "/usr/bin/mysqlhotcopy --allowold -u root $file /backup/mysql/";

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.
  Posted by Fili - on October 31, 2007
-bash version of Marko Hrastovec script-

for i in `/usr/bin/find /var/lib/mysql/* -type d -printf "%f\n"`;do /usr/bin/mysqlhotcopy --allowold -u root $i $BACKUP_DIR; done
  Posted by Rupert Peddle on November 21, 2007
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:

The manual page is at
  Posted by Wietze Lindeboom on September 28, 2009
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.
Sign Up Login You must be logged in to post a comment.