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.

When running a repair table on a corrupted or damaged table, will this guarantee replication to break?

Documentation says "in the event that a table on the master becomes damaged and you use REPAIR TABLE to repair it, you should first stop replication (if it is still running) before using REPAIR TABLE, then afterward compare the master's and slave's copies " ..

Questions

  • Is this a simple STOP SLAVE; on the replica?
  • If so, wouldn't the binary logs just send that back once slave has started again?

tia

share|improve this question
add comment

1 Answer 1

I think this advice is more a safety suggestion than a requirement. Further, if a table is corrupted/damaged such than it needs repair ran on it that means something that wasn't supposed to happen happened. The slave very well may be okay and not need to go through the expensive table rebuild.

While it is true a slave will execute anything written to the binlogs after it was stopped once restarted, you can simply prevent certain statements from even being written to the binlog in the first place.

If you run

set sql_log_bin=0;

then anything you execute in that session thereafter will not be written to the binlog; will never be executed on the slave.

Edit

After repairing a table on the master you'll want to checksum it. If you can afford to have your master table offline for a period beyond the repair you can just run

checksum table ;

If you need to get back in action quickly you should look to using pt-table-checksum from the Percona Toolkit. This might also be something to consider over a straight checksum even if the table isn't being actively used.

Here's why. pt-table-checksum will check your table in chunks. You can read the details on exactly how it operates in the docs. In short it will check parts of the table, for example, IDS 1-1000, 1001-2000, etc. This is meant to server as an anti-lock braking system if you will for live tables. A portion is checksumed then the lock on the table or rows being checked is released, allowing other live traffic waiting to make some progress in between chunks.

Further, even if extended locking is not a concern it gives you insight to what parts of the tables differ. Maybe it turns out you only need to synch up a small segment of rows instead of copying for gigabytes of data.

The percona toolkit also includes pt-table-sync to resolve such conflicts.

share|improve this answer
    
Nice writeup, +1 !!! –  RolandoMySQLDBA Apr 16 '12 at 16:14
add comment

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.