ProxySQL读写分离测试（续）

 
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.

 

 
posted @ 2018-08-10 23:24  aaron8219  阅读(475)  评论(0)  编辑  收藏  举报