mysql实现读写分离（proxy）与高可用（MGR）

---恢复内容开始---

实验目的是MySQL高可用且根据规则实现读写分离

首先准备三台MySQL服务器，版本是二进制的mysql-5.7.27

第一台：192.168.110.113

第二台：192.168.110.112

第三台：192.168.110.110

三台机器配置/etc/hosts，三台机器的目录都是/data，第一，二台机器设置为种子节点

如果三台机器是新装的数据库，不用先进行mysqldump更新至一致，如果有数据且不小的话建议先mysqldump更新至一致

在第一台上面，设置/etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
 basedir = /usr/local/mysql
 datadir = /data
# port = .....
# server_id = .....
# socket = .....
 pid-file = /data/rabbitmq4.pid
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#datadir=/data
socket=/tmp/mysql.sock

server-id=100                      # 必须
gtid_mode=on                       # 必须
enforce_gtid_consistency=on        # 必须
log-bin=/data/master-bin           # 必须
binlog_format=row                  # 必须
binlog_checksum=none               # 必须
master_info_repository=TABLE       # 必须
relay_log_info_repository=TABLE    # 必须
relay_log=/data/relay-log          # 必须，如果不给，将采用默认值
log_slave_updates=ON               # 必须
sync-binlog=1                      # 建议
log-error=/data/error.log
#pid-file=/data/mysqld.pid

transaction_write_set_extraction=XXHASH64         # 必须
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"  # 必须
loose-group_replication_start_on_boot=off        # 建议设置为OFF
loose-group_replication_member_weigth = 40   # 非必需，mysql 5.7.20才开始支持该选项
loose-group_replication_local_address="192.168.110.113:20001"   # 必须，下一行也必须
loose-group_replication_group_seeds="192.168.110.113:20001,192.168.110.112:20002"

 

启动数据库

配置远程复制用户密码

create user repl@'192.168.110.%' identified by 'P@ssword1!';

grant replication slave on *.* to repl@'192.168.110.%';

加载组复制插件

install plugin group_replication soname 'group_replication.so';

查看插件情况

show plugins;

配置族复制通道

change master to
            master_user='repl',
            master_password='P@ssword1!'
            for channel 'group_replication_recovery';

查看relay log的元数据

select * from mysql.slave_relay_log_infoG

第一台配置组复制引导组

mysql> set @@global.group_replication_bootstrap_group=on;
mysql> start group_replication;
mysql> set @@global.group_replication_bootstrap_group=off;

查看组复制成员状态

select * from performance_schema.replication_group_members;

 

第二台数据库

配置/etc/my.cnf

[mysqld]
datadir=/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock

server-id=110                      # 必须，每个节点都不能相同
gtid_mode=on                       # 必须
enforce_gtid_consistency=on        # 必须
log-bin=/data/master-bin           # 必须
binlog_format=row                  # 必须
binlog_checksum=none               # 必须
master_info_repository=TABLE       # 必须
relay_log_info_repository=TABLE    # 必须
relay_log=/data/relay-log          # 必须，如果不给，将采用默认值
log_slave_updates=ON               # 必须
sync-binlog=1                      # 建议
log-error=/data/error.log
pid-file=/data/mysqld.pid

transaction_write_set_extraction=XXHASH64         # 必须
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"  # 必须
loose-group_replication_start_on_boot=off        # 建议设置为OFF
loose-group_replication_member_weigth = 20   # 非必需，mysql 5.7.20才开始支持该选项
loose-group_replication_local_address="192.168.110.112:20002"   # 必须，下一行也必须
loose-group_replication_group_seeds="192.168.110.113:20001,192.168.110.112:20002"

加载组复制插件

install plugin group_replication soname 'group_replication.so';

配置组复制通道

change master to
            master_user='repl',
            master_password='P@ssword1!'
            for channel 'group_replication_recovery';

开启组复制功能，由于是第二台，所以只需要开启组复制功能即可

start group_replication;

查看组复制成员状态

select * from performance_schema.replication_group_members

 

 第三台数据库

配置/etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
 #log_bin= mysql-bin
 #server_id= 1
# These are commonly set, remove the # and set as required.
 basedir = /usr/local/mysql
 datadir = /data
 #character_set_database = utf8
 #character_set_server = utf8
