MGR+ProxySQL部署初试

一.规划

IP、操作系统、主机名、角色、软件包版本、安装方式

【node1】

ip地址：10.10.1.211

OS：CentOS 7.6

主机名：vl7bjsqltest01

角色：master

MySQL版本：5.7.28

安装方式：通用二进制包

【node2】

ip地址：10.10.1.212

OS：CentOS 7.6

主机名：vl7bjsqltest02

角色：slave1

MySQL版本：5.7.28

安装方式：通用二进制包

【node3】

ip地址：10.10.1.213

OS：CentOS 7.6

主机名：vl7bjsqltest03

角色：slave2

MySQL版本：5.7.28

安装方式：通用二进制包

【node4】

ip地址：10.10.1.214

OS：CentOS 7.6

主机名：vl7bjsqltest04

角色：ProxySQL

Proxy版本：1.4.16

安装方式：rpm


二.MySQL节点基础环境准备

1.配置主机名

编辑四个主机名和ip映射关系

vim /etc/hosts

修改主机名

vim /etc/sysconfig/network

2.清理历史环境

 rpm -qa |grep mariadb

如我这里打印出来的信息是mariadb-libs-5.5.60-1.el7_5.x86_64

rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64

3.创建用户和目录

useradd mysql -s /sbin/nologin

id mysql

mkdir -p /app/database/

mkdir -p /data/3306/

mkdir -p /binlog/

chown -R mysql.mysql /app/ /data/ /binlog/


三.MySQL安装

上传tar.gz文件至/app/database/目录，并解压:

tar xf mysql-5.7.28-el7-x86_64.tar.gz

我们重命名为mysql

mv mysql-5.7.28-el7-x86_64/  mysql/

环境变量配置：

vim /etc/profile

添加一行：

export PATH=/app/database/mysql/bin:$PATH

生效配置：

source /etc/profile

验证：

mysql -V

初始化：

mysqld --initialize-insecure --user=mysql --basedir=/app/database/mysql --datadir=/data/3306/

基本配置文件，以master节点为例，不同节点server_id需要设置不同数字（之后还会增加MGR参数和其他参数）
cat > /etc/my.cnf <<EOF

[mysqld]

user=mysql

basedir=/app/database/mysql

datadir=/data/3306

server_id=1   

port=3306

socket=/tmp/mysql.sock

[mysql]

socket=/tmp/mysql.sock

EOF

准备mysql启动脚本

cd /app/database/mysql/support-files/

cp mysql.server /etc/init.d/mysqld 

将脚本添加到systemd管理中

chkconfig --add mysqld

启动

systemctl start mysqld

mysql -uroot -p进入，不需要输入密码

创建自己的账号

create user 'wenjie.wang' identified by'123456';

grant all privileges on *.* to 'wenjie.wang'@'%';

更改5.7版本的root密码：

update mysql.user set authentication_string = password('123456') where user='root';

重启mysql后生效


四.MGR配置文件

master节点：

[mysqld]

user=mysql

datadir=/data/3306

basedir=/app/database/mysql

port=3306

socket=/data/3306/mysql.sock

pid-file=/data/3306/mysqld.pid

log-error=/data/3306/mysqlerr.log

innodb_file_per_table=1

skip_name_resolve=1

slow_query_log=1

slow_query_log_file=/data/3306/mysql-slow.log

long_query_time=1

symbolic-links=0

explicit_defaults_for_timestamp=1

log_bin=/binlog/mysqlbin

log_bin_index=/binlog/mysql-bin.index

binlog_format=row

sync_binlog=1

server_id=1

gtid_mode=ON

enforce_gtid_consistency=ON

master_info_repository=TABLE

relay_log_info_repository=TABLE

binlog_checksum=NONE

log_slave_updates=ON

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="03f43914-7f38-4a00-919f-f748794c04ac"

loose-group_replication_start_on_boot=off

loose-group_replication_local_address="10.10.1.211:33061"

loose-group_replication_group_seeds="10.10.1.211:33061,10.10.1.212:33062,10.10.1.213:33063"

loose-group_replication_bootstrap_group=off

loose-group_replication_single_primary_mode=on

