欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

ProxySQL读写分离测试(续)

程序员文章站 2022-05-03 13:43:32
Preface I've implemented ProxySQL on PXC yesterday but got some errors when configured query rules.I'm gonna do it again in my master-slave environmen ......
 
Preface
 
    I've implemented ProxySQL on PXC yesterday but got some errors when configured query rules.I'm gonna do it again in my master-slave environment again.Let's see the procedure.
 
Procedure
 
Start ProxySQL.
1 [root@zlm1 17:22:46 /var/lib]
2 #service proxysql start
3 Starting ProxySQL: ProxySQL is already running.
4 
5 [root@zlm1 17:23:16 /var/lib]
6 #ps aux|grep proxysql
7 root       666  0.0  0.5  58180  5180 ?        S    15:06   0:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
8 root       667  0.0  2.0 104152 21068 ?        Sl   15:06   0:02 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
9 root      5523  0.0  0.0 112640   960 pts/1    R+   15:55   0:00 grep --color=auto proxysql

 

Login ProxySQL configure the hostgroups.
 1 [root@zlm1 17:27:11 ~]
 2 #mysql -uadmin -padmin -h127.0.0.1 -P6032
 3 mysql: [Warning] Using a password on the command line interface can be insecure.
 4 Welcome to the MySQL monitor.  Commands end with ; or \g.
 5 Your MySQL connection id is 1
 6 Server version: 5.5.30 (ProxySQL Admin Module)
 7 
 8 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 9 
10 Oracle is a registered trademark of Oracle Corporation and/or its
11 affiliates. Other names may be trademarks of their respective
12 owners.
13 
14 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
15 
16 admin@127.0.0.1:6032 [(none)]>select * from mysql_replication_hostgroups;
17 Empty set (0.00 sec)
18 
19 admin@127.0.0.1:6032 [(none)]>insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup) values(10,20);
20 Query OK, 1 row affected (0.00 sec)
21 
22 admin@127.0.0.1:6032 [(none)]>select * from mysql_replication_hostgroups;
23 +------------------+------------------+---------+
24 | writer_hostgroup | reader_hostgroup | comment |
25 +------------------+------------------+---------+
26 | 10               | 20               |         |
27 +------------------+------------------+---------+
28 1 row in set (0.00 sec)
29 
30 admin@127.0.0.1:6032 [(none)]>select * from runtime_mysql_replication_hostgroups;
31 Empty set (0.00 sec)
32 
33 admin@127.0.0.1:6032 [(none)]>select * from disk.mysql_replication_hostgroups;
34 Empty set (0.00 sec)
35 
36 admin@127.0.0.1:6032 [(none)]>load mysql servers to runtime;save mysql servers to disk;
37 Query OK, 0 rows affected (0.00 sec)
38 
39 admin@127.0.0.1:6032 [(none)]>select * from runtime_mysql_replication_hostgroups;
40 +------------------+------------------+---------+
41 | writer_hostgroup | reader_hostgroup | comment |
42 +------------------+------------------+---------+
43 | 10               | 20               |         |
44 +------------------+------------------+---------+
45 1 row in set (0.00 sec)
46 
47 admin@127.0.0.1:6032 [(none)]>select * from disk.mysql_replication_hostgroups;
48 +------------------+------------------+---------+
49 | writer_hostgroup | reader_hostgroup | comment |
50 +------------------+------------------+---------+
51 | 10               | 20               |         |
52 +------------------+------------------+---------+
53 1 row in set (0.00 sec)

 

