MySQL Replication - transactions missing in M->S1->S2 setup

Question

I have MySQL chained replication setup as Master->Slave1->Slave2.

Events are replicating perfectly fine from M->S1 and also recorded into the Relay & Binary logs on S1 but then S2 doesn't receive these events.

SHOW SLAVE STATUS on S2 is showing both IO & SQL threads running, Exec_Master_Log_Pos is constantly changing but Relay_Log_Space & Relay_Log_Pos stay static.

Master is MySQL 5.5, S1 & S2 are both MySQL 5.6.40.

Interesting that if any updates are done directly on S1 then they replicate across to S2 correctly.

I have checked disk space isn't full on any of the servers.

Have also looked and put a comment on Mysql replication is enabled but stuck at "waiting for binlog to be updated"

On S1 I have:

mysql> show global variables like '%slave%';
+------------------------------+-----------------------+
| Variable_name                | Value                 |
+------------------------------+-----------------------+
| init_slave                   |                       |
| log_slave_updates            | ON                    |
| log_slow_slave_statements    | OFF                   |
| rpl_stop_slave_timeout       | 31536000              |
| slave_allow_batching         | OFF                   |
| slave_checkpoint_group       | 512                   |
| slave_checkpoint_period      | 300                   |
| slave_compressed_protocol    | OFF                   |
| slave_exec_mode              | STRICT                |
| slave_load_tmpdir            | /tmp                  |
| slave_max_allowed_packet     | 1073741824            |
| slave_net_timeout            | 3600                  |
| slave_parallel_workers       | 0                     |
| slave_pending_jobs_size_max  | 16777216              |
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
| slave_skip_errors            | OFF                   |
| slave_sql_verify_checksum    | ON                    |
| slave_transaction_retries    | 10                    |
| slave_type_conversions       |                       |
| sql_slave_skip_counter       | 0                     |
+------------------------------+-----------------------+

On M:
 - SELECT @@global.server_id=6
 - SELECT @@global.server_uuid=05ac0062-7af8-11e5-9af2-9c8e991000d2

On S1:
 - SELECT @@global.server_id=4
 - SELECT @@global.server_uuid=062ad58e-59ed-11e8-a7ea-000d3a32adb2

On S2:
 - SELECT @@global.server_id=6
 - SELECT @@global.server_uuid=184b1c69-7868-11e8-aeab-000d3a321333

Answer 1

The problem stems from log_slave_updates. You need that in S1 so the binary logs of S1 can be the source for S2 reading events.

Please add this line to my.cnf in S1

[mysqld]
log-slave-updates

and restart mysqld on S1. Then, setup replication from S1 to S2. S2 is currently out of sync with S1, so S2 may need to be reloaded or sync'd up using pt-table-checksum and pt-table-sync.

UPDATE 2018-06-25 12:23 EDT

Thank you for the update,

Here is your problem:

M1 and S2 have the same server_id.

S2 will never process an event if the incoming event's server_id is the same as S2.

Change the server_id on S2 to something other than 4 or 6 used by M and S1.