When you cannot replicate from master M to slave S

Working on some replication topology automation, here are some rules that will prevent you from replicating from a MySQL server M to a slave S:

  • M does not have binary logs (log_bin) enabled
  • M is itself a slave and does not have log_slave_updates enabled
  • M has a higher major version than S (e.g. M is 5.6 and S is 5.5, but do note log_bin_use_v1_row_events)
  • Both servers have same server_id
  • S has log_bin & log_slave_updates, uses STATEMENT binlog_format, and M uses ROW or MIXED binlog_format
  • S has log_bin & log_slave_updates, uses MIXED binlog_format, and M uses ROW binlog_format
  • S and M are using different replication filters (some rules could work; good luck if you’re that adventurous)

[EDIT: the above is configuration-wise]

Did I miss anything? Please comment below.

8 thoughts on “When you cannot replicate from master M to slave S

  1. Binlog checksums will break replication from 5.6 to 5.5 and the same might be true for binlog-rows-query-log-events. See also http://bugs.mysql.com/bug.php?id=72522

    And some non-config issues:
    Non-upgraded timestamps can be an issue.
    http://bugs.mysql.com/bug.php?id=70124

    And a different set of storage engines between the slave and server can also cause some issues.

    And if SSL is used there can be issues if one is using OpenSSL and the other is using YaSSL. And in the same category are all related ‘client’ issues like support for the authentication plugin etc.

  2. Can you explain the point:

    –S has log_bin & log_slave_updates, uses MIXED binlog_format, and M uses ROW binlog_format

    Slave being on Mixed mode should accept either format? Why should Master be not ROW binlog_format?

  3. If your master has ROW format, and your slave has log_slave_updates but uses STATEMENT format, it will refuse to replicate, since it can’t translate ROW back to STATEMENT. STATEMENT to ROW is fine. STATEMENT to MIXED is fine. MIXED to ROW is fine. ROW to MIXED is disallowed.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.