MySQL backups with mysqlhotcopy

MySQLPreviously 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.

One thought on “MySQL backups with mysqlhotcopy

  1. Very nice post shared here. Information posted here is really very effective. Thanks to the author of this post And please keep posting such informative material.

Leave a Reply to Mysql Backup Cancel reply

Your email address will not be published. Required fields are marked *