loose-group_replication_enforce_update_everywhere_checks= off

[mysql]

socket=/data/3306/mysql.sock

slave节点配置文件只需要把loose-group_replication_local_address改成对应的即可；


五.开启MGR

主节点：

创建用户

set sql_log_bin=0;

grant replication slave,replication client on *.* to repl@'localhost' identified by '123456';

grant replication slave,replication client on *.* to repl@'127.0.0.1' identified by '123456';

grant replication slave,replication client on *.* to repl@'10.10.1.%' identified by '123456';

SET SQL_LOG_BIN=1;

开启分布式复制

change master to master_user='repl',master_password='123456' for channel 'group_replication_recovery';

加载GR插件

install plugin group_replication soname 'group_replication.so';

show plugins;

启动复制程序

set global group_replication_bootstrap_group=ON;

start group_replication;

set global group_replication_bootstrap_group=OFF;

#检测组是否创建并已加入新成员

select * from performance_schema.replication_group_members;

从节点：

创建用户

set sql_log_bin=0;

grant replication slave,replication client on *.* to repl@'localhost' identified by '123456';

grant replication slave,replication client on *.* to repl@'127.0.0.1' identified by '123456';

grant replication slave,replication client on *.* to repl@'10.10.1.%' identified by '123456';

SET SQL_LOG_BIN=1;

开启分布式复制

change master to master_user='repl',master_password='123456' for channel 'group_replication_recovery';

加载GR插件

install plugin group_replication soname 'group_replication.so';

show plugins;

启动复制程序

start group_replication;

#检测组是否创建并已加入新成员

select * from performance_schema.replication_group_members;

#查看主节点UUID，并结合上一条语句的打印结果来判断（8.0之后上面一条语句就够了）

SHOW STATUS LIKE 'group_replication_primary_member';


六.MGR同步验证

主节点创建测试数据：

create database wwj;

use wwj;

create table tb(id int unsignedauto_increment primary key not null,age int not null);

insert into tb(age) values(35),(40);


从节点检查：

select * from wwj.tb;

正常同步；



七.主节点宕机测试

宕机前

select * from performance_schema.replication_group_members;


show status like‘group_replication_primary_member’;

查看当前谁是master


根据当前master的uuid和上面打印结果对比，可得知当前master的主机名

这时候我们停止主节点服务

然后到从节点查看当前master的uuid，果然变了：


再查看复制组：


当前02节点为主

此时重新开启主节点服务，然后重新加入组：

change master tomaster_user='repl',master_password='123456' for channel'group_replication_recovery';

start group_replication;


重新加入组成功，之后检查主节点就是之前的02节点。

正式环境很难这样直接加入，很可能slave执行的事务gtid与master不一致，因此就需要停机时间将宕机节点重新同步。如果直接加入的话，后面可以通过查看一张表记录来判断落后多少事务。

select * from sys.gr_member_routing_candidate_status;

这个是由ProxySQL需要的脚本创建的，在全部部署完成后才可使用。


八.安装ProxySQL

在node4上

安装依赖：

yum -y install perl.x86_64

yum install -y libaio.x86_64

yum -y install net-tools.x86_64

yum install perl-DBD-MySQL -y

官网下载rpm

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

我这里是proxysql-1.4.16-1.1.el7.x86_64.rpm

rpm -ivh proxysql-1.4.16-1.1.el7.x86_64.rpm

启动

systemctl start proxysql

netstat -anlp | grep proxysql

6032是ProxySQL的管理端口号，6033是对外服务的端口号

proxysql --help

然后按正常方法在本机二进制安装mysql


九.ProxySQL配置用户和服务器

ProxySQL管理用的用户名和密码都是默认的admin

mysql -uadmin -p -h127.0.0.1 -P6032 --prompt='proxysql> '

master节点创建ProxySQL的监控用户monitor和对外访问用户proxysql，并赋予权限：

mysql> create user 'monitor'@'%' identified by '123456';

mysql> grant all on *.* to 'monitor'@'%';

mysql> create user 'proxysql'@'%' identified by '123456';

mysql> grant all on *.* to 'proxysql'@'%';

