MySQL Sync

To sync tables sitting on an online MySQL server to a local MySQL server…

mysqldump -al -h [remote-host] --password=[passwd] --user=[username] 
  --add-drop-table [db-name] | mysql --user=[username] --password=[passwd]
  -h localhost [db-name]

To sync tables sitting in a local MySQL server to a remote MySQL server…

mysqldump -al --user=[username] --password=[passwd] -h localhost
  --add-drop-table [db-name] | mysql -h [remote-host] --user=[username]
  --password=[passwd] [db-name]
  • Share/Bookmark
By Mikhail Esteves | April 26th, 2004 in LAMP, Tips

4 Comments

Jon BridenbaughSeptember 19th, 2005 at 10:15 pm

I have used this script so many times over the last year it’s not even funny. I have it printed out and hanging next to my desk.

Just wanted to say thanks for putting it up and also to mention a “gotcha” for those of us that write a lot of sql at the command line:

Do NOT use a semicolon at the end of mysqldump commands.

I kept getting “Unknown database ‘foo;’ errors and only after about 10 attempts did I realize my mistake.

dheerajJuly 7th, 2006 at 12:19 pm

thanks a lot for giving me the solution..i just run it for a sample database ..its workin!!! i tried a lot and broken my head for sync solutiion… i tried many tools from net … but had some problem in runnin them …
Thanks a lot again…

jennyOctober 6th, 2007 at 2:16 pm

wow

Anthony TopperJune 10th, 2008 at 11:27 pm

Thanks for posting Mikhail. I will find these commands very useful.

Also, I took a peak at your flickr account; your photos are very nice.

Leave a comment

Your comment