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=
> 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=
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=
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 ::
extras | 2.9 kB ::
proxysql_repo | 2.9 kB ::
updates | 2.9 kB ::
proxysql_repo//primary_db | kB ::
Resolving Dependencies
--> Running transaction check
---> Package proxysql.x86_64 :1.4.- will be installed
--> Finished Dependency Resolution Dependencies Resolved =================================================================================================================================================
Package Arch Version Repository Size
=================================================================================================================================================
Installing:
proxysql x86_64 1.4.- proxysql_repo 5.8 M Transaction Summary
=================================================================================================================================================
Install Package Total download size: 5.8 M
Installed size: M
Downloading packages:
warning: /var/cache/yum/x86_64//proxysql_repo/packages/proxysql-1.4.--centos7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 79953b49: NO
KEYPublic key for proxysql-1.4.--centos7.x86_64.rpm is not installed
proxysql-1.4.--centos7.x86_64.rpm | 5.8 MB ::
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: bf69 3ca6 00c7 99eb 04a5 62fb 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.-.x86_64 /
Verifying : proxysql-1.4.-.x86_64 / Installed:
proxysql.x86_64 :1.4.- 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 *: *:*
LISTEN ::: :::*
[root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# service proxysql start
Starting ProxySQL: -- :: [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 *: *:*
LISTEN *: *:*
LISTEN *: *:*
LISTEN *: *:*
LISTEN *: *:*
LISTEN *: *:*
LISTEN ::: :::*
[root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# 端口功能说明:
：
ProxySQL的管理端口
：
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.
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.5. (ProxySQL Admin Module) Copyright (c) , , 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.-MariaDB, for Linux (x86_64) using readline 5.1 Connection id:
Current database: admin
Current user: admin
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MySQL
Server version: 5.5. (ProxySQL Admin Module)
Protocol version:
Connection: 127.0.0.1 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port:
Uptime: min sec Threads: Questions: Slow queries:
-------------- 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 ~]#