0

Say one of the tables got corrupted, will that corrupted table still get replicated to the other DB? Or, will the replication stop to prevent the other DB from being corrupted too?

Note: Question is for both master-master or master-slave setup.

  • Elaborate on what you mean by "corrupted data". Also, provide SHOW VARIABLES LIKE 'binlog_format'; And version of MySQL. – Rick James May 22 at 16:45
  • @RickJames This is only theoretical question. I'm not experiencing it right now but I've experienced a "corrupted" table before in MyISAM and was able to "repair" it via phpmyadmin only but that was not a replicated table. FWIW I'll be using InnoDB and MySQL 5.7 on the theoretical replicated db. – IMB May 22 at 17:10
1

A crash often leaves MyISAM tables in need of "REPAIR". Switch to InnoDB to avoid such.

Even then, it was rarely the "data" that was corrupted, just the index file (.MYI). The index is maintained separately on each server; replication is not involved.

Bottom line: Don't use MyISAM any more.

If the Master discovers corruption, it will halt. In this case, nothing will go to the Slave.

Replication involves sending changes (either in the form of write SQL statements or the effect of those statements), so corruption is not propagated.

If your definition of "corruption" includes a malicious DROP TABLE, that "corruption" will be propagated to the Slave.

  • Is that simply because MyISAM isn't ACID compliant? I note most of the tables in the master-master setup I inherited are MyISAM. I assumed there was a reason for that so I have left them be. Your answer suggests that is a mistake. – topshot May 22 at 19:54
  • Thanks for the answer but sorry this doesn't quite answer the main question though: Assuming InnoDB is used and table corruption still happened, will the replication activity halt to prevent the destination DB from being corrupted too? – IMB May 22 at 19:57
  • 1
    @IMB - I added some more. – Rick James May 22 at 22:40
  • @topshot - ACID is the sole reason. However, in implementing ACID, the data format is made much more robust. And much more complicated. One person implemented MyISAM. Later another person implemented InnoDB. There was probably no common code until the two products merged as separate "Engines" in version 4.1. – Rick James May 22 at 22:45
  • 1
    @topshot - True, InnoDB tables must not be manipulated in the filesystem. Here is my collection of things to consider when converting from MyISAM to InnoDB. – Rick James May 23 at 21:24

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.