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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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