Comparing two databases with mysqldump?

Question

I have two mysql databases which should be identical to each-other. One was made by an old version of a script and another was made a new refactored version.

I need to show that these databases are identical.

I had the idea of getting a mysqldump for each one and comparing them with diff... but apparently it's not so simple.

It seems the order of the inserts in each mysqldump differs such that both files come out completely different from each-other (which they are not and it is easily discernable that the data is just showing up on different lines).

I am looking for a way to either order the output of mysqldump or use another method to show that these two databases are exactly the same (or if not point out the differences).

Thank you for any help with this.

I have tried following this: http://2skies.com/docs/Comparing_mysql_tables_with_mysqldump_and_diff but the results are ordered differently. — chaimp, Jan 26 '11 at 07:25
Have you checked this thread: http://stackoverflow.com/questions/225772/compare-two-mysql-databases — hade, Jan 26 '11 at 07:29
mysqldiff appears to only compare structure (not content), but thanks — chaimp, Jan 26 '11 at 08:14

score 2 · Accepted Answer · answered Jan 26 '11 at 07:26

2

There is the --compatible flag that could help you doc here.

You will assign it the mysql lowest version, for example:

mysqldump --compatible=mysql40

so your output should be the same for both version (assuming they are greater than 4.0, otherwise use mysql323).

answered Jan 26 '11 at 07:26

Simone

11,655
1
30
43

Tried this and it does not make a difference. I am not trying with one table which appears to be identical when I view the sorted data, however 29709 lines are found in the diff output... Thank you for your answer though. – chaimp Jan 26 '11 at 08:14
2

@jeffp identical "sorted data" doesn't mean that data are phisically stored in the same way. You could try adding the `--order-by-primary` option flag. – Simone Jan 26 '11 at 08:25

score 0 · Answer 2 · answered Nov 14 '11 at 15:12

0

I'm using Meld to compare dumps. Works well.

answered Nov 14 '11 at 15:12

Fedir RYKHTIK

9,844
6
58
68

Comparing two databases with mysqldump?

2 Answers2