MySQL/MariaDB数据库的PROXY实现读写分离

    MySQL/MariaDB数据库的PROXY实现读写分离

                       作者:尹正杰 

版权声明:原创作品,谢绝转载!否则将追究法律责任。

 

一.ProxySQL概述

1>.各家互联网公司读写分离的解决方案

mysql-proxy:Oracle
  https://downloads.mysql.com/archives/proxy/
Atlas:Qihoo
  https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md
dbproxy:美团
  https://github.com/Meituan-Dianping/DBProxy
Cetus:网易乐得
  https://github.com/Lede-Inc/cetus
Amoeba:
  https://sourceforge.net/projects/amoeba/
Cobar:阿里巴巴,
  Amoeba的升级版
Mycat:基于Cobar
  http:
//www.mycat.io/
ProxySQL:
  https://proxysql.com/

2>.什么是ProxySQL

MySQL中间件,两个版本:官方版和percona版。

percona版是基于官方版基础上修改,C++语言开发,轻量级但性能优异(支持处理千亿级数据),具有中间件所需的绝大多数功能,包括:
  多种方式的的读/写分离
  定制基于用户、基于schema、基于语句的规则对SQL语句进行路由
  缓存查询结果
  后端节点监控

官方站点:
  https://proxysql.com/
官方手册:   https://github.com/sysown/proxysql/wiki

 

二.ProxySQL安装

1>.基于yum仓库安装

