ProxySQL 读写分离

ProxySQL 读写分离

角色 主机IP server_id
Proxysql 192.168.119.130
Master 192.168.119.130:3306   62
Slave 192.168.119.130:3307   63

主从已搭建好。
注意点：slave节点需要设置read_only=1

首先启动ProxySQL。
service proxysql start

使用mysql客户端连接到ProxySQL的管理接口，该接口的默认管理员用户和密码都是admin。
[root@testdb1 ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin> '

向ProxySQL中添加MySQL节点
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.119.130',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(20,'192.168.119.130',3307);
load mysql servers to runtime;
save mysql servers to disk;

查看是否插入成功，以及它们的状态：
admin> select * from mysql_serversG

监控后端MySQL节点
在master上执行：
create user monitor@'192.168.119.%' identified by 'chengce243';
grant replication client on *.* to monitor@'192.168.119.%';
flush privileges;

在ProxySQL上配置监控
set mysql-monitor_username='monitor';
set mysql-monitor_password='chengce243';
load mysql variables to runtime;
save mysql variables to disk;

以上设置实际上是在修改global_variables表，它和下面两个语句是等价的：
UPDATE global_variables SET variable_value='monitor'
WHERE variable_name='mysql-monitor_username';

UPDATE global_variables SET variable_value='chengce243'
WHERE variable_name='mysql-monitor_password';

在ProxySQL上查看监控状态：
admin> select * from mysql_server_connect_log;

在ProxySQL上对心跳信息的监控(对ping指标的监控)：
admin> select * from mysql_server_ping_log;

在ProxySQL上配置节点分组：
insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup) values(10,20);
load mysql servers to runtime;
save mysql servers to disk;

在ProxySQL上查看
admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;

配置mysql_users
在master节点上执行：
grant all on *.* to sqlsender@'192.168.119.%' identified by 'chengce243';

在ProxySQL上
insert into mysql_users(username,password,default_hostgroup) values('root','chengce243',10);
insert into mysql_users(username,password,default_hostgroup) values('sqlsender','chengce243',10);
load mysql users to runtime;
save mysql users to disk;

admin> select * from mysql_usersG
*************************** 1. row ***************************
username: root
password: chengce243
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
comment:
*************************** 2. row ***************************
username: sqlsender
password: chengce243
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
comment:
2 rows in set (0.00 sec)

只有 active =1的用户才是有效的用户。

至于 transaction_persistent 字段，当它的值为1时，表示事务持久化：当某连接使用该用户开启了一个事务后，那么在事务提交/回滚之前，所有的语句都路由到同一个组中，避免语句分散到不同组(更进一步的，它会自动禁用multiplexing，让同一个事务的语句从同一个连接路由出去，保证路由到同一个组的同一个节点)。在以前的版本中，默认值为0，不知道从哪个版本开始，它的默认值为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;

读写分离：配置路由规则
在ProxySQL上
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),
(2,1,'^SELECT',20,1);

load mysql query rules to runtime;
save mysql query rules to disk;

另开一个终端，分别测试下它们是否能路由到默认的hostgroup_id=10(它是一个写组)读、写数据
[root@testdb1 ~]# mysql -usqlsender -pchengce243 -P6033 -h127.0.0.1 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 63 |
+-------------+

读操作已经路由给读组，再看看写操作。这里以事务持久化进行测试。
[root@testdb1 ~]# mysql -uroot -pchengce243 -P6033 -h127.0.0.1 -e '
start transaction;
select @@server_id;
commit;
select @@server_id;'

mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 62 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 63 |
+-------------+