Configure the mysql servers.
  1 admin@127.0.0.1:6032 [(none)]>select * from mysql_servers;
  2 Empty set (0.00 sec)
  3 
  4 admin@127.0.0.1:6032 [(none)]>insert into mysql_servers(hostgroup_id,hostname,max_connections,max_replication_lag) values(10,'192.168.56.100',100,300);
  5 Query OK, 1 row affected (0.00 sec)
  6 
  7 admin@127.0.0.1:6032 [(none)]>insert into mysql_servers(hostgroup_id,hostname,max_connections,max_replication_lag) values(20,'192.168.56.101',100,300);
  8 Query OK, 1 row affected (0.00 sec)
  9 
 10 admin@127.0.0.1:6032 [(none)]>select * from mysql_servers;
 11 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
 12 | hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
 13 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
 14 | 10           | 192.168.56.100 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
 15 | 20           | 192.168.56.101 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
 16 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
 17 2 rows in set (0.00 sec)
 18 
 19 admin@127.0.0.1:6032 [(none)]>select * from runtime_mysql_servers;
 20 Empty set (0.00 sec)
 21 
 22 admin@127.0.0.1:6032 [(none)]>select * from disk.mysql_servers;
 23 Empty set (0.00 sec)
 24 
 25 admin@127.0.0.1:6032 [(none)]>load mysql servers to runtime;save mysql servers to disk;
 26 Query OK, 0 rows affected (0.00 sec)
 27 
 28 Query OK, 0 rows affected (0.05 sec)
 29 
 30 admin@127.0.0.1:6032 [(none)]>select * from runtime_mysql_servers;
 31 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
 32 | hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
 33 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
 34 | 20           | 192.168.56.101 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
 35 | 20           | 192.168.56.100 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
 36 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
 37 2 rows in set (0.00 sec)
 38 
 39 //Why does the hostgroup_id in table "runtime_mysql_servers" still "20"?
 40 
 41 admin@127.0.0.1:6032 [(none)]>select * from disk.mysql_servers;
 42 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
 43 | hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
 44 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
 45 | 10           | 192.168.56.100 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
 46 | 20           | 192.168.56.101 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
 47 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
 48 2 rows in set (0.00 sec)
 49 
 50 //The hostgroup_id in table "disk.mysql_servers" has taken effect.
 51 
 52 //Check the variables of "read_only" and "super_read_only".
 53 zlm@192.168.56.100:3306 [(none)]>show variables like '%read_only%';
 54 +-----------------------+-------+
 55 | Variable_name         | Value |
 56 +-----------------------+-------+
 57 | innodb_read_only      | OFF   |
 58 | read_only             | OFF   |
 59 | super_read_only       | OFF   |
 60 | transaction_read_only | OFF   |
 61 | tx_read_only          | OFF   |
 62 +-----------------------+-------+
 63 5 rows in set (0.00 sec)
 64 
 65 admin@127.0.0.1:6032 [(none)]>load mysql servers to runtime;
 66 Query OK, 0 rows affected (0.00 sec)
 67 
 68 admin@127.0.0.1:6032 [(none)]>select * from runtime_mysql_servers;
 69 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
 70 | hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
 71 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
 72 | 20           | 192.168.56.101 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
 73 | 20           | 192.168.56.100 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
 74 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
 75 2 rows in set (0.00 sec)
 76 
 77 //It's still "20".
 78 
 79 admin@127.0.0.1:6032 [(none)]>select * from mysql_servers;
 80 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
 81 | hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
 82 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
 83 | 20           | 192.168.56.100 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
 84 | 20           | 192.168.56.101 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
 85 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
 86 2 rows in set (0.00 sec)
 87 
 88 //The "hostgroup_id" in mysql_serves also turned to be "20".Why does it happen?It's the reason why my query rule did not take effect yesterday.
 89 
 90 admin@127.0.0.1:6032 [(none)]>SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
 91 +----------------+------+------------------+-------------------------+---------------------------------------------------------------+
 92 | hostname       | port | time_start_us    | connect_success_time_us | connect_error                                                 |
 93 +----------------+------+------------------+-------------------------+---------------------------------------------------------------+
 94 | 192.168.56.100 | 3306 | 1533999241153417 | 0                       | Access denied for user 'monitor'@'zlm1' (using password: YES) |
 95 | 192.168.56.101 | 3306 | 1533999240299607 | 0                       | Access denied for user 'monitor'@'zlm1' (using password: YES) |
 96 | 192.168.56.101 | 3306 | 1533999181251828 | 0                       | Access denied for user 'monitor'@'zlm1' (using password: YES) |
 97 | 192.168.56.100 | 3306 | 1533999180299465 | 0                       | Access denied for user 'monitor'@'zlm1' (using password: YES) |
 98 | 192.168.56.101 | 3306 | 1533999120959376 | 0                       | Access denied for user 'monitor'@'zlm1' (using password: YES) |
 99 | 192.168.56.100 | 3306 | 1533999120299350 | 0                       | Access denied for user 'monitor'@'zlm1' (using password: YES) |
