Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have few questions regarding consistency in Mysql in two scenarios

1th scenario We have two hosts with the same database A and B (the same data, different copy) if there any way to check if the data is really the same. Maybe kind of md5 hash on table would help. If there are existing solutions? Of course it's not complicated to do something by myself, kind of validator.

2th scenario Let's say we have very strict logical schema for database. I need to check that our DB works according to schema. Let's say every Order has ProductId and etc. It sounds much more complicated, if you have faced these problem, how you deal with them.

Thanks!

share|improve this question

1 Answer 1

up vote 3 down vote accepted

Run CHECKSUM TABLE command against a table.

There is a caveat for this. According to the MySQL Documentation on CHECKSUM TABLE:

If the checksums for two tables are different, then it is almost certain that the tables are different in some way. However, because the hashing function used by CHECKSUM TABLE is not guaranteed to be collision-free, there is a slight chance that two tables which are not identical can produce the same checksum.

OK, CHECKSUM TABLE is 99.99%, not 100%.

How can you aggressively check all the items of a table for matches and mismatches? You can actually run a great tool from Percona Tools called pt-table-sync. It will check for differences in tables between Master and Slave, as long as the table structure is identical.

For example, to sync the table db.tb1 on a Slave in relation to its Master, run pt-table-sync as follows:

pt-table-sync --print --sync-to-master h=SlaveIP,D=db,t=tbl > SQLtoFixDifferences.sql

When you run this, the table db.tb1 on the Slave is compared to that of its Master using the Primary Key (or Unique Key is there is no Primary Key) of the Slave's db.tb1. The output will be a series of SQL commands, usually REPLACE INTO and DELETE FROM. The script SQLtoFixDifferences.sql is to be executed on the Slave. When done, the data in db.tb1 should be identical to its counterpart on the Master.

To test it then, run these commands:

CHECKSUM TABLE db.tb1;
SELECT COUNT(1) FROM db.tb1;

The results should be identical on Master and Slave.

CAVEAT

I have never used pt-table-sync. I have been using mk-table-sync (MAATKIT tool from Percona) for years and I know it has always worked for me. I fully trust Percona's pt-table-sync is at the least the same, if not superior, quality.

share|improve this answer
    
thank you very much for a complete answer! The aggressively check can be done by exhaustive comparison between master and slave, row by row, which is of course doesn't effective as your solution, thanks again! –  com Jan 17 '12 at 19:43
2  
pt-table-sync is the same codebase as mk-table-sync, except that we've been maintaining it and fixing bugs, so pt-table-sync is actually better quality. –  Baron Schwartz Jan 17 '12 at 22:26
    
@Baron I should just go head first and try it out. That way, I can reflect confidence in my answers based on having used it. –  RolandoMySQLDBA Jan 17 '12 at 23:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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