Checksum of data imported from mysqldump not equal to the original checksum?

Question

I'm tring to create a mysql replication instance. So I mysqldump --complete-insert --skip-dump-date -uuser -ppass db table > master.sql.

Also I've imported the master.sql to the slave instance without starting slave. But now I find the checksums not the same.

Dumped with the same mysqldump options, diff master.sql slave.sql only prints out:

5c5
< -- Server version 5.6.30-0ubuntu0.14.04.1-log
---
> -- Server version 5.6.30-0ubuntu0.14.04.1

Any ideas? Why the checksums not equal?

Thanks in advance.

Answer 1

This is age-old scenario I have dealt with before.

Would you believe there are times when the checksum of table when dumped and reload on another server simply does not match the checksum of the source DB, regardless how the checksum was evaluated and no matter how you correct it ?

Three(3) things can alter the checksum values of tables between source and target DB

• Order table rows were loaded
• ChunckSize used by pt-table-checksum
• VARCHAR byte alignment

Rather than reinventing the wheel, please read me earlier posts

• Nov 16, 2012 : Maatkit shows the MySQL replication error in one table, but won't fix it
• Mar 14, 2014 : Native checksum table says there's a difference, pt-table-checksum does not

Answer 2

In the file slave.sql, modify the top line, delete -log. Then re-diff your files. If that's the only difference (and it appears to be), then there's no problem - the data part of your mysqldump is identical! (The small "difference" appears to be in the header generated by mysqldump).

A checksum will vary greatly even if the file contents differ by only one bit! A discrepancy of 4 characters is, to all intents and purposes, enormous in this context.

From here:

Depending on its design goals, a good checksum algorithm will usually output a significantly different value, even for small changes made to the input. This is especially true of cryptographic hash functions, which may be used to detect many data corruption errors and verify overall data integrity;

(and the really important bit)

if the computed checksum for the current data input matches the stored value of a previously computed checksum, there is a very high probability the data has not been accidentally altered or corrupted.

This is a facinating topic all in its own right and the maths gets very difficult very quickly. Just one thing worthy of note is the "Birthday attack".

This means that even if you have matching checksums, there is a small (in reality, infinitesimal) possibility that your data does not match. Day-today, this is not a problem - included for completeness.