100 | 192.168.56.100 | 3306 | 1533999061361175 | 0                       | Access denied for user 'monitor'@'zlm1' (using password: YES) |
101 | 192.168.56.101 | 3306 | 1533999060299292 | 0                       | Access denied for user 'monitor'@'zlm1' (using password: YES) |
102 | 192.168.56.100 | 3306 | 1533999001472876 | 0                       | Access denied for user 'monitor'@'zlm1' (using password: YES) |
103 | 192.168.56.101 | 3306 | 1533999000299091 | 0                       | Access denied for user 'monitor'@'zlm1' (using password: YES) |
104 +----------------+------+------------------+-------------------------+---------------------------------------------------------------+
105 10 rows in set (0.00 sec)
106 
107 //It seems the privileges was configurated abnormally.
108 
109 zlm@192.168.56.100:3306 [(none)]>select user,host from mysql.user;
110 +---------------+--------------+
111 | user          | host         |
112 +---------------+--------------+
113 | repl          | 192.168.56.% |
114 | zlm           | 192.168.56.% |
115 | bkuser        | localhost    |
116 | monitor       | localhost    |
117 | mysql.session | localhost    |
118 | mysql.sys     | localhost    |
119 | root          | localhost    |
120 +---------------+--------------+
121 7 rows in set (0.00 sec)
122 
123 zlm@192.168.56.100:3306 [(none)]>drop user monitor@localhost;
124 Query OK, 0 rows affected (0.00 sec)
125 
126 zlm@192.168.56.100:3306 [(none)]>grant all privileges on *.* to monitor@'%' identified by 'monitor';
127 ERROR 1045 (28000): Access denied for user 'zlm'@'192.168.56.%' (using password: YES)
128 zlm@192.168.56.100:3306 [(none)]>exit
129 Bye
130 
131 [root@zlm1 17:59:50 /data/backup]
132 #mysql -uroot -pPassw0rd -hlocalhost -S /tmp/mysql3306.sock
133 mysql: [Warning] Using a password on the command line interface can be insecure.
134 Welcome to the MySQL monitor.  Commands end with ; or \g.
135 Your MySQL connection id is 2758
136 Server version: 5.7.21-log MySQL Community Server (GPL)
137 
138 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
139 
140 Oracle is a registered trademark of Oracle Corporation and/or its
141 affiliates. Other names may be trademarks of their respective
142 owners.
143 
144 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
145 
146 root@localhost:mysql3306.sock [(none)]>grant all privileges on *.* to monitor@'%' identified by 'monitor';
147 Query OK, 0 rows affected, 1 warning (0.00 sec)
148 
149 root@localhost:mysql3306.sock [(none)]>select user,host from mysql.user;
150 +---------------+--------------+
151 | user          | host         |
152 +---------------+--------------+
153 | monitor       | %            |
154 | repl          | 192.168.56.% |
155 | zlm           | 192.168.56.% |
156 | bkuser        | localhost    |
157 | mysql.session | localhost    |
158 | mysql.sys     | localhost    |
159 | root          | localhost    |
160 +---------------+--------------+
161 7 rows in set (0.00 sec)
162 
163 admin@127.0.0.1:6032 [(none)]>SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
164 +----------------+------+------------------+-------------------------+---------------------------------------------------------------+
165 | hostname       | port | time_start_us    | connect_success_time_us | connect_error                                                 |
166 +----------------+------+------------------+-------------------------+---------------------------------------------------------------+
167 | 192.168.56.101 | 3306 | 1533999721321021 | 1040                    | NULL                                                          |
168 | 192.168.56.100 | 3306 | 1533999720303754 | 441                     | NULL                                                          |
169 | 192.168.56.100 | 3306 | 1533999661174268 | 370                     | NULL                                                          |
170 | 192.168.56.101 | 3306 | 1533999660302486 | 1468                    | NULL                                                          |
171 | 192.168.56.100 | 3306 | 1533999601377823 | 0                       | Access denied for user 'monitor'@'zlm1' (using password: YES) |
172 | 192.168.56.101 | 3306 | 1533999600302394 | 0                       | Access denied for user 'monitor'@'zlm1' (using password: YES) |
173 | 192.168.56.101 | 3306 | 1533999541205310 | 0                       | Access denied for user 'monitor'@'zlm1' (using password: YES) |
174 | 192.168.56.100 | 3306 | 1533999540302168 | 0                       | Access denied for user 'monitor'@'zlm1' (using password: YES) |
175 | 192.168.56.101 | 3306 | 1533999480925661 | 0                       | Access denied for user 'monitor'@'zlm1' (using password: YES) |
176 | 192.168.56.100 | 3306 | 1533999480302043 | 0                       | Access denied for user 'monitor'@'zlm1' (using password: YES) |
177 +----------------+------+------------------+-------------------------+---------------------------------------------------------------+
178 10 rows in set (0.00 sec)
179 
180 //The monitor became normal.
181 
182 admin@127.0.0.1:6032 [(none)]>select * from mysql_servers;
183 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
184 | hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
185 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
186 | 10           | 192.168.56.100 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
187 | 20           | 192.168.56.100 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
188 | 10           | 192.168.56.101 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
189 | 20           | 192.168.56.101 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
190 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
191 4 rows in set (0.00 sec)
192 
193 admin@127.0.0.1:6032 [(none)]>delete from mysql_servers;
194 Query OK, 4 rows affected (0.00 sec)
195 
196 admin@127.0.0.1:6032 [(none)]>insert into mysql_servers(hostgroup_id,hostname,max_connections,max_replication_lag) values(10,'192.168.56.100',100,300),(20,'192.168.56.101',100,300);
197 Query OK, 2 rows affected (0.00 sec)
198 
199 admin@127.0.0.1:6032 [(none)]>select * from mysql_servers;
200 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
201 | hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
202 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
203 | 10           | 192.168.56.100 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
204 | 20           | 192.168.56.101 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
205 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
206 2 rows in set (0.00 sec)
207 
208 admin@127.0.0.1:6032 [(none)]>load mysql servers to runtime;save mysql servers to disk;
209 Query OK, 0 rows affected (0.00 sec)
210 
211 Query OK, 0 rows affected (0.02 sec)
212 
213 admin@127.0.0.1:6032 [(none)]>select * from mysql_servers;
214 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
215 | hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
216 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
217 | 10           | 192.168.56.100 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
218 | 10           | 192.168.56.101 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
219 | 20           | 192.168.56.101 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
220 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
221 3 rows in set (0.00 sec)
222 
223 //Why there're three records in table "mysql_servers"?
224 
225 admin@127.0.0.1:6032 [(none)]>select * from runtime_mysql_servers;
226 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
227 | hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
228 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
229 | 10           | 192.168.56.100 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
230 | 20           | 192.168.56.101 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
231 | 10           | 192.168.56.101 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
232 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
233 3 rows in set (0.00 sec)
234 
235 admin@127.0.0.1:6032 [(none)]>select * from disk.mysql_servers;
236 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
237 | hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
238 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
239 | 10           | 192.168.56.100 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
240 | 20           | 192.168.56.101 | 3306 | ONLINE | 1      | 0           | 100             | 300                 | 0       | 0              |         |
241 +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
242 2 rows in set (0.00 sec)
243 
244 admin@127.0.0.1:6032 [(none)]>show variables like '%also%';
245 +-------------------------------------+-------+
246 | Variable_name                       | Value |
247 +-------------------------------------+-------+
248 | mysql-monitor_writer_is_also_reader | true  |
249 +-------------------------------------+-------+
250 1 row in set (0.00 sec)
251 
252 admin@127.0.0.1:6032 [(none)]>SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
253 +----------------+------+------------------+-------------------------+---------------+
254 | hostname       | port | time_start_us    | connect_success_time_us | connect_error |
255 +----------------+------+------------------+-------------------------+---------------+
256 | 192.168.56.100 | 3306 | 1534001701142271 | 357                     | NULL          |
257 | 192.168.56.101 | 3306 | 1534001700318759 | 1260                    | NULL          |
258 | 192.168.56.101 | 3306 | 1534001641047510 | 1215                    | NULL          |
259 | 192.168.56.100 | 3306 | 1534001640318616 | 435                     | NULL          |
260 | 192.168.56.101 | 3306 | 1534001581271612 | 1089                    | NULL          |
261 | 192.168.56.100 | 3306 | 1534001580317548 | 201                     | NULL          |
262 | 192.168.56.100 | 3306 | 1534001521182217 | 1198                    | NULL          |
263 | 192.168.56.101 | 3306 | 1534001520317373 | 922                     | NULL          |
264 | 192.168.56.101 | 3306 | 1534001461241620 | 1110                    | NULL          |
265 | 192.168.56.100 | 3306 | 1534001460316352 | 326                     | NULL          |
266 +----------------+------+------------------+-------------------------+---------------+
267 10 rows in set (0.00 sec)

 

