88必发在线娱乐场 2

ProxySQL初体验

 

proxysql的配置系统特点

1 允许轻松自动更新配置。为此,有一个MySQL兼容的管理界面
2 允许在运行时修改尽可能多的配置项,而无需重新启动守护程序
3 允许轻松回滚错误的配置

这是使用多层配置系统实现的,允许设置从一层移动到另一层。配置系统的3层如下图所示:
+————————-+
|         RUNTIME         |
+————————-+
       /|\          |
        |           |
    [1] |       [2] |
        |          \|/
+————————-+
|         MEMORY          |
+————————-+ _
       /|\          |      |\
        |           |        \
    [3] |       [4] |         \ [5]
        |          \|/         \
+————————-+  +————————-+
|          DISK           |  |       CONFIG FILE       |
+————————-+  +————————-+

Preface

RUNTIME 

表示正在处理请求的线程使用的ProxySQL的内存中数据结构。这些包含使用的全局变量的值,分组到主机组的后端服务器列表或可连接到代理的MySQL用户列表。请注意,操作者永远不能直接修改RUNTIME配置部分的内容,总是需要通过底层来修改。

 

MEMORY(有时也称为main)

表示内存中的SQLite3数据库,它通过MySQL兼容界面暴露给外部。用户可以将MySQL客户端连接到此接口,并查询不同的表和数据库。通过此界面可用的配置表如下:

mysql_servers  – 后端服务器列表
mysql_users  –
可以连接到ProxySQL的用户列表和凭据。请注意,ProxySQL也将使用这些凭据连接到后端服务器
mysql_query_rules  –
用于将流量路由到不同的后端服务器的规则列表。这些规则也可能导致重写查询或缓存结果
global_variables  –
可以在运行时调整的整个代理中使用的全局变量列表。全局变量示例:
proxysql>select * from global_variables limit 3;
+——————————–+—————-+
| variable_name                  | variable_value |
+——————————–+—————-+
| mysql-shun_on_failures         | 5              |
| mysql-shun_recovery_time_sec   | 10             |
| mysql-query_retries_on_failure | 1              |
+——————————–+—————-+
3 rows in set (0.00 sec)
mysql_collations – 可用于代理使用的MySQL排序规则列表。
这些是直接从客户端库提取的。
[仅在调试版本中可用] debug_levels –
ProxySQL与其详细级别一起发送的调试语句的类型列表。
这允许我们在运行时轻松配置在日志中有什么样的语句来调试不同的问题。
这仅在调试版本中可用,因为它可能会影响性能

    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”.

DISK和CONFIG FILE

DISK表示磁盘上的SQLite3数据库,默认位置为$(DATADIR)/proxysql.db。
在重新启动过程中,内存中未被保留的配置将丢失,因此将配置保留在DISK中是非常重要的。
CONFIG文件是经典的配置文件,我们将在下一节中看到它与其他配置层之间的关系。

 

配置系统的加载方式

Introduce

启动过程

在正常启动时,ProxySQL读取其配置文件(如果存在),以确定其datadir。
接下来发生的事情取决于它的数据库文件(磁盘)是否存在于其datadir中。
如果找到数据库文件,ProxySQL将从持久的磁盘数据库初始化其内存中的配置。
因此,磁盘被加载到内存中,然后传播到运行时配置。
如果找不到数据库文件,并且存在配置文件,则会将配置文件解析并将其内容加载到内存数据库中,然后将其保存在磁盘数据库中,并在运行时加载。
如果重要的是要注意,如果找到数据库文件,则不会解析配置文件。
也就是说,在正常启动期间,ProxySQL仅从持久存储的磁盘数据库中初始化其内存中的配置。

 

初始启动(或 – 初始标志)

在初始启动时,内存和运行时配置从配置文件而不是数据库文件中填充。
有可能强制初始启动运行proxysql的–initial标志,它通过重命名旧数据库来重置数据库。
完成后,配置也会持续到磁盘数据库,这将用于下一次重新启动。

 
 
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).

重新加载启动(或–reload标志)

如果proxysql使用–reload标志执行,则会尝试将配置文件中的配置与数据库文件的内容合并。
之后,它执行定期启动。
不能保证ProxySQL会成功管理这两个配置源的冲突,并且用户应该验证合并是否符合预期。

 

在运行时修改配置

在运行时修改配置是通过ProxyServer的MySQL管理端口完成的。
连接到它之后,我们会看到一个MySQL兼容的界面来查询几个与ProxySQL相关的表:
proxysql>show tables;
+————————————–+
| tables                               |
+————————————–+
| global_variables                     |
| mysql_collations                     |
| mysql_query_rules                    |
| mysql_replication_hostgroups         |
| mysql_servers                        |
| mysql_users                          |
| runtime_global_variables             |
| runtime_mysql_query_rules            |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers                |
| runtime_mysql_users                  |
| runtime_scheduler                    |
| scheduler                            |
+————————————–+
13 rows in set (0.00 sec)
每个这样的表在管理界面中具有明确定义的角色:

mysql_servers包含用于连接到ProxySQL的后端服务器列表
mysql_users包含用于向ProxySQL和后端服务器进行身份验证的用户列表
mysql_query_rules包含缓存,路由或重写通过代理的SQL查询的规则
global_variables包含单个表中的MySQL变量和管理变量
debug_levels仅用于ProxySQL的调试版本
这些表表示从上图的中间层(内存数据库),可以使用标准SQL查询进行操作。
为了从该层向上或向下移动配置,请参阅下一节。

88必发在线娱乐场 1

Moving config between layers(在层之间移动配置)

