Skip to content

# When you cannot replicate from master M to slave S

MySQL Replication April 28, 2014April 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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
8
Leave a Reply

7 Comment threads
1 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
7 Comment authors
Recent comment authors

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

Subscribe
Notify of
Guest
Richard Bensley

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’.

Guest
Abdel-Mawla

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 ?

Author
shlomi

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 🙂

Guest
Mark Leith

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.

Guest
Jean-François Gagné

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

Guest
Daniël van Eeden

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.

Guest
Ankit

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?

Author
shlomi

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.

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.