mysql> flush privileges;

在proxysql节点添加主从服务器列表：

proxysql> select * from mysql_servers;

当前是空值

proxysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(10,'10.10.1.212',3306,1,'master'),(10,'10.10.1.211',3306,1,'slave1'),(10,'10.10.1.213',3306,3,'slave2');

proxysql> load mysql servers to runtime;

proxysql> save mysql servers to disk;

proxysql> select * from mysql_servers;

备注：

（1）表mysql_servers：后端可以连接MySQL主机的列表

（2）所有节点都是ONLINE状态

（3）slave2节点的查询权重调整为3，为了让更多的读请求路由到这台配置更好的主机上

（4）表mysql_servers常用字段说明，参考：

https://blog.51cto.com/qiuyue/2413300

配置和注册监控用户monitor，并验证监控信息：

proxysql> set mysql-monitor_username='monitor';

proxysql> set mysql-monitor_password='123456';

proxysql> load mysql variables to runtime;

proxysql> save mysql variables to disk;

proxysql> select * from global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');

select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;

select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;

配置ProxySQL主从分组信息：

查看表中的字段可使用命令proxysql> show create table mysql_replication_hostgroups\G

proxysql> insert into mysql_replication_hostgroups values (10,20,'proxysql');

proxysql> load mysql servers to runtime;

proxysql> save mysql servers to disk;

proxysql> select * from mysql_replication_hostgroups;

可看到读写组的id

proxysql> select hostgroup_id,hostname,port,status,weight,comment from mysql_servers;

此时hostgroup_id会按照我们设置的显示

此时再select * from mysql_servers;发现hostgroup_id也按照我们设置的改变

备注：ProxySQL会根据MySQL中read_only的取值将主机进行分组，read_only=0的master节点被分配到编号为10的写入组，而read_only=1的两个slave节点则被分配到编号为20的读取组

配置对外访问用户proxysql：

proxysql> insert into mysql_users(username,password,default_hostgroup) values('proxysql','123456',10);

proxysql> load mysql users to runtime;

proxysql> save mysql users to disk;

proxysql> select * from mysql_users\G

区别于之前的mysql-monitor_username的查看方式

我们随便进入主节点或从节点

mysql -uproxysql -p -h10.10.1.214 -P6033 -e 'select @@hostname;'

最后打印的结果都是主节点主机名vl7bjsqltest02，即此时的master


十.配置路由规则

proxysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^select.*for update$',10,1),(1,'^select',20,1);

proxysql> load mysql query rules to runtime;

proxysql> save mysql query rules to disk;

proxysql> select rule_id,active,match_pattern,destination_hostgroup,apply from mysql_query_rules;


十一.读写分离测试

清空stats_mysql_query_digest表：

# mysql -uadmin -p -h127.0.0.1 -P6032 --prompt='proxysql> '

proxysql> select * from stats_mysql_query_digest_reset;

proxysql> select * from stats_mysql_query_digest;

表stats_mysql_query_digest：SQL的执行次数、时间消耗等

# mysql -uproxysql -p -h10.10.1.214 -P6033

mysql> select * from wwj.tb;

mysql> insert into wwj.tb(age) values(0),(100);

mysql> select * from wwj.tb for update;

再来看stats_mysql_query_digest表中记录



十二.查询的负载均衡测试

# for i in {1..12}; do mysql -uproxysql -p123456 -h10.10.1.214 -P6033 -e 'select @@hostname' -s -N; done

选项说明：

（1）-s：以制表符作为分隔符打印结果

（2）-N：结果中不包含列名


上述命令执行结果可得出读请求在两台slave节点间切换，且vl7bjsqltest03和vl7bjsqltest01的出现比例接近1:1不符合配置的1:3的权重

有待后续考量




十三.故障转移测试

单主模型脚本gr_sw_mode_checker.sh，

下载地址https://github.com/ZzzCrazyPig/proxysql_groupreplication_checker

或直接查看

https://github.com/ZzzCrazyPig/proxysql_groupreplication_checker/blob/master/gr_sw_mode_checker.sh

