Comparing database states

Quick and dirty way two compare two different states of a database using mysqldump:

$ mysqldump --order-by-primary --extended-insert=FALSE --compact --user=X --password database > before.sql
$ mysqldump --order-by-primary --extended-insert=FALSE --compact --user=X --password database > after.sql
$ diff before.sql after.sql > diff.txt

The problem I ran into the first time I tried this was that mysqldump inserts by default several rows in the same line, which makes difficult to identify what is actually new. That gets fixed with –extended-insert=FALSE. The other two options (–order-by-primary and –compact) just makes diff’s life easier and gives less clutter.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s