本文主要是介绍MySQL基于GTID的组复制（MGR），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
环境准备
| IP | 主机名 | 操作系统 |
|---|---|---|
| 192.168.131.129 | mgr-node1 | CentOS7.6 |
| 192.168.131.130 | mgr-node2 | CentOS7.6 |
| 192.168.131.131 | mgr-node3 | CentOS7.6 |
[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
[root@mgr-node1 ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemonLoaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)Active: inactive (dead)Docs: man:firewalld(1)
[root@mgr-node1 ~]# sestatus
SELinux status: disabled
[root@localhost ~]# hostnamectl --static set-hostname mgr-node1
[root@localhost ~]# hostnamectl --static set-hostname mgr-node2
[root@localhost ~]# hostnamectl --static set-hostname mgr-node3
[root@localhost ~]# hostnamectl --static set-hostname proxysql
[root@mgr-node1 ~]# vim /etc/hosts
192.168.131.129 mgr-node1
192.168.131.130 mgr-node2
192.168.131.131 mgr-node3
192.168.131.132 proxysql
安装mysql
# 三台都需要安装
[root@mgr-node1 ~]# wget https://repo.mysql.com/mysql57-community-release-el7-11.noarch.rpm
[root@mgr-node1 ~]# yum -y install mysql57-community-release-el7-11.noarch.rpm
[root@mgr-node1 ~]# yum -y install yum-utils # 安装yum管理工具
[root@mgr-node1 ~]# yum-config-manager --disable mysql80-community # 禁用8.0版本
[root@mgr-node1 ~]# yum-config-manager --enable mysql57-community # 启用5.7版本
[root@mgr-node1 ~]# yum repolist enabled | grep mysql # 检查一下，确保只有一个版本
mysql-connectors-community/x86_64 MySQL Connectors Community 165
mysql-tools-community/x86_64 MySQL Tools Community 115
mysql57-community/x86_64 MySQL 5.7 Community Server 444
[root@mgr-node1 ~]# yum -y install mysql-community-server mysql
[root@mgr-node1 ~]# systemctl enable mysqld --now # 设为开机自启，并立即启动
[root@mgr-node1 ~]# systemctl status mysqld
● mysqld.service - MySQL ServerLoaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)Active: active (running) since Sun 2020-07-19 06:00:41 CST; 40s agoDocs: man:mysqld(8)http://dev.mysql.com/doc/refman/en/using-systemd.htmlProcess: 21909 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)Process: 21860 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)Main PID: 21912 (mysqld)CGroup: /system.slice/mysqld.service└─21912 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pidJul 19 06:00:36 proxysql systemd[1]: Starting MySQL Server...
Jul 19 06:00:41 proxysql systemd[1]: Started MySQL Server.
配置mysql
# 三台机器都需要修改默认密码
[root@mgr-node1 ~]# grep "temporary password" /var/log/mysqld.log
2020-07-18T22:00:38.730773Z 1 [Note] A temporary password is generated for root@localhost: H/bkI+e%2mr=
[root@mgr-node1 ~]# mysql -uroot -p'H/bkI+e%2mr='
mysql> alter user 'root'@'localhost' identified by 'Test123.com';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)[root@mgr-node1 ~]# mysql -uroot -p # 改完密码测试一下
Enter password:
mysql> show databases; # 看一下库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)mysql> select version(); # 查看一下版本
+-----------+
| version() |
+-----------+
| 5.7.31 |
+-----------+
1 row in set (0.00 sec)
MGR组复制部署
mgr-node1配置
[root@mgr-node1 ~]# uuidgen # 创一个uuid给MGR当组名使用
03d87c93-9d96-43ad-bcec-592e07beff3f
[root@mgr-node1 ~]# cp /etc/my.cnf{,.bak} # 国际管理，备份配置文件，给自己留一条退路
[root@mgr-node1 ~]# egrep -v "^$|#" /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
master_info_repository=TABLE
relay_log_info_repository=TABLE # relay.info记录在table中
binlog_checksum=NONE # 不生成checksum， 这样就可以兼容旧版本的mysql，默认NONElog_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1skip_slave_start = 1transaction_write_set_extraction=XXHASH64 # 以便在server收集写集合的同时将其记录到二进制日志。写集合基于每行的主键，并且是行更改后的唯一标识此标识将用于检测冲突。
loose-group_replication_group_name="1f6a5a0c-162e-4c68-9520-fb5a70c090f0" # 这里必须使用UUID的格式
loose-group_replication_start_on_boot=off # #为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF
loose-group_replication_local_address= "192.168.131.129:33066" # #用于组间通信的地址
loose-group_replication_group_seeds= "192.168.131.129:33066,192.168.131.130:33066,192.168.131.131:33066"
loose-group_replication_bootstrap_group=off # 配置是否自动引导组
loose-group_replication_single_primary_mode=off # 关闭单主模式
loose-group_replication_enforce_update_everywhere_checks=on # #开启多主模式
loose-group_replication_ip_whitelist="192.168.131.0/24,127.0.0.1/8" # 允许加入组复制的客户机来源的ip白名单
[root@mgr-node1 ~]# systemctl restart mysqld # 重启mysql
[root@mgr-node1 ~]# mysql -uroot -p
Enter password:
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)mysql> grant replication slave on *.* to mgr_slave@'192.168.131.%' identified by 'Test123@com';
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)mysql> reset master;
Query OK, 0 rows affected (0.00 sec)mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)mysql> change master to master_user='mgr_slave',master_password='Test123@com' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.02 sec)
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
......
......
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
mysql> set global group_replication_bootstrap_group=on;
Query OK, 0 rows affected (0.01 sec)mysql> start group_replication;
Query OK, 0 rows affected (2.34 sec)mysql> set global group_replication_bootstrap_group=off;
Query OK, 0 rows affected (0.00 sec)mysql> select * from performance_schema.replication_group_members; # 要保证group_replication_applier的状态为"ONLINE"
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1ca31701-c942-11ea-8881-000c29e23bd4 | proxysql | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
# 创建一个库，写一点内容，提供给后面测试使用
mysql> create database test character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)mysql> use test;
Database changed
mysql> create table if not exists hello_world (id int(10) primary key auto_increment,name varchar(50) not null);
Query OK, 0 rows affected (0.05 sec)mysql> insert into test.hello_world values(1,"python"),(2,"shell"),(3,"yaml"),(4,"go");
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> select * from test.hello_world;
+----+--------+
| id | name |
+----+--------+
| 1 | python |
| 2 | shell |
| 3 | yaml |
| 4 | go |
+----+--------+
4 rows in set (0.00 sec)
mgr-node2和mgr-node3配置
[root@mgr-node2 ~]# egrep -v "^$|#" /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server_id = 2 # 其他配置不变，需要修改server_id，不能一样
gtid_mode = on
enforce_gtid_consistency = on
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONElog_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1skip_slave_start = 1transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="1f6a5a0c-162e-4c68-9520-fb5a70c090f0"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.131.130:33066" # 本机ip，需要修改
loose-group_replication_group_seeds= "192.168.131.129:33066,192.168.131.130:33066,192.168.131.131:33066"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
loose-group_replication_ip_whitelist="192.168.131.0/24,127.0.0.1/8"
[root@mgr-node3 ~]# systemctl restart mysqld
--------------------------------------------------------------------------------------
[root@mgr-node3 ~]# egrep -v "^$|#" /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server_id = 3
gtid_mode = on
enforce_gtid_consistency = on
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONElog_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1skip_slave_start = 1transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="1f6a5a0c-162e-4c68-9520-fb5a70c090f0"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.131.131:33066"
loose-group_replication_group_seeds= "192.168.131.129:33066,192.168.131.130:33066,192.168.131.131:33066"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
loose-group_replication_ip_whitelist="192.168.131.0/24,127.0.0.1/8"
[root@mgr-node3 ~]# systemctl restart mysqld
[root@mgr-node2 ~]# mysql -p
Enter password:
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)mysql> grant replication slave on *.* to mgr_slave@'192.168.131.%' identified by 'Test123@com';
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)mysql> reset master;
Query OK, 0 rows affected (0.01 sec)mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)mysql> change master to master_user='mgr_slave',master_password='Test123@com' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.03 sec)mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
......
......
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
mysql> start group_replication;
Query OK, 0 rows affected (4.10 sec)mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 0eb6361b-c8ff-11ea-bdb8-000c29afb37d | mgr-node3 | 3306 | ONLINE |
| group_replication_applier | 1ca31701-c942-11ea-8881-000c29e23bd4 | proxysql | 3306 | ONLINE |
| group_replication_applier | d2710ec5-c900-11ea-98d1-000c29d7f446 | mgr-node2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)mysql> select * from test.hello_world;
+----+--------+
| id | name |
+----+--------+
| 1 | python |
| 2 | shell |
| 3 | yaml |
| 4 | go |
+----+--------+
4 rows in set (0.00 sec)
测试
# mgr-node1上更新数据
mysql> select * from test.hello_world;
+----+--------+
| id | name |
+----+--------+
| 1 | python |
| 2 | shell |
| 3 | yaml |
| 4 | go |
+----+--------+
4 rows in set (0.00 sec)mysql> update test.hello_world set id=7 where name="go";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# mgr-node2和mgr-node3上查看
mysql> select * from test.hello_world;
+----+--------+
| id | name |
+----+--------+
| 1 | python |
| 2 | shell |
| 3 | yaml |
| 7 | go |
+----+--------+
4 rows in set (0.00 sec)# 数据更新成功
这篇关于MySQL基于GTID的组复制（MGR）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
