11

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.

Cedric
  • 5,135
  • 11
  • 42
  • 61
  • Thanks for Galz and ADW for your help. Even if I am not using the solution you've propposed, I considered it anyway, so it was useful. I am diging into liquibase atm. See : http://stackoverflow.com/questions/225772/compare-two-mysql-databases which is a related issue (but broader. my issue was especially about MySQL, command line, and free tool). Check http://forum.liquibase.org/#Topic/49382000000251069 as well for an example, and see if it is a feasible solution. – Cedric Jul 15 '11 at 09:53

3 Answers3

16

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
ADW
  • 4,030
  • 17
  • 13
4

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.

Community
  • 1
  • 1
Royce Williams
  • 1,487
  • 15
  • 24
  • Thanks for sharing Royce! Didn't know [Artful Software query archive page](http://www.artfulsoftware.com/infotree/queries.php)! – antoniom Apr 13 '16 at 14:19
1

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/

Sunderam Dubey
  • 1
  • 11
  • 20
  • 40
Galz
  • 6,713
  • 4
  • 33
  • 39