MySQL主从延迟复制的方法总结

本篇内容主要讲解“MySQL主从延迟复制的方法总结”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL主从延迟复制的方法总结”吧!

安州网站建设公司创新互联,安州网站设计制作，有大型网站制作公司丰富经验。已为安州近千家提供企业网站建设服务。企业网站搭建\成都外贸网站建设公司要多少钱，请找那个售后服务好的安州做网站的公司定做！

方法介绍

1.percona公司pt-slave-delay工具

主库:

[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(1,'chuck');

Query OK, 1 row affected (0.03 sec)

root@localhost [test] 04: 16: 27 >commit;

Query OK, 0 rows affected (0.00 sec)

从库查询:

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)

可以看到主从现在状态是正常的.

设置延迟

[mysql@localhost ~]$ pt-slave-delay --delay=1m --interval=15s --run-time=10m u=root,p=mysql,h=localhost,P=3307 --socket=/usr/local/mysql/mysql1.sock 

从库状态改变

设置延迟后从库停止了sql_thread线程: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)

主库再插入一条记录

root@localhost [test] 04: 17: 29 >insert into tb values(2,'chuck');

Query OK, 1 row affected (0.02 sec)

root@localhost [test] 04: 22: 10 >commit;

Query OK, 0 rows affected (0.00 sec)

延迟日志

[mysql@localhost ~]$ pt-slave-delay --delay=1m --interval=15s --run-time=10m u=root,p=mysql,h=localhost,P=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

可以看到大概一分钟后,从库开启sql_thread线程.

从库记录

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

+------+-------+

| id   | val   |

+------+-------+

|    1 | chuck |

|    2 | chuck |

+------+-------+

2 rows in set (0.00 sec)

2.使用CHANGE MASTER TO MASTER_DELAY 单位为秒

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)

主库插入记录

root@localhost [test] 04: 55: 44 >insert into tb values (3,'chuck');

Query OK, 1 row affected (0.02 sec)

root@localhost [test] 04: 55: 55 >commit;

Query OK, 0 rows affected (0.00 sec)

从库查询主从状态

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 //预计还有多长时间延迟

      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)

大概一分钟后数据在从库应用.

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)

到此，相信大家对“MySQL主从延迟复制的方法总结”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！