How to mirror a MySQL database using just one server

Question

I need to frequently duplicate one database on my MySQL server to a mirror database on the same MySQL server.

I am writing a bash script that makes a mirror of my web application (PHP/MySQL) on a separate subdomain. Users can use this mirror to try out all kinds of crazy things without affecting the production environment. The script is supposed to run automatically every night.

Duplicating the source code is easy. I just use rsync -a to copy everything from the directory holding the production environment to the mirror directory and apply a patch to update a few configuration files. The problem is with duplicating the database, which has grown to nearly 3 GB in size.

When I use mysqldump live_db | mysql mirror_db then it takes forever to duplicate the database. Also, during that time CPU usage peaks to 100% and my web application becomes unusably slow.

Database replication does not seem to be an option since I only have one MySQL server. Replication requires two servers.

What is the best way to duplicate the production database? It doesn't have to be fast, but it should not impact performance of the system too much. People should still be able to use my web application while the mirror script is running in the background.

Answer 1

Possible solutions I can think of:

1) Buy a faster server

2) Copy the raw tables. This isn't ideal but if it's just for testing could be viable.

I'm going to assume you're using MyISAM tables but here are the steps:

• Ideally write lock the tables in the production database, ignore if this is not possible
• Copy the binary table data/indexes from /var/lib/mysql/production_schema (or similar) to /var/lib/mysql/development_schema. rsync -a (run as root) would be idea for doing this with.
• restart the MySQL server (this is your only downtime in the whole process)
• CHECK/REPAIR all of the tabes in development_schema

It's worth adding that I wouldn't use this method as a way of copying data from one production environment to another without stopping the server before making the copy.

3) consider whether you need all of the production data in the development environment. Could you be smart and only copy a subset of the data?

Answer 2

If you have MyISAM tables, you could use mysqlhotcopy.