Compare two MySQL databases, on command-line, with a free tool

Question

I would like to generate diff-like SQL files to compare the DATA and the STRUCTURE of two MySQL databases.

These resulting files would be executed as SQL queries, on command-line.

There is various old threads related on SO, and on various forums, but they deal with non-free tool with user interface (not command-line tools).

Also, the database should not be synced directly, but only by executing the resulting diff SQL script.

Answer 1

The following will have a go at showing you the differences (the first part of your question) but the output won't really be useable as patch files.

To compare two tables:

 mysql -u whatever -e "describe table" database1 > file1.txt
 mysql -u whatever -e "describe table" database2 > file2.txt
 diff file1.txt file2.txt

To compare data:

 mysql -u whatever -e "select * from table" database1 > file1.txt
 mysql -u whatever -e "select * from table" database2 > file2.txt
 diff file1.txt file2.txt

To compare databases:

 mysqldump --extended-insert=FALSE -u whatever database1 > file1.txt
 mysqldump --extended-insert=FALSE -u whatever database2 > file2.txt
 diff file1.txt file2.txt

Answer 2

Check out the open-source Percona Toolkit ---specifically, the pt-table-sync utility. It uses checksums on indexes and other strategies to diff tables fast. Its primary purpose is syncing replicas, but with a little extra work, it's a great diff tool. See my full answer about it here.

EDIT: I forgot to mention that comparison of the structure is a different beast. I do this with a stored procedure that you can call from the command line, but it may not be what you're looking for.

Here's a snippet from a shell-script wrapper that I wrote for showing schema diffs:

mysql ${MYSQL_CNF_OPTION} -u ${DB_USER} \
-e "USE sys; CALL compareDBs('${DBDIFF_LOCAL_DB1}','${DBDIFF_LOCAL_DB2}');"

It calls the compareDBs stored procedure, which I got from the Artful Software query archive page.

Answer 3

Tools for schema compare are more common. There are some perl packages for this which can easily be run from command line. I also found one for data diff (which is quite a complex problem!), but it's kind of old and I am not sure how good it is.

Schema compare: http://adamspiers.org/computing/mysqldiff/

Data Compare: http://rossbeyer.net/software/mysql_coldiff/