mysql8.0 读写分离_proxysql + mysql8.0 MGR + 读写分离

一、环境

1.1 版本

操作系统：CentOS 7.5

中间件：proxysql 1.4.15

数据库：mysql 8.0.16

1.2 数据库(innodb cluster)   ip:192.168.1.1

port:8010    cluster-node1

port:8011    cluster-node2

port:8012    cluster-node3

port:8013    cluster-node4

port:8014    cluster-node5

1.3 中间件(proxysql)

port:6042    管理端口

port:6043    应用端口

1.4 hostgroup分组规划

写组：     writer_hostgroup = 10

备写组：backup_writer_hostgroup = 20

读组：  reader_hostgroup = 30

离线组：offline_hostgroup = 40

二、配置proxysql

2.1 初始化proxysql

#如果想重新配置一个proxysql，建议使用初始化命令清空历史数据。

systemctl stop proxysql6042

service proxysql6042 initial

2.2 配置后端数据库地址

#插入集群的各节点地址，HG=40代表脱机组，后期通过自动检测让其自动再分配组。

insert into mysql_servers (hostgroup_id, hostname, port,comment) values(40,'192.168.1.1',8010,'cluster');

insert into mysql_servers (hostgroup_id, hostname, port,comment) values(40,'192.168.1.1',8011,'cluster');

insert into mysql_servers (hostgroup_id, hostname, port,comment) values(40,'192.168.1.1',8012,'cluster');

insert into mysql_servers (hostgroup_id, hostname, port,comment) values(40,'192.168.1.1',8013,'cluster');

insert into mysql_servers (hostgroup_id, hostname, port,comment) values(40,'192.168.1.1',8014,'cluster');

select * from mysql_servers;

#将配置写入运行中，然后再保存到磁盘

LOAD MYSQL SERVERS TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;

2.3 配置用户

1>配置监控用户

在mysql创建监控用户

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor@123';      #监控账号

GRANT select ON sys.* TO 'monitor'@'%';

在proxysql配置监控用户

set mysql-monitor_username='monitor';

set mysql-monitor_password='monitor@123';

将配置写入运行中，然后再保存到磁盘

LOAD MYSQL VARIABLES TO RUNTIME;

SAVE MYSQL VARIABLES TO DISK;

查看监控是否正常，如果有数据，则正常

select * from mysql_server_connect_log;

select * from mysql_server_ping_log;

2>配置后端mysql用户

配置后端用户，用于访问应用访问，在mysql库中创建

CREATE USER 'proxydb'@'%' IDENTIFIED BY 'proxydb';

GRANT select,delete,insert,update,execute ON *.* TO 'proxydb'@'%';

#(***)注意:配置默认组为HG=10,后期读写分离未匹配到的请求全部路由到默认组。

insert into mysql_users(username,password,default_hostgroup) values('proxydb','proxydb',10);

select * from mysql_users;

#将配置写入运行中，然后再保存到磁盘

load mysql users to runtime;

save mysql users to disk;

2.4 配置分组、监控

1>定义分组规则

#根据预定义规则进行分组，并插入数据，其中10为写组，20为备写组，30读组，40离线组。

#ProxySQL通过表mysql_group_replication_hostgroups原生支持mgr和cluster。它将根据分组规则，实时监控节点状态，动态维护runtime_mysql_servers表的各个组成员信息。

select * from mysql_group_replication_hostgroups;

insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active) values(10,20,30,40,1);

#查看

select writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active from mysql_group_replication_hostgroups;

+------------------+-------------------------+------------------+-------------------+--------+

| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active |

+------------------+-------------------------+------------------+-------------------+--------+

| 10 | 20 | 30 | 40 | 1 |

+------------------+-------------------------+------------------+-------------------+--------+

1 row in set (0.00 sec)

#将配置写入运行中，然后再保存到磁盘

LOAD MYSQL SERVERS TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;

2>执行监控脚本

执行以下监控脚本，proxysql将定时读取sys.gr_member_routing_candidate_status的数据，动态维护runtime_mysql_servers各个组成员。

#以下SQL在mysql执行

USE sys;

DELIMITER $$

CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);$$

CREATE FUNCTION gr_member_in_primary_partition()

RETURNS VARCHAR(3)

DETERMINISTIC

BEGIN

RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM

performance_schema.replication_group_members WHERE MEMBER_STATE NOT IN ('ONLINE', 'RECOVERING')) >=

