7.9 The mysqlhotcopy Database Backup Program

7.9 The mysqlhotcopy Database Backup Program

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 be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ISAM tables. It runs on Unix, and on NetWare as of MySQL 4.0.18.

 shell>  mysqlhotcopy   db_name  [  /path/to/new_directory  ] shell>  mysqlhotcopy   db_name_1  ...  db_name_n /path/to/new_directory  shell>  mysqlhotcopy   db_name   ./   regex   /  

mysqlhotcopy supports the following options:

• --help , -?

  Display a help message and exit.

• --allowold

  Don't abort if target already exists (rename it by adding an _old suffix).

• --checkpoint= db_name.tbl_name

  Insert checkpoint entries into the specified db_name.tbl_name .

• --debug

  Enable debug output.

• --dryrun , -n

  Report actions without doing them.

• --flushlog

  Flush logs after all tables are locked.

• --keepold

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

• --method= #

  Method for copy ( cp or scp ).

• --noindices

  Don't include full index files in the backup. This makes the backup smaller and faster. The indexes can be reconstructed later with myisamchk -rq or isamchk -rq .

• --password= password , -p password

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

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

• --regexp= expr

  Copy all databases with names matching the given regular expression.

• --socket= path , -S path

  The Unix socket file to use for the connection.

• --suffix= str

  The suffix for names of copied databases.

• --tmpdir= path

  The temporary directory (instead of /tmp ).

• -- user = user_name , -u user_name

  The MySQL username to use when connecting to the server.

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

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

Use perldoc for additional mysqlhotcopy documentation:

 shell>  perldoc mysqlhotcopy