Configure the mysql users.
 1 admin@127.0.0.1:6032 [(none)]>select * from mysql_users;
 2 Empty set (0.00 sec)
 3 
 4 admin@127.0.0.1:6032 [(none)]>insert into mysql_users(username,password,active,default_hostgroup,default_schema) values('zlm','zlmzlm',1,10,'zlm');
 5 Query OK, 1 row affected (0.00 sec)
 6 
 7 admin@127.0.0.1:6032 [(none)]>select * from mysql_users;
 8 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
 9 | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
10 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
11 | zlm      | zlmzlm   | 1      | 0       | 10                | zlm            | 0             | 1                      | 0            | 1       | 1        | 10000           |
12 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
13 1 row in set (0.00 sec)
14 
15 admin@127.0.0.1:6032 [(none)]>select * from runtime_mysql_users;
16 Empty set (0.00 sec)
17 
18 admin@127.0.0.1:6032 [(none)]>select * from disk.mysql_users;
19 Empty set (0.00 sec)
20 
21 admin@127.0.0.1:6032 [(none)]>load mysql users to runtime;save mysql users to disk;
22 Query OK, 0 rows affected (0.00 sec)
23 
24 Query OK, 0 rows affected (0.01 sec)
25 
26 admin@127.0.0.1:6032 [(none)]>select * from runtime_mysql_users;
27 +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
28 | username | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
29 +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
30 | zlm      | *512FB3FAA6F522E351929CAC70AD8EBB0F5901B6 | 1      | 0       | 10                | zlm            | 0             | 1                      | 0            | 0       | 1        | 10000           |
31 | zlm      | *512FB3FAA6F522E351929CAC70AD8EBB0F5901B6 | 1      | 0       | 10                | zlm            | 0             | 1                      | 0            | 1       | 0        | 10000           |
32 +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
33 2 rows in set (0.00 sec)
34 
35 admin@127.0.0.1:6032 [(none)]>select * from disk.mysql_users;
36 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
37 | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
38 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
39 | zlm      | zlmzlm   | 1      | 0       | 10                | zlm            | 0             | 1                      | 0            | 1       | 1        | 10000           |
40 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
41 1 row in set (0.00 sec)

 

