Lock and Copy

How Does MySQL Hot Copy Work?

 
Lock and copy is often incorrectly called Hot Backup.  An example program that does lock and copy for MySQL is called mysqlhotcopy

How Does MySQL Hot Copy Work?

1. FLUSH TABLES WITH READ LOCK
2. Full copy of MyISAM Tables (e.g. cp or scp)
3. UNLOCK TABLES

Advantages

1. Faster than a SQL Dump
    
2. Restore is fast (just copy MyISAM files).

Disadvantages

1. Only works on MyISAM. Do NOT use with InnoDB!
    
2. Holds a global read lock on all tables blocking writes from other connections for the duration of the full backup.  Locking and Flushing is NOT optional.  There must not be any writes to the MyISAM files during the copy or they WILL BE CORRUPT.  The Flush is required to put the MyISAM files into a consistent state because mysqld caches writes to MyISAM pages in memory.
    
3. The global Lock is held as long as it takes to copy all of the MyISAM files.  In pratice for larger mission critical databases this means failed queries while the database is unavailable and queries time out waiting on the lock and tyhe appication is down!
    
4. Restore is not straightforward.  You must carefully copy files back into the MySQL data directory and change permissions on them.  This can be dangerous if you make a mistake.  A less dangerous operation is to shutdown the MySQL daemon and copy MyISAM files back into the data directory