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?

  • Why not replicate to a reports machine - unless you have the dbs on different disks? – Vérace Nov 4 '15 at 13:04

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.

| improve this answer | |

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  
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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