The mysqlhotcopy utility is a perl script that uses several basic system and SQL commands to backup a database. More specifically, it will lock the tables, flush the tables, make a copy, and unlock the tables. Although it is the fastest method available for backing up a MySQL database, it is limited to backing up only those databases residing on the same machine as where it is executed.

The function mysqlhotcopy can be executed to backup one database, a number of databases, or only those databases matching a name specified by a regular expression. In this section, the syntax involved with each scenario is provided, followed with a few examples.

Using mysqlhotcopy to backup just one database:

%>mysqlhotcopy [options] db_name /path/to/new_directory

Using mysqlhotcopy to backup just several databases:

%>mysqlhotcopy [options] db_name_1 ... db_name_n /path/to/new_directory

Using mysqlhotcopy to backup only those tables within a given database that match a regular expression:

%>mysqlhotcopy [options] db_name./regex/

The options can be viewed by executing the following command:

%>mysqlhotcopy --help

Examples:
Experiment with mysqlhotcopy by backing up the widgets database to the directory path “/usr/mysql/backups/”. Execute the following command:

%>mysqlhotcopy -u root -p widgets /usr/mysql/backups

As a second example, assume that the widgets database contains the tables: “products2000”, “products2001”, “clientele2000”, and “clientele2001”, with the four digits at the end of each name representing the year for which that data represents. The administrator wants to backup only those tables relative to the year “2000”:

%>mysqlhotcopy -u root -p widgets./^.+('2000')$/ /usr/mysql/backups

In the above example, the regular expression /^.+(‘2000’)$/ tells mysqlhotcopy to only backup those tables ending with the string “2000”.