ProxySQL初体验
程序员文章站
2022-08-09 09:18:50
Preface As we all know,it's a common sense that separate reading and writing operations can immensely increse the performance of MySQL database.Especi ......
Preface
As we all know,it's a common sense that separate reading and writing operations can immensely increse the performance of MySQL database.Especially the query operations by executing select statement relevant with large tables.Therefore,we usually choose a proxy tool to deal with it.There're a lot of tools can be used nowadays such as mycat(by Apache),dble(based on mycat by Action),atlas,dbproxy(based on atlas of Qihoo360 by MeituanDianping),cetus(by NetEase) and so forth.I'm not going to compare who's the better tool to use.I'm just prefer to having a test on another popular tool which is called "ProxySQL".
Introduce
ProxySQL is a low-weight proxy tool based on a SQLite database.It provids hight performance espcially in high concurrent environment what we can see below(compared with the MaxScale).
The configuration of ProxySQL is a three-layer structure:
We usually conifuge the parameter in layer of memory,and then load them into layer of runtime to make it take effect.In the end,we should save them to disk for durability storage.It also provides some simple syntax to transfer configurations between those layers as below:
- LOAD MYSQL object FROM MEMORY or LOAD MYSQL object TO RUNTIME
- SAVE MYSQL object TO MEMORY or SAVE MYSQL object FROM RUNTIME
- LOAD MYSQL object TO MEMORY or LOAD MYSQL object FROM DISK
- SAVE MYSQL object FROM MEMORY or SAVE MYSQL object TO DISK
- LOAD MYSQL object FROM CONFIG
The comparison with other popular middleware tools.
Official website:
Github websit:
Percona websit:
Procedure
1. Installation
Download and install ProxySQL in rpm mode on node zlm2.
1 [root@zlm2 08:00:34 ~] 2 #wget https://github.com/sysown/proxysql/releases/download/v1.4.10/proxysql-1.4.10-1-centos7.x86_64.rpm 3 --2018-08-10 08:01:20-- https://github.com/sysown/proxysql/releases/download/v1.4.10/proxysql-1.4.10-1-centos7.x86_64.rpm 4 Resolving github.com (github.com)... 13.229.188.59, 52.74.223.119, 13.250.177.223 5 Connecting to github.com (github.com)|13.229.188.59|:443... connected. 6 HTTP request sent, awaiting response... 302 Found 7 Location: https://github-production-release-asset-2e65be.s3.amazonaws.com/27358084/305d1618-9a5b-11e8-9b04-df8e3393a6b4?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20180810%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20180810T060119Z&X-Amz-Expires=300&X-Amz-Signature=f4f676653d4cd4f34f4b75b2a4e7dfeaee8bf9d8e35dc91938f41961123a9c9f&X-Amz-SignedHeaders=host&actor_id=0&response-content-disposition=attachment%3B%20filename%3Dproxysql-1.4.10-1-centos7.x86_64.rpm&response-content-type=application%2Foctet-stream [following] 8 --2018-08-10 08:01:29-- https://github-production-release-asset-2e65be.s3.amazonaws.com/27358084/305d1618-9a5b-11e8-9b04-df8e3393a6b4?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20180810%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20180810T060119Z&X-Amz-Expires=300&X-Amz-Signature=f4f676653d4cd4f34f4b75b2a4e7dfeaee8bf9d8e35dc91938f41961123a9c9f&X-Amz-SignedHeaders=host&actor_id=0&response-content-disposition=attachment%3B%20filename%3Dproxysql-1.4.10-1-centos7.x86_64.rpm&response-content-type=application%2Foctet-stream 9 Resolving github-production-release-asset-2e65be.s3.amazonaws.com (github-production-release-asset-2e65be.s3.amazonaws.com)... 54.231.32.91 10 Connecting to github-production-release-asset-2e65be.s3.amazonaws.com (github-production-release-asset-2e65be.s3.amazonaws.com)|54.231.32.91|:443... connected. 11 HTTP request sent, awaiting response... 200 OK 12 Length: 5982016 (5.7M) [application/octet-stream] 13 Saving to: ‘proxysql-1.4.10-1-centos7.x86_64.rpm’ 14 15 100%[===========================================================================================================>] 5,982,016 1.12MB/s in 12s 16 17 2018-08-10 08:02:14 (472 KB/s) - ‘proxysql-1.4.10-1-centos7.x86_64.rpm’ saved [5982016/5982016] 18 19 20 [root@zlm2 08:02:14 ~] 21 #ls -l|grep proxysql 22 -rw-r--r-- 1 root root 5982016 Aug 7 15:03 proxysql-1.4.10-1-centos7.x86_64.rpm 23 24 [root@zlm2 08:04:48 ~] 25 #yum -y localinstall proxysql-1.4.10-1-centos7.x86_64.rpm 26 Loaded plugins: fastestmirror 27 Examining proxysql-1.4.10-1-centos7.x86_64.rpm: proxysql-1.4.10-1.x86_64 28 Marking proxysql-1.4.10-1-centos7.x86_64.rpm to be installed 29 Resolving Dependencies 30 --> Running transaction check 31 ---> Package proxysql.x86_64 0:1.4.10-1 will be installed 32 --> Finished Dependency Resolution 33 34 Dependencies Resolved 35 36 ===================================================================================================================================================== 37 Package Arch Version Repository Size 38 ===================================================================================================================================================== 39 Installing: 40 proxysql x86_64 1.4.10-1 /proxysql-1.4.10-1-centos7.x86_64 22 M 41 42 Transaction Summary 43 ===================================================================================================================================================== 44 Install 1 Package 45 46 Total size: 22 M 47 Installed size: 22 M 48 Downloading packages: 49 Running transaction check 50 Running transaction test 51 Transaction test succeeded 52 Running transaction 53 Installing : proxysql-1.4.10-1.x86_64 1/1 54 Verifying : proxysql-1.4.10-1.x86_64 1/1 55 56 Installed: 57 proxysql.x86_64 0:1.4.10-1 58 59 Complete!
Check the default configuration file.
1 [root@zlm2 08:05:09 ~]
2 #rpm -ql proxysql
3 /etc/init.d/proxysql
4 /etc/proxysql.cnf //This is the configuration file of ProxySQL.
5 /usr/bin/proxysql
6 /usr/share/proxysql/tools/proxysql_galera_checker.sh //This is the script to check the status of hostgroups.
7 /usr/share/proxysql/tools/proxysql_galera_writer.pl
8
9 [root@zlm2 08:08:13 ~]
10 #cat /etc/proxysql.cnf
11 #file proxysql.cfg
12
13 ########################################################################################
14 # This config file is parsed using libconfig , and its grammar is described in:
15 # http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
16 # Grammar is also copied at the end of this file
17 ########################################################################################
18
19 ########################################################################################
20 # IMPORTANT INFORMATION REGARDING THIS CONFIGURATION FILE:
21 ########################################################################################
22 # On startup, ProxySQL reads its config file (if present) to determine its datadir.
23 # What happens next depends on if the database file (disk) is present in the defined
24 # datadir (i.e. "/var/lib/proxysql/proxysql.db").
25 #
26 # If the database file is found, ProxySQL initializes its in-memory configuration from
27 # the persisted on-disk database. So, disk configuration gets loaded into memory and
28 # then propagated towards the runtime configuration.
29 #
30 # If the database file is not found and a config file exists, the config file is parsed
31 # and its content is loaded into the in-memory database, to then be both saved on-disk
32 # database and loaded at runtime.
33 #
34 # IMPORTANT: If a database file is found, the config file is NOT parsed. In this case
35 # ProxySQL initializes its in-memory configuration from the persisted on-disk
36 # database ONLY. In other words, the configuration found in the proxysql.cnf
37 # file is only used to initial the on-disk database read on the first startup.
38 #
39 # In order to FORCE a re-initialise of the on-disk database from the configuration file
40 # the ProxySQL service should be started with "service proxysql initial".
41 #
42 ########################################################################################
43
44 datadir="/var/lib/proxysql" //This is the position of SQLite database of ProxySQL.
45
46 admin_variables=
47 {
48 admin_credentials="admin:admin" //The default user/password is admin/admin.
49 # mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
50 mysql_ifaces="0.0.0.0:6032" //The default admin port is 6032.
51 # refresh_interval=2000
52 # debug=true
53 }
54
55 mysql_variables=
56 {
57 threads=4
58 max_connections=2048
59 default_query_delay=0
60 default_query_timeout=36000000
61 have_compress=true
62 poll_timeout=2000
63 # interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
64 interfaces="0.0.0.0:6033" //The default service port is 6033.
65 default_schema="information_schema"
66 stacksize=1048576
67 server_version="5.5.30"
68 connect_timeout_server=3000
69 # make sure to configure monitor username and password
70 # https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
71 monitor_username="monitor"
72 monitor_password="monitor"
73 monitor_history=600000
74 monitor_connect_interval=60000
75 monitor_ping_interval=10000
76 monitor_read_only_interval=1500
77 monitor_read_only_timeout=500
78 ping_interval_server_msec=120000
79 ping_timeout_server=500
80 commands_stats=true
81 sessions_sort=true
82 connect_retries_on_failure=10
83 }
84
85
86 # defines all the MySQL servers
87 mysql_servers =
88 (
89 # {
90 # address = "127.0.0.1" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
91 # port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
92 # hostgroup = 0 # no default, required
93 # status = "ONLINE" # default: ONLINE
94 # weight = 1 # default: 1
95 # compression = 0 # default: 0
96 # max_replication_lag = 10 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned
97 # },
98 # {
99 # address = "/var/lib/mysql/mysql.sock"
100 # port = 0
101 # hostgroup = 0
102 # },
103 # {
104 # address="127.0.0.1"
105 # port=21891
106 # hostgroup=0
107 # max_connections=200
108 # },
109 # { address="127.0.0.2" , port=3306 , hostgroup=0, max_connections=5 },
110 # { address="127.0.0.1" , port=21892 , hostgroup=1 },
111 # { address="127.0.0.1" , port=21893 , hostgroup=1 }
112 # { address="127.0.0.2" , port=3306 , hostgroup=1 },
113 # { address="127.0.0.3" , port=3306 , hostgroup=1 },
114 # { address="127.0.0.4" , port=3306 , hostgroup=1 },
115 # { address="/var/lib/mysql/mysql.sock" , port=0 , hostgroup=1 }
116 )
117
118
119 # defines all the MySQL users
120 mysql_users:
121 (
122 # {
123 # username = "username" # no default , required
124 # password = "password" # default: ''
125 # default_hostgroup = 0 # default: 0
126 # active = 1 # default: 1
127 # },
128 # {
129 # username = "root"
130 # password = ""
131 # default_hostgroup = 0
132 # max_connections=1000
133 # default_schema="test"
134 # active = 1
135 # },
136 # { username = "user1" , password = "password" , default_hostgroup = 0 , active = 0 }
137 )
138
139
140
141 #defines MySQL Query Rules
142 mysql_query_rules:
143 (
144 # {
145 # rule_id=1
146 # active=1
147 # match_pattern="^SELECT .* FOR UPDATE$"
148 # destination_hostgroup=0
149 # apply=1
150 # },
151 # {
152 # rule_id=2
153 # active=1
154 # match_pattern="^SELECT"
155 # destination_hostgroup=1
156 # apply=1
157 # }
158 )
159
160 scheduler=
161 (
162 # {
163 # id=1
164 # active=0
165 # interval_ms=10000
166 # filename="/var/lib/proxysql/proxysql_galera_checker.sh"
167 # arg1="0"
168 # arg2="0"
169 # arg3="0"
170 # arg4="1"
171 # arg5="/var/lib/proxysql/proxysql_galera_checker.log"
172 # }
173 )
174
175
176 mysql_replication_hostgroups=
177 (
178 # {
179 # writer_hostgroup=30
180 # reader_hostgroup=40
181 # comment="test repl 1"
182 # },
183 # {
184 # writer_hostgroup=50
185 # reader_hostgroup=60
186 # comment="test repl 2"
187 # }
188 )
189
190
191
192
193 # http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
194 #
195 # Below is the BNF grammar for configuration files. Comments and include directives are not part of the grammar, so they are not included here.
196 #
197 # configuration = setting-list | empty
198 #
199 # setting-list = setting | setting-list setting
200 #
201 # setting = name (":" | "=") value (";" | "," | empty)
202 #
203 # value = scalar-value | array | list | group
204 #
205 # value-list = value | value-list "," value
206 #
207 # scalar-value = boolean | integer | integer64 | hex | hex64 | float
208 # | string
209 #
210 # scalar-value-list = scalar-value | scalar-value-list "," scalar-value
211 #
212 # array = "[" (scalar-value-list | empty) "]"
213 #
214 # list = "(" (value-list | empty) ")"
215 #
216 # group = "{" (setting-list | empty) "}"
217 #
218 # empty =
Start ProxySQL and check tables of it.
1 [root@zlm2 08:30:12 ~] 2 #service proxysql start 3 Starting ProxySQL: 2018-08-10 08:30:38 [INFO] Using config file /etc/proxysql.cnf 4 DONE! 5 6 [root@zlm2 08:30:38 ~] 7 #ps aux|grep proxysql 8 root 4307 0.0 0.5 58688 5184 ? S 08:30 0:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql 9 root 4308 0.3 1.8 102612 19020 ? Sl 08:30 0:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql 10 root 4335 0.0 0.0 112640 960 pts/0 R+ 08:30 0:00 grep --color=auto proxysql 11 12 [root@zlm2 08:32:20 ~] 13 #cd /var/lib/proxysql 14 15 [root@zlm2 08:33:55 /var/lib/proxysql] 16 #ls -l 17 total 244 18 -rw------- 1 root root 122880 Aug 10 08:16 proxysql.db 19 -rw------- 1 root root 7320 Aug 10 08:30 proxysql.log 20 -rw-r--r-- 1 root root 5 Aug 10 08:30 proxysql.pid 21 -rw------- 1 root root 110592 Aug 10 08:33 proxysql_stats.db 22 -rw-r--r-- 1 root root 2 Aug 10 08:29 reload 23 24 [root@zlm2 08:33:57 /var/lib/proxysql] 25 #mysql -uadmin -padmin -h127.0.0.1 -P6032 26 mysql: [Warning] Using a password on the command line interface can be insecure. 27 Welcome to the MySQL monitor. Commands end with ; or \g. 28 Your MySQL connection id is 1 29 Server version: 5.5.30 (ProxySQL Admin Module) 30 31 Copyright (c) 2009-2018 Percona LLC and/or its affiliates 32 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 33 34 Oracle is a registered trademark of Oracle Corporation and/or its 35 affiliates. Other names may be trademarks of their respective 36 owners. 37 38 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 39 40 (admin@127.0.0.1 6032)[(none)]>show databases; 41 +-----+---------------+-------------------------------------+ 42 | seq | name | file | 43 +-----+---------------+-------------------------------------+ 44 | 0 | main | | 45 | 2 | disk | /var/lib/proxysql/proxysql.db | 46 | 3 | stats | | 47 | 4 | monitor | | 48 | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db | 49 +-----+---------------+-------------------------------------+ 50 5 rows in set (0.00 sec) 51 52 //There's five databases in all in PorxySQL database.In the previous version,there's no stats_history database; 53 54 (admin@127.0.0.1 6032)[(none)]>show tables from main; 55 +--------------------------------------------+ 56 | tables | 57 +--------------------------------------------+ 58 | global_variables | //It contains all the variables of ProxySQL. 59 | mysql_collations | 60 | mysql_group_replication_hostgroups | 61 | mysql_query_rules | //It contains all the query rules we'd like to use. 62 | mysql_query_rules_fast_routing | 63 | mysql_replication_hostgroups | 64 | mysql_servers | //It contains the information of all servers relevant with MySQL. 65 | mysql_users | //It contains the information of all users relevant with MySQL. 66 | proxysql_servers | //It contains the information of all servers relevant with ProxySQL. 67 | runtime_checksums_values | 68 | runtime_global_variables | 69 | runtime_mysql_group_replication_hostgroups | 70 | runtime_mysql_query_rules | 71 | runtime_mysql_query_rules_fast_routing | 72 | runtime_mysql_replication_hostgroups | 73 | runtime_mysql_servers | 74 | runtime_mysql_users | 75 | runtime_proxysql_servers | 76 | runtime_scheduler | 77 | scheduler | 78 +--------------------------------------------+ 79 20 rows in set (0.00 sec) 80 81 //All the "runtime" prefixed tables in "main" database contain the current configuration in runtime mode.The other tables can be modified in memory mode. 82 83 (admin@127.0.0.1 6032)[(none)]>show tables from disk; 84 +------------------------------------+ 85 | tables | 86 +------------------------------------+ 87 | global_variables | 88 | mysql_collations | 89 | mysql_group_replication_hostgroups | 90 | mysql_query_rules | 91 | mysql_query_rules_fast_routing | 92 | mysql_replication_hostgroups | 93 | mysql_servers | 94 | mysql_users | 95 | proxysql_servers | 96 | scheduler | 97 +------------------------------------+ 98 10 rows in set (0.00 sec) 99 100 //All the tables contains the relevant data which we have saved in disk by "SAVE MYSQL xxx TO DISK;" command. 101 //All the tables have the the same structure as those in "main" database. 102 103 (admin@127.0.0.1 6032)[(none)]>show tables from monitor; 104 +------------------------------------+ 105 | tables | 106 +------------------------------------+ 107 | mysql_server_connect_log | 108 | mysql_server_group_replication_log | 109 | mysql_server_ping_log | 110 | mysql_server_read_only_log | 111 | mysql_server_replication_lag_log | 112 +------------------------------------+ 113 5 rows in set (0.00 sec) 114 115 (admin@127.0.0.1 6032)[(none)]>show tables from stats; 116 +--------------------------------------+ 117 | tables | 118 +--------------------------------------+ 119 | global_variables | 120 | stats_memory_metrics | 121 | stats_mysql_commands_counters | 122 | stats_mysql_connection_pool | 123 | stats_mysql_connection_pool_reset | 124 | stats_mysql_global | 125 | stats_mysql_prepared_statements_info | 126 | stats_mysql_processlist | 127 | stats_mysql_query_digest | 128 | stats_mysql_query_digest_reset | 129 | stats_mysql_query_rules | 130 | stats_mysql_users | 131 | stats_proxysql_servers_checksums | 132 | stats_proxysql_servers_metrics | 133 | stats_proxysql_servers_status | 134 +--------------------------------------+ 135 15 rows in set (0.00 sec)
2. Configure the separation of reading and writing.
Configure the hostgroup id.
1 (admin@127.0.0.1 6032)[main]>show create table mysql_replication_hostgroups\G 2 *************************** 1. row *************************** 3 table: mysql_replication_hostgroups 4 Create Table: CREATE TABLE mysql_replication_hostgroups ( 5 writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY, 6 reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0), 7 comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup)) 8 1 row in set (0.00 sec) 9 10 (admin@127.0.0.1 6032)[main]>insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,'Test of ProxySQL'); 11 Query OK, 1 row affected (0.00 sec) 12 13 (admin@127.0.0.1 6032)[main]>select * from mysql_replication_hostgroups; 14 +------------------+------------------+------------------+ 15 | writer_hostgroup | reader_hostgroup | comment | 16 +------------------+------------------+------------------+ 17 | 10 | 20 | Test of ProxySQL | 18 +------------------+------------------+------------------+ 19 1 row in set (0.00 sec) 20 21 (admin@127.0.0.1 6032)[main]>select * from main.runtime_mysql_replication_hostgroups; 22 Empty set (0.00 sec) 23 24 (admin@127.0.0.1 6032)[main]>select * from disk.mysql_replication_hostgroups; 25 Empty set (0.00 sec) 26 27 (admin@127.0.0.1 6032)[main]>load mysql servers to runtime;save mysql servers to disk; 28 Query OK, 0 rows affected (0.00 sec) 29 30 Query OK, 0 rows affected (0.04 sec) 31 32 (admin@127.0.0.1 6032)[main]>select * from main.runtime_mysql_replication_hostgroups; 33 +------------------+------------------+------------------+ 34 | writer_hostgroup | reader_hostgroup | comment | 35 +------------------+------------------+------------------+ 36 | 10 | 20 | Test of ProxySQL | 37 +------------------+------------------+------------------+ 38 1 row in set (0.00 sec) 39 40 (admin@127.0.0.1 6032)[main]>select * from disk.mysql_replication_hostgroups; 41 +------------------+------------------+------------------+ 42 | writer_hostgroup | reader_hostgroup | comment | 43 +------------------+------------------+------------------+ 44 | 10 | 20 | Test of ProxySQL | 45 +------------------+------------------+------------------+ 46 1 row in set (0.00 sec)
Configure the MySQL servers in different hostgroup.
1 (admin@127.0.0.1 6032)[main]>show create table mysql_servers\G
2 *************************** 1. row ***************************
3 table: mysql_servers
4 Create Table: CREATE TABLE mysql_servers (
5 hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
6 hostname VARCHAR NOT NULL,
7 port INT NOT NULL DEFAULT 3306,
8 status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
9 weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1,
10 compression INT CHECK (compression >=0 AND compression <= 102400) NOT NULL DEFAULT 0,
11 max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
12 max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
13 use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
14 max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
15 comment VARCHAR NOT NULL DEFAULT '',
16 PRIMARY KEY (hostgroup_id, hostname, port) )
17 1 row in set (0.00 sec)
18
19 (admin@127.0.0.1 6032)[main]>insert into mysql_servers(hostgrop_id,hostname,port,max_connections,max_replication_lag,max_latency_ms) values(10,'192.168.1.101',3308,50,10,1000),(20,'192.168.1.102',3308,50,10,1000),(20,'192.168.1.103',3308,50,10,1000);
20 Query OK, 3 row affected (0.01 sec)
21
22 (admin@127.0.0.1 6032)[main]>select * from mysql_servers;
23 +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
24 | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
25 +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
26 | 10 | 192.168.1.101 | 3308 | ONLINE | 1 | 0 | 50 | 10 | 0 | 1000 | |
27 | 20 | 191.168.1.102 | 3308 | ONLINE | 1 | 0 | 50 | 10 | 0 | 1000 | |
28 | 20 | 192.168.1.103 | 3308 | ONLINE | 1 | 0 | 50 | 10 | 0 | 1000 | |
29 +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
30 3 rows in set (0.00 sec)
31
32 (admin@127.0.0.1 6032)[main]>select * from runtime_mysql_servers;
33 Empty set (0.00 sec)
34
35 (admin@127.0.0.1 6032)[main]>select * from disk.mysql_servers;
36 Empty set (0.00 sec)
37
38 (admin@127.0.0.1 6032)[main]>load mysql servers to runtime;save mysql servers to disk;
39 Query OK, 0 rows affected (0.01 sec)
40
41 Query OK, 0 rows affected (0.03 sec)
42
43 (admin@127.0.0.1 6032)[main]>select * from runtime_mysql_servers;
44 +--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
45 | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
46 +--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
47 | 20 | 191.168.1.102 | 3308 | SHUNNED | 1 | 0 | 50 | 10 | 0 | 1000 | |
48 | 20 | 192.168.1.101 | 3308 | ONLINE | 1 | 0 | 50 | 10 | 0 | 1000 | |
49 | 20 | 192.168.1.103 | 3308 | ONLINE | 1 | 0 | 50 | 10 | 0 | 1000 | |
50 +--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
51 3 rows in set (0.00 sec)
52
53 (admin@127.0.0.1 6032)[main]>select * from disk.mysql_servers;
54 +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
55 | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
56 +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
57 | 10 | 192.168.1.101 | 3308 | ONLINE | 1 | 0 | 50 | 10 | 0 | 1000 | |
58 | 20 | 191.168.1.102 | 3308 | ONLINE | 1 | 0 | 50 | 10 | 0 | 1000 | |
59 | 20 | 192.168.1.103 | 3308 | ONLINE | 1 | 0 | 50 | 10 | 0 | 1000 | |
60 +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
61 3 rows in set (0.00 sec)
Configure the monitor user and product user.
1 [root@zlm2 09:54:24 /var/lib/proxysql]
2 #ls -l
3 total 748
4 -rw------- 1 root root 122880 Aug 10 09:39 proxysql.db
5 -rw------- 1 root root 347160 Aug 10 09:47 proxysql.log
6 -rw-r--r-- 1 root root 5 Aug 10 08:30 proxysql.pid
7 -rw------- 1 root root 110592 Aug 10 09:46 proxysql_stats.db
8 -rw-r--r-- 1 root root 2 Aug 10 08:29 reload
9
10 [root@zlm2 09:54:32 /var/lib/proxysql]
11 #tail proxysql.log
12 2018-08-10 09:54:30 MySQL_Monitor.cpp:605:monitor_read_only_thread(): [ERROR] Timeout on read_only check for 192.168.1.101:3308 after 0ms. Unable to create a connection. If the server is overload, increase mysql-monitor_connect_timeout. Error: timeout on creating new connection: Access denied for user 'monitor'@'192.168.1.101' (using password: YES).
13 2018-08-10 09:54:30 MySQL_Monitor.cpp:724:monitor_read_only_thread(): [ERROR] Server 192.168.1.101:3308 missed 3 read_only checks. Assuming read_only=1
14 2018-08-10 09:54:32 MySQL_Monitor.cpp:605:monitor_read_only_thread(): [ERROR] Timeout on read_only check for 192.168.1.103:3308 after 1ms. Unable to create a connection. If the server is overload, increase mysql-monitor_connect_timeout. Error: timeout on creating new connection: Access denied for user 'monitor'@'192.168.1.101' (using password: YES).
15 2018-08-10 09:54:32 MySQL_Monitor.cpp:724:monitor_read_only_thread(): [ERROR] Server 192.168.1.103:3308 missed 3 read_only checks. Assuming read_only=1
16 2018-08-10 09:54:32 MySQL_Monitor.cpp:605:monitor_read_only_thread(): [ERROR] Timeout on read_only check for 192.168.1.101:3308 after 0ms. Unable to create a connection. If the server is overload, increase mysql-monitor_connect_timeout. Error: timeout on creating new connection: Access denied for user 'monitor'@'192.168.1.101' (using password: YES).
17 2018-08-10 09:54:32 MySQL_Monitor.cpp:724:monitor_read_only_thread(): [ERROR] Server 192.168.1.101:3308 missed 3 read_only checks. Assuming read_only=1
18 2018-08-10 09:54:33 MySQL_Monitor.cpp:605:monitor_read_only_thread(): [ERROR] Timeout on read_only check for 192.168.1.101:3308 after 0ms. Unable to create a connection. If the server is overload, increase mysql-monitor_connect_timeout. Error: timeout on creating new connection: Access denied for user 'monitor'@'192.168.1.101' (using password: YES).
19 2018-08-10 09:54:33 MySQL_Monitor.cpp:724:monitor_read_only_thread(): [ERROR] Server 192.168.1.101:3308 missed 3 read_only checks. Assuming read_only=1
20 2018-08-10 09:54:33 MySQL_Monitor.cpp:605:monitor_read_only_thread(): [ERROR] Timeout on read_only check for 192.168.1.103:3308 after 1ms. Unable to create a connection. If the server is overload, increase mysql-monitor_connect_timeout. Error: timeout on creating new connection: Access denied for user 'monitor'@'192.168.1.101' (using password: YES).
21 2018-08-10 09:54:33 MySQL_Monitor.cpp:724:monitor_read_only_thread(): [ERROR] Server 192.168.1.103:3308 missed 3 read_only checks. Assuming read_only=1
22
23 [root@zlm2 09:54:18 /data/mysql/mysql3308/data]
24 #tail error.log
25 2018-08-10T09:54:09.757619+01:00 773 [Note] Access denied for user 'monitor'@'192.168.1.101' (using password: YES)
26 2018-08-10T09:54:11.248093+01:00 774 [Note] Access denied for user 'monitor'@'192.168.1.101' (using password: YES)
27 2018-08-10T09:54:12.748305+01:00 775 [Note] Access denied for user 'monitor'@'192.168.1.101' (using password: YES)
28 2018-08-10T09:54:14.253693+01:00 776 [Note] Access denied for user 'monitor'@'192.168.1.101' (using password: YES)
29 2018-08-10T09:54:15.753818+01:00 777 [Note] Access denied for user 'monitor'@'192.168.1.101' (using password: YES)
30 2018-08-10T09:54:17.274359+01:00 778 [Note] Access denied for user 'monitor'@'192.168.1.101' (using password: YES)
31 2018-08-10T09:54:18.754761+01:00 779 [Note] Access denied for user 'monitor'@'192.168.1.101' (using password: YES)
32 2018-08-10T09:54:19.177448+01:00 780 [Note] Access denied for user 'monitor'@'192.168.1.101' (using password: YES)
33 2018-08-10T09:54:19.386483+01:00 781 [Note] Access denied for user 'monitor'@'192.168.1.101' (using password: YES)
34 2018-08-10T09:54:20.270737+01:00 782 [Note] Access denied for user 'monitor'@'192.168.1.101' (using password: YES)
35
36 //The user "monitor" has no privileges to connect in MySQL instance yet.
37
38 //Add monitor user "monitor" in the MySQL instance and grant privileges.
39 (root@localhost mysql3308.sock)[(none)]>grant all privileges on *.* to monitor@'%' identified by 'monitor';
40 Query OK, 0 rows affected, 1 warning (0.02 sec)
41
42 (root@localhost mysql3308.sock)[(none)]>select user,host from mysql.user;
43 +---------------+-------------+
44 | user | host |
45 +---------------+-------------+
46 | monitor | % |
47 | repl | 192.168.1.% |
48 | zlm | 192.168.1.% |
49 | mysql.session | localhost |
50 | mysql.sys | localhost |
51 | root | localhost |
52 | sst | localhost |
53 +---------------+-------------+
54 7 rows in set (0.00 sec)
55
56 [root@zlm2 10:03:06 /var/lib/proxysql]
57 #tail -20 proxysql.log
58 2018-08-10 10:00:20 [INFO] New mysql_replication_hostgroups table
59 writer_hostgroup: 10 , reader_hostgroup: 20, Test of ProxySQL
60 2018-08-10 10:00:20 [INFO] New mysql_group_replication_hostgroups table
61 2018-08-10 10:00:20 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
62 HID: 10 , address: 192.168.1.103 , port: 3308 , weight: 1 , status: ONLINE , max_connections: 50 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 1000000000 , comment:
63 HID: 10 , address: 192.168.1.101 , port: 3308 , weight: 1 , status: ONLINE , max_connections: 50 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 1000000000 , comment:
64 HID: 20 , address: 191.168.1.102 , port: 3308 , weight: 1 , status: ONLINE , max_connections: 50 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 1000000000 , comment:
65 HID: 20 , address: 192.168.1.103 , port: 3308 , weight: 1 , status: ONLINE , max_connections: 50 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 1000000000 , comment:
66 HID: 20 , address: 192.168.1.101 , port: 3308 , weight: 1 , status: ONLINE , max_connections: 50 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 1000000000 , comment:
67 2018-08-10 10:00:20 [INFO] Dumping mysql_servers
68 +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
69 | hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer |
70 +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
71 | 10 | 192.168.1.103 | 3308 | 1 | 0 | 0 | 50 | 10 | 0 | 1000 | | 139639198273536 |
72 | 20 | 192.168.1.101 | 3308 | 1 | 0 | 0 | 50 | 10 | 0 | 1000 | | 139639198867456 |
73 | 20 | 192.168.1.103 | 3308 | 1 | 0 | 0 | 50 | 10 | 0 | 1000 | | 139639212485120 |
74 | 20 | 191.168.1.102 | 3308 | 1 | 0 | 0 | 50 | 10 | 0 | 1000 | | 139639212484992 |
75 | 10 | 192.168.1.101 | 3308 | 1 | 0 | 0 | 50 | 10 | 0 | 1000 | | 139639198412544 |
76 +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
77 2018-08-10 10:00:29 MySQL_Monitor.cpp:1437:monitor_ping(): [ERROR] Server 191.168.1.102:3308 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.
78
79 //Now,the monitor user "monitor" of ProxySQL can get the monitor information.
80
81 //Continue to configure the product user which we need to connect in MySQL instance.
82 (admin@127.0.0.1 6032)[main]>show create table mysql_users\G
83 *************************** 1. row ***************************
84 table: mysql_users
85 Create Table: CREATE TABLE mysql_users (
86 username VARCHAR NOT NULL,
87 password VARCHAR,
88 active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
89 use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
90 default_hostgroup INT NOT NULL DEFAULT 0,
91 default_schema VARCHAR,
92 schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
93 transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
94 fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
95 backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
96 frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
97 max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
98 PRIMARY KEY (username, backend),
99 UNIQUE (username, frontend))
100 1 row in set (0.00 sec)
101
102 (admin@127.0.0.1 6032)[main]>insert into mysql_users(username,password,active,default_hostgroup,default_schema) values('zlm','zlmzlm',1,20,'zlm');
103 Query OK, 1 row affected (0.00 sec)
104
105 (admin@127.0.0.1 6032)[main]>select * from mysql_users;
106 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
107 | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
108 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
109 | zlm | zlmzlm | 1 | 0 | 20 | zlm | 0 | 1 | 0 | 1 | 1 | 10000 |
110 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
111 1 row in set (0.00 sec)
112
113 (admin@127.0.0.1 6032)[main]>load mysql users to runtime;save mysql users to disk;
114 Query OK, 0 rows affected (0.00 sec)
115
116 Query OK, 0 rows affected (0.01 sec)
117
118 (admin@127.0.0.1 6032)[main]>select * from runtime_mysql_users;
119 +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
120 | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
121 +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
122 | zlm | *512FB3FAA6F522E351929CAC70AD8EBB0F5901B6 | 1 | 0 | 20 | zlm | 0 | 1 | 0 | 0 | 1 | 10000 |
123 | zlm | *512FB3FAA6F522E351929CAC70AD8EBB0F5901B6 | 1 | 0 | 20 | zlm | 0 | 1 | 0 | 1 | 0 | 10000 |
124 +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
125 2 rows in set (0.00 sec)
126
127 (admin@127.0.0.1 6032)[main]>select * from disk.mysql_users;
128 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
129 | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
130 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
131 | zlm | zlmzlm | 1 | 0 | 20 | zlm | 0 | 1 | 0 | 1 | 1 | 10000 |
132 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
133 1 row in set (0.00 sec)
Configure the mysql query rules.
1 (admin@127.0.0.1 6032)[main]>show create table mysql_query_rules\G 2 *************************** 1. row *************************** 3 table: mysql_query_rules 4 Create Table: CREATE TABLE mysql_query_rules ( 5 rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 6 active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0, 7 username VARCHAR, 8 schemaname VARCHAR, 9 flagIN INT NOT NULL DEFAULT 0, 10 client_addr VARCHAR, 11 proxy_addr VARCHAR, 12 proxy_port INT, 13 digest VARCHAR, 14 match_digest VARCHAR, 15 match_pattern VARCHAR, 16 negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0, 17 re_modifiers VARCHAR DEFAULT 'CASELESS', 18 flagOUT INT, 19 replace_pattern VARCHAR, 20 destination_hostgroup INT DEFAULT NULL, 21 cache_ttl INT CHECK(cache_ttl > 0), 22 reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL, 23 timeout INT UNSIGNED, 24 retries INT CHECK (retries>=0 AND retries <=1000), 25 delay INT UNSIGNED, 26 next_query_flagIN INT UNSIGNED, 27 mirror_flagOUT INT UNSIGNED, 28 mirror_hostgroup INT UNSIGNED, 29 error_msg VARCHAR, 30 OK_msg VARCHAR, 31 sticky_conn INT CHECK (sticky_conn IN (0,1)), 32 multiplex INT CHECK (multiplex IN (0,1,2)), 33 log INT CHECK (log IN (0,1)), 34 apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0, 35 comment VARCHAR) 36 1 row in set (0.00 sec) 37 38 (admin@127.0.0.1 6032)[main]>insert into mysql_query_rules(active,username,match_pattern,schemaname,destination_hostgroup,apply) values(1,'zlm','^select','zlm',20,1); 39 Query OK, 1 row affected (0.00 sec) 40 41 (admin@127.0.0.1 6032)[main]>select active,username,match_pattern,schemaname,destination_hostgroup,apply from mysql_query_rules; 42 +--------+----------+---------------+------------+-----------------------+-------+ 43 | active | username | match_pattern | schemaname | destination_hostgroup | apply | 44 +--------+----------+---------------+------------+-----------------------+-------+ 45 | 1 | zlm | ^select | zlm | 20 | 1 | 46 +--------+----------+---------------+------------+-----------------------+-------+ 47 1 row in set (0.00 sec) 48 49 (admin@127.0.0.1 6032)[main]>load mysql query rules to runtime;save mysql query rules to disk; 50 Query OK, 0 rows affected (0.00 sec) 51 52 Query OK, 0 rows affected (0.02 sec) 53 54 (admin@127.0.0.1 6032)[main]>select active,username,match_pattern,schemaname,destination_hostgroup,apply from runtime_mysql_query_rules; 55 +--------+----------+---------------+------------+-----------------------+-------+ 56 | active | username | match_pattern | schemaname | destination_hostgroup | apply | 57 +--------+----------+---------------+------------+-----------------------+-------+ 58 | 1 | zlm | ^select | zlm | 20 | 1 | 59 +--------+----------+---------------+------------+-----------------------+-------+ 60 1 row in set (0.00 sec) 61 62 (admin@127.0.0.1 6032)[main]>select active,username,match_pattern,schemaname,destination_hostgroup,apply from disk.mysql_query_rules; 63 +--------+----------+---------------+------------+-----------------------+-------+ 64 | active | username | match_pattern | schemaname | destination_hostgroup | apply | 65 +--------+----------+---------------+------------+-----------------------+-------+ 66 | 1 | zlm | ^select | zlm | 20 | 1 | 67 +--------+----------+---------------+------------+-----------------------+-------+ 68 1 row in set (0.00 sec)
3. Test dml operations
Login MySQL Instance with product user 'zlm'.
1 //Connect with port 6033 to use the query rule of ProxySQL.
2 [root@zlm2 11:13:16 /data/mysql/mysql3308/data]
3 #mysql -uzlm -pzlmzlm -h192.168.1.101 -P6033
4 mysql: [Warning] Using a password on the command line interface can be insecure.
5 Welcome to the MySQL monitor. Commands end with ; or \g.
6 Your MySQL connection id is 4
7 Server version: 5.5.30 (ProxySQL)
8
9 Copyright (c) 2009-2018 Percona LLC and/or its affiliates
10 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
11
12 Oracle is a registered trademark of Oracle Corporation and/or its
13 affiliates. Other names may be trademarks of their respective
14 owners.
15
16 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
17
18 (zlm@192.168.1.101 6033)[(none)]>show tables; //The default database automatically became zlm although I have't specify which database to use.
19 +---------------+
20 | Tables_in_zlm |
21 +---------------+
22 | t1 |
23 | t2 |
24 +---------------+
25 2 rows in set (0.00 sec)
26
27 (zlm@192.168.1.101 6033)[(none)]>select * from t1;
28 +----+------------+
29 | id | name |
30 +----+------------+
31 | 1 | MySQL |
32 | 2 | Oracle |
33 | 3 | PostgreSQL |
34 | 4 | Redis |
35 +----+------------+
36 4 rows in set (0.00 sec)
37
38 (zlm@192.168.1.101 6033)[(none)]>insert into t1 values(5,'MongoDB');
39 Query OK, 1 row affected (0.01 sec)
40
41 (zlm@192.168.1.101 6033)[(none)]>select * from t1;
42 +----+------------+
43 | id | name |
44 +----+------------+
45 | 1 | MySQL |
46 | 2 | Oracle |
47 | 3 | PostgreSQL |
48 | 4 | Redis |
49 | 5 | MongoDB |
50 +----+------------+
51 5 rows in set (0.00 sec)
52
53 //Check the stats we've catched in "stats_mysql_query_digest" table of "stats" database of ProxySQL.
54 (admin@127.0.0.1 6032)[main]>use stats;
55 Reading table information for completion of table and column names
56 You can turn off this feature to get a quicker startup with -A
57
58 Database changed
59 (admin@127.0.0.1 6032)[stats]>show tables; //Notice,if you don't specify "from xxx",it will show tables of "main" database.
60 +--------------------------------------------+
61 | tables |
62 +--------------------------------------------+
63 | global_variables |
64 | mysql_collations |
65 | mysql_group_replication_hostgroups |
66 | mysql_query_rules |
67 | mysql_query_rules_fast_routing |
68 | mysql_replication_hostgroups |
69 | mysql_servers |
70 | mysql_users |
71 | proxysql_servers |
72 | runtime_checksums_values |
73 | runtime_global_variables |
74 | runtime_mysql_group_replication_hostgroups |
75 | runtime_mysql_query_rules |
76 | runtime_mysql_query_rules_fast_routing |
77 | runtime_mysql_replication_hostgroups |
78 | runtime_mysql_servers |
79 | runtime_mysql_users |
80 | runtime_proxysql_servers |
81 | runtime_scheduler |
82 | scheduler |
83 +--------------------------------------------+
84 20 rows in set (0.00 sec)
85
86 (admin@127.0.0.1 6032)[stats]>show tables from stats; //Speicify the target database to be "stats".
87 +--------------------------------------+
88 | tables |
89 +--------------------------------------+
90 | global_variables |
91 | stats_memory_metrics |
92 | stats_mysql_commands_counters |
93 | stats_mysql_connection_pool |
94 | stats_mysql_connection_pool_reset |
95 | stats_mysql_global |
96 | stats_mysql_prepared_statements_info |
97 | stats_mysql_processlist |
98 | stats_mysql_query_digest |
99 | stats_mysql_query_digest_reset |
100 | stats_mysql_query_rules |
101 | stats_mysql_users |
102 | stats_proxysql_servers_checksums |
103 | stats_proxysql_servers_metrics |
104 | stats_proxysql_servers_status |
105 +--------------------------------------+
106 15 rows in set (0.00 sec)
107
108 (admin@127.0.0.1 6032)[stats]>select hostgroup,schemaname,username,substr(digest_text,120,-120),count_star from stats_mysql_query_digest;
109 +-----------+------------+----------+----------------------------------+------------+
110 | hostgroup | schemaname | username | substr(digest_text,120,-120) | count_star |
111 +-----------+------------+----------+----------------------------------+------------+
112 | 20 | zlm | zlm | select * from t1 | 2 |
113 | 20 | zlm | zlm | insert into t1 values(?,?) | 1 |
114 | 20 | zlm | zlm | select USER() | 2 |
115 | 20 | zlm | zlm | show tables | 1 |
116 | 20 | zlm | zlm | select @@version_comment limit ? | 2 |
117 +-----------+------------+----------+----------------------------------+------------+
118 5 rows in set (0.00 sec)
119
120 //It's due to I've set "default_hostgroup" with "20".Actually it should be set with "10" of writer_hostgroup.
121
122 (admin@127.0.0.1 6032)[stats]>select * from mysql_users;
123 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
124 | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
125 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
126 | zlm | zlmzlm | 1 | 0 | 20 | zlm | 0 | 1 | 0 | 1 | 1 | 10000 |
127 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
128 1 row in set (0.00 sec)
129
130 (admin@127.0.0.1 6032)[stats]>update mysql_users set default_hostgroup=10;
131 Query OK, 1 row affected (0.00 sec)
132
133 (admin@127.0.0.1 6032)[stats]>select * from mysql_users;
134 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
135 | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
136 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
137 | zlm | zlmzlm | 1 | 0 | 10 | zlm | 0 | 1 | 0 | 1 | 1 | 10000 |
138 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
139 1 row in set (0.00 sec)
140
141 (admin@127.0.0.1 6032)[stats]>load mysql users to runtime;save mysql users to disk;
142 Query OK, 0 rows affected (0.00 sec)
143
144 Query OK, 0 rows affected (0.05 sec)
145
146 (admin@127.0.0.1 6032)[stats]>select * from runtime_mysql_users;
147 +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
148 | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
149 +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
150 | zlm | *512FB3FAA6F522E351929CAC70AD8EBB0F5901B6 | 1 | 0 | 10 | zlm | 0 | 1 | 0 | 0 | 1 | 10000 |
151 | zlm | *512FB3FAA6F522E351929CAC70AD8EBB0F5901B6 | 1 | 0 | 10 | zlm | 0 | 1 | 0 | 1 | 0 | 10000 |
152 +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
153 2 rows in set (0.00 sec)
154
155 (admin@127.0.0.1 6032)[stats]>select * from disk.mysql_users;
156 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
157 | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
158 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
159 | zlm | zlmzlm | 1 | 0 | 10 | zlm | 0 | 1 | 0 | 1 | 1 | 10000 |
160 +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
161 1 row in set (0.00 sec)
162
163 //Clear the statistic data of table "stats_mysql_query_digest".
164 (admin@127.0.0.1 6032)[stats]>select * from stats_mysql_query_digest_reset;
165 +-----------+------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
166 | hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
167 +-----------+------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
168 | 20 | zlm | zlm | 0x3765930C7143F468 | select * from t1 | 2 | 1533892415 | 1533892545 | 828 | 204 | 624 |
169 | 20 | zlm | zlm | 0x3CE4C46484576DFD | insert into t1 values(?,?) | 1 | 1533892538 | 1533892538 | 13359 | 13359 | 13359 |
170 | 20 | zlm | zlm | 0x594F2C744B698066 | select USER() | 2 | 1533892377 | 1533892397 | 0 | 0 | 0 |
171 | 20 | zlm | zlm | 0x99531AEFF718C501 | show tables | 1 | 1533892400 | 1533892400 | 2607 | 2607 | 2607 |
172 | 20 | zlm | zlm | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 2 | 1533892377 | 1533892397 | 0 | 0 | 0 |
173 +-----------+------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
174 5 rows in set (0.01 sec)
175
176 (admin@127.0.0.1 6032)[stats]>select * from stats_mysql_query_digest;
177 Empty set (0.00 sec)
178
179 //Do the dml operations again.
180 (zlm@192.168.1.101 6033)[(none)]>show tables;
181 +---------------+
182 | Tables_in_zlm |
183 +---------------+
184 | t1 |
185 | t2 |
186 +---------------+
187 2 rows in set (0.00 sec)
188
189 (zlm@192.168.1.101 6033)[(none)]>select * from t2;
190 +----+------------+
191 | id | name |
192 +----+------------+
193 | 1 | MySQL |
194 | 2 | Oracle |
195 | 3 | SQL Server |
196 | 4 | Redis |
197 | 5 | MongoDB |
198 | 6 | PostgreSQL |
199 +----+------------+
200 6 rows in set (0.01 sec)
201
202 (zlm@192.168.1.101 6033)[(none)]>insert into t2 values(7,'Hadoop');
203 Query OK, 1 row affected (0.01 sec)
204
205 //Check the diagnostic data in table "stats_mysql_query_digest" again.
206 (admin@127.0.0.1 6032)[stats]>select active,username,match_pattern,schemaname,destination_hostgroup,apply from mysql_query_rules;
207 +--------+----------+---------------+------------+-----------------------+-------+
208 | active | username
赞 (0)
打赏
微信扫一扫
相关文章:
-
-
简单总结一下对于数据的分组和分组函数。 本文所举实例，数据来源oracle用户scott下的emp,dept ,salgrade 3表：数据如下： ... [阅读全文] -
大数据介绍 大数据本质也是数据，但是又有了新的特征，包括数据来源广、数据格式多样化（结构化数据、非结构化数据、Excel文件、文本文件等）、数据量大... [阅读全文]
-
从oracle往greenplum迁移，查询性能不满足要求的定位以及调优过程
一、前言 在一次对比oracle和greenplum查询性能过程中，由于greenplum查询性能不理想，因此进行定位分析，提升greenplum的... [阅读全文] -
1、若你用的Linux系统是CentOS的话，这是一个坑： 它会提示你JAVA_HOME找不到，现在去修改文件: 这是CentOS的一个大坑，手动配... [阅读全文]
-
一、安装环境 1、操作系统版本：CentOS 7.5 2、MySQL版本：5.7.22（社区版） 3、MySQL安装包：mysql-5.7.22-l... [阅读全文]
-
版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。
上一篇： JS之捕获冒泡和事件委托
发表评论