When you cannot replicate from master M to slave S

April 28, 2014

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.

tags:
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

8 Comments to "When you cannot replicate from master M to slave S"

  1. Richard Bensley wrote:

    Ensure the user account setup for replication has the 'REPLICATION SLAVE' privilege for fetching events from M.

    It is often mistaken with 'REPLICATION CLIENT' which is only used for 'SHOW MASTER STATUS' and 'SHOW SLAVE STATUS'.

  2. Abdel-Mawla wrote:

    Hi,

    If you are talking about general conditions where you can not establish the replication, what about firewall restrictions or slave MySQL user privileges problem ?

  3. shlomi wrote:

    Thanks both; the above discusses configuration issues, and not a general replication HOWTO. It is of interest when moving slaves across the replication topology.

    Firewall/network are all valid and quite outside the scope of this post :)

  4. Mark Leith wrote:

    In 5.6+ you should also ensure each instance has a unique server_uuid (auto.cnf in datadir, FS clones can sometimes copy it), especially with GTID enabled instances.

  5. Jean-François Gagné wrote:

    M is GTID_MODE=ON and S is GTID_MODE=OFF (or the other way around).

  6. Daniël van Eeden wrote:

    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.

  7. Ankit wrote:

    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?

  8. shlomi wrote:

    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 Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org