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.
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’.
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 ?
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 🙂
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.
M is GTID_MODE=ON and S is GTID_MODE=OFF (or the other way around).
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.
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?
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.