为了在三层之间移动配置,通过管理界面有一组不同的管理命令。
一旦了解了三层中的每一层意味着什么,语义应该是相当明显的。
连同每个命令的说明,旁边写有一个数字。 数字对应于上图中的箭头。

 

For handling MySQL users:

[1] LOAD MYSQL USERS FROM MEMORY / LOAD MYSQL USERS TO RUNTIME
loads MySQL users from the in-memory database to the runtime data
structures
[2] SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME
persists the MySQL users from the runtime data structures to the
in-memory database
[3] LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK
loads MySQL users from the on-disk database to the in-memory database
[4] SAVE MYSQL USERS FROM MEMORY / SAVE MYSQL USERS TO DISK
persists the MySQL users from the in-memory database to the on-disk
database
[5] LOAD MYSQL USERS FROM CONFIG
loads from the configuration file the users into the in-memory
database

    

For handling MySQL servers:

[1] LOAD MYSQL SERVERS FROM MEMORY / LOAD MYSQL SERVERS TO RUNTIME
loads MySQL servers from the in-memory database to the runtime data
structures
[2]88必发在线娱乐场, SAVE MYSQL SERVERS TO MEMORY / SAVE MYSQL SERVERS FROM RUNTIME
persists the MySQL servers from the runtime data structures to the
in-memory database
[3] LOAD MYSQL SERVERS TO MEMORY / LOAD MYSQL SERVERS FROM DISK
loads MySQL servers from the on-disk database to the in-memory
database
[4] SAVE MYSQL SERVERS FROM MEMORY / SAVE MYSQL SERVERS TO DISK
persists the MySQL servers from the in-memory database to the on-disk
database
[5] LOAD MYSQL SERVERS FROM CONFIG
loads from the configuration file the servers into the in-memory
database

*    The configuration of ProxySQL is a three-layer structure:*

For handling MySQL query rules:

[1] LOAD MYSQL QUERY RULES FROM MEMORY / LOAD MYSQL QUERY RULES TO
RUNTIME
loads MySQL query rules from the in-memory database to the runtime data
structures
[2] SAVE MYSQL QUERY RULES TO MEMORY / SAVE MYSQL QUERY RULES FROM
RUNTIME
persists the MySQL query rules from the runtime data structures to the
in-memory database
[3] LOAD MYSQL QUERY RULES TO MEMORY / LOAD MYSQL QUERY RULES FROM
DISK
loads MySQL query rules from the on-disk database to the in-memory
database
[4] SAVE MYSQL QUERY RULES FROM MEMORY / SAVE MYSQL QUERY RULES TO
DISK
persists the MySQL query rules from the in-memory database to the
on-disk database
[5] LOAD MYSQL QUERY RULES FROM CONFIG
loads from the configuration file the query rules into the in-memory
database

 

For handling MySQL variables:

[1] LOAD MYSQL VARIABLES FROM MEMORY / LOAD MYSQL VARIABLES TO
RUNTIME
loads MySQL variables from the in-memory database to the runtime data
structures
[2] SAVE MYSQL VARIABLES FROM MEMORY / SAVE MYSQL VARIABLES TO DISK
persists the MySQL variables from the in-memory database to the on-disk
database
[3] LOAD MYSQL VARIABLES TO MEMORY / LOAD MYSQL VARIABLES FROM DISK
loads MySQL variables from the on-disk database to the in-memory
database
[4] SAVE MYSQL VARIABLES TO MEMORY / SAVE MYSQL VARIABLES FROM
RUNTIME
persists the MySQL variables from the runtime data structures to the
in-memory database
[5] LOAD MYSQL VARIABLES FROM CONFIG
loads from the configuration file the variables into the in-memory
database

88必发在线娱乐场 2

For handling admin variables:

[1] LOAD ADMIN VARIABLES FROM MEMORY / LOAD ADMIN VARIABLES TO
RUNTIME
loads admin variables from the in-memory database to the runtime data
structures
[2] SAVE ADMIN VARIABLES TO MEMORY / SAVE ADMIN VARIABLES FROM
RUNTIME
persists the admin variables from the runtime data structures to the
in-memory database
[3] LOAD ADMIN VARIABLES TO MEMORY / LOAD ADMIN VARIABLES FROM DISK
loads admin variables from the on-disk database to the in-memory
database
[4] SAVE ADMIN VARIABLES FROM MEMORY / SAVE ADMIN VARIABLES TO DISK
persists the admin variables from the in-memory database to the on-disk
database

 

  *  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.

http://www.proxysql.com/compare

 

Official website:

http://www.proxysql.com/

 

Github websit:

https://github.com/sysown/proxysql/releases/

 

Percona
websit:

http://www.percona.com/downloads/proxysql

 

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 | match_pattern | schemaname | destination_hostgroup | apply |
209 +--------+----------+---------------+------------+-----------------------+-------+
210 | 1      | zlm      | ^select       | zlm        | 20                    | 1     |
211 +--------+----------+---------------+------------+-----------------------+-------+
212 1 row in set (0.00 sec)
213 
214 (admin@127.0.0.1 6032)[stats]>select hostgroup,schemaname,username,substr(digest_text,120,-120),count_star from stats_mysql_query_digest;
215 +-----------+------------+----------+------------------------------+------------+
216 | hostgroup | schemaname | username | substr(digest_text,120,-120) | count_star |
217 +-----------+------------+----------+------------------------------+------------+
218 | 20        | zlm        | zlm      | insert into t2 values(?,?)   | 1          |
219 | 20        | zlm        | zlm      | select * from t2             | 1          |
220 | 20        | zlm        | zlm      | show tables                  | 1          |
221 +-----------+------------+----------+------------------------------+------------+
222 3 rows in set (0.00 sec)
223 
224 //It still doesn't take effect.What's wrong with it?