暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / proxysql 读写分离(MGR)

proxysql 读写分离(MGR)

原创 XY 2023-11-17
156

MGR服务器信息

主机名

真实Ip

角色

mysql1

10.60.34.10

主

mysql2

10.60.34.9

备

mysql3

10.60.34.11

备

 

 

安装 proxysql

上传 proxysql-2.5.0-1-centos7.x86_64.rpm 依赖包

yum install perl-DBD*   
yum installperl-DBI*     ---安装必要依赖


rpm -ivh proxysql-2.5.0-1-centos7.x86_64.rpm  --下载依赖包


rpm -qa | grep proxysql                       --检查环境
rpm -ql proxysql


systemctl status proxysql.service             --验证 proxysql 状态
systemctl start proxysql.service

注意：还需要安装一个 MySQL 客户端（或者解压一个 MySQL 二进制安装包，之后到bin目录登录 proxy SQL）

 

关闭防火墙

systemctl disable firewalld.service
vi /etc/selinux/config
# 将 SELINUX=enforcing 改为 SELINUX=disabled

 

 

连接

mysql -uadmin -padmin -h127.0.0.1 -P6032

 

 

 

 

添加数据库

insert into mysql_servers(hostgroup_id,hostname,port) values (10,'10.60.34.10',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'10.60.34.9',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'10.60.34.11',3306);


load mysql servers to runtime;
save mysql servers to disk;

注意：IP应该为真实IP。ip addr 或者 ip a 查看。组号相同没关系，只读权限的数据库 proxy SQL 会自动将其划分为只读组。

 

 

MGR写库 增加 监控用户 和 业务代理用户

CREATE USER 'monitor'@'%' IDENTIFIED BY "monitor@1025";
CREATE USER 'proxysql'@'%' IDENTIFIED BY "proxysql@1025";


GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%' ;
GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' ;

 

ProxySQL 增加 监控用户 和 业务账号

监控账号

set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor@1025';

 

程序账号

insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('proxysql','proxysql@1025',1,10,1);
load mysql variables to runtime;
save mysql servers to disk;

 

MGR写库增加mgr监控视图

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();$$

注意：这是8.0的监控视图和5.7不通用

设置读写组

主负责写、从负责读，当MGR主库切换后，代理自动识别主从。

ProxySQL代理每一个后端MGR集群时，都必须为这个 MGR 定义 写组10、备写组20、读组30、离线组40。

insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) values (10,20,30,40,1,1,0,100);
load mysql servers to runtime;
save mysql servers to disk;
load mysql users to runtime;
save mysql users to disk;
load mysql variables to runtime;
save mysql variables to disk;

配置读写分离规则

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),
(2,1,'^SELECT',30,1);


load mysql query rules to runtime;
save mysql query rules to disk;
load mysql servers to runtime;
save mysql servers to disk;
load mysql users to runtime;
save mysql users to disk;
load mysql variables to runtime;
save mysql variables to disk;

 

检查状态

select hostgroup_id, hostname, port,status from runtime_mysql_servers;

发现之前的主库自动成为 10 写组，从库自动成为了 30 读组

测试

使用 业务代理用户 登录 proxysql 客户端 （6033端口）

mysql -uproxysql -pproxysql@1025 -h127.0.0.1 -P6033

执行一个 create 写操作，一个 select 读操作

 

登录 proxysql 管理端 验证（6032端口）

mysql -uadmin -padmin -h127.0.0.1 -P6032

查看

select hostgroup,digest_text from stats_mysql_query_digest;

验证成功，10写组，30读组MGR服务器信息

主机名

真实Ip

角色

mysql1

10.60.34.10

主

mysql2

10.60.34.9

备

mysql3

10.60.34.11

备

 

 

安装 proxysql

上传 proxysql-2.5.0-1-centos7.x86_64.rpm 依赖包

yum install perl-DBD*   
yum installperl-DBI*     ---安装必要依赖


rpm -ivh proxysql-2.5.0-1-centos7.x86_64.rpm  --下载依赖包


rpm -qa | grep proxysql                       --检查环境
rpm -ql proxysql


systemctl status proxysql.service             --验证 proxysql 状态
systemctl start proxysql.service

注意：还需要安装一个 MySQL 客户端（或者解压一个 MySQL 二进制安装包，之后到bin目录登录 proxy SQL）

 

关闭防火墙

systemctl disable firewalld.service
vi /etc/selinux/config
# 将 SELINUX=enforcing 改为 SELINUX=disabled

 

 

连接

mysql -uadmin -padmin -h127.0.0.1 -P6032



 

 

 

添加数据库

insert into mysql_servers(hostgroup_id,hostname,port) values (10,'10.60.34.10',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'10.60.34.9',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'10.60.34.11',3306);


load mysql servers to runtime;
save mysql servers to disk;

注意：IP应该为真实IP。ip addr 或者 ip a 查看。组号相同没关系，只读权限的数据库 proxy SQL 会自动将其划分为只读组。

 

 

MGR写库 增加 监控用户 和 业务代理用户

CREATE USER 'monitor'@'%' IDENTIFIED BY "monitor@1025";
CREATE USER 'proxysql'@'%' IDENTIFIED BY "proxysql@1025";


GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%' ;
GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' ;

 

ProxySQL 增加 监控用户 和 业务账号

监控账号

set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor@1025';

 

程序账号

insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('proxysql','proxysql@1025',1,10,1);
load mysql variables to runtime;
save mysql servers to disk;

 

MGR写库增加mgr监控视图

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();$$

注意：这是8.0的监控视图和5.7不通用

设置读写组

主负责写、从负责读，当MGR主库切换后，代理自动识别主从。

ProxySQL代理每一个后端MGR集群时，都必须为这个 MGR 定义 写组10、备写组20、读组30、离线组40。

insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) values (10,20,30,40,1,1,0,100);
load mysql servers to runtime;
save mysql servers to disk;
load mysql users to runtime;
save mysql users to disk;
load mysql variables to runtime;
save mysql variables to disk;

配置读写分离规则

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),
(2,1,'^SELECT',30,1);


load mysql query rules to runtime;
save mysql query rules to disk;
load mysql servers to runtime;
save mysql servers to disk;
load mysql users to runtime;
save mysql users to disk;
load mysql variables to runtime;
save mysql variables to disk;

 

检查状态

select hostgroup_id, hostname, port,status from runtime_mysql_servers;


发现之前的主库自动成为 10 写组，从库自动成为了 30 读组

测试

使用 业务代理用户 登录 proxysql 客户端 （6033端口）

mysql -uproxysql -pproxysql@1025 -h127.0.0.1 -P6033


执行一个 create 写操作，一个 select 读操作

 

登录 proxysql 管理端 验证（6032端口）

mysql -uadmin -padmin -h127.0.0.1 -P6032

查看

select hostgroup,digest_text from stats_mysql_query_digest;


验证成功，10写组，30读组

proxysql
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论