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)