MySQL的主从复制(Replication)的监控

一.MySQL的主从复制(Replication)的监控概述

    MySQL的主从复制(Replication)的监控分为两块来监控，一个是主库的监控，一个是从库的监控。

    本章节只会简单介绍一些MySQL的主从复制(Replication)的手动监控的方法。后期需要将其脚本话进行监控，或者基于zabbix，open-falcon等优秀的监控工具来监控MySQL数据库。

二.主库监控

1.查看"binlog_Dump"线程

[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SHOW PROCESSLIST;"
+----+------+---------------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+
| Id | User | Host                            | db   | Command     | Time  | State                                                         | Info             |
+----+------+---------------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+
|  5 | copy | docker201.oldboyedu.com:49590 | NULL | Binlog Dump | 40266 | Master has sent all binlog to slave; waiting for more updates | NULL             |
|  6 | copy | docker201.oldboyedu.com:49592 | NULL | Binlog Dump | 40262 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 13 | root | localhost                       | NULL | Query       |     0 | starting                                                      | SHOW PROCESSLIST |
+----+------+---------------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+
[root@docker201.oldboyedu.com ~]# 

2.查看从库信息

[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SHOW SLAVE HOSTS;"
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         9 |      | 3309 |         7 | 9780aded-52db-11eb-8192-000c29820c67 |
|         8 |      | 3308 |         7 | 8ab67585-52db-11eb-bd03-000c29820c67 |
+-----------+------+------+-----------+--------------------------------------+
[root@docker201.oldboyedu.com ~]# 

三.从库监控

    如下所示，我们可以使用"SHOW SLAVE STATUS\G"命令来查看MySQL主从复制时从库的监控信息:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3309.sock -e "SHOW SLAVE STATUS\G"
        *************************** 1. row ***************************
                       Slave_IO_State: Waiting for master to send event`
                          Master_Host: 172.200.1.201
                          Master_User: copy
                          Master_Port: 3307
                        Connect_Retry: 10
                      Master_Log_File: mysql-bin.000005
                  Read_Master_Log_Pos: 350
                       Relay_Log_File: docker201-relay-bin.000003
                        Relay_Log_Pos: 563
                Relay_Master_Log_File: mysql-bin.000005
                     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: 350
                      Relay_Log_Space: 940
                      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: 7
                          Master_UUID: 7c5bfd7b-52db-11eb-b842-000c29820c67
                     Master_Info_File: /oldboyedu/data/mysql3309/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: 
                    Executed_Gtid_Set: 
                        Auto_Position: 0
                 Replicate_Rewrite_DB: 
                         Channel_Name: 
                   Master_TLS_Version: 
        [root@docker201.oldboyedu.com ~]# 

    上面的参数我们并不会逐一去解释，我们会挑一些生产环境中咱们比较关心的参数来进行说明:
        主库相关的信息，来自"master.info"文件中记录的内容:
            Master_Host: 172.200.1.201
            Master_User: copy
            Master_Port: 3307
            Connect_Retry: 10
            Master_Log_File: mysql-bin.000005
            Read_Master_Log_Pos: 350

        从库的relay-log的执行情况，来自于"relay-log.info"，一般用作判断主从复制"延时"的情况:
            Relay_Log_File: docker201-relay-bin.000003
            Relay_Log_Pos: 563
            Relay_Master_Log_File: mysql-bin.000005
            Exec_Master_Log_Pos: 350
            Seconds_Behind_Master: 0

        从库的线程状态及具体的报错信息:
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            Last_IO_Errno: 0
            Last_IO_Error: 
            Last_SQL_Errno: 0
            Last_SQL_Error: 

        过滤复制相关信息:
            Replicate_Do_DB: 
            Replicate_Ignore_DB: 
            Replicate_Do_Table: 
            Replicate_Ignore_Table: 
            Replicate_Wild_Do_Table: 
            Replicate_Wild_Ignore_Table:

        延时从库的配置信息:(需要注意的是，主从复制延迟使我们不想看到的，但延迟从库是我们人为故意配置的)
            SQL_Delay: 0
            SQL_Remaining_Delay: NULL

        记录GTID相关复制信息:
            Retrieved_Gtid_Set: 
            Executed_Gtid_Set: