1

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érace
    Nov 4, 2015 at 13:04

2 Answers 2

1

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.

1

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  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.