Synchronize MySQL databases

Discussion in 'Mac Apps and Mac App Store' started by rainydays, Dec 11, 2008.

  1. rainydays macrumors 6502a

    Joined:
    Nov 6, 2006
    #1
    Hi!

    I've been looking for a nice way to synchronize MySQL databases in a quick and easy way on the Mac, but so far I haven't found any good options.

    Since I'm doing my development locally on both my iMac and my MacBook I'd like to be able to sync the site I'm working on with a click of a button.
    There are very nice file syncing tools available (I'm currently using Synk Standard), but as far as MySQL syncing (two-way) goes the only one I've found is Navicat.

    While Navicat works great they have for some reason not made it possible to access from the command line in the mac version, which means that I can't run the db sync automatically when I run the file sync.
    I've tried UI scripting but it just won't work reliably (it clicks in the wrong places all of the time).

    So do you know about any good MySQL syncing tools, that sync in both directions and are scriptable?
     
  2. 4np macrumors 6502a

    4np

    Joined:
    Feb 23, 2005
    Location:
    The Netherlands
    #2
    I've created a shell script to synchronise my blog back to my macbook pro, including both the PHP scripts as well as the MySQL database content. I use this mainly for backup purposes or to add and test new functionality locally. Note that this does NOT synchronise from my MBP (dev) to my live blog :) And be careful with the rm -rf part ;)

    Code:
    4np@mymbp ~/Workspace/blog $ cat live-to-dev.sh 
    #!/bin/sh
    # define work vars
    DATE=`date "+%Y%m%d_%H%M%S"`
    
    # start synchronisation
    echo '.removing all scripts'
    rm -rf htdocs > /dev/null
    echo '.fetching all live files'
    scp -r USERNAME@LIVESERVER:/path/to/htdocs . > /dev/null
    echo '.fetching live database content'
    ssh USERNAME@LIVESERVER mysqldump -u MYSQLUSER --password=MYSQLPASSWORD DATABASENAME > database-$DATE.sql
    tar -czf database-$DATE.tgz database-$DATE.sql
    rm database-$DATE.sql
    echo '.import and transform live database content'
    echo '	-changing mydomain.com into dev.mydomain.com'
    tar -zxOf database-$DATE.tgz | \
    	sed 's/mydomain.com/dev.mydomain.com/g' | \
    	/Applications/MAMP/Library/bin/mysql -u WordPress --password=MYLOCALPASSWORD -h localhost WordPress
    echo '.settings directories writable'
    chmod -R gou+rwx ./htdocs/wp-content/uploads/
    echo '.done...'
    
    4np@mymbp ~/Workspace/blog $ 
    
    
    Perhaps you can use and modify my shell script for your particular issue. Or just set up replication for MySQL instead :)
     
  3. rainydays thread starter macrumors 6502a

    Joined:
    Nov 6, 2006
    #3
    Thanks! That's not really what I'm looking for though. I'd prefer a two way sync, one that lets me quickly solve any conflicts (preferably with a gui) as well.

    I guess I could use two one-way scripts if I remember to always sync before I do anything. I think Maatkit (which includes a syncing tool) has been ported to the mac, so I think I might give that one a try.

    As far as I understand it, replication only works in one direction, so it seems pretty useless in this situation.
     
  4. 4np macrumors 6502a

    4np

    Joined:
    Feb 23, 2005
    Location:
    The Netherlands
    #4
    Have you considered just using one MySQL database instead? Then you don't have this synchronising issue :) And filewise, perhaps you could use a versioning system like subversion.
     
  5. rainydays thread starter macrumors 6502a

    Joined:
    Nov 6, 2006
    #5
    Yeah, I did that for a while. But the problem is that I had to run the database on my MacBook since I don't always have an internet connection when traveling. So I had to keep the MB running even when I was working with the iMac.

    I use SVN for some projects, but for most of what I do it's not needed, and Synk works great for files.
    I guess I will have to keep using Navicat, even though it would be nice if I could run the MySQL sync at the same time as the file sync without having to do it manually.

    Oh well, it could be worse :)
     
  6. iomatic macrumors regular

    Joined:
    Mar 25, 2004
    #6
    Digging this up. Is there a way to point to the remote WordPress DB so that local development can occur for themes, etc., but have the most current "live" data? I've already figured out a way to (tediously) download the db and restore it on the local dev. machine, naturally.

    Thanks!


     
  7. iomatic macrumors regular

    Joined:
    Mar 25, 2004

Share This Page