[root@node104.yinzhengjie.org.cn ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
> [proxysql_repo]
> name= ProxySQL YUM repository
> baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
> gpgcheck=1
> gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
> EOF
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# cat /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[root@node104.yinzhengjie.org.cn ~]# yum -y install proxysql
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirrors.zju.edu.cn
 * extras: mirrors.zju.edu.cn
 * updates: mirrors.zju.edu.cn
base                                                                                                                      | 3.6 kB  00:00:00     
extras                                                                                                                    | 2.9 kB  00:00:00     
proxysql_repo                                                                                                             | 2.9 kB  00:00:00     
updates                                                                                                                   | 2.9 kB  00:00:00     
proxysql_repo/7/primary_db                                                                                                |  16 kB  00:00:01     
Resolving Dependencies
--> Running transaction check
---> Package proxysql.x86_64 0:1.4.16-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================================================================
 Package                          Arch                           Version                             Repository                             Size
=================================================================================================================================================
Installing:
 proxysql                         x86_64                         1.4.16-1                            proxysql_repo                         5.8 M

Transaction Summary
=================================================================================================================================================
Install  1 Package

Total download size: 5.8 M
Installed size: 22 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/proxysql_repo/packages/proxysql-1.4.16-1-centos7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 79953b49: NO
KEYPublic key for proxysql-1.4.16-1-centos7.x86_64.rpm is not installed
proxysql-1.4.16-1-centos7.x86_64.rpm                                                                                      | 5.8 MB  00:03:34     
Retrieving key from http://repo.proxysql.com/ProxySQL/repo_pub_key
Importing GPG key 0x79953B49:
 Userid     : "rene cannnao (Proxysql Repository) <rene.cannao@gmail.com>"
 Fingerprint: 1448 bf69 3ca6 00c7 99eb 9358 04a5 62fb 7995 3b49
 From       : http://repo.proxysql.com/ProxySQL/repo_pub_key
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : proxysql-1.4.16-1.x86_64                                                                                                      1/1 
  Verifying  : proxysql-1.4.16-1.x86_64                                                                                                      1/1 

Installed:
  proxysql.x86_64 0:1.4.16-1                                                                                                                     

Complete!
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# yum -y install proxysql

2>.基于RPM下载安装

博主推荐阅读:
    https://github.com/sysown/proxysql/releases

3>.ProxySQL组成

[root@node104.yinzhengjie.org.cn ~]# rpm -ql proxysql
/etc/init.d/proxysql      #服务脚本
/etc/proxysql.cnf        #配置文件
/usr/bin/proxysql        #主程序
/usr/share/proxysql/tools/proxysql_galera_checker.sh    
/usr/share/proxysql/tools/proxysql_galera_writer.pl
[root@node104.yinzhengjie.org.cn ~]#  

 

三.ProxySQL实现读写分离

1>.试验环境说明

node102.yinzhengjie.org.cn:
  master节点
node103.yinzhengjie.org.cn:   slave节点
node104.yinzhengjie.org.cn:
  ProxySQL节点

2>.实现读写分离前,先实现主从复制(注意:slave节点需要设置read_only=1)

[root@node102.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
server-id                    = 102
binlog_format                = row
log_bin                      = /data/mysql/logbin/master-102
character-set-server         = utf8mb4
default_storage_engine       = InnoDB
datadir                      = /var/lib/mysql
socket                       = /var/lib/mysql/mysql.sock

[mysqld_safe]
log-error                    = /var/log/mariadb/mariadb.log
pid-file                     = /var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d
[root@node102.yinzhengjie.org.cn ~]# 
[root@node102.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/
total 0
[root@node102.yinzhengjie.org.cn ~]# 
[root@node102.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total 0
[root@node102.yinzhengjie.org.cn ~]# 
[root@node102.yinzhengjie.org.cn ~]# systemctl start mariadb
[root@node102.yinzhengjie.org.cn ~]# 
[root@node102.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/
total 940
-rw-rw---- 1 mysql mysql  26813 Nov 11 19:31 master-102.000001
-rw-rw---- 1 mysql mysql 921736 Nov 11 19:31 master-102.000002
-rw-rw---- 1 mysql mysql    245 Nov 11 19:31 master-102.000003
-rw-rw---- 1 mysql mysql    111 Nov 11 19:31 master-102.index
[root@node102.yinzhengjie.org.cn ~]# 
[root@node102.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total 37852
-rw-rw---- 1 mysql mysql    16384 Nov 11 19:31 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Nov 11 19:31 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Nov 11 19:31 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Nov 11 19:31 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Nov 11 19:31 ib_logfile1
drwx------ 2 mysql mysql     4096 Nov 11 19:31 mysql
srwxrwxrwx 1 mysql mysql        0 Nov 11 19:31 mysql.sock
drwx------ 2 mysql mysql     4096 Nov 11 19:31 performance_schema
drwx------ 2 mysql mysql        6 Nov 11 19:31 test
[root@node102.yinzhengjie.org.cn ~]# 
[root@node102.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> 
MariaDB [(none)]> SELECT user,host,password FROM mysql.user;
+------+----------------------------+----------+
| user | host                       | password |
+------+----------------------------+----------+
| root | localhost                  |          |
| root | node102.yinzhengjie.org.cn |          |
| root | 127.0.0.1                  |          |
| root | ::1                        |          |
|      | localhost                  |          |
|      | node102.yinzhengjie.org.cn |          |
+------+----------------------------+----------+
6 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'copy'@'172.30.1.10%' IDENTIFIED BY 'yinzhengjie';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SELECT user,host,password FROM mysql.user;
+------+----------------------------+-------------------------------------------+
| user | host                       | password                                  |
+------+----------------------------+-------------------------------------------+
| root | localhost                  |                                           |
| root | node102.yinzhengjie.org.cn |                                           |
| root | 127.0.0.1                  |                                           |
| root | ::1                        |                                           |
|      | localhost                  |                                           |
|      | node102.yinzhengjie.org.cn |                                           |
| copy | 172.30.1.10%               | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
+------+----------------------------+-------------------------------------------+
7 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-102.000001 |     26813 |
| master-102.000002 |    921736 |
| master-102.000003 |       403 |
+-------------------+-----------+
3 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host      | db   | Command | Time | State | Info             | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
|  2 | root | localhost | NULL | Query   |    0 | NULL  | SHOW PROCESSLIST |    0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> QUIT
Bye
[root@node102.yinzhengjie.org.cn ~]# 
[root@node102.yinzhengjie.org.cn ~]# 
master节点详细操作过程戳这里
[root@node103.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
server-id                   = 103
binlog_format               = row
read-only                   = on
relay_log                   = relay-log-103
relay_log_index             = relay-log-103.index
character-set-server        = utf8mb4
default_storage_engine      = InnoDB
datadir                     = /var/lib/mysql
socket                      = /var/lib/mysql/mysql.sock

[mysqld_safe]
log-error                   = /var/log/mariadb/mariadb.log
pid-file                    = /var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total 0
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# systemctl start mariadb
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total 37852
-rw-rw---- 1 mysql mysql    16384 Nov 11 19:36 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Nov 11 19:36 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Nov 11 19:36 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Nov 11 19:36 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Nov 11 19:36 ib_logfile1
drwx------ 2 mysql mysql     4096 Nov 11 19:36 mysql
srwxrwxrwx 1 mysql mysql        0 Nov 11 19:36 mysql.sock
drwx------ 2 mysql mysql     4096 Nov 11 19:36 performance_schema
drwx------ 2 mysql mysql        6 Nov 11 19:36 test
[root@node103.yinzhengjie.org.cn ~]# 
[root@node103.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> CHANGE MASTER TO 
    -> MASTER_HOST='172.30.1.102', 
    -> MASTER_USER='copy', 
    -> MASTER_PASSWORD='yinzhengjie', 
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master-102.000003', 
    -> MASTER_LOG_POS=403,
    -> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host      | db   | Command | Time | State | Info             | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
|  2 | root | localhost | NULL | Query   |    0 | NULL  | SHOW PROCESSLIST |    0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW PROCESSLIST\G
*************************** 1. row ***************************
      Id: 2
    User: root
    Host: localhost
      db: NULL
 Command: Query
    Time: 0
   State: NULL
    Info: SHOW PROCESSLIST
Progress: 0.000
*************************** 2. row ***************************
      Id: 3
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 4
   State: Waiting for master to send event
    Info: NULL
Progress: 0.000
*************************** 3. row ***************************
      Id: 4
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 4
   State: Slave has read all relay log; waiting for the slave I/O thread to update it
    Info: NULL
Progress: 0.000
3 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.1.102
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master-102.000003
          Read_Master_Log_Pos: 403
               Relay_Log_File: relay-log-103.000002
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-102.000003
             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: 403
              Relay_Log_Space: 822
              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: 102
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> 
MariaDB [(none)]> SHOW VARIABLES LIKE 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
slave节点详细操作过程戳这里

3>.启动ProxySQL

[root@node104.yinzhengjie.org.cn ~]# ss -ntl
State       Recv-Q Send-Q                           Local Address:Port                                          Peer Address:Port              
LISTEN      0      128                                          *:22                                                       *:*                  
LISTEN      0      128                                         :::22                                                      :::*                  
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# service proxysql start
Starting ProxySQL: 2019-11-11 19:18:24 [INFO] Using config file /etc/proxysql.cnf
DONE!
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# ss -ntl    #启动后会监听两个默认端口,即6032和6033
State       Recv-Q Send-Q                           Local Address:Port                                          Peer Address:Port              
LISTEN      0      128                                          *:6032                                                     *:*                  
LISTEN      0      128                                          *:6033                                                     *:*                  
LISTEN      0      128                                          *:6033                                                     *:*                  
LISTEN      0      128                                          *:6033                                                     *:*                  
LISTEN      0      128                                          *:6033                                                     *:*                  
LISTEN      0      128                                          *:22                                                       *:*                  
LISTEN      0      128                                         :::22                                                      :::*                  
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# 

端口功能说明:
6032:
  ProxySQL的管理端口
6033:   ProxySQL对外提供服务的端口

4>.使用mysql客户端连接到ProxySQL的管理接口6032,默认管理员用户和密码都是admin

proxysql默认数据库功能说明:
  在main和monitor数据库中的表,"runtime_"开头的是运行时的配置不能修改,只能修改非"runtime_"表。

  修改后必须执行"LOAD … TO RUNTIME"才能加载到RUNTIME生效,执行save … to disk将配置持久化保存到磁盘
[root@node104.yinzhengjie.org.cn ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> STATUS
--------------
mysql  Ver 15.1 Distrib 5.5.64-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:        1
Current database:    admin
Current user:        admin
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server:            MySQL
Server version:        5.5.30 (ProxySQL Admin Module)
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:        6032
Uptime:            3 min 59 sec

Threads: 0  Questions: 0  Slow queries: 0
--------------

MySQL [(none)]> 
[root@node104.yinzhengjie.org.cn ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
MySQL [(none)]> SHOW DATABASES;      #查看默认的数据库信息
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> SHOW DATABASES;      #查看默认的数据库信息
MySQL [(none)]> SHOW DATABASES;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> 
MySQL [(none)]> 
MySQL [(none)]> USE main
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [main]> 
MySQL [main]> SHOW TABLES;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_variables                           |
| mysql_collations                           |
| mysql_group_replication_hostgroups         |
| mysql_query_rules                          |
| mysql_query_rules_fast_routing             |
| mysql_replication_hostgroups               |
| mysql_servers                              |
| mysql_users                                |
| proxysql_servers                           |
| runtime_checksums_values                   |
| runtime_global_variables                   |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules                  |
| runtime_mysql_query_rules_fast_routing     |
| runtime_mysql_replication_hostgroups       |
| runtime_mysql_servers                      |
| runtime_mysql_users                        |
| runtime_proxysql_servers                   |
| runtime_scheduler                          |
| scheduler                                  |
+--------------------------------------------+
20 rows in set (0.00 sec)

MySQL [main]> 
MySQL [(none)]> USE main
MySQL [main]> USE monitor
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [monitor]> 
MySQL [monitor]> SHOW TABLES;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_variables                           |
| mysql_collations                           |
| mysql_group_replication_hostgroups         |
| mysql_query_rules                          |
| mysql_query_rules_fast_routing             |
| mysql_replication_hostgroups               |
| mysql_servers                              |
| mysql_users                                |
| proxysql_servers                           |
| runtime_checksums_values                   |
| runtime_global_variables                   |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules                  |
| runtime_mysql_query_rules_fast_routing     |
| runtime_mysql_replication_hostgroups       |
| runtime_mysql_servers                      |
| runtime_mysql_users                        |
| runtime_proxysql_servers                   |
| runtime_scheduler                          |
| scheduler                                  |
+--------------------------------------------+
20 rows in set (0.00 sec)

MySQL [monitor]> 
MySQL [main]> USE monitor

5>.向ProxySQL中添加MySQL节点,以下操作不需要use main也可成功

[root@node104.yinzhengjie.org.cn ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> 
MySQL [(none)]> SELECT * FROM mysql_servers;
Empty set (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.30.1.102',3306);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.30.1.103',3306);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> 
MySQL [(none)]> SELECT * FROM mysql_servers\G
*************************** 1. row ***************************
       hostgroup_id: 10
           hostname: 172.30.1.102
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
*************************** 2. row ***************************
       hostgroup_id: 10
           hostname: 172.30.1.103
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
2 rows in set (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> 

6>.添加监控后端节点的用户(ProxySQL通过每个节点的read_only值来自动调整它们是属于读组还是写组)

[root@node102.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 43
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> 
MariaDB [(none)]> SELECT user,host,password FROM mysql.user;
+------+----------------------------+-------------------------------------------+
| user | host                       | password                                  |
+------+----------------------------+-------------------------------------------+
| root | localhost                  |                                           |
| root | node102.yinzhengjie.org.cn |                                           |
| root | 127.0.0.1                  |                                           |
| root | ::1                        |                                           |
|      | localhost                  |                                           |
|      | node102.yinzhengjie.org.cn |                                           |
| copy | 172.30.1.10%               | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
+------+----------------------------+-------------------------------------------+
7 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> grant replication client on *.* to monitor@'172.30.1.10%' identified by 'yinzhengjie';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SELECT user,host,password FROM mysql.user;
+---------+----------------------------+-------------------------------------------+
| user    | host                       | password                                  |
+---------+----------------------------+-------------------------------------------+
| root    | localhost                  |                                           |
| root    | node102.yinzhengjie.org.cn |                                           |
| root    | 127.0.0.1                  |                                           |
| root    | ::1                        |                                           |
|         | localhost                  |                                           |
|         | node102.yinzhengjie.org.cn |                                           |
| copy    | 172.30.1.10%               | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| monitor | 172.30.1.10%               | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
+---------+----------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> 
在master节点创建monitor@'172.30.1.10%'监控节点的用户,slave节点会自动复制
[root@node104.yinzhengjie.org.cn ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]>
MySQL [(none)]> SELECT * FROM global_variables WHERE variable_value LIKE 'monitor';
+------------------------+----------------+
| variable_name          | variable_value |
+------------------------+----------------+
| mysql-monitor_username | monitor        |
| mysql-monitor_password | monitor        |    #默认连接用户名和密码都是monitor
+------------------------+----------------+
2 rows in set (0.01 sec)

MySQL [(none)]> 
MySQL [(none)]> set mysql-monitor_username='monitor';    #由于我们设置的mysql用户和它是一样的,我们这里可用不改
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> set mysql-monitor_password='yinzhengjie';  #别忘记修改密码,默认的密码是不正确的
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_username';
+------------------------+----------------+
| variable_name          | variable_value |
+------------------------+----------------+
| mysql-monitor_username | monitor        |
+------------------------+----------------+
1 row in set (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_password';
+------------------------+----------------+
| variable_name          | variable_value |
+------------------------+----------------+
| mysql-monitor_password | yinzhengjie    |
+------------------------+----------------+
1 row in set (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> LOAD mysql variables TO RUNTIME;    #加载到RUNTIME
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> SAVE mysql variables TO DISK;      #并保存到disk
Query OK, 98 rows affected (0.00 sec)

MySQL [(none)]> 

7>.查看监控心跳信息(对ping指标的监控)

MySQL [(none)]> SELECT * FROM mysql_server_ping_log;
+--------------+------+------------------+----------------------+------------+
| hostname     | port | time_start_us    | ping_success_time_us | ping_error |
+--------------+------+------------------+----------------------+------------+
| 172.30.1.102 | 3306 | 1573473766705038 | 312                  | NULL       |
| 172.30.1.103 | 3306 | 1573473766888675 | 416                  | NULL       |
| 172.30.1.103 | 3306 | 1573473776705876 | 409                  | NULL       |
| 172.30.1.102 | 3306 | 1573473776808233 | 376                  | NULL       |
| 172.30.1.102 | 3306 | 1573473786707184 | 370                  | NULL       |
| 172.30.1.103 | 3306 | 1573473786865825 | 754                  | NULL       |
| 172.30.1.102 | 3306 | 1573473796707929 | 393                  | NULL       |
| 172.30.1.103 | 3306 | 1573473796900890 | 1066                 | NULL       |
| 172.30.1.103 | 3306 | 1573473806708842 | 465                  | NULL       |
| 172.30.1.102 | 3306 | 1573473806828995 | 407                  | NULL       |
| 172.30.1.102 | 3306 | 1573473816709795 | 426                  | NULL       |
| 172.30.1.103 | 3306 | 1573473816844609 | 1128                 | NULL       |
| 172.30.1.102 | 3306 | 1573473826710224 | 323                  | NULL       |
| 172.30.1.103 | 3306 | 1573473826904691 | 397                  | NULL       |
| 172.30.1.103 | 3306 | 1573473836711399 | 393                  | NULL       |
| 172.30.1.102 | 3306 | 1573473836876597 | 438                  | NULL       |
| 172.30.1.103 | 3306 | 1573473846712444 | 467                  | NULL       |
| 172.30.1.102 | 3306 | 1573473846823168 | 385                  | NULL       |
| 172.30.1.103 | 3306 | 1573473856712922 | 387                  | NULL       |
| 172.30.1.102 | 3306 | 1573473856818348 | 454                  | NULL       |
| 172.30.1.103 | 3306 | 1573473866714114 | 416                  | NULL       |
| 172.30.1.102 | 3306 | 1573473866903522 | 358                  | NULL       |
| 172.30.1.102 | 3306 | 1573473876715019 | 383                  | NULL       |
| 172.30.1.103 | 3306 | 1573473876832186 | 438                  | NULL       |
| 172.30.1.102 | 3306 | 1573473886715974 | 390                  | NULL       |
| 172.30.1.103 | 3306 | 1573473886827599 | 449                  | NULL       |
| 172.30.1.102 | 3306 | 1573473896717386 | 353                  | NULL       |
| 172.30.1.103 | 3306 | 1573473896854300 | 405                  | NULL       |
| 172.30.1.103 | 3306 | 1573473906718604 | 416                  | NULL       |
| 172.30.1.102 | 3306 | 1573473906913744 | 452                  | NULL       |
| 172.30.1.102 | 3306 | 1573473916719927 | 420                  | NULL       |
| 172.30.1.103 | 3306 | 1573473916843097 | 412                  | NULL       |
| 172.30.1.103 | 3306 | 1573473926720546 | 421                  | NULL       |
| 172.30.1.102 | 3306 | 1573473926864018 | 477                  | NULL       |
| 172.30.1.103 | 3306 | 1573473936720770 | 414                  | NULL       |
| 172.30.1.102 | 3306 | 1573473936850568 | 223                  | NULL       |
| 172.30.1.103 | 3306 | 1573473946721286 | 461                  | NULL       |
| 172.30.1.102 | 3306 | 1573473946853504 | 366                  | NULL       |
| 172.30.1.102 | 3306 | 1573473956721294 | 429                  | NULL       |
| 172.30.1.103 | 3306 | 1573473956858230 | 720                  | NULL       |
| 172.30.1.102 | 3306 | 1573473966721832 | 439                  | NULL       |
| 172.30.1.103 | 3306 | 1573473966887706 | 427                  | NULL       |
| 172.30.1.102 | 3306 | 1573473976722318 | 383                  | NULL       |
| 172.30.1.103 | 3306 | 1573473976890283 | 432                  | NULL       |
| 172.30.1.102 | 3306 | 1573473986722580 | 412                  | NULL       |
| 172.30.1.103 | 3306 | 1573473986882159 | 481                  | NULL       |
| 172.30.1.102 | 3306 | 1573473996723889 | 332                  | NULL       |
| 172.30.1.103 | 3306 | 1573473996851881 | 381                  | NULL       |
| 172.30.1.103 | 3306 | 1573474006724718 | 345                  | NULL       |
| 172.30.1.102 | 3306 | 1573474006831028 | 503                  | NULL       |
| 172.30.1.102 | 3306 | 1573474016725793 | 402                  | NULL       |
| 172.30.1.103 | 3306 | 1573474016884878 | 653                  | NULL       |
| 172.30.1.102 | 3306 | 1573474026726600 | 512                  | NULL       |
| 172.30.1.103 | 3306 | 1573474026859475 | 483                  | NULL       |
| 172.30.1.103 | 3306 | 1573474036727412 | 443                  | NULL       |
| 172.30.1.102 | 3306 | 1573474036896904 | 466                  | NULL       |
| 172.30.1.102 | 3306 | 1573474046728093 | 388                  | NULL       |
| 172.30.1.103 | 3306 | 1573474046868450 | 343                  | NULL       |
| 172.30.1.103 | 3306 | 1573474056729007 | 1059                 | NULL       |
| 172.30.1.102 | 3306 | 1573474056901480 | 1145                 | NULL       |
| 172.30.1.103 | 3306 | 1573474066729490 | 551                  | NULL       |
| 172.30.1.102 | 3306 | 1573474066832262 | 452                  | NULL       |
| 172.30.1.103 | 3306 | 1573474076729665 | 465                  | NULL       |
| 172.30.1.102 | 3306 | 1573474076844374 | 479                  | NULL       |
| 172.30.1.102 | 3306 | 1573474086730424 | 359                  | NULL       |
| 172.30.1.103 | 3306 | 1573474086854698 | 831                  | NULL       |
| 172.30.1.102 | 3306 | 1573474096731333 | 1119                 | NULL       |
| 172.30.1.103 | 3306 | 1573474096851435 | 422                  | NULL       |
| 172.30.1.103 | 3306 | 1573474106733159 | 1023                 | NULL       |
| 172.30.1.102 | 3306 | 1573474106836339 | 336                  | NULL       |
| 172.30.1.103 | 3306 | 1573474116733577 | 467                  | NULL       |
| 172.30.1.102 | 3306 | 1573474116885685 | 334                  | NULL       |
| 172.30.1.102 | 3306 | 1573474126736928 | 399                  | NULL       |
| 172.30.1.103 | 3306 | 1573474126903675 | 416                  | NULL       |
| 172.30.1.103 | 3306 | 1573474136738394 | 387                  | NULL       |
| 172.30.1.102 | 3306 | 1573474136874691 | 1243                 | NULL       |
| 172.30.1.102 | 3306 | 1573474146739939 | 318                  | NULL       |
| 172.30.1.103 | 3306 | 1573474146919396 | 495                  | NULL       |
| 172.30.1.103 | 3306 | 1573474156741259 | 430                  | NULL       |
| 172.30.1.102 | 3306 | 1573474156922112 | 341                  | NULL       |
| 172.30.1.103 | 3306 | 1573474166742048 | 390                  | NULL       |
| 172.30.1.102 | 3306 | 1573474166873967 | 388                  | NULL       |
| 172.30.1.102 | 3306 | 1573474176742613 | 388                  | NULL       |
| 172.30.1.103 | 3306 | 1573474176852148 | 1858                 | NULL       |
| 172.30.1.103 | 3306 | 1573474186744181 | 431                  | NULL       |
| 172.30.1.102 | 3306 | 1573474186903091 | 1291                 | NULL       |
| 172.30.1.102 | 3306 | 1573474196745528 | 313                  | NULL       |
| 172.30.1.103 | 3306 | 1573474196868030 | 429                  | NULL       |
| 172.30.1.102 | 3306 | 1573474206746389 | 353                  | NULL       |
| 172.30.1.103 | 3306 | 1573474206932071 | 371                  | NULL       |
| 172.30.1.102 | 3306 | 1573474216748054 | 381                  | NULL       |
| 172.30.1.103 | 3306 | 1573474216882002 | 1026                 | NULL       |
| 172.30.1.102 | 3306 | 1573474226750489 | 352                  | NULL       |
| 172.30.1.103 | 3306 | 1573474226931858 | 295                  | NULL       |
| 172.30.1.103 | 3306 | 1573474236751555 | 388                  | NULL       |
| 172.30.1.102 | 3306 | 1573474236943901 | 1202                 | NULL       |
| 172.30.1.102 | 3306 | 1573474246753043 | 419                  | NULL       |
| 172.30.1.103 | 3306 | 1573474246915047 | 443                  | NULL       |
| 172.30.1.103 | 3306 | 1573474256754572 | 422                  | NULL       |
| 172.30.1.102 | 3306 | 1573474256925638 | 366                  | NULL       |
| 172.30.1.103 | 3306 | 1573474266755617 | 392                  | NULL       |
| 172.30.1.102 | 3306 | 1573474266944209 | 1078                 | NULL       |
| 172.30.1.102 | 3306 | 1573474276757797 | 365                  | NULL       |
| 172.30.1.103 | 3306 | 1573474276956932 | 399                  | NULL       |
| 172.30.1.102 | 3306 | 1573474286759918 | 255                  | NULL       |
| 172.30.1.103 | 3306 | 1573474286954114 | 369                  | NULL       |
| 172.30.1.103 | 3306 | 1573474296761937 | 1316                 | NULL       |
| 172.30.1.102 | 3306 | 1573474296887668 | 360                  | NULL       |
| 172.30.1.103 | 3306 | 1573474306763075 | 376                  | NULL       |
| 172.30.1.102 | 3306 | 1573474306935123 | 435                  | NULL       |
| 172.30.1.103 | 3306 | 1573474316764196 | 424                  | NULL       |
| 172.30.1.102 | 3306 | 1573474316963756 | 445                  | NULL       |
| 172.30.1.103 | 3306 | 1573474326765164 | 409                  | NULL       |
| 172.30.1.102 | 3306 | 1573474326948759 | 427                  | NULL       |
| 172.30.1.103 | 3306 | 1573474336766293 | 352                  | NULL       |
| 172.30.1.102 | 3306 | 1573474336885775 | 394                  | NULL       |
| 172.30.1.102 | 3306 | 1573474346767760 | 1230                 | NULL       |
| 172.30.1.103 | 3306 | 1573474346935298 | 504                  | NULL       |
| 172.30.1.103 | 3306 | 1573474356767794 | 349                  | NULL       |
| 172.30.1.102 | 3306 | 1573474356928059 | 375                  | NULL       |
+--------------+------+------------------+----------------------+------------+
120 rows in set (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> SELECT * FROM mysql_server_ping_log;

8>.查看监控连接是否正常的(如果connect_error的结果为NULL则表示正常)

[root@node104.yinzhengjie.org.cn ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> 
MySQL [(none)]> SELECT * FROM mysql_server_connect_log;
+--------------+------+------------------+-------------------------+---------------+
| hostname     | port | time_start_us    | connect_success_time_us | connect_error |
+--------------+------+------------------+-------------------------+---------------+
| 172.30.1.103 | 3306 | 1573475556879146 | 2672                    | NULL          |
| 172.30.1.102 | 3306 | 1573475557609036 | 837                     | NULL          |
| 172.30.1.102 | 3306 | 1573475616879949 | 1229                    | NULL          |
| 172.30.1.103 | 3306 | 1573475617933590 | 944                     | NULL          |
| 172.30.1.103 | 3306 | 1573475676880890 | 1365                    | NULL          |
| 172.30.1.102 | 3306 | 1573475677675150 | 1424                    | NULL          |
| 172.30.1.103 | 3306 | 1573475736882598 | 2140                    | NULL          |
| 172.30.1.102 | 3306 | 1573475737970939 | 1029                    | NULL          |
| 172.30.1.102 | 3306 | 1573475796882819 | 1152                    | NULL          |
| 172.30.1.103 | 3306 | 1573475797861201 | 1147                    | NULL          |
| 172.30.1.102 | 3306 | 1573475856884790 | 1027                    | NULL          |
| 172.30.1.103 | 3306 | 1573475857754898 | 1104                    | NULL          |
| 172.30.1.102 | 3306 | 1573475916884925 | 905                     | NULL          |
| 172.30.1.103 | 3306 | 1573475917606882 | 1540                    | NULL          |
| 172.30.1.103 | 3306 | 1573475976885309 | 862                     | NULL          |
| 172.30.1.102 | 3306 | 1573475977598052 | 3293                    | NULL          |
| 172.30.1.102 | 3306 | 1573476036886656 | 1072                    | NULL          |
| 172.30.1.103 | 3306 | 1573476038013475 | 1113                    | NULL          |
| 172.30.1.102 | 3306 | 1573476096888126 | 972                     | NULL          |
| 172.30.1.103 | 3306 | 1573476097654488 | 1196                    | NULL          |
+--------------+------+------------------+-------------------------+---------------+
20 rows in set (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> 
MySQL [(none)]> SELECT * FROM mysql_server_connect_log;

9>.查看read_only和replication_lag的监控日志

[root@node104.yinzhengjie.org.cn ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> 
MySQL [(none)]> SELECT * FROM mysql_server_read_only_log;                         #需要等待运行一段时间才会有数据
+--------------+------+------------------+-----------------+-----------+-------+
| hostname     | port | time_start_us    | success_time_us | read_only | error |
+--------------+------+------------------+-----------------+-----------+-------+
| 172.30.1.103 | 3306 | 1573477265369377 | 538             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477265394844 | 483             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477266869687 | 395             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477266895097 | 542             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477268370552 | 505             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477268395438 | 613             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477269870859 | 496             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477269887648 | 775             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477271371759 | 482             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477271399828 | 390             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477272872573 | 420             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477272901309 | 1234            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477274373363 | 409             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477274391103 | 1186            | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477275873475 | 423             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477275891228 | 475             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477277374206 | 479             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477277390700 | 487             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477278875605 | 1150            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477278897787 | 1094            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477280376078 | 1160            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477280406901 | 1160            | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477281876672 | 500             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477281906079 | 1220            | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477283377047 | 480             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477283393525 | 553             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477284877725 | 450             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477284906116 | 740             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477286378016 | 468             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477286401007 | 482             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477287878662 | 491             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477287903901 | 355             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477289379786 | 485             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477289406039 | 384             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477290881112 | 408             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477290901317 | 461             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477292382246 | 558             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477292409459 | 500             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477293883758 | 1449            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477293911969 | 1221            | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477295384133 | 1038            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477295415124 | 852             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477296885351 | 499             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477296909282 | 356             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477298386522 | 506             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477298406988 | 421             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477299886870 | 553             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477299910200 | 429             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477301386859 | 405             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477301403674 | 1090            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477302887645 | 420             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477302904297 | 418             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477304388179 | 414             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477304413610 | 440             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477305888776 | 614             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477305906257 | 471             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477307388716 | 451             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477307413492 | 444             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477308889412 | 460             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477308920181 | 1221            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477310389961 | 454             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477310417209 | 417             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477311890581 | 427             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477311914226 | 472             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477313391035 | 442             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477313416680 | 1089            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477314892385 | 449             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477314909095 | 520             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477316393176 | 528             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477316420545 | 431             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477317893349 | 459             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477317916123 | 388             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477319395147 | 1166            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477319419215 | 1240            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477320895513 | 435             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477320925654 | 415             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477322396349 | 517             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477322419589 | 442             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477323897520 | 430             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477323918838 | 407             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477325398112 | 892             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477325427270 | 399             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477326899351 | 514             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477326928560 | 484             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477328400149 | 872             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477328423143 | 408             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477329900575 | 402             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477329922968 | 440             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477331401527 | 833             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477331429747 | 835             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477332902972 | 864             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477332930690 | 425             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477334403615 | 446             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477334423164 | 644             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477335903936 | 368             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477335927367 | 403             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477337404632 | 420             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477337433055 | 362             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477338905406 | 345             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477338922924 | 474             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477340405889 | 436             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477340434130 | 480             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477341907376 | 1361            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477341938107 | 429             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477343407895 | 443             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477343427571 | 492             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477344909540 | 1157            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477344927432 | 655             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477346409609 | 630             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477346434849 | 430             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477347911115 | 1298            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477347936304 | 1379            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477349411593 | 347             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477349430976 | 407             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477350912468 | 457             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477350936793 | 392             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477352413613 | 451             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477352431986 | 341             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477353915157 | 1288            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477353943959 | 517             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477355415747 | 604             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477355439841 | 447             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477356915922 | 319             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477356940143 | 514             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477358417033 | 391             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477358446203 | 398             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477359917761 | 532             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477359935169 | 450             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477361418959 | 419             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477361436283 | 448             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477362920156 | 474             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477362949355 | 347             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477364420357 | 411             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477364447153 | 481             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477365921196 | 623             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477365939632 | 719             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477367422167 | 445             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477367448608 | 464             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477368922391 | 433             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477368939954 | 440             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477370423541 | 465             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477370451800 | 355             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477371924125 | 628             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477371953349 | 374             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477373425258 | 543             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477373449381 | 315             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477374926764 | 951             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477374953839 | 655             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477376426967 | 522             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477376451252 | 387             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477377927998 | 503             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477377957261 | 460             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477379428853 | 366             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477379455202 | 381             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477380930713 | 1279            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477380950163 | 1468            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477382430928 | 409             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477382447522 | 412             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477383931529 | 332             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477383961726 | 399             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477385432411 | 360             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477385458734 | 401             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477386933453 | 395             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477386951930 | 477             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477388434586 | 345             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477388461049 | 394             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477389934888 | 469             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477389954862 | 596             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477391435828 | 345             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477391465309 | 391             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477392936409 | 376             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477392960861 | 386             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477394437194 | 435             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477394455041 | 534             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477395938185 | 399             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477395958636 | 389             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477397439642 | 432             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477397465911 | 558             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477398941967 | 1103            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477398959130 | 1262            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477400443479 | 362             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477400470664 | 432             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477401945264 | 463             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477401962708 | 447             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477403446677 | 409             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477403464126 | 450             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477404947927 | 490             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477404972338 | 469             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477406448933 | 401             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477406471405 | 400             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477407950362 | 482             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477407973713 | 497             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477409451644 | 594             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477409478916 | 429             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477410953637 | 1148            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477410972679 | 368             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477412455249 | 370             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477412475557 | 400             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477413957342 | 463             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477413975952 | 388             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477415459101 | 432             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477415487450 | 438             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477416960291 | 1164            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477416988242 | 1141            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477418461923 | 421             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477418486163 | 434             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477419963321 | 333             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477419978872 | 456             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477421463950 | 386             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477421488292 | 460             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477422964649 | 449             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477422987169 | 344             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477424466509 | 475             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477424489028 | 452             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477425967928 | 1283            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477425987346 | 1187            | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477427467625 | 591             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477427493026 | 1135            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477428970166 | 583             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477428987638 | 589             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477430471572 | 443             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477430494767 | 406             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477431972637 | 397             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477431992991 | 439             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477433474251 | 371             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477433492656 | 376             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477434976745 | 389             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477435003174 | 401             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477436478477 | 479             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477436501892 | 389             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477437978890 | 516             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477438000239 | 348             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477439480546 | 1118            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477439500828 | 1386            | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477440980780 | 487             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477440999350 | 595             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477442483830 | 567             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477442508080 | 517             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477443985039 | 461             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477444005563 | 451             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477445487066 | 465             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477445505840 | 1332            | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477446988811 | 984             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477447008765 | 1162            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477448489452 | 388             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477448505132 | 444             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477449990200 | 1035            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477450020649 | 386             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477451491016 | 1343            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477451520039 | 1408            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477452991979 | 1143            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477453009230 | 1170            | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477454491628 | 430             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477454519194 | 431             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477455992640 | 703             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477456020706 | 812             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477457493008 | 451             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477457515272 | 393             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477458993779 | 410             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477459015209 | 443             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477460494025 | 456             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477460517382 | 381             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477461995168 | 402             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477462020311 | 402             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477463495701 | 441             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477463518118 | 452             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477464996355 | 391             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477465012839 | 456             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477466497513 | 471             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477466518856 | 369             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477467998737 | 411             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477468021286 | 459             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477469500172 | 355             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477469521808 | 476             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477471001215 | 499             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477471025562 | 447             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477472502413 | 440             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477472519399 | 635             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477474003349 | 431             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477474023728 | 443             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477475504414 | 455             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477475532607 | 520             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477477005215 | 386             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477477025535 | 360             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477478506453 | 994             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477478524517 | 474             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477480007138 | 488             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477480030457 | 367             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477481507996 | 419             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477481533178 | 358             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477483008195 | 443             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477483031548 | 390             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477484509262 | 412             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477484535447 | 460             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477486010125 | 494             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477486029597 | 412             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477487510910 | 434             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477487530570 | 422             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477489011452 | 461             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477489034040 | 505             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477490511810 | 457             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477490531306 | 509             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477492012826 | 692             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477492034297 | 517             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477493514368 | 716             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477493539504 | 680             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477495015156 | 435             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477495044312 | 422             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477496516006 | 451             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477496541714 | 1433            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477498017332 | 433             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477498045556 | 615             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477499517767 | 495             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477499543158 | 435             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477501018516 | 417             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477501033927 | 442             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477502519857 | 429             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477502544089 | 494             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477504021027 | 428             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477504045321 | 518             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477505522207 | 442             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477505547436 | 518             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477507023227 | 559             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477507051981 | 452             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477508523453 | 695             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477508543938 | 528             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477510024714 | 430             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477510040623 | 374             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477511525941 | 482             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477511551429 | 465             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477513027031 | 434             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477513048556 | 411             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477514529707 | 401             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477514546282 | 490             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477516030909 | 597             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477516060926 | 407             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477517531816 | 434             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477517555615 | 1227            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477519033038 | 448             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477519049550 | 464             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477520533728 | 750             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477520557145 | 744             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477522034044 | 525             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477522049672 | 437             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477523535641 | 1062            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477523558885 | 1123            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477525039141 | 1080            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477525061119 | 873             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477526540109 | 513             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477526565059 | 397             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477528041702 | 614             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477528060110 | 450             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477529543195 | 1417            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477529560226 | 746             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477531043767 | 423             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477531072104 | 464             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477532545181 | 817             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477532573355 | 413             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477534045739 | 437             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477534072137 | 449             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477535546400 | 429             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477535564224 | 421             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477537049337 | 691             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477537066587 | 415             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477538553481 | 444             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477538578647 | 429             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477540054711 | 595             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477540083759 | 441             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477541558410 | 390             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477541583812 | 456             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477543059638 | 431             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477543090978 | 525             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477544563202 | 481             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477544590671 | 500             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477546063511 | 499             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477546088020 | 447             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477547565524 | 1093            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477547596419 | 405             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477549066286 | 1259            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477549092657 | 1172            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477550568689 | 383             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477550586464 | 1286            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477552069715 | 550             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477552088939 | 617             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477553572802 | 1141            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477553599965 | 1164            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477555073321 | 357             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477555091745 | 462             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477556574590 | 425             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477556603321 | 1568            | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477558075209 | 426             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477558096568 | 467             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477559577915 | 978             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477559608793 | 413             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477561080220 | 1176            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477561098593 | 1292            | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477562581601 | 1124            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477562609551 | 409             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477564083706 | 939             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477564102762 | 443             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477565585492 | 624             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477565602953 | 610             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477567086498 | 473             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477567105112 | 577             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477568588068 | 451             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477568613747 | 451             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477570089222 | 1216            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477570119160 | 1190            | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477571590355 | 913             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477571610553 | 438             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477573090390 | 342             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477573111875 | 484             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477574591260 | 363             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477574615836 | 504             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477576092761 | 1215            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477576119492 | 373             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477577593378 | 456             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477577622725 | 624             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477579094545 | 552             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477579117134 | 481             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477580595488 | 380             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477580612975 | 434             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477582096930 | 467             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477582117475 | 443             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477583598194 | 454             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477583621585 | 518             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477585098887 | 538             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477585117347 | 498             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477586599986 | 440             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477586629373 | 486             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477588101269 | 498             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477588118584 | 498             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477589602702 | 415             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477589627978 | 493             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477591103722 | 686             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477591119478 | 394             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477592604153 | 490             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477592623752 | 506             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477594105271 | 462             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477594130533 | 521             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477595606529 | 506             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477595625282 | 474             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477597107684 | 387             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477597127280 | 447             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477598608208 | 446             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477598631916 | 528             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477600109431 | 1225            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477600134421 | 933             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477601609861 | 413             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477601631202 | 443             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477603111116 | 428             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477603133551 | 460             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477604611933 | 481             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477604636666 | 457             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477606113279 | 472             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477606138514 | 464             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477607614378 | 515             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477607635642 | 450             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477609115021 | 452             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477609137361 | 464             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477610615814 | 512             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477610644654 | 441             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477612117053 | 777             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477612146182 | 459             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477613617367 | 555             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477613636915 | 461             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477615119064 | 445             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477615145346 | 461             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477616619586 | 479             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477616645823 | 444             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477618120308 | 554             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477618140581 | 421             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477619621288 | 578             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477619643527 | 411             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477621121443 | 458             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477621144894 | 382             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477622622305 | 367             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477622640795 | 469             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477624122533 | 503             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477624144954 | 407             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477625623217 | 546             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477625652546 | 431             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477627123926 | 534             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477627149375 | 417             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477628626040 | 1236            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477628653634 | 591             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477630126100 | 398             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477630152379 | 532             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477631627335 | 476             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477631646793 | 537             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477633127420 | 437             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477633144973 | 456             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477634628027 | 415             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477634657205 | 497             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477636128815 | 474             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477636145357 | 437             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477637630267 | 510             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477637659582 | 399             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477639131292 | 419             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477639159466 | 419             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477640632298 | 386             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477640657814 | 687             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477642132864 | 404             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477642152194 | 414             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477643633492 | 477             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477643660317 | 472             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477645134265 | 582             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477645151740 | 426             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477646635281 | 429             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477646661672 | 483             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477648136342 | 1161            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477648152667 | 1125            | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477649636450 | 521             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477649657055 | 589             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477651136629 | 411             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477651160107 | 400             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477652637407 | 304             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477652659084 | 473             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477654137874 | 438             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477654167174 | 402             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477655638512 | 467             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477655666846 | 393             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477657139892 | 1336            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477657161889 | 533             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477658640262 | 482             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477658657077 | 495             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477660140655 | 394             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477660165107 | 409             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477661641545 | 443             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477661671710 | 467             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477663141816 | 633             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477663158259 | 403             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477664642801 | 840             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477664672726 | 465             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477666143612 | 439             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477666174372 | 437             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477667643845 | 466             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477667674013 | 446             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477669144952 | 429             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477669163385 | 424             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477670646527 | 1002            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477670666706 | 587             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477672147470 | 1272            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477672166680 | 1413            | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477673648056 | 423             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477673671657 | 451             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477675148906 | 435             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477675172138 | 446             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477676650402 | 449             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477676678802 | 387             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477678151286 | 446             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477678179789 | 567             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477679652053 | 424             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477679682290 | 502             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477681153424 | 451             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477681174012 | 403             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477682654376 | 383             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477682678134 | 1096            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477684155231 | 470             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477684181893 | 591             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477685655352 | 447             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477685682703 | 398             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477687155878 | 550             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477687181311 | 398             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477688656317 | 453             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477688674237 | 402             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477690156694 | 400             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477690181171 | 394             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477691656953 | 423             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477691680236 | 358             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477693157578 | 440             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477693186869 | 382             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477694658827 | 555             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477694686971 | 410             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477696159790 | 490             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477696176404 | 457             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477697660413 | 439             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477697679903 | 551             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477699161019 | 398             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477699191317 | 437             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477700662071 | 454             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477700684417 | 431             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477702163750 | 1368            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477702183509 | 947             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477703664248 | 444             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477703689625 | 461             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477705164612 | 465             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477705182916 | 338             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477706665383 | 415             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477706692676 | 471             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477708166292 | 449             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477708190696 | 390             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477709667616 | 1228            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477709693091 | 944             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477711167729 | 442             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477711191977 | 442             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477712669467 | 1292            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477712686836 | 1434            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477714170626 | 1177            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477714194547 | 1250            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477715671382 | 1169            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477715693666 | 434             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477717171903 | 330             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477717202988 | 598             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477718672056 | 361             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477718699493 | 404             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477720172496 | 383             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477720201658 | 389             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477721672883 | 464             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477721688599 | 583             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477723173535 | 442             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477723192979 | 381             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477724674800 | 446             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477724702041 | 407             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477726175418 | 429             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477726195833 | 438             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477727676875 | 595             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477727705864 | 436             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477729177231 | 463             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477729202641 | 427             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477730677500 | 439             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477730707910 | 507             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477732177796 | 429             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477732197182 | 516             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477733678881 | 578             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477733706439 | 483             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477735179520 | 559             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477735202854 | 422             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477736680029 | 453             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477736698604 | 471             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477738180526 | 445             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477738201977 | 531             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477739681299 | 434             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477739705874 | 485             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477741181749 | 340             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477741212090 | 447             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477742681979 | 489             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477742712187 | 420             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477744182979 | 740             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477744211024 | 769             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477745683460 | 406             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477745705809 | 497             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477747184293 | 459             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477747202764 | 409             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477748685557 | 1213            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477748704520 | 1002            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477750185904 | 400             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477750208647 | 435             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477751686901 | 495             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477751704412 | 452             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477753188133 | 423             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477753204685 | 442             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477754688402 | 405             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477754712623 | 404             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477756189066 | 491             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477756213417 | 481             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477757689871 | 454             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477757707307 | 474             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477759190190 | 392             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477759214466 | 335             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477760690964 | 448             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477760719218 | 386             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477762192498 | 1292            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477762221538 | 1200            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477763693267 | 489             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477763716780 | 1439            | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477765193855 | 1170            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477765224793 | 1492            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477766694564 | 470             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477766716968 | 478             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477768194661 | 470             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477768223073 | 396             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477769695286 | 396             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477769724533 | 421             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477771195516 | 451             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477771214831 | 426             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477772696573 | 460             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477772719024 | 344             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477774197782 | 438             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477774225069 | 363             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477775698215 | 620             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477775728495 | 689             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477777198580 | 424             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477777221352 | 459             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477778699231 | 662             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477778728422 | 466             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477780200496 | 1372            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477780227162 | 874             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477781700828 | 1398            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477781720965 | 472             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477783201077 | 438             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477783232603 | 1398            | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477784702425 | 625             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477784730415 | 343             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477786203638 | 443             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477786232873 | 387             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477787704612 | 728             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477787728787 | 386             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477789205825 | 397             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477789232016 | 415             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477790706413 | 597             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477790737422 | 381             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477792207242 | 421             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477792225724 | 463             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477793708279 | 464             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477793731495 | 391             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477795209389 | 416             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477795231651 | 424             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477796710378 | 456             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477796732770 | 473             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477798211279 | 447             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477798234560 | 445             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477799712475 | 1113            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477799736878 | 571             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477801213115 | 410             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477801236499 | 446             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477802714780 | 1266            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477802733654 | 453             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477804214966 | 423             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477804241332 | 399             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477805716070 | 431             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477805735452 | 429             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477807218279 | 1022            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477807234541 | 1041            | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477808719017 | 491             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477808747226 | 467             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477810220437 | 462             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477810242823 | 398             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477811721381 | 362             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477811750532 | 435             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477813222898 | 481             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477813242338 | 400             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477814724605 | 1076            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477814743638 | 461             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477816225373 | 445             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477816246852 | 476             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477817725992 | 427             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477817748438 | 430             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477819227660 | 389             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477819247088 | 429             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477820727881 | 451             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477820757939 | 436             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477822229023 | 410             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477822252301 | 424             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477823730437 | 1167            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477823751410 | 421             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477825232430 | 1139            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477825252726 | 1195            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477826732716 | 999             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477826754137 | 1029            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477828234282 | 1295            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477828261675 | 1176            | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477829735061 | 797             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477829759416 | 839             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477831235506 | 444             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477831254260 | 383             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477832736223 | 364             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477832762399 | 405             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477834236353 | 449             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477834264621 | 583             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477835737612 | 449             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477835764880 | 496             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477837239368 | 1263            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477837264278 | 407             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477838739090 | 400             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477838756636 | 424             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477840240287 | 412             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477840256689 | 452             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477841741898 | 527             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477841772125 | 815             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477843243641 | 489             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477843266824 | 464             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477844745041 | 409             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477844763441 | 396             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477846246852 | 415             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477846275921 | 501             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477847747849 | 447             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477847771467 | 468             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477849248027 | 551             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477849272440 | 402             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477850748274 | 394             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477850770713 | 395             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477852250602 | 1202            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477852270977 | 1239            | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477853751872 | 391             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477853771395 | 385             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477855254243 | 1071            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477855275296 | 1548            | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477856756058 | 596             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477856775596 | 840             | 0         | NULL  |
| 172.30.1.102 | 3306 | 1573477858257760 | 1077            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477858285676 | 1054            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477859759400 | 1221            | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477859780691 | 1241            | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477861260617 | 385             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477861280059 | 410             | 1         | NULL  |
| 172.30.1.103 | 3306 | 1573477862762375 | 472             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477862786168 | 352             | 0         | NULL  |
| 172.30.1.103 | 3306 | 1573477864264160 | 423             | 1         | NULL  |
| 172.30.1.102 | 3306 | 1573477864286705 | 492             | 0         | NULL  |
+--------------+------+------------------+-----------------+-----------+-------+
800 rows in set (0.01 sec)

MySQL [(none)]> 
MySQL [(none)]>  
MySQL [(none)]> SELECT * FROM mysql_server_read_only_log;   #需要等待运行一段时间才会有数据
[root@node104.yinzhengjie.org.cn ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> 
MySQL [(none)]> SELECT * FROM mysql_server_replication_lag_log;
Empty set (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> SELECT * FROM mysql_server_replication_lag_log;

10>.设置分组信息

需要修改的是main库中的mysql_replication_hostgroups表,该表有3个字段:
  writer_hostgroup,
  reader_hostgroup,
  comment,
指定写组的id为10,读组的id为20
[root@node104.yinzhengjie.org.cn ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> 
MySQL [(none)]> INSERT INTO mysql_replication_hostgroups VALUES(10,20,"test");
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> LOAD mysql servers TO RUNTIME;      #将mysql_replication_hostgroups表的修改加载到RUNTIME生效
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> SAVE mysql servers TO DISK;
Query OK, 0 rows affected (0.02 sec)

MySQL [(none)]> 
MySQL [(none)]> SELECT hostgroup_id,hostname,port,status,weight FROM mysql_servers;    #还记得之前我们故意将这2个节点加入到组号为10吗?我们并没有指定读写组,而Monitor模块监控后端的read_only值,按照read_only的值将节点自动移动到读/写组。
+--------------+--------------+------+--------+--------+
| hostgroup_id | hostname     | port | status | weight |
+--------------+--------------+------+--------+--------+
| 10           | 172.30.1.102 | 3306 | ONLINE | 1      |
| 20           | 172.30.1.103 | 3306 | ONLINE | 1      |
+--------------+--------------+------+--------+--------+
rows in set (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]>    
详细过程戳这里

11>.配置发送SQL语句的用户

[root@node102.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 168
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> 
MariaDB [(none)]> SELECT user,host,password FROM mysql.user;
+---------+----------------------------+-------------------------------------------+
| user    | host                       | password                                  |
+---------+----------------------------+-------------------------------------------+
| root    | localhost                  |                                           |
| root    | node102.yinzhengjie.org.cn |                                           |
| root    | 127.0.0.1                  |                                           |
| root    | ::1                        |                                           |
|         | localhost                  |                                           |
|         | node102.yinzhengjie.org.cn |                                           |
| copy    | 172.30.1.10%               | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| monitor | 172.30.1.10%               | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
+---------+----------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> grant all on *.* to sqluser@'172.30.1.10%' identified by 'yinzhengjie';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> SELECT user,host,password FROM mysql.user;
+---------+----------------------------+-------------------------------------------+
| user    | host                       | password                                  |
+---------+----------------------------+-------------------------------------------+
| root    | localhost                  |                                           |
| root    | node102.yinzhengjie.org.cn |                                           |
| root    | 127.0.0.1                  |                                           |
| root    | ::1                        |                                           |
|         | localhost                  |                                           |
|         | node102.yinzhengjie.org.cn |                                           |
| copy    | 172.30.1.10%               | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| monitor | 172.30.1.10%               | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| sqluser | 172.30.1.10%               | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
+---------+----------------------------+-------------------------------------------+
9 rows in set (0.00 sec)

MariaDB [(none)]> 
在master节点上创建访问用户
[root@node104.yinzhengjie.org.cn ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> 
MySQL [(none)]> SELECT * FROM mysql_users\G
Empty set (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('sqluser','yinzhengjie',10);   #在ProxySQL配置,将用户sqluser添加到mysql_users表中, default_hostgroup默认组设置为写组10,当读写分离的路由规则不符合时,会访问默认组的数据库
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> SELECT * FROM mysql_users\G
*************************** 1. row ***************************
              username: sqluser
              password: yinzhengjie
                active: 1
               use_ssl: 0
     default_hostgroup: 10
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
row in set (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> LOAD mysql users TO RUNTIME;    #这个表名千万别写错了哟~
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> SAVE mysql users TO DISK;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> 

12>.使用sqluser用户测试是否能路由到默认的10写组实现读、写数据

[root@node104.yinzhengjie.org.cn ~]# mysql -usqluser -pyinzhengjie -P6033 -h127.0.0.1 -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
|         102 |
+-------------+
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# mysql -usqluser -pyinzhengjie -P6033 -h127.0.0.1 -e 'create database testdb'
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# mysql -usqluser -pyinzhengjie -P6033 -h127.0.0.1 -e 'use testdb;create table test(id int)'
[root@node104.yinzhengjie.org.cn ~]# 

13>.配置路由规则,实现读写分离

[root@node104.yinzhengjie.org.cn ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> 
MySQL [(none)]> SELECT * FROM mysql_query_rules;       #与规则有关的表,目前我们还未定义任何规则
Empty set (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> SELECT hostgroup_id,hostname,port,status,weight FROM mysql_servers;
+--------------+--------------+------+--------+--------+
| hostgroup_id | hostname     | port | status | weight |
+--------------+--------------+------+--------+--------+
| 10           | 172.30.1.102 | 3306 | ONLINE | 1      |
| 20           | 172.30.1.103 | 3306 | ONLINE | 1      |
+--------------+--------------+------+--------+--------+
2 rows in set (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
Query OK, 2 rows affected (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> SELECT * FROM mysql_query_rules\G                       #注意:因ProxySQL根据rule_id顺序进行规则匹配,select ... for update规则的rule_id必须要小于普通的select规则的rule_id。
*************************** 1. row ***************************
              rule_id: 1
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT.*FOR UPDATE$
        match_pattern: NULL
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 10
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
    next_query_flagIN: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
               OK_msg: NULL
          sticky_conn: NULL
            multiplex: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 2. row ***************************
              rule_id: 2
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT
        match_pattern: NULL
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 20
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
    next_query_flagIN: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
               OK_msg: NULL
          sticky_conn: NULL
            multiplex: NULL
                  log: NULL
                apply: 1
              comment: NULL
2 rows in set (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> LOAD mysql query rules TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> 
MySQL [(none)]> SAVE mysql query rules TO DISK;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> 

14>.测试读写分离

 [root@node104.yinzhengjie.org.cn ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> 
MySQL [(none)]> SELECT hostgroup_id,hostname,port,status,weight FROM mysql_servers;
+--------------+--------------+------+--------+--------+
| hostgroup_id | hostname     | port | status | weight |
+--------------+--------------+------+--------+--------+
| 10           | 172.30.1.102 | 3306 | ONLINE | 1      |
| 20           | 172.30.1.103 | 3306 | ONLINE | 1      |
+--------------+--------------+------+--------+--------+
2 rows in set (0.00 sec)

MySQL [(none)]> QUIT
Bye
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# mysql -usqluser -pyinzhengjie -P6033 -h127.0.0.1 -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
|         103 |
+-------------+
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# 
测试读操作是否路由给20的读组
[root@node104.yinzhengjie.org.cn ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> 
MySQL [(none)]> SELECT hostgroup_id,hostname,port,status,weight FROM mysql_servers;
+--------------+--------------+------+--------+--------+
| hostgroup_id | hostname     | port | status | weight |
+--------------+--------------+------+--------+--------+
| 10           | 172.30.1.102 | 3306 | ONLINE | 1      |
| 20           | 172.30.1.103 | 3306 | ONLINE | 1      |
+--------------+--------------+------+--------+--------+
2 rows in set (0.00 sec)

MySQL [(none)]> QUIT
Bye
[root@node104.yinzhengjie.org.cn ~]# 
[root@node104.yinzhengjie.org.cn ~]# mysql -usqluser -pyinzhengjie -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id'
+-------------+
| @@server_id |
+-------------+
|         102 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|         103 |
+-------------+
[root@node104.yinzhengjie.org.cn ~]# 
测试写操作,以事务方式进行测试

15>.路由的信息(查询stats库中的stats_mysql_query_digest表)

[root@node104.yinzhengjie.org.cn ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> 
MySQL [(none)]> SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text                      |
+----+----------+------------+----------------------------------+
| 10 | 5091     | 1          | create table test(id int)        |
| 20 | 2512     | 3          | select @@server_id               |
| 10 | 1558     | 2          | select @@server_id               |
| 10 | 704      | 1          | start transaction                |
| 10 | 519      | 1          | create database testdb           |
| 10 | 389      | 1          | SELECT DATABASE()                |
| 10 | 215      | 1          | commit                           |
| 10 | 0        | 6          | select @@version_comment limit ? |
+----+----------+------------+----------------------------------+
8 rows in set (0.00 sec)

MySQL [(none)]> QUIT
Bye
[root@node104.yinzhengjie.org.cn ~]# 

 

posted @ 2019-11-11 12:08  尹正杰  阅读(797)  评论(0编辑  收藏  举报