为了让ProxySQL能够找到后端的MySQL节点,需要将后端的MySQL Server加入到ProxySQL中。ProxySQL的一切配置行为都是在修改main库中的对应的表,所以添加节点到ProxySQL中实际上也是通过修改相关表来实现的。
管理后端节点有几个过程:
mysql_servers
。global_vairbles
,相关的变量为mysql-monitor_
开头的变量。mysql_replication_hostgroups
。mysql_users
。几个注意点:
(1).ProxySQL是通过监控后端节点的read_only
值来自动调整节点所属组的,例如read_only=1
的节点会移动到读组,read_only=0
的节点会移动到写组。所以,在配置读、写组之前,需要先监控后端节点。
(2).对于传统的主从复制,默认的read_only=0
,所以在第一步中,各slave节点的配置文件中需要加上read_only=1
。对于组复制、Galera,因为会自动强制设置非写节点的read_only=1
,所以无需额外配置该属性。
(3).ProxySQL支持传统主从复制结构(即异步、半同步、gtid复制)的后端,读、写组相关的表是mysql_replication_hostgroups
。还支持MySQL组复制结构的后端,相关的表是mysql_group_replication_hostgroups
,还支持Galera(如percona XtraDB cluster)结构的后端,不过ProxySQL是通过scheduler调度proxysql_galera_checker.sh
脚本来支持Galera的,而且目前还没有mysql_galera_hostgroups
(ProxySQL 2.0才新增该表)。
本文暂时只解释mysql_servers
和mysql_replication_hostgroups
,组复制相关的表在在后面介绍"ProxySQL+组复制"的文章中再介绍。
完成了上面的过程后,节点就一切正常了,然后就可以去配置ProxySQL的路由规则、查询缓存、SQL语句重写等功能。这些在后面的文章会详细介绍。
在开始本文下面的配置之前,请确保已经明白如何使用,以及。
假如后端有3个节点,使用的是传统的异步复制(1 master, 2 slave)。这3个节点的IP地址为:
192.168.100.22 192.168.100.23 192.168.100.24
要加入3个后端MySQL节点,只需向mysql_servers表插入3行对应的记录即可。以下是使用了一大堆默认值的insert语句:
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.100.22',3306); insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.100.23',3306); insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.100.24',3306); load mysql servers to runtime; save mysql servers to disk;
上面的insert语句表示将后端MySQL节点192.168.100.xx:3306
加入到hostgroup_id=10
的组中。
其实mysql_servers表有很多字段,很多字段都有默认值,正如上面的insert语句,除了3个字段,其它使用的全是字段的默认值。
以下是mysql_servers表的字段属性。
| 字段 | 数据类型 | 允许null | 默认值 | |---------------------|----------|-----------|--------| | hostgroup_id (pk) | int | not null | 0 | | hostname (pk) | varchar | not null | 无 | | port (pk) | int | not null | 3306 | | status | varchar | not null | online | | weight | int | not null | 1 | | compression | int | not null | 0 | | max_connections | int | not null | 1000 | | max_replication_lag | int | not null | 0 | | use_ssl | int | not null | 0 | | max_latency_ms | int | not null | 0 | | comment | varchar | not null | '' |
可见,新添加一个节点时,唯一必须指定的字段就是hostname值。如果不指定hostgroup_id
,那么节点将自动加入到hostgroup_id=0
的组中。
各字段的意义如下:
max_connections
值是合理的,以避免MySQL超负荷时ProxySQL继续向其发送请求。关于该表各字段的用法,请参见(我已翻译)。
以下是上面插入数据成功后的结果:
admin> select * from mysql_servers\G *************************** 1. row *************************** hostgroup_id: 10 hostname: 192.168.100.22 port: 3306 status: ONLINE weight: 1 compression: 0 max_connections: 1000 max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 comment: *************************** 2. row *************************** hostgroup_id: 10 hostname: 192.168.100.23 port: 3306 status: ONLINE weight: 1 compression: 0 max_connections: 1000 max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 comment: *************************** 3. row *************************** hostgroup_id: 10 hostname: 192.168.100.24 port: 3306 status: ONLINE weight: 1 compression: 0 max_connections: 1000 max_replication_lag: 0 use_ssl: 0 max_latency_ms: 0 comment:
需要注意的是,同一个节点是可以同时存在于多个组中的。最典型的情形是master既充当写节点,也充当读节点,这时它就同时存在于写组和读组。
ProxySQL通过Monitor模块监控后端MySQL Server的read_only
值来自动调整节点所属的组。所以,在配置读、写组之前,必须先配置好监控。
本文只简单介绍该模块的监控类型,以及如何配置监控。对于Monitor模块理论的详细介绍,请参见以下两篇文章:
首先看下Monitor库中的表:
admin> show tables from monitor; +------------------------------------+ | tables | +------------------------------------+ | mysql_server_connect_log | | mysql_server_group_replication_log | | mysql_server_ping_log | | mysql_server_read_only_log | | mysql_server_replication_lag_log | +------------------------------------+
Monitor监控4种指标:connect、ping、read_only和replication lag。下面稍作介绍。
1.connect监控
ProxySQL连接到各后端是否成功,成功/失败的连接将记录到表mysql_server_connect_log
中。
2.ping监控
这是一种心跳检测。Monitor模块向所有后端MySQL节点发起ping检查,ping成功/失败的情况将记录到表mysql_server_ping_log
中。当ping某后端的失败次数达到了mysql-monitor_ping_max_failures
时表示失去心跳,将发送一个信号给MySQL的主机组管理器来杀掉和该后端节点的所有连接。
请和connect监控区分开,connect监控是通过建立连接来检测和后端节点连接的连通性。ping监控是心跳检测,ProxySQL通过MySQL的一个ping API发送给后端MySQL服务上,然后等待ping回复,虽然是ping检测,但也是需要建立连接的。
所以,有两个确定连接不可用公式:
3.read_only监控
检查mysql_replication_hostgroups
表中所有节点的read_only
值,并记录到mysql_server_read_only_log
。如果read_only=1
,表示只读,是一个slave,这样的节点将会自动移入reader_hostgroup
中,如果read_only=0
,表示可写,可能是master,这样的节点将会自动移入writer_hostgroup
中。
4.replication lag监控
对mysql_servers
表中所有配置了max_replication_lag
的后端slave节点都检查复制延迟,通过show slave status
返回结果中的Seconds_Behind_Master
字段,判断slave和master之间的延迟程度,并记录到mysql_server_replication_lag_log
表中。
如果Seconds_Behind_Master > max_replication_lag,表示该slave延迟很严重,ProxySQL会自动避开这种slave节点,直到Seconds_Behind_Master < max_replication_lag。
Monitor监控上述指标时,会使用MySQL节点上的用户连接到后端节点,所以,需要先在后端节点上创建负责监控的用户。监控connect、ping和read_only这3项指标时,该用户只需具有USAGE
权限,如果要监控replication lag指标,则需要replication client
权限。
首先,在后端节点上创建用于监控的用户,顺便为其授予replication client
权限。只需在一个写节点(例如master)上创建即可,它会复制到其它节点上。
create user monitor@'192.168.100.%' identified by 'P@ssword1!'; grant replication client on *.* to monitor@'192.168.100.%';
然后,在ProxySQL上配置这个监控用户,配置方式是修改全局变量。
set mysql-monitor_username='monitor'; set mysql-monitor_password='P@ssword1!';
由于ProxySQL上所有的配置修改都是在修改main库中对应的表,上面的变量在main.global_variables
中,所以上面两个set语句和下面两个update语句是等价的。
update global_variables set variable_value='monitor' where variable_name='mysql-monitor_username'; update global_variables set variable_value='P@ssword1!' where variable_name='mysql-monitor_password';
在将配置load到runtime之前,可以先查看下connect和ping对应的log表。
admin> select * from mysql_server_connect_log; +----------------+------+------------------+-------------------------+---------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +----------------+------+------------------+-------------------------+---------------+ | 192.168.100.22 | 3306 | 1530968712977867 | 4174 | NULL | | 192.168.100.23 | 3306 | 1530968712988986 | 4908 | NULL | | 192.168.100.24 | 3306 | 1530968713000074 | 3044 | NULL | | 192.168.100.22 | 3306 | 1530968772978982 | 3407 | NULL | | 192.168.100.23 | 3306 | 1530968772989627 | 3404 | NULL | | 192.168.100.24 | 3306 | 1530968773000778 | 3444 | NULL | +----------------+------+------------------+-------------------------+---------------+ admin> select * from mysql_server_ping_log; +----------------+------+------------------+----------------------+-------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +----------------+------+------------------+----------------------+-------------+ | 192.168.100.22 | 3306 | 1530968712666540 | 452 | NULL | | 192.168.100.23 | 3306 | 1530968712668779 | 458 | NULL | | 192.168.100.24 | 3306 | 1530968712671541 | 324 | NULL | | 192.168.100.22 | 3306 | 1530968722667071 | 1190 | NULL | | 192.168.100.23 | 3306 | 1530968722669574 | 1162 | NULL | | 192.168.100.24 | 3306 | 1530968722673162 | 1380 | NULL | +----------------+------+------------------+----------------------+-------------+
不难发现,监控操作在load到runtime之前就已经生效了。这是有意为之的:通过这种方式,可以在节点添加到生产环境之前执行一些基本的健康检查。
监控的节点一切正常后(error=NULL),将配置load到runtime。
load mysql variables to runtime; save mysql variables to disk;
目前read_only和replication_lag的监控日志还是空。
admin> select * from mysql_server_read_only_log; Empty set (0.00 sec) admin> select * from mysql_server_replication_lag_log; Empty set (0.00 sec)
这是因为还没有对ProxySQL中的节点分组:writer_hostgroup、reader_hostgroup。
设置分组信息,需要修改的是main库中的mysql_replication_hostgroups
表(组复制则是mysql_group_replication_hostgroups
),该表只有3个字段:第一个字段名为writer_hostgroup,第二个字段为reader_hostgroup,第三个字段为注释字段,可随意写。
例如,指定写组的id为10,读组的id为20。
insert into mysql_replication_hostgroups values(10,20); admin> select * from mysql_replication_hostgroups; +------------------+------------------+----------+ | writer_hostgroup | reader_hostgroup | comment | +------------------+------------------+----------+ | 10 | 20 | cluster1 | +------------------+------------------+----------+
在该配置加载到RUNTIME生效之前,先查看下各mysql server所在的组。
admin> select hostgroup_id,hostname,port,status,weight from mysql_servers; +--------------+----------------+------+--------+--------+ | hostgroup_id | hostname | port | status | weight | +--------------+----------------+------+--------+--------+ | 10 | 192.168.100.22 | 3306 | ONLINE | 1 | | 10 | 192.168.100.23 | 3306 | ONLINE | 1 | | 10 | 192.168.100.24 | 3306 | ONLINE | 1 | +--------------+----------------+------+--------+--------+
3个节点都在hostgroup_id=10
的组中。
现在,将刚才mysql_replication_hostgroups
表的修改加载到RUNTIME生效。
load mysql servers to runtime; save mysql servers to disk;
一加载,Monitor模块就会开始监控后端的read_only值,当监控到read_only值后,就会按照read_only的值将某些节点自动移动到读/写组。
例如,此处所有节点都在id=10
的写组,slave1和slave2都是slave,它们的read_only=1
,这两个节点将会移动到id=20
的组。如果一开始这3节点都在id=20
的读组,那么移动的将是Master节点,会移动到id=10
的写组。
看结果:
admin> select hostgroup_id,hostname,port,status,weight from mysql_servers; +--------------+----------------+------+--------+--------+ | hostgroup_id | hostname | port | status | weight | +--------------+----------------+------+--------+--------+ | 10 | 192.168.100.22 | 3306 | ONLINE | 1 | | 20 | 192.168.100.23 | 3306 | ONLINE | 1 | | 20 | 192.168.100.24 | 3306 | ONLINE | 1 | +--------------+----------------+------+--------+--------+
配置好read_only的监控后,Monitor模块会每隔一段时间监控一次read_only值。
admin> select * from mysql_server_read_only_log; +----------------+------+------------------+-----------------+-----------+--------+ | hostname | port | time_start_us | success_time_us | read_only | error | +----------------+------+------------------+-----------------+-----------+--------+ | 192.168.100.22 | 3306 | 1530970372197917 | 8487 | 0 | NULL | | 192.168.100.23 | 3306 | 1530970372198992 | 7907 | 1 | NULL | | 192.168.100.24 | 3306 | 1530970372199835 | 8064 | 1 | NULL | | 192.168.100.22 | 3306 | 1530970373698824 | 10078 | 0 | NULL | | 192.168.100.23 | 3306 | 1530970373699825 | 9845 | 1 | NULL | | 192.168.100.24 | 3306 | 1530970373700786 | 10745 | 1 | NULL | +----------------+------+------------------+-----------------+-----------+--------+
Monitor模块会监控后端主机组中各slave的数据是否延迟于master,这个延迟行为称为replication lag
,俗称拖后腿。
如果某个slave节点上的数据比master落后很多(临界值见下文),表示这个slave节点处理速度慢,数据较旧。ProxySQL采用一种称为自动避开(automatic shunned)的方式,临时避开这个落后的节点。当ProxySQL避开某节点后,ProxySQL不会把SQL语句路由给这个节点。
ProxySQL有几种情况可能会触发自动避开节点的行为:
本文介绍关于replication lag的内容。
Monitor模块会每隔一段时间(mysql-monitor_replication_lag_interval
)去检查一次拖后腿情况,检测的方式是获取show slave status
中的Seconds_Behind_Master
字段值,然后和mysql_servers
表中max_replication_lag
字段的值比较:
只有传统复制结构的slave节点才需要设置max_replication_lag
字段,master无需设置,组复制和galera也无需设置,因为这两种复制结构中show slave status
的结果为空。例如,将读组中的所有节点都设置最多落后于master 10秒钟。
update mysql_servers set max_replication_lag=10 where hostgroup_id=20; load mysql servers to runtime; save mysql servers to disk;
需要注意的是,Seconds_Behind_Master的值并不总是可靠的,见 https://dev.mysql.com/doc/refman/5.7/en/show-slave-status.html 。
配置好后端MySQL节点、监控、分组后,接下来要配置的是MySQL的用户。
需要注意,ProxySQL中有3套用户:
admin-admin_credentials
设置,普通用户/密码通过变量admin-stats_credentials
设置。USAGE
权限,监控replication lag时需要replication client
权限。mysql-monitor_username
和mysql-monitor_password
将监控用户加入到ProxySQL中。例如,使用root用户来处理SQL请求。先在后端的写节点(如master节点)上授权root,该操作会复制给其它节点。
grant all on *.* to root@'192.168.100.%' identified by 'P@ssword1!';
然后,向ProxySQL的mysql_users
插入这个用户即可。这个表的字段很多,大多数字段都有默认值,以下是大部分使用默认值的插入语句:
insert into mysql_users(username,password,default_hostgroup) values ('root','P@ssword1!',10); load mysql users to runtime; save mysql users to disk;
上面指定了root用户的用户名、密码以及该用户默认的路由目标组。
ProxySQL有多种粒度的路由规则,每个用户都有默认的路由目标组,当使用该用户发送的SQL语句没有能够匹配的语句级路由规则,则会将该SQL语句路由给该用户默认的路由目标组。
例如,navicat工具使用root用户连接到了ProxySQL,发送了一个select语句,如果没有配置select语句的路由规则,那么这个select语句将默认路由给root用户的默认组。
下面先介绍一下mysql_users
表中的密码相关内容,然后再详细介绍mysql_users
表。
ProxySQL向mysql_users
表添加用户时,支持明文密码和hash加密密码。这个hash密码和mysql的password()
的算法是一样的。
但是,ProxySQL内部使用的是SQLite3引擎,不支持password()
。所以,想要向ProxySQL中使用hash加密密码,可以先通过mysql的password()
函数创建一个hash密码,然后copy到mysql_users表中。
例如:
[root@s4 ~]# mysql -uroot -pP@ssword1! -e 'select password("P@ssword1!");' mysql: [Warning] Using a password on the command line interface can be insecure. +-------------------------------------------+ | password("P@ssword1!") | +-------------------------------------------+ | *50572A5FABC7DA9CEE5EB5977EDDE59E38967422 | +-------------------------------------------+
然后插入到ProxySQL的mysql_users表:
insert into mysql_users(username,password,default_hostgroup) values ('root','*50572A5FABC7DA9CEE5EB5977EDDE59E38967422',10);
ProxySQL和MySQL对密码的处理方式都是:以"*"开头的密码,表示是hash加密密码。
在MySQL和ProxySQL中,使用SHA1(SHA1('clear_password'))
对clear_password进行加密。无法根据加密的hash密码推导回原始的明文密码。
当客户端连接到ProxySQL时,ProxySQL将使用该加密的密码对用户进行认证。如果该客户端是第一次认证,则ProxySQL会推导出一部分的hash密码SHA1('clear_password')
。推导出的信息会存储到runtime的内部数据结构中,以便ProxySQL连接后端MySQL时使用。
从1.2.3版本开始,引入了一个布尔类型的全局变量admin-hash_passwords
,默认为true。该变量表示,内存数据库的mysql_users
表中的明文密码,在load mysql users to runtime
时,对明文密码进行hash加密,并保存到runtime数据结构中。有了这个特性,可以以另一种方式保存加密密码:只需将其刷回内存数据库即可。
例如:
Admin> SELECT username,password FROM mysql_users; +----------+-----------+ | username | password | +----------+-----------+ | user1 | password1 | # 明文密码 | user2 | password2 | +----------+-----------+ Admin> LOAD MYSQL USERS TO RUNTIME; # 加载到runtime数据结构 Admin> SELECT username,password FROM mysql_users; +----------+-----------+ | username | password | +----------+-----------+ | user1 | password1 | # 还是明文密码 | user2 | password2 | +----------+-----------+
这个时候,runtime数据结构中的密码是加密密码,而内存数据库中的密码是明文密码。
将runtime数据结构数据刷回内存数据库,即可将加密密码保存到内存数据库中,然后还可以将加密的密码持久化到disk。
Admin> save mysql users to memory; Admin> SELECT username,password FROM mysql_users; +----------+-------------------------------------------+ | username | password | +----------+-------------------------------------------+ | user1 | *668425423DB5193AF921380129F465A6425216D0 | | user2 | *DC52755F3C09F5923046BD42AFA76BD1D80DF2E9 | +----------+-------------------------------------------+ Admin> save mysql users to disk;
唯一需要注意的是,admin-hash-passwords
变量是admin-
变量而非mysql-
变量,这意味着修改了这个变量后(虽然基本不会去修改),load/save操作的目标是"admin variables",而非"mysql variables"。
load admin variables to runtime; save admin variables to disk;
以下是mysql_users
表的属性。
| 字段名 | 数据类型 | 可为空? | 默认值 | |-----------------------|---------|----------|-------| |username (pk,uk) | VARCHAR | NOT NULL | | |password | VARCHAR | NULL | | |active | INT | NOT NULL | 1 | |use_ssl | INT | NOT NULL | 0 | |default_hostgroup | INT | NOT NULL | 0 | |default_schema | VARCHAR | NULL | | |schema_locked | INT | NOT NULL | 0 | |transaction_persistent | INT | NOT NULL | 1 | |fast_forward | INT | NOT NULL | 0 | |backend (pk) | INT | NOT NULL | 1 | |frontend (uk) | INT | NOT NULL | 1 | |max_connections | INT | NOT NULL | 10000 |
各字段的意义:
active=0
的用户会保留在库中,但不会加载到runtime数据结构中,只有active=1
用户才是有效用户。该字段默认值为1。mysql_servers
中的max_connections
字段控制的。注意,当前版本的ProxySQL要求所有的用户均设置frontend和backend为1(即所有用户都可以进行frontend --> ProxySQL
以及ProxySQL --> backend
的连接认证)。将来版本中,ProxySQL将分离这两部分连接的用户凭据。这样前端将永远不知道后端的用户凭据,它们只能通过中间的ProxySQL发送请求,无法直接和后端节点建立连接,从而提高安全性。
关于快速转发fast_forward:
mysql_users表中的transaction_persistent
字段,当它的值为1时,表示事务持久化:当某连接使用该用户开启了一个事务后,那么在事务提交/回滚之前,所有的语句都路由到同一个组中,避免语句分散到不同组。在以前的版本中,默认值为0,不知道从哪个版本开始,它的默认值为1。我们期望的值为1,所以强烈建议插入用户后先检查下这个字段的值是否为1,如果为0,则执行下面的语句修改为1。
update mysql_users set transaction_persistent=1 where username='root'; update mysql_users set transaction_persistent=1 where username='sqlsender'; load mysql users to runtime; save mysql users to disk;
添加后端节点、监控后端节点、添加MySQL用户是使用ProxySQL所必须完成的步骤。这3个步骤虽然需要操作的过程不多,但是涉及的内容还是比较多的。
强烈建议将mysql_servers、mysql_users、mysql_replication_hostgroups这3个表的所有字段都了解一遍。不仅如此,要熟练使用ProxySQL,还应该对main库中的表的各个字段都比较熟悉,至少要知道它们什么意思。
下面,将添加后端节点、监控后端节点、添加MySQL用户的操作过程抽取出来,算是个步骤总结:
######### 1.添加后端节点 # # insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.100.22',3306), (10,'192.168.100.23',3306), (10,'192.168.100.24',3306); load mysql servers to runtime; save mysql servers to disk; # 查看下各节点是否都是 ONLINE select * from mysql_servers\G ######### 2.在后端MySQL上创建监控用户和处理SQL语句的用户 # # 在后端master节点上执行以下语句 # create user monitor@'192.168.100.%' identified by 'P@ssword1!'; grant replication client on *.* to monitor@'192.168.100.%'; grant all on *.* to root@'192.168.100.%' identified by 'P@ssword1!'; ######### 3.在ProxySQL中配置监控用户 # # set mysql-monitor_username='monitor'; set mysql-monitor_password='P@ssword1!'; # 以上两个set语句等价于下面两个update: update global_variables set variable_value='monitor' where variable_name='mysql-monitor_username'; update global_variables set variable_value='P@ssword1!' where variable_name='mysql-monitor_password'; load mysql variables to runtime; save mysql variables to disk; # 查看下connect和ping的监控是否正常 select * from mysql_server_connect_log order by time_start_us desc limit 6; select * from mysql_server_ping_log order by time_start_us desc limit 6; ######### 4.配置读、写组 # # insert into mysql_replication_hostgroups values(10,20); load mysql servers to runtime; save mysql servers to disk; ######### 5.在ProxySQL中配置MySQL用户 # # insert into mysql_users(username,password,default_hostgroup) values ('root','P@ssword1!',10); load mysql users to runtime; save mysql users to memory; save mysql users to disk; update mysql_users set transaction_persistent=1 where username='root'; load mysql users to runtime; save mysql users to disk;
在后面的文章中,我会介绍以下percona版本的ProxySQL,percona ProxySQL提供了一个管理工具,它能通过额外的配置文件自动化配置上面一大堆的操作,大大简化了初始搭建使用ProxySQL的过程。
如对本文有疑问, 点击进行留言回复!!
项目实战: CMDB自动化资产扫描(搞懂这个项目 算你没白学运维)
MySQL中建表时可空(NULL)和非空(NOT NULL)的用法详解
SSD原理及Pytorch代码解读——网络架构(二):特征提取网络及总体计算过程
网友评论