Synchronize two databases schema in MySQL

Question

I was looking for a portable script or command line program that can synchronize two MySQL databases schema. I am not looking for a GUI based solution because that can't be automated or run with the buid/deployment tool.

Basically what it should do is scan database1 and database2. Check the schema difference (tables and indexes) and propose a bunch of SQL statements to run on one so that it gets the similiar structure of the other minimizing data damage as much as possible.

If someone can indicate a PHP, Python or Ruby package where this type of solution is implemented, I can try to copy the code from there.

A lot of MySQL GUI tools probably can do this, but I am looking for a scriptable solution.

Edit: Sorry for not being more clear: What I am looking for is synchronization in table structure while keeping data intact as far as possible. Not data replication.

More info:

Why replication won't work.

1. The installation bases are spread around the state.
2. We want the installer to perform dynamic fixes on the DB based on chagnes made in the latest version, regardless of what older version the end user might be using.
3. Changes are mostly like adding new column to a tables, creating new indexes, or dropping indexes, adding tables or dropping tables used by the system internally (we don't drop user data table).

If it's a GUI: No it can't be used. We don't want to bunddle a 20MB app with our installer just for DB diff. Specially when the original installer is less than 1 MB.

Drug

+1 Excellent question. I was going to ask it because I am looking for the same solution... but found this. – Grandioso

Answer 1

Have you considered using MySQL replication ?

Argive

I suspect the OP is looking for something as simple as possible to just replicate the schema. I guess MySql replication could do that, but its not exactly simple to set up. – Christianna

Replication is a lot easier to set up (if you have the rights) then to write what would be a SQL diff tool. – Therapeutic

It appears this requires data duplication also, per ...because data is replicated to the slave.... Also the PHP example provided requires a DROP DATABASE function - which is out of scope for the question. – Grandioso

Answer 2

SQLyog does that and it is awesome. We use it in production often.

Irfan

I agree, excellent tool. However, it doesn't work on most of my webservers due to port blocking. – Grandioso

@Talvi Watia, They provide a PHP proxy for such cases. – Irfan

Answer 3

I know it's an old question but it was the first result on google for what I was searching for (exact same thing as the initial question)

I found the answer still here but I don't remember the URL it's a script that started from:

mysqldump --skip-comments --skip-extended-insert -u root -p dbName1>file1.sql
mysqldump --skip-comments --skip-extended-insert -u root -p dbName2>file2.sql
diff file1.sql file2.sql

and ended up more like this

#!/bin/sh

echo "Usage: dbdiff [user1:pass1@dbname1] [user2:pass2@dbname2] [ignore_table1:ignore_table2...]"

dump () {
  up=${1%%@*}; user=${up%%:*}; pass=${up##*:}; dbname=${1##*@};
  mysqldump --opt --compact --skip-extended-insert -u $user -p$pass $dbname $table > $2
}

rm -f /tmp/db.diff

# Compare
up=${1%%@*}; user=${up%%:*}; pass=${up##*:}; dbname=${1##*@};
for table in `mysql -u $user -p$pass $dbname -N -e "show tables" --batch`; do
  if [ "`echo $3 | grep $table`" = "" ]; then
    echo "Comparing '$table'..."
    dump $1 /tmp/file1.sql
    dump $2 /tmp/file2.sql
    diff -up /tmp/file1.sql /tmp/file2.sql >> /tmp/db.diff
  else
    echo "Ignored '$table'..."
  fi
done
less /tmp/db.diff
rm -f /tmp/file1.sql /tmp/file2.sql

Sacristy

Answer 4

check this one is codeigniter database diff script generator

https://github.com/vaimeo/ci-database-diff-generator

Wesle

it's is a very nice solution. I had to adapt it a little to my framework, but good. – Superstar

is this still working? Can you give more details on how to use it please? – Ayannaaycock

Yes grab the code and follow the readme file from github – Wesle

Answer 5

For a long-term, professional solution, you should keep an eye on Schemamatic (http://versabanq.com/products/schemamatic.php). This link shows a GUI app but all it does is manipulate a command-line software. In this page there is link to its google code site where the C# .Net version of Schemamatic can be found. Your perfect solution would be to add support for MySQL to Schemamatic. For SQL Server, it's perfect and does exactly what you mentioned.

Now, for a short-term solution I would suggest dumping the data you want with MySQL's command-line tools like: mysqldump -A -c -uroot -ppassword >bkpmysql.sql

And play with it, although it should take quite some time to achieve what you want. Schemamatic really seems to me your best choice. Let me know if you need any clarification when/if trying Schemamatic.

Comparator

Answer 6

You might want to look at some tools such as dbdeploy (this is a java or a .net version) and liquidbase and others.

Although most of these I think will apply sets of changes to a DB in a controlled manner. Don't know if they can reverse engineer from existing schemas and compare.

E.

Chrissychrist