【MySQL】PROXYSQL 服务检查及验证

1.检查mysql_servers 

"admin@127.0.0.1">select * from mysql_servers\G
*************************** 1. row ***************************
       hostgroup_id: 2000011
           hostname: 192.168.1.11
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 10
            use_ssl: 0
     max_latency_ms: 0
            comment: gwpt-mysql-proxy
*************************** 2. row ***************************
       hostgroup_id: 1001213
           hostname: 192.168.1.13
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 10
            use_ssl: 0
     max_latency_ms: 0
            comment: gwpt-mysql-proxy
*************************** 3. row ***************************
       hostgroup_id: 1001213
           hostname: 192.168.1.12
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 10
            use_ssl: 0
     max_latency_ms: 0
            comment: gwpt-mysql-proxy
3 rows in set (0.00 sec)

有三个节点，分为两个主机组。

2.查看业务账号 

*************************** 1. row ***************************
              username: sspu
              password: Sspu#123
                active: 1
               use_ssl: 0
     default_hostgroup: 2000011
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
1 row in set (0.00 sec)

3.查询规则检查

"admin@127.0.0.1">select * from mysql_query_rules\G
*************************** 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: NULL
        match_pattern: ^SELECT.*FOR UPDATE$
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 2000011
            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: NULL
        match_pattern: ^SELECT
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 1001213
            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)

4.SELECT 语句测试 

[root@mysql1 proxysql]# mysql -usspu -pSspu#123 -h 192.168.1.11 -P 6033 -e "select @@hostname";
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql2     |
+------------+
[root@mysql1 proxysql]# mysql -usspu -pSspu#123 -h 192.168.1.11 -P 6033 -e "select @@hostname";
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql3     |
+------------+
[root@mysql1 proxysql]# mysql -usspu -pSspu#123 -h 192.168.1.11 -P 6033 -e "select @@hostname";
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql3     |
+------------+
[root@mysql1 proxysql]# mysql -usspu -pSspu#123 -h 192.168.1.11 -P 6033 -e "select @@hostname";
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql2     |
+------------+

可以看到SELECT 语句基本都在mysql2/mysql3从节点上。

5.DML 语句测试 

[root@mysql1 proxysql]# mysql -usspu -pSspu#123 -h 192.168.1.11 -P 6033 -e "insert into sspudb.my_tab1 values(3,'xsq3');commit;show variables like 'server_id';";
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| server_id     | 113306 |
+---------------+--------+
[root@mysql1 proxysql]# 
[root@mysql1 proxysql]# 
[root@mysql1 proxysql]# 
[root@mysql1 proxysql]# mysql -usspu -pSspu#123 -h 192.168.1.11 -P 6033 -e "update sspudb.my_tab1 set name='xsq-11' where id=1;commit;show variables like 'server_id';";
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| server_id     | 113306 |
+---------------+--------+
[root@mysql1 proxysql]# 
[root@mysql1 proxysql]# 
[root@mysql1 proxysql]# mysql -usspu -pSspu#123 -h 192.168.1.11 -P 6033 -e "delete from sspudb.my_tab1 where id=1;commit;show variables like 'server_id';";
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| server_id     | 113306 |
+---------------+--------+
[root@mysql1 proxysql]# mysql -usspu -pSspu#123 -h 192.168.1.11 -P 6033 -e "select * from sspudb.my_tab1 where id=2 for update;commit;show variables like 'server_id';";
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| id   | name |
+------+------+
|    2 | xsq2 |
+------+------+
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| server_id     | 113306 |
+---------------+--------+

可以看到:insert into ,update,delete ,select for update 等DML语句全部都在主节点。由此可见，我们已经实现了读写分离。SEELCT 全部在从节点:mysql2,mysql3,  DML(insert into ,update,delete ,select for update) 全部在主节点。

6.检查SQL语句在哪些组上 

"proxy@192.168.1.11">select hostgroup,schemaname,username,substr(digest_text,120,-120),count_star
    -> from stats_mysql_query_digest ORDER BY sum_time DESC;
+-----------+--------------------+----------+----------------------------------------------------+------------+
| hostgroup | schemaname         | username | substr(digest_text,120,-120)                       | count_star |
+-----------+--------------------+----------+----------------------------------------------------+------------+
| 2000011   | information_schema | sspu     | insert into sspudb.my_tab1 values(?,?)             | 3          |
| 1001213   | information_schema | sspu     | select @@hostname                                  | 19         |
| 2000011   | information_schema | sspu     | show variables like ?                              | 5          |
| 2000011   | information_schema | sspu     | delete from sspudb.my_tab1 where id=?              | 1          |
| 2000011   | information_schema | sspu     | update sspudb.my_tab1 set name=? where id=?        | 1          |
| 2000011   | information_schema | sspu     | select * from sspudb.my_tab1 where id=? for update | 1          |
| 2000011   | information_schema | sspu     | commit                                             | 5          |
| 2000011   | information_schema | sspu     | select @@version_comment limit ?                   | 25         |
+-----------+--------------------+----------+----------------------------------------------------+------------+

7.检查各类SQL的执行时间 

"proxy@192.168.1.11">select Command,Total_Time_us,Total_cnt from stats_mysql_commands_counters where Total_cnt >0;
+---------+---------------+-----------+
| Command | Total_Time_us | Total_cnt |
+---------+---------------+-----------+
| COMMIT  | 0             | 5         |
| DELETE  | 2245          | 1         |
| INSERT  | 47034         | 3         |
| SELECT  | 18922         | 45        |
| UPDATE  | 2019          | 1         |
| SHOW    | 8975          | 5         |
+---------+---------------+-----------+
6 rows in set (0.01 sec)