1

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
  • I need to know three things: 1) What is the output of SELECT @@global.server_id; from each server ??? 2) What is the output of SELECT @@global.server_uuid; from each server ??? 3) Are they the same ??? – RolandoMySQLDBA Jun 25 '18 at 16:09
  • Added the required info to my question @RolandoMySQLDBA and I am guessing the Server ID = 6 on M and S2 might be the reason? – thatsaru Jun 25 '18 at 16:16
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.

  • Thanks for your reply @RolandoMySQLDBA. log-slave-updates is already there as I can confirm this via SHOW GLOBAL VARIABLES LIKE '%slave%' on S1. I used mysqlbinlog utility to read the binary logs on S1 to confirm that a transaction issued on M has actually been logged in binlog on S1. I expected that this should have replicated across to S2 but that's not happened. – thatsaru Jun 25 '18 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.