Howto: copy of a production mysql database using mysqlhotcopy backup

Do you need to see what the data was like yesterday, last week in your tables?

Here are the steps to create a copy of a mysql database on the same machine, with no downtime.

Assumption: You have made backups using mysqlhotcopy and are using myisam tables.
Caveat: Make sure you copy the backup into the new directory, otherwise you will nuke the current database. You have been warned.

1. Create a new directory in your mysql data directory (/var/lib/mysql): mkdir new_db
2. Copy the backup you want into that new directory: cp /backup_dir/current_db/* new_db/
3. Log into mysql and create the new database: CREATE DATABASE IF NOT EXISTS new_db;
4. If any tables have been created since the backup you will have to create them in new_db, using this command: CREATE TABLE IF NOT EXIST new_db.table_name like current_db.table_name;
5. Check/Validate all the tables in new_db are ok using command: CHECK TABLE new_db.table_name;

You can use this method to create a copy to check for changes between now and the last backup, this might be for recovery reasons or audit reasons.

The reason I created this post was that I spent at least 30 minutes looking using google for a solution to this and whilst there were plenty articles about backups, nothing mentioned making a copy locally, on the same system.

Have Fun

Paul