Monday, April 11, 2011

What is the best way/tool to synchronize two MySql servers (not in the same cluster).

I find myself develop apps in my home, which then, on the commercial server are filled with data.
I need a simple and easy way to dump a MySql DB from the commercial server into my private one.
Is there a tool/command to do this?
(I thought about making my DB a slave, but then, I don't always have the permissions to touch the commercial MySql server, and I do develop on my Server...which could cause problems, if they where linked that way).

From stackoverflow
  • This is how you can backup a database and reload it as well.

    On master server

    mysqldump -u[username] -p[password] databasename > database.sql
    

    Or if you need to include functions and or triggers do

    mysqldump -u[username] -p[password] --routines --triggers databasename > database.sql
    

    Copy database.sql to dev server

    On dev server

    mysql -u[username] -p[password] databasename < database.sql
    
    Suroot : Just as a note, mysqldumps can take an extended amount of time depending on the amount of table. If your outfit is 24x7 then this might not be a viable option. I have no other options for you though.
    zodeus : I Agree completely.
    Itay Moav : I will need an access to a shell, right? That is a problem, as not every hosting gives a shell access.
    zodeus : Unfortunately yes you do need a shell. If you don't have shell access I recommend SQLYog we have been using it our shop for years. I think it's around $100.
  • you might try the SQLyog Job Agent - it's getting good reviews.

0 comments:

Post a Comment