Configure the query rules.
 1 admin@127.0.0.1:6032 [(none)]>select * from mysql_query_rules;
 2 Empty set (0.00 sec)
 3 
 4 admin@127.0.0.1:6032 [(none)]>insert into mysql_query_rules(active,username,match_pattern,schemaname,destination_hostgroup,apply) values(1,'zlm','^select','zlm',20,1);
 5 Query OK, 1 row affected (0.00 sec)
 6 
 7 admin@127.0.0.1:6032 [(none)]>select active,username,match_pattern,schemaname,destination_hostgroup,apply from mysql_query_rules;
 8 +--------+----------+---------------+------------+-----------------------+-------+
 9 | active | username | match_pattern | schemaname | destination_hostgroup | apply |
10 +--------+----------+---------------+------------+-----------------------+-------+
11 | 1      | zlm      | ^select       | zlm        | 20                    | 1     |
12 +--------+----------+---------------+------------+-----------------------+-------+
13 1 row in set (0.00 sec)
14 
15 admin@127.0.0.1:6032 [(none)]>select * from runtime_mysql_query_rules;
16 Empty set (0.00 sec)
17 
18 admin@127.0.0.1:6032 [(none)]>select * from disk.mysql_query_rules;
19 Empty set (0.00 sec)
20 
21 admin@127.0.0.1:6032 [(none)]>load mysql query rules to runtime;save mysql query rules to disk;
22 Query OK, 0 rows affected (0.00 sec)
23 
24 admin@127.0.0.1:6032 [(none)]>select active,username,match_pattern,schemaname,destination_hostgroup,apply from runtime_mysql_query_rules;
25 +--------+----------+---------------+------------+-----------------------+-------+
26 | active | username | match_pattern | schemaname | destination_hostgroup | apply |
27 +--------+----------+---------------+------------+-----------------------+-------+
28 | 1      | zlm      | ^select       | zlm        | 20                    | 1     |
29 +--------+----------+---------------+------------+-----------------------+-------+
30 1 row in set (0.00 sec)
31 
32 admin@127.0.0.1:6032 [(none)]>select active,username,match_pattern,schemaname,destination_hostgroup,apply from disk.mysql_query_rules;
33 +--------+----------+---------------+------------+-----------------------+-------+
34 | active | username | match_pattern | schemaname | destination_hostgroup | apply |
35 +--------+----------+---------------+------------+-----------------------+-------+
36 | 1      | zlm      | ^select       | zlm        | 20                    | 1     |
37 +--------+----------+---------------+------------+-----------------------+-------+
38 1 row in set (0.00 sec)

 

