I have two MySQL databases called LIVE and REPORTS on the same server.
I want to copy the LIVE database and all its contents into the REPORTS database everyday at a specific time frame between 1 am and 5 am.
How could I do this automatically?
1
-
Why not replicate to a reports machine - unless you have the dbs on different disks?– VéraceNov 4, 2015 at 13:04
Add a comment
|
2 Answers
You can setup second mysql instance with replication. If you need to "copy" only during certain time period, you may choose to cron a script which will start and stop replication according to the time. Again you can only choose to replicate-do-db certain databases as well.
The following command should do the trick:
mysqldump --create-options --complete-insert -u *username* -p*password* LIVE > mysql -u *username* -p*password* REPORTS
If you are using Linux, you can add a line to /etc/crontab as follows:
# m h dom mon dow user command
* 1 * * * root mysqldump --create-options --complete-insert -u *username* -p*password* LIVE > mysql -u *username* -p*password* REPORTS
