Bash: mysqldump before and after to compare Database content during verification of n

veryangryman

Im testing a method of checking that our DB doesn't lose or gain anything new with software changes by dumping the DB before and after and diffing the outputs. As a practice, i simply create a software snapshot with zero changes so that the expected output is no differences.

First we dump the empty DB

sudo mysqldump statsdb > /var/tmp/emptyStatsDb

I then process a data file against latest version of our software

mysqldump statsdb > /var/tmp/statsdbBefore

Empty the Database

mysql statsdb < /var/tmp/emptyStatsDb 

Then we process the SNAPSHOT version with proposed changes before dumping the DB again

mysqldump statsdb > /var/tmp/statsdbAfter

We then diff the 2 files.

diff /var/tmp/statsdbBefore /var/tmp/statsdbAfter 

However, i get many, many differences reported even when doing no code changes. I'm not sure why. Are my commands correct for what i'm doing.

cython

Not sure about the commands specifically, but might help if you can classify the nature of the changes reported by your diff?

As far as structural changes go, have you looked at a schema compare in MySQL workbench? While I've not used it before myself (we're Oracle mainly where I work), it looks like it might get you part of the way there, and even looks like you could compare the current DB against a previous SQL export.

As for comparing the data, that's obviously trickier. Are you using an inbuilt upgrade mechanism that you're concerned about potentially unknown changes, or what's the driver behind this, so to speak?

MyPeopleDrankTheSoup

you'd have to post more details of the differences, but one diff that will always be there is the dump date! use the --skip-dump-date switch

BrokenArrows

Also the dump can't specify in what order tables or lines are dumped. There is no guarantee it will be the same.