How can I make an automatic copy of a MySQL database to another MySQL database on the same server?

Question

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 manually and automatically?

this question would probably get better answers at Database Administrators. — vhu
– vhu, Commented Nov 4, 2015 at 11:20
Thanks. This is to make a manual copy. Can an automatic copy be done? — tase akpobome
– tase akpobome, Commented Nov 4, 2015 at 11:25
Sure, just use cron or other task scheduler available on your platform. For simple data copy you might even get away with MySQL events — vhu
– vhu, Commented Nov 4, 2015 at 11:26

Alexander · Accepted Answer · 2015-11-04 11:46:36Z

1

Assuming you are running a unix like OS , you can run a cron job with mysqldbcopy:

crontab -e

Add your job :

30 2 * * * mysqldbcopy --source=user:pwd@localhost --destination=user:pwd@localhost source_database:cloned_database

This will run every day at 2:30 AM.

Note: Storing credentials in a crontab can result in them leaking to logs, mail, etc ...

answered Nov 4, 2015 at 11:41

Alexander

12.8k5 gold badges62 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1