I have a large MySQL database, lets call it live_db
, which I want to replicate on the same machine to provide a test system to play around with (test_db
), including table structure and data. In regular intervals I want to update the test_db
with the content of the live_db
; if possible incremental.
Is there some built-in mechanism in MySQL to do that? I think that master-slave replication is not the thing I want since it should be possible to alter data in the test_db
. These changes do not have to be preserved, though.
Regards,
CGD
On either the source or destination SQL Server instance, launch the Copy Database Wizard in SQL Server Management Studio from Object Explorer and expand Databases. Then right-click a database, point to Tasks, and then select Copy Database.
This indicates that using the MySQL community installer, we can install a different version of MySQL but cannot install multiple instances of the same version. To run multiple instances, the second instance of MySQL must install as a windows service. The installation must be performed manually using a command prompt.
The mysql
command line client will accept a stream of SQL statements from standard input. You can therefore pipe the output of mysqldump
directly into mysql
on the command line. Doing this as a cron job will regularly overwrite your test data with updated live data:
mysql --user=username --password=passwd -e 'DROP DATABASE test_db;' mysql --user=username --password=passwd -e 'CREATE DATABASE test_db;' mysqldump --user=username --password=passwd live_db | mysql --user=username --password=passwd test_db
Note that since your data is large, it will take a long time.
Michaels answer abowe works well but does not copy events, stored procedures or triggers.
To copy those a few more switches is needed for mysqldump: --events --triggers --routines
To complement an already made copy:
mysqldump --user=username --password=passwd --no-data --no-create-info --no-create-db --events --triggers --routines live_db | mysql --user=username --password=passwd test_db
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With