1

I have a database called LIVE and I want to create a scenario were I copy the LIVE database every night at 2:30 am. I want the copied database to be called REPORT. So every night I want the REPORT database to be overwritten by the content of the LIVE database. I found a way to do this by running a cron job with mysqldbcopy.

30 2 * * * mysqldbcopy --source=user:pwd@localhost --destination=user:pwd@LIVE:REPORT

I need to know if the code above when executed by cron will overwrite REPORT with the LIVE database every night at 2.30 because I am confused by the copy statement. Copy seems like a one time thing. So will the code above :

Create a database called REPORT Copy LIVE database into REPORT Overwrite REPORT the next day with the latest copy of LIVE and continuously do so. If my code above does not do that can someone adjust it to do that please.

1
30 2 * * * mysqldbcopy --source=user:pwd@localhost --destination=user:pwd@LIVE:REPORT

I want to do the same thing and about to test for myself in next week or so.

  1. are you sure @LIVE:REPORT is correct?
    LIVE:REPORT try remove @
  2. try --drop-first ( old way was --force < 1.4.2 )

So:

30 2 * * * mysqldbcopy --source=user:pwd@localhost --destination=user:pwd  LIVE:REPORT --drop-first

This is what I am going to test. I am admittedly a noob - fair warning.


After thinking I may tweak script a little.

0 12 * * * mysqldbcopy --source=user:pass@host:port:socket --destination=user:pass@host:port:socket orig_db:new_db --drop-first
  1. I will most likely make cred.cnf file to call with credentials for both source and destination.

cred.cnf file like this (mine hidden and locked down)...

[source]
port=3306
user=user
password=secret
host=pathtodb

[destination]
port=3306
user=user
password=secret
host=pathtodb

This is how my testing cron job will look...

0 12 * * * mysqldbcopy --source=/path/to/cred.cnf[source] --destination=/path/to/cred.cnf[destination] orig_db:new_db --drop-first

good luck. I will follow-up when I get time to test my system and confirm this works or not.

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.