# port = .....
# server_id = .....
 socket = /tmp/mysql.sock
 #pid = /data-mysql/docker.pid
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
server-id=120                      # 必须
gtid_mode=on                      
enforce_gtid_consistency=on       
log-bin=/data/master-bin          
binlog_format=row                 
binlog_checksum=none              
master_info_repository=TABLE      
relay_log_info_repository=TABLE   
relay_log=/data/relay-log         
log_slave_updates=ON              
sync-binlog=1     

transaction_write_set_extraction=XXHASH64        
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" 
loose-group_replication_start_on_boot=off
loose-group_replication_member_weigth = 30
loose-group_replication_local_address="192.168.110.110:20003"
loose-group_replication_group_seeds="192.168.110.113:20001,192.168.110.112:20002"
log-error=/data/error.log
pid-file=/data/docker.pid

启动数据库

加载插件

install plugin group_replication soname 'group_replication.so';

配置组复制

change master to
            master_user='repl',
            master_password='P@ssword1!'
            for channel 'group_replication_recovery';

开启组复制功能

start group_replication;

查看节点状态

select * from performance_schema.replication_group_members

 

实验过程：提示无法更新mysqld.pid文件，一般是配置文件错误，数据目录没指定，数据目录权限不对，还有就是数据库初始化不正确，从这几个方面入手

当开启组复制后，成员状态一直在recovering，可能是配置组复制通道的用户名密码设置有误，如repl@'网段'设置成不与当前内网网段一致

也有可能是gtid事务，如确定以往事务可以跳过则执行 reset master，再加组

 

配置好组复制后，若关闭单台服务器，那么只需关闭组复制功能，即stop group_replication，恢复重新加入组即可，start group_replication

若关闭整个组复制，最好的做法是先关闭从节点（不止组复制功能，整个实例都要关闭），最后在关闭主节点，完后要开启组复制，先在主节点开启组复制引导组，即

mysql> set @@global.group_replication_bootstrap_group=on;
mysql> start group_replication;
mysql> set @@global.group_replication_bootstrap_group=off;

从节点在开启组复制功能

start group_replication

 

 

使用proxysql组件进行读写分离

proxysql：192.168.110.114

配置yum源

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

yum安装

yum -y install proxysql

开启proxysql

service start proxysql

使用mysql本地连接，若没有mysql客户端工具，可从上面机器/usr/local/mysql/bin下拷贝mysql过来

mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin> '

runtime_开头的是运行时的配置，这些是不能修改的。要修改ProxySQL的配置，需要修改了非runtime_表，修改后必须执行LOAD ... TO RUNTIME才能加载到RUNTIME生效，执行save ... to disk才能将配置持久化保存到磁盘

把上面三个节点加入proxyql中

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.110.113',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.110.112',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.110.110',3306);

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

在主库上新建用户来让proxysql监控mysql

 在master上执行：
mysql> create user monitor@'192.168.110.%' identified by 'P@ssword1!';
mysql> grant replication client on *.* to monitor@'192.168.110.%';

回到proxysql配置

set mysql-monitor_username='monitor';
set mysql-monitor_password='P@ssword1!';

修改后，加载到RUNTIME，并保存到disk。

load mysql variables to runtime;
save mysql variables to disk;

指定写组的id为10，读组的id为20。

insert into mysql_replication_hostgroups values(10,20,30);    ---其中30是注释，随便写

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

这样proxysql会根据后端数据库的read-only属性把机器加入到对应的组内，因为上面MGR配置单主模式，所以会自动设置read-only属性

接着，设置客户端通过proxysql访问后端数据库的用户密码

在master节点上执行：(只需master执行即可，会复制给两个slave) 

grant all on *.* to root@'192.168.110.%' identified by 'P@ssword1!';
grant all on *.* to sqlsender@'192.168.110.%' identified by 'P@ssword1!';

回到proxysql上

insert into mysql_users(username,password,default_hostgroup) values('root','P@ssword1!',10);
insert into mysql_users(username,password,default_hostgroup) values('sqlsender','P@ssword1!',10);
load mysql users to runtime;
save mysql users to disk;

插入两个规则，目的是将select语句分离到hostgroup_id=20的读组，但由于select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，所以应该路由到hostgroup_id=10的写组。

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;

至此读写分离规则生效

以上，组复制是2016年官方推出的高可用功能，并且5.7.20后才支持这个功能，所以旧版本的MySQL不能用，旧版本的要用这个功能只能更新到新版，但是要结合应用程序代码是否能被新版本的数据库兼容