Shulou(Shulou.com)05/31 Report--

This article mainly explains "the summary of the methods of MySQL master-slave delayed replication". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn the "MySQL master-slave delayed replication methods summary" bar!

Method introduction

1.percona pt-slave-delay tools

Main library:

[mysql@localhost ~] $login

Logging to file'/ tmp/master.log'

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 18500

Server version: 5.6.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Root@localhost [(none)] 04: 16: 08 >

Root@localhost [(none)] 04: 16: 08 > use test

Database changed

Root@localhost [test] 04: 16: 16 > insert into tb values

Query OK, 1 row affected (0.03 sec)

Root@localhost [test] 04: 16: 27 > commit

Query OK, 0 rows affected (0.00 sec)

Query from the library:

Root@localhost [test] 04: 16: 52 > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.168

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000033

Read_Master_Log_Pos: 1006

Relay_Log_File: localhost-relay-bin.000063

Relay_Log_Pos: 1176

Relay_Master_Log_File: mysql-bin.000033

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

Relay_Log_Space: 1353

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

Master_UUID: 7618d547-5d81-11e7-b9ec-b083fee71372

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 the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 7618d547-5d81-11e7-b9ec-b083fee71372:2052039-2052041

Executed_Gtid_Set: 7618d547-5d81-11e7-b9ec-b083fee71372:1-2052041

C7a64be9-61e6-11e7-9697-b083fee71372:1-3

Auto_Position: 1

1 row in set (0.00 sec)

Root@localhost [test] 04: 17: 05 > select * from tb

+-+ +

| | id | val |

+-+ +

| | 1 | chuck |

+-+ +

1 row in set (0.00 sec)

It can be seen that the present state of the master is normal.

Set delay

[mysql@localhost ~] $pt-slave-delay-- delay=1m-- interval=15s-- run-time=10m ubiquitous rootline pendant mysql pamphlet localhost pamphlet 3307-- socket=/usr/local/mysql/mysql1.sock

Change from library statu

Sql_thread thread stopped from library after setting delay: Slave_SQL_Running: No

Root@localhost [test] 04: 19: 05 > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.168

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000033

Read_Master_Log_Pos: 1812

Relay_Log_File: localhost-relay-bin.000063

Relay_Log_Pos: 1739

Relay_Master_Log_File: mysql-bin.000033

Slave_IO_Running: Yes

Slave_SQL_Running: No

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

Relay_Log_Space: 2159

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

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: 7618d547-5d81-11e7-b9ec-b083fee71372

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 7618d547-5d81-11e7-b9ec-b083fee71372:2052039-2052044

Executed_Gtid_Set: 7618d547-5d81-11e7-b9ec-b083fee71372:1-2052043

C7a64be9-61e6-11e7-9697-b083fee71372:1-3

Auto_Position: 1

1 row in set (0.00 sec)

Insert another record into the main library

Root@localhost [test] 04: 17: 29 > insert into tb values (2)

Query OK, 1 row affected (0.02 sec)

Root@localhost [test] 04: 22: 10 > commit

Query OK, 0 rows affected (0.00 sec)

Delay log

[mysql@localhost ~] $pt-slave-delay-- delay=1m-- interval=15s-- run-time=10m ubiquitous rootline pendant mysql pamphlet localhost pamphlet 3307-- socket=/usr/local/mysql/mysql1.sock

2017-07-21T16:22:04 slave running 0 seconds behind

2017-07-21T16:22:04 STOP SLAVE until 2017-07-21T16:23:04 at master position mysql-bin.000033/1569

2017-07-21T16:22:19 slave stopped at master position mysql-bin.000033/1569

2017-07-21T16:22:34 slave stopped at master position mysql-bin.000033/1812

2017-07-21T16:22:49 slave stopped at master position mysql-bin.000033/1812

2017-07-21T16:23:04 no new binlog events

2017-07-21T16:23:19 slave stopped at master position mysql-bin.000033/1812

2017-07-21T16:23:34 START SLAVE until master 2017-07-21T16:22:34 mysql-bin.000033/1812

You can see that about a minute later, the sql_ thread is started from the library.

Slave database record

Root@localhost [test] 04: 24: 24 > select * from tb

+-+ +

| | id | val |

+-+ +

| | 1 | chuck |

| | 2 | chuck |

+-+ +

2 rows in set (0.00 sec)

two。 Use CHANGE MASTER TO MASTER_DELAY units in seconds

Root@localhost [(none)] 04: 50: 02 > stop slave

Query OK, 0 rows affected, 1 warning (0.00 sec)

Root@localhost [(none)] 04: 50: 04 > CHANGE MASTER TO MASTER_DELAY = 60

Query OK, 0 rows affected (0.04 sec)

Root@localhost [(none)] 04: 50: 10 > start slave

Query OK, 0 rows affected (0.01 sec)

Root@localhost [(none)] 04: 50: 14 > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.168

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000033

Read_Master_Log_Pos: 1812

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 408

Relay_Master_Log_File: mysql-bin.000033

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

Relay_Log_Space: 616

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

Master_UUID: 7618d547-5d81-11e7-b9ec-b083fee71372

Master_Info_File: mysql.slave_master_info

SQL_Delay: 60

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

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: 7618d547-5d81-11e7-b9ec-b083fee71372:1-2052044

C7a64be9-61e6-11e7-9697-b083fee71372:1-3

Auto_Position: 1

1 row in set (0.00 sec)

The main library inserts records

Root@localhost [test] 04: 55: 44 > insert into tb values

Query OK, 1 row affected (0.02 sec)

Root@localhost [test] 04: 55: 55 > commit

Query OK, 0 rows affected (0.00 sec)

Query master-slave status from the library

Root@localhost [(none)] 04: 56: 06 > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.168

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000033

Read_Master_Log_Pos: 2055

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 408

Relay_Master_Log_File: mysql-bin.000033

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

Relay_Log_Space: 859

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

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: 7618d547-5d81-11e7-b9ec-b083fee71372

Master_Info_File: mysql.slave_master_info

SQL_Delay: 60

SQL_Remaining_Delay: 46 / / how much time delay is expected

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: 7618d547-5d81-11e7-b9ec-b083fee71372:2052045

Executed_Gtid_Set: 7618d547-5d81-11e7-b9ec-b083fee71372:1-2052044

C7a64be9-61e6-11e7-9697-b083fee71372:1-3

Auto_Position: 1

1 row in set (0.00 sec)

About a minute later, the data will be applied from the library.

Root@localhost [(none)] 04: 56: 38 > select * from test.tb

+-+ +

| | id | val |

+-+ +

| | 1 | chuck |

| | 2 | chuck |

| | 3 | chuck |

+-+ +

3 rows in set (0.00 sec)

At this point, I believe you have a deeper understanding of the "summary of MySQL master-slave delayed replication methods". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.