Shulou(Shulou.com)06/01 Report--

Allowing the slave nodes in the MySQL topology to delay for an appropriate amount of time can help avoid catastrophic errors on the master node.

The MASTER_DELAY attribute specifies how long SQL_THREAD will tentatively spend on the slave node, in seconds. The default value is 0 and the upper limit is 68 years.

Mysql > stop slave

Query OK, 0 rows affected (0.06 sec)

Mysql > change master to master_delay=20

Query OK, 0 rows affected (0.01 sec)

Mysql > start slave

Click (here) to collapse or open

Mysql > stop slave

Query OK, 0 rows affected (0.01 sec)

Mysql > change master to master_delay=20

Query OK, 0 rows affected (0.02 sec)

Mysql > start slave

Query OK, 0 rows affected (0.02 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.8.57

Master_User: repl

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-bin.000021

Read_Master_Log_Pos: 154

Relay_Log_File: relay-log.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000021

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: 154

Relay_Log_Space: 521

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File: / usr/local/mysql/certs/ca-cert.pem

Master_SSL_CA_Path:

Master_SSL_Cert: / usr/local/mysql/certs/client-cert.pem

Master_SSL_Cipher:

Master_SSL_Key: / usr/local/mysql/certs/client-key.pem

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: 1

Master_UUID: 9ad24233-aeef-11e7-aa1b-080027768e58

Master_Info_File: mysql.slave_master_info

SQL_Delay: 20

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:

1 row in set (0.00 sec) can see SQL_Delay: 20

Here is a test:

Truncate the test table at the master node

Click (here) to collapse or open

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | tb_admin |

| | tb_admin_bak |

+-+

2 rows in set (0.00 sec)

Mysql > truncate table tb_admin_bak

Query OK, 0 rows affected (0.02 sec) is viewing table information from the slave node

Click (here) to collapse or open

Mysql > select count (*) from tb_admin_bak

+-+

| | count (*) |

+-+

| | 1 |

+-+

1 row in set (0.02 sec)

Mysql > select count (*) from tb_admin_bak

+-+

| | count (*) |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

Mysql > show full processlist\ G

* * 1. Row *

Id: 3

User: root

Host: localhost

Db: test

Command: Query

Time: 0

State: starting

Info: show full processlist

* 2. Row * *

Id: 8

User: system user

Host:

Db: NULL

Command: Connect

Time: 334

State: Waiting for master to send event

Info: NULL

* 3. Row * *

Id: 9

User: system user

Host:

Db: NULL

Command: Connect

Time: 100

State: Waiting until MASTER_DELAY seconds after master executed event

Info: NULL

3 rows in set (0.00 sec)

Mysql > select count (*) from tb_admin_bak

+-+

| | count (*) |

+-+

| | 0 |

+-+

1 row in set (0.00 sec) found that the table data from the node was not cleaned at the beginning, and the data was not cleaned up until 20s later.

Delete the test table in the main library

Click (here) to collapse or open

Mysql > drop table tb_admin_bak

Query OK, 0 rows affected (0.06 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | tb_admin |

+-+

1 row in set (0.00 sec) is checking whether the table exists from the library

Click (here) to collapse or open

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | tb_admin |

| | tb_admin_bak |

+-+

2 rows in set (0.00 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | tb_admin |

| | tb_admin_bak |

+-+

2 rows in set (0.00 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | tb_admin |

| | tb_admin_bak |

+-+

2 rows in set (0.00 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | tb_admin |

| | tb_admin_bak |

+-+

2 rows in set (0.00 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | tb_admin |

| | tb_admin_bak |

+-+

2 rows in set (0.00 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | tb_admin |

| | tb_admin_bak |

+-+

2 rows in set (0.00 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | tb_admin |

| | tb_admin_bak |

+-+

2 rows in set (0.00 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | tb_admin |

+-+

1 row in set (0.00 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | tb_admin |

+-+

1 row in set (0.00 sec) before the test table is deleted.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.