I've got a large MySQL database, allows refer to it as live_db, which I wish to replicate on a single machine use a test system to adjust (test_db), including table structure and data. In regular times I wish to update the test_db using the content from the live_db if at all possible incremental.

Can there be some built-in mechanism in MySQL to achieve that? I believe that master-slave replication isn't the one thing I would like since it ought to be easy to alter data within the test_db. These changes don't have to be maintained, though.



Certainly there has to be better ideas than mine, but here you go:

I have never attempted this, so use at the own risk! You are able to pipe the creation of mysqldump straight into mysql around the command line. Carrying this out like a cron job will regularly overwrite your test data with up-to-date live data:

mysql -uuser -ppassword -e 'DROP DATABASE test_db;'
mysql -uuser -ppassword -e 'CREATE DATABASE test_db;'
mysqldump -uuser -ppassword live_db | mysql -uuser -ppassword test_db

Observe that as your information is large, it will require a very long time.

From the carrying this out in phpMyAdmin:

  1. I decided the DB I needed copying...
  2. I released it to some *.sql file on my small box...
  3. I produced a brand new DB...
  4. after choosing that new DB I imported that file. Presto!