1

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.

  • 1
    I think you're better off if you use pt-table-checksum instead to checksum master and slave instances. – jerichorivera Jul 19 '16 at 11:19
  • @jerichorivera forgot to say, I just imported the master.sql to slave, without starting the slave. Also, the binlog is row-based while the pt-table-checksum requires statement-based? – jasonz Jul 22 '16 at 2:21
  • 1
    pt-tc requires statement for its session only, so you can use --no-check-binlog-format option – jerichorivera Jul 22 '16 at 2:23
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

  • Thank you. The pt-table-checksum checksums of two instances are the same. I know pt-table-checksum use 'INSERT...SELECT' to calculate chunk checksums. But I don't know how mysql checksum tables. Also how can I check VARCHAR byte alighment cause the version of two mysql instances are the same. The table is MyISAM. – jasonz Jul 22 '16 at 3:05
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.