Synchronize MySQL databases

[rainydays]

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?

 

[4np]

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

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

 

[rainydays]

Perhaps you can use and modify my shell script for your particular issue. Or just set up replication for MySQL instead

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.

 

[4np]

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.

 

[rainydays]

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.

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

 

[iomatic]

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!

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

 

[iomatic]

Really? No developers have this issue?