Learning MySQL

mysqlhotcopy

If you want to create a copy of a database on the same host as the server, and all the tables in your database are of the MyISAM (or the older ISAM) type, then you may find mysqlhotcopy handy. This is a Perl script file that’s in the scripts directory, and differs from mysqldump in that it’s a binary copy, so you get the MySQL database files, not a text file of SQL statements, after copying. It’s also faster.

You may wonder why you need a special command to copy the database files. After all, they’re already there in the data directory, and you could use the operating system copy command (e.g., cp or copy) to copy them. The problem is that if the server is running, what you have on disk is not always consistent with the status according to the MySQL server. The mysqlhotcopy command takes care of the locking needed to ensure that the copies are consistent, even if the server is running.

Let’s look at an example that copies the database music to the database music_bak:

# mysqlhotcopy --user=root --password=the_mysql_root_password music music_bak
Locked 4 tables in 0 seconds.
Flushed tables (`music`.`album`, `music`.`artist`, `music`.`played`, `music`.`track`)
  in 0 seconds.
Copying 13 files...
Copying indices for 0 files...
Unlocked tables.
mysqlhotcopy copied 4 tables (13 files) in 1 second (1 seconds overall).

There are two things worth mentioning here. First, the server has to be running when you run mysqlhotcopy. Second, you must have operating-system-level access to ...