Test whether proxy can seperate writing and reading operations.
  1 admin@127.0.0.1:6032 [(none)]>show create table stats.stats_mysql_query_digest\G
  2 *************************** 1. row ***************************
  3        table: stats_mysql_query_digest
  4 Create Table: CREATE TABLE stats_mysql_query_digest (
  5     hostgroup INT,
  6     schemaname VARCHAR NOT NULL,
  7     username VARCHAR NOT NULL,
  8     digest VARCHAR NOT NULL,
  9     digest_text VARCHAR NOT NULL,
 10     count_star INTEGER NOT NULL,
 11     first_seen INTEGER NOT NULL,
 12     last_seen INTEGER NOT NULL,
 13     sum_time INTEGER NOT NULL,
 14     min_time INTEGER NOT NULL,
 15     max_time INTEGER NOT NULL,
 16     PRIMARY KEY(hostgroup, schemaname, username, digest))
 17 1 row in set (0.00 sec)
 18 
 19 admin@127.0.0.1:6032 [(none)]>select hostgroup,schemaname,username,substr(digest_text,120,-120),count_star from stats_mysql_query_digest_reset;
 20 Empty set (0.00 sec)
 21 
 22 [root@zlm1 18:36:17 /data/backup]
 23 #mysql -uzlm -pzlmzlm -h192.168.56.100 -P6033
 24 mysql: [Warning] Using a password on the command line interface can be insecure.
 25 Welcome to the MySQL monitor.  Commands end with ; or \g.
 26 Your MySQL connection id is 6
 27 Server version: 5.5.30 (ProxySQL)
 28 
 29 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 30 
 31 Oracle is a registered trademark of Oracle Corporation and/or its
 32 affiliates. Other names may be trademarks of their respective
 33 owners.
 34 
 35 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 36 
 37 zlm@192.168.56.100:6033 [(none)]>show tables;
 38 +---------------+
 39 | Tables_in_zlm |
 40 +---------------+
 41 | test          |
 42 +---------------+
 43 1 row in set (0.00 sec)
 44 
 45 zlm@192.168.56.100:6033 [(none)]>select * from test;
 46 +------+------+
 47 | id   | name |
 48 +------+------+
 49 |    1 | aaa  |
 50 |    2 | bbb  |
 51 |    3 | ccc  |
 52 +------+------+
 53 3 rows in set (0.00 sec)
 54 
 55 zlm@192.168.56.100:6033 [(none)]>insert into test values(4,'eee');
 56 Query OK, 1 row affected (0.00 sec)
 57 
 58 zlm@192.168.56.100:6033 [(none)]>update test set name='ddd' where id=4;
 59 Query OK, 1 row affected (0.00 sec)
 60 Rows matched: 1  Changed: 1  Warnings: 0
 61 
 62 zlm@192.168.56.100:6033 [(none)]>select * from test;
 63 +------+------+
 64 | id   | name |
 65 +------+------+
 66 |    1 | aaa  |
 67 |    2 | bbb  |
 68 |    3 | ccc  |
 69 |    4 | ddd  |
 70 +------+------+
 71 4 rows in set (0.00 sec)
 72 
 73 admin@127.0.0.1:6032 [(none)]>select hostgroup,schemaname,username,substr(digest_text,120,-120),count_star from stats_mysql_query_digest;
 74 +-----------+------------+----------+-----------------------------------+------------+
 75 | hostgroup | schemaname | username | substr(digest_text,120,-120)      | count_star |
 76 +-----------+------------+----------+-----------------------------------+------------+
 77 | 10        | zlm        | zlm      | update test set name=? where id=? | 1          |
 78 | 20        | zlm        | zlm      | select * from test                | 2          |
 79 | 10        | zlm        | zlm      | show tables                       | 1          |
 80 | 10        | zlm        | zlm      | select USER()                     | 1          |
 81 | 10        | zlm        | zlm      | insert into test values(?,?)      | 1          |
 82 | 10        | zlm        | zlm      | select @@version_comment limit ?  | 1          |
 83 +-----------+------------+----------+-----------------------------------+------------+
 84 6 rows in set (0.00 sec)
 85 
 86 //What baffled me is that why the "select USER()" and "select @@version_comment limit ?" were not in the hostgroup "20"?
 87 
 88 zlm@192.168.56.100:6033 [(none)]>select @@hostname;
 89 +------------+
 90 | @@hostname |
 91 +------------+
 92 | zlm2       |
 93 +------------+
 94 1 row in set (0.00 sec)
 95 
 96 //The select operation has been executed on slave zlm2.
 97 
 98 admin@127.0.0.1:6032 [(none)]>select hostgroup,schemaname,username,substr(digest_text,120,-120),count_star from stats_mysql_query_digest;
 99 +-----------+------------+----------+-----------------------------------+------------+
100 | hostgroup | schemaname | username | substr(digest_text,120,-120)      | count_star |
101 +-----------+------------+----------+-----------------------------------+------------+
102 | 10        | zlm        | zlm      | update test set name=? where id=? | 1          |
103 | 20        | zlm        | zlm      | select * from test                | 2          |
104 | 10        | zlm        | zlm      | show tables                       | 1          |
105 | 10        | zlm        | zlm      | select USER()                     | 1          |
106 | 20        | zlm        | zlm      | select @@hostname                 | 1          |
107 | 10        | zlm        | zlm      | insert into test values(?,?)      | 1          |
108 | 10        | zlm        | zlm      | select @@version_comment limit ?  | 1          |
109 +-----------+------------+----------+-----------------------------------+------------+
110 7 rows in set (0.00 sec)
111 
112 //The new statement of "select @@hostname" was put into hostgroup "20" correctly.