| Bug #98736 | Not able to add replication delay on Slave server | ||
|---|---|---|---|
| Submitted: | 25 Feb 2020 12:36 | Modified: | 2 Mar 2020 12:28 |
| Reporter: | pon suresh pandian soundara rajan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 8.0.19 | OS: | CentOS (7.4 ) |
| Assigned to: | MySQL Verification Team | CPU Architecture: | x86 (CentOS Linux release 7.4.1708 (Core) ) |
| Tags: | Not able to add replication delay | ||
[26 Feb 2020 5:03]
MySQL Verification Team
Hi, You are using 8.0, please look at https://dev.mysql.com/doc/refman/8.0/en/replication-delayed.html What do immediate_commit_timestamp and original_commit_timestamp show when you set a delay on the slave.
[27 Feb 2020 10:46]
pon suresh pandian soundara rajan
Hi Bogdan Kecman, Here i have mentioned the immediate_commit_timestamp and original_commit_timestamp values after setting the sleep delay on slave server.Kindly refer the below logs Logs: ----- ### INSERT INTO `accounts`.`test` ### SET ### @1='c' /* STRING(4) meta=65028 nullable=1 is_null=0 */ # at 1208142 #200225 17:41:07 server id 38 end_log_pos 1208173 CRC32 0x209f8da5 Xid = 33604 COMMIT/*!*/; # at 1208173 #200225 17:41:11 server id 38 end_log_pos 1208259 CRC32 0x2eb5bbb0 GTID last_committed=79 sequence_number=80 rbr_only=yes original_committed_timestamp=1582632671614785 immediate_commit_timestamp=1582795594995564 transaction_length=287 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1582632671614785 (2020-02-25 17:41:11.614785 IST) # immediate_commit_timestamp=1582795594995564 (2020-02-27 14:56:34.995564 IST) /*!80001 SET @@session.original_commit_timestamp=1582632671614785*//*!*/;
[28 Feb 2020 1:13]
MySQL Verification Team
Hi,
Sorry but I cannot reproduce a problem:
MASTER:
master [localhost:20920] {msandbox} (test) > select * from t1;
+----+------+
| id | x |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.00 sec)
master [localhost:20920] {msandbox} (test) > select now(); insert into t1 (x) values (10);
+---------------------+
| now() |
+---------------------+
| 2020-02-28 02:10:38 |
+---------------------+
1 row in set (0.00 sec)
Query OK, 1 row affected (0.00 sec)
master [localhost:20920] {msandbox} (test) >
SLAVE:
slave1 [localhost:20921] {msandbox} (test) > stop slave;
Query OK, 0 rows affected (0.00 sec)
slave1 [localhost:20921] {msandbox} (test) > change master to MASTER_DELAY=600;
Query OK, 0 rows affected (0.01 sec)
slave1 [localhost:20921] {msandbox} (test) > start slave;
Query OK, 0 rows affected (0.00 sec)
slave1 [localhost:20921] {msandbox} (test) > show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rsandbox
Master_Port: 20920
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 8597
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 8597
Relay_Log_Space: 526
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 00020920-1111-1111-1111-111111111111
Master_Info_File: mysql.slave_master_info
SQL_Delay: 600
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace:
1 row in set (0.00 sec)
slave1 [localhost:20921] {msandbox} (test) > select now();
+---------------------+
| now() |
+---------------------+
| 2020-02-28 02:10:44 |
+---------------------+
1 row in set (0.00 sec)
slave1 [localhost:20921] {msandbox} (test) > select * from t1;
+----+------+
| id | x |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.00 sec)
slave1 [localhost:20921] {msandbox} (test) > show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rsandbox
Master_Port: 20920
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 8875
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 8597
Relay_Log_Space: 804
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 18
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 00020920-1111-1111-1111-111111111111
Master_Info_File: mysql.slave_master_info
SQL_Delay: 600
SQL_Remaining_Delay: 583
Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace:
1 row in set (0.00 sec)
[28 Feb 2020 1:14]
MySQL Verification Team
As you can see:
SQL_Delay: 600
SQL_Remaining_Delay: 583
is properly set and the data is not replicated because it is delayed
[29 Feb 2020 11:12]
pon suresh pandian soundara rajan
Hi Bogdan Kecman, Thanks for your comment.I have checked this issue on different server. This issue was is reproduce.Please close this bug.
Description: Hi Oracle Team, I have one master & slave server.I am trying to set the 900 sec of replication delay on my slave server. I have followed the below steps.But it's not working. MySQL Version - Master - 8.0.19 MySQL Community Server - GPL Slave - 8.0.19 MySQL Community Server - GPL Test Scenario : -------------- Currently there is no delay was added in replication : Logs: ------ root@localhost:(none)>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: XXXXX Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000204 Read_Master_Log_Pos: 794296731 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 12387231 Relay_Master_Log_File: mysql-bin.000204 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 794296731 Relay_Log_Space: 12387439 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 38 Master_UUID: 8aa25f1c-64e1-11e9-8809-0681252b46ef Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 8aa25f1c-64e1-11e9-8809-0681252b46ef:767740-768366 Executed_Gtid_Set: 8aa25f1c-64e1-11e9-8809-0681252b46ef:1-768366, c2fb717a-802d-11e9-b875-0a85cfda68d0:1-11737 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: Now i am adding the 900 sec delay on my slave. Logs: ----- root@localhost:(none)>stop slave;change master to master_delay=900;start slave;show slave status\G Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.18 sec) Query OK, 0 rows affected (0.00 sec) root@localhost:(none)>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: XXXXX Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000204 Read_Master_Log_Pos: 794296731 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 416 Relay_Master_Log_File: mysql-bin.000204 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 794296731 Relay_Log_Space: 624 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 38 Master_UUID: 8aa25f1c-64e1-11e9-8809-0681252b46ef Master_Info_File: mysql.slave_master_info SQL_Delay: 900 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 8aa25f1c-64e1-11e9-8809-0681252b46ef:1-768366, c2fb717a-802d-11e9-b875-0a85cfda68d0:1-11737 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.00 sec) Now I am inserting some data on my master server. In Master Server : ----------------- root@localhost:accounts>show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `col1` char(1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) root@localhost:accounts>insert into test values ('c'); Query OK, 1 row affected (0.00 sec) root@localhost:accounts>insert into test values ('d'); Query OK, 1 row affected (0.00 sec) root@localhost:accounts>insert into test values ('f'); Query OK, 1 row affected (0.00 sec) root@localhost:accounts>select count(*) from test; +----------+ | count(*) | +----------+ | 26 | +----------+ 1 row in set (0.01 sec) In slave server : ---------------- root@localhost:accounts>select count(*) from test; +----------+ | count(*) | +----------+ | 26 | +----------+ 1 row in set (0.01 sec) How to repeat: Test Scenario : -------------- Currently there is no delay was added in replication : Logs: ------ root@localhost:(none)>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: XXXXX Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000204 Read_Master_Log_Pos: 794296731 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 12387231 Relay_Master_Log_File: mysql-bin.000204 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 794296731 Relay_Log_Space: 12387439 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 38 Master_UUID: 8aa25f1c-64e1-11e9-8809-0681252b46ef Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 8aa25f1c-64e1-11e9-8809-0681252b46ef:767740-768366 Executed_Gtid_Set: 8aa25f1c-64e1-11e9-8809-0681252b46ef:1-768366, c2fb717a-802d-11e9-b875-0a85cfda68d0:1-11737 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: Now i am adding the 900 sec delay on my slave. Logs: ----- root@localhost:(none)>stop slave;change master to master_delay=900;start slave;show slave status\G Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.18 sec) Query OK, 0 rows affected (0.00 sec) root@localhost:(none)>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: XXXXX Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000204 Read_Master_Log_Pos: 794296731 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 416 Relay_Master_Log_File: mysql-bin.000204 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 794296731 Relay_Log_Space: 624 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 38 Master_UUID: 8aa25f1c-64e1-11e9-8809-0681252b46ef Master_Info_File: mysql.slave_master_info SQL_Delay: 900 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 8aa25f1c-64e1-11e9-8809-0681252b46ef:1-768366, c2fb717a-802d-11e9-b875-0a85cfda68d0:1-11737 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.00 sec) Now I am inserting some data on my master server. In Master Server : ----------------- root@localhost:accounts>show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `col1` char(1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) root@localhost:accounts>insert into test values ('c'); Query OK, 1 row affected (0.00 sec) root@localhost:accounts>insert into test values ('d'); Query OK, 1 row affected (0.00 sec) root@localhost:accounts>insert into test values ('f'); Query OK, 1 row affected (0.00 sec) root@localhost:accounts>select count(*) from test; +----------+ | count(*) | +----------+ | 26 | +----------+ 1 row in set (0.01 sec) In slave server : ---------------- root@localhost:accounts>select count(*) from test; +----------+ | count(*) | +----------+ | 26 | +----------+ 1 row in set (0.01 sec)