With MySQL hotcopy we can create backups by copying the binary database files. This is fast and clean. We still need to perform table locks, but this goes much faster. How to do create backups, and how to restore them?
mysqlhotcopy is a Perl script to create MySQL backup. In contrast with the mysqldump program, mysqlhotcopy does not generate any SQL formatted output. All it does is force a lock on a table, then copy the raw table files, and release the lock. Mysqlhotcopy only works on MyIsam's, and only on a local machine.
To create a mysqlhotcopy backup, I use the command
/usr/local/mysql/bin/mysqlhotcopy --allowold -user=$DBUSER -password=$DBPASS $DBNAME $backupdir
With of course the right shell variables set. This command will copy a bunch of .frm, .MYD and .MYI files to the specified directory. You can now do with this whatever you want. I'm packing all the files in a tar.bz2 with:
tar -C $backupdir/ -cjf $backupdir/$DBNAME-1.tar.bz2 $DBNAME
After packing it, I deploy the backup to our backup servers, and bring it into the backup cycle.
This is all fairly straight forward. The problem starts when you have to restore these files. You cannot run an SQL insert on a running database as you would do with a mysqldump .sql backup. You will have to switch off the database. Then extract your backup to the MySQL data directory, usually something like /var/lib/mysql. Once this is done, you will have to rebuild the database indices. Do this with:
myisamchk -rq *.MYI
You should execute this command from within your database file directory, so in our case: /var/lib/mysql/$DBNAME. That's all there is to it.