((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),

'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN

performance_schema.replication_group_member_stats USING(member_id) where member_id=my_id());

END$$

CREATE VIEW gr_member_routing_candidate_status AS SELECT

sys.gr_member_in_primary_partition() as viable_candidate,

IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM

performance_schema.global_variables WHERE variable_name IN ('read_only',

'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,

Count_Transactions_Remote_In_Applier_Queue as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert'

from performance_schema.replication_group_member_stats where member_id=my_id();$$

DELIMITER ;

mysql执行，查看各节点状态

select * from sys.gr_member_routing_candidate_status;

脚本执行成功后，检查发现已按照我们的分组规则自动分组

select * from runtime_mysql_servers;

2.5 配置读写分离

#删除旧的查询路由规则

DELETE FROM mysql_query_rules;

#插入新的路由规则

#(***)注意：设置rule_id从101开始，因为规则处理以rule_id的顺序进行。预留rule_id，方便后期添加新规则。

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)

VALUES(101,1,'^SELECT.*FOR UPDATE$',10,1),(102,1,'^SELECT',30,1);

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)

VALUES(100,1,'.*@.*',10,1);

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK;

以上路由规则的含义大致如下：

--首先匹配'rule_id'=101的规则，将以'SELECT'开头,'FOR UPDATE'结尾的语句路由到HG10;

--然后匹配'rule_id'=102的规则，将上一条规则匹配之外的且以'SELECT'开头的语句路由到HG30;

--最后将以上规则未匹配到的所有请求路由到默认组HG10。

查看

select rule_id,active,match_digest,negate_match_pattern,destination_hostgroup,apply,flagIN,flagOUT from mysql_query_rules;

+---------+--------+----------------------+----------------------+-----------------------+-------+--------+---------+

| rule_id | active | match_digest | negate_match_pattern | destination_hostgroup | apply | flagIN | flagOUT |

+---------+--------+----------------------+----------------------+-----------------------+-------+--------+---------+

| 101 | 1 | ^SELECT.*FOR UPDATE$ | 0 | 10 | 1 | 0 | NULL |

| 102 | 1 | ^SELECT | 0 | 30 | 1 | 0 | NULL |

+---------+--------+----------------------+----------------------+-----------------------+-------+--------+---------+

2 rows in set (0.00 sec)

#验证读写分离(退出管理员模式，使用业务账号验证读写分离)

#登录后执行一系列操作，然后去管理口查看路由效果

mysql -h127.0.0.1 -uproxydb -P6043 -pproxydb#使用应用账号登录

#在管理口查看读写分离效果

mysql -h127.0.0.1 -uadmin -P6042 -pproxydb

select hostgroup,schemaname,username,digest,digest_text,count_star from stats_mysql_query_digest;

三、proxysql管理

3.1 账号管理

--查看默认管理账号

select @@admin-admin_credentials; /* 默认管理账号只能本地访问*/

+---------------------------+

| @@admin-admin_credentials |

+---------------------------+

| admin:admin |

+---------------------------+

--修改本地管理账号，并增加远程管理账号

set admin-admin_credentials='admin:admin@123;myadmin:myadmin@123';

--立即生效且永久保存到磁盘

load admin variables to runtime;

save admin variables to disk;

3.2 监控库  --monitor库

主要用于监控后端DB健康状态

1、mysql_server_connect_log

#检查MySQL服务是否可用，该表记录检测连接的日志

相关参数：

--mysql-monitor_connect_interval：控制其检测的时间间隔 #默认2分钟

--mysql-monitor_connect_timeout：控制连接是否超时(默认200毫秒)

2、mysql_server_ping_log

#检测服务器是否可用，该表记录ping日志

相关参数：

--mysql-monitor_ping_interval：控制 ping 的时间间隔，默认值：10000(毫秒)

3.2 生产环境的配置建议

set mysql-default_charset='utf8mb4';

set mysql-default_sql_mode='NO_ENGINE_SUBSTITUTION';

set mysql-mysql-max_allowed_packet=67108864;

set mysql-ping_interval_server_msec=300000;#sleep会话保持时长300s

set mysql-max_connections=4000;#ProxySQL能处理的和前端客户端的最大连接数

set mysql-server_version='8.0.16';#proxysql响应给客户端的MySQL版本号

LOAD MYSQL VARIABLES TO RUNTIME;

SAVE MYSQL VARIABLES TO DISK;