technical documents

Comparing mysql tables with mysqldump and diff

Posted by: Dan on July 29, 2007 09:52:23 AM +00:00
Have you ever wondered how to compare the content between two databases?

In this example, we'll export our databases to to text files. Note the "--skip-opt" argument, supplied here as MySQL 4.1 and higher enable the "--opt" options by default, resulting in smaller, faster files. Usually we want this but in this case we disable it so diff can compare line by line.

MySQL Commands:

mysqldump -u username -p --skip-opt dbname1 xar_block_instances > one.sql
mysqldump -u username -p --skip-opt dbname2 xar_block_instances > two.sql

The diff command:

diff -y --suppress-common-lines one.sql two.sql > diff.txt

This command will compare the two files and output the differences to a file called diff.txt.

Updated: 05 Apr 10 07:34