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:
- I decided the DB I needed copying...
- I released it to some *.sql file on my small box...
- I produced a brand new DB...
- after choosing that new DB I imported that file. Presto!