MGR组复制技术
MGR组复制简介
MySQL是目前最流行的开源关系型数据库,国内金融行业也开始全面使用,其中MySQL 5.7.17提出的MGR(MySQL Group
Replication)既可以很好的保证数据一致性又可以自动切换,具备故障检测功能、支持多节点写入,MGR是一项被普遍看好的技术。MGR (MySQL Group Replication)是MySQL自带的一个插件,可以灵活部署。MySQL MGR集群是多个MySQL
Server节点共同组成的分布式集群,每个Server都有完整的副本,它是基于ROW格式的二进制日志文件和GTID特性。架构主要是APIs层、组件层、复制协议模块层和GCS
API+Paxos引擎层构成。应用发来的事务从MySQL
Server经过MGR的APIs接口层分发到组件层,组件层去capture事务相关信息,然后经过复制协议层进行事务传输,最后经过GCS
API+Paxos引擎层保证事务在各个节点数据最终一致性。这是事务进入MGR层内部处理过程。
实验:利用三台虚拟机搭建MGR组复制集群
一、环境准备:
1.准备三台机器(redhat linux),然后:
修改主机名(mgr01、mgr02、mgr03)
hostnamectl set-hostname mgr01
修改/etc/hosts(本地解析,三台机器的ip与主机名,每台机器上都要写三个解析)
关闭和禁用防火墙
[root@mrg01 ~]# systemctl stop firewalld
关闭和禁用SELinux
[root@mrg01 ~]#setenforce 0
生成密钥对(每台机器都要做)
[root@mrg01 ~]#ssh-keygen -t rsa
传输密钥对(三台机器均需传给三台机器)
[root@web ~]# scp /root/.ssh/id_rsa.pub root@mgr01:/root/.ssh/authorized_keys
[root@web ~]# scp /root/.ssh/id_rsa.pub root@mgr02:/root/.ssh/authorized_keys
[root@web ~]# scp /root/.ssh/id_rsa.pub root@mgr03:/root/.ssh/authorized_keys
验证免密登陆
2.清除原有的数据库文件
yum remove mysql-server -y
rm -rf /etc/my.cnf.d/
rm -rf /var/lib/mysql/
rm -rf /var/log/mysql/
3.安装数据库:每个机器都做
mount /dev/sr0 /mnt
yum install mysql-server -y
systemctl start mysqld
二、配置第一个服务器(主服务器)
1.编写配置文件:
cd /etc/my.cnf.d/
vim mysql-server.cnf
追加内容:
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1 #server_id确保每个机器不一样
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
plugin_load_add='group_replication.so'
#uuid确保每个机器都一样,可以用uuidgen生成
group_replication_group_name="8e1969ec-3ae3-4bd1-b80f-6de58b837ff5"
group_replication_start_on_boot=off
#当前主机的主机名和复制组端口,建议用主机名
group_replication_local_address= "mgr01:33061" #当前主机名
group_replication_group_seeds= "mgr01:33061,mgr02:33061,mgr03:33061" #三台主机名
group_replication_bootstrap_group=off
配置文件编辑完成
2.启动数据库服务
[root@mgr01 ~]# systemctl start mysqld
[root@mgr01 ~]# mysql -uroot -p
mysql> use mysql;
3.创建复制组的用户
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Test@1234';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
4.复制用户凭据到复制组通道
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Test@1234' FOR CHANNEL 'group_replication_recovery';
5.查看复制组插件是否装载
mysql> SHOW PLUGINS;
如果有以下内容则表示已装载
group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL
6.启动复制组
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='Test@1234';
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
7.查看复制组
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | a49b5c8f-fd44-11eb-a9e2-000c29707010 | mgr01 | 3306 | ONLINE | PRIMARY | 8.0.21 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)
三、配置第二、三台服务器
1.复制第一台服务器的mysql配置
[root@mgr02 ~]# cd /etc/my.cnf.d/
[root@mgr02 my.cnf.d]# rm -rf mysql-server.cnf
[root@mgr02 my.cnf.d]# scp mgr01:/etc/my.cnf.d/mysql-server.cnf
2.编辑配置文件
[root@mgr02 my.cnf.d]vim mysql-server.cnf
#只需要编辑以下两个相关配置:
server_id=2
group_replication_local_address= "mgr02:33061"
3.启动mysql服务器
[root@mgr02 my.cnf.d]# systemctl start mysqld
4.连接服务器
[root@mgr02 my.cnf.d]# mysql -uroot -p
5.切换数据库
mysql> use mysql;
6.创建复制组用户
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'Test@1234';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
7.复制用户凭据到复制组通道
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Test@1234' FOR CHANNEL 'group_replication_recovery';
8.查看复制组插件是否装载
mysql> SHOW PLUGINS;
如果有以下内容则表示已装载
group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL
9.启动复制组
mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='Test@1234';
10.查看复制组
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | a49b5c8f-fd44-11eb-a9e2-000c29707010 | mgr01 | 3306 | ONLINE | PRIMARY | 8.0.21 |
| group_replication_applier | a9eed5dc-fd44-11eb-aec2-000c29de2f00 | mgr02 | 3306 | ONLINE | SECONDARY | 8.0.21 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)
以上是第二台服务器的配置,第三台和第二台服务器的配置是一样的
如果需要停止复制组,则使用:
mysql> stop GROUP_REPLICATION