将下载的脚本gr_sw_mode_checker.sh放置至/var/lib/proxysql目录中，并赋予执行权限和修改属主属组：

# chmod +x /var/lib/proxysql/gr_sw_mode_checker.sh

# chown proxysql.proxysql /var/lib/proxysql/gr_sw_mode_checker.sh

下载addition_to_sys.sql，

下载地址https://github.com/lefred/mysql_gr_routing_check/ 打不开的话CSDN下载，或者查看自己简书

用下载的sql文件在master节点创建表sys.gr_member_routing_candidate_status：# mysql -uroot -p < /addition_to_sys.sql

mysql> select * from sys.gr_member_routing_candidate_status;

主节点和从节点显示不同，见截图

主：


从：



配置scheduler：

proxysql> insert into scheduler(active,interval_ms,filename,arg1,arg2,arg3,arg4) values(1,5000,'/var/lib/proxysql/gr_sw_mode_checker.sh',10,20,1,'/var/lib/proxysql/gr_sw_mode_checker.log');

proxysql> load scheduler to runtime;

proxysql> save scheduler to disk;

proxysql> select * from scheduler\G

问题排查可查看日志文件/var/lib/proxysql/gr_sw_mode_checker.log

查看MGR状态信息：

proxysql> select hostgroup_id,hostname,port,status,weight,comment from mysql_servers;

mysql> select * from performance_schema.replication_group_members;

mysql> select if((select @@server_uuid)=(select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member'),1,0) as is_primary_mode,@@server_id;

主从显示信息不同

主：


从：



master节点模拟MySQL服务故障：# systemctl stop mysqld.service

之后10.10.1.211变成主（is_primary_mode 变成 1）

当master节点意外宕机或下线，在满足大多数节点存活的情况下，group内部发起选举，选出下一个可用的读节点，提升其为master节点。master节点选举根据group内剩余存活节点的UUID按字典升序排列，然后选择排在最前的节点作为新的master节点。

is_primary_mode的值为1说明slave1节点已经被选举为新的master节点。

mysql> select * from performance_schema.replication_group_members;

mysql> select if((select @@server_uuid)=(select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member'),1,0) as is_primary_mode,@@server_id;


宕机前后


mysql> select * from sys.gr_member_routing_candidate_status;

创建测试数据：

mysql> insert into wwj.tb(age) values(60),(80);

mysql> select * from wwj.tb;

在proxySQL节点也查找

mysql -uproxysql -p -h10.10.1.214 -P6033

mysql> select * from wwj.tb;

发现一致


十四.恢复

原master节点10.10.1.212恢复MySQL服务：# systemctl start mysqld.service

原master节点加入复制组：mysql> start group_replication;

select * from performance_schema.replication_group_members;

select if((select @@server_uuid)=(select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member'),1,0) as is_primary_mode,@@server_id;

select * from sys.gr_member_routing_candidate_status;

发现事务落后（transactions_behind）已为0

再查看数据

select * from wwj.tb;发现数据已同步

经验证proxy节点上，虽然最后列表中会有4个节点信息，但是依旧能正常写入

我们看一下会不会被负载均衡分掉

for i in {1..12}; do mysql -uproxysql -p123456 -h10.10.1.214 -P6033 -e 'select @@hostname' -s -N; done

发现依旧是分摊到vl7bjsqltest03和vl7bjsqltest01上

尝试重新配置：

proxysql> select hostgroup_id,hostname,port,status,weight,comment from mysql_servers;

proxysql> delete from mysql_servers where hostgroup_id = 20 and hostname = '10.10.1.211';

proxysql> load mysql servers to runtime;

proxysql> save mysql servers to disk;

发现全部分摊到vl7bjsqltest03上

后来重启服务

systemctl restart proxysql

发现vl7bjsqltest03和vl7bjsqltest02大致比为3:1，符合权重


最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 202,607评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,047评论 2赞 379
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 149,496评论 0赞 335
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,405评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,400评论 5赞 364
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,479评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,883评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,535评论 0赞 256
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,743评论 1赞 295
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,544评论 2赞 319
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,612评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,309评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,881评论 3赞 306
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,891评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,136评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,783评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,316评论 2赞 342