Previously I have written about MySQL backups using mysqldump. But there is another way to backup MySQL databases and that is by using a perl script included with MySQL called mysqlhotcopy. Things to note about mysqlhotcopy are:

• It only works with MyISAM and ARCHIVE storage engines. Does not work with InnoDB.
• Is faster that mysqldump
• Creates a copy of the database files instead of a SQL dump file

You can find out whether your database has InnoDB or MyISAM tables by login into your mysql command shell and running the show table status command:

SHOW TABLE STATUS FROM `TestDB`;

Backing up a database

To backup a database using mysqlhotcopy I recommend adding the appropriate password and username to your .my.cnf file first:

[mysqlhotcopy]
user=root
password=password

Then run mysqlhotcopy:

mysqlhotcopy db_name /path/to/backup_dir/

Backup all databases using mysqlhotcopy

It is possible to backup all the databases in the /var/lib/mysql directory using this PHP script that I created:

#!/usr/bin/php
<?php
$backup_dir = '/root/dbbackup/';
$mysql_dir = '/var/lib/mysql/';
 
if ( $handle = opendir( $mysql_dir ) ) {
    while ( false !== ( $file = readdir( $handle ) ) ) {
	if ( $file != '.' && $file != '..' && is_dir( $mysql_dir . $file ) )  {
	        system( "mysqlhotcopy -q --addtodest $file $backup_dir" );
	}
    }
}

Just download the mysqlhotcopy script, change the hash bang to reflect your PHP interpreter’s location and chmod +x it to make it executable. Also change the backup_dir variable as per your requirements. Run it as a root in a cron job to automate backups.

Restoring the mysqlhotcopy backups

Restoring the backups is as easy as using a copy command. But first shutdown the mysql service:

service mysqld stop
cp -r /root/dbbackup/* /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/*
service mysqld start

The chown command is important because the files have to be owned by the mysql user.