MySQL基于GTID的组复制（MGR）

2024-05-13 04:08
文章标签 mysql 复制 database gtid mgr

本文主要是介绍MySQL基于GTID的组复制（MGR），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

环境准备

IP主机名操作系统
192.168.131.129mgr-node1CentOS7.6
192.168.131.130mgr-node2CentOS7.6
192.168.131.131mgr-node3CentOS7.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）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/984643。

相关文章

MySQL数据库双机热备的配置方法详解

MySQL数据库双机热备的配置方法详解

《MySQL数据库双机热备的配置方法详解》在企业级应用中,数据库的高可用性和数据的安全性是至关重要的,MySQL作为最流行的开源关系型数据库管理系统之一,提供了多种方式来实现高可用性,其中双机热备（M... 目录1. 环境准备1.1 安装mysql1.2 配置MySQL1.2.1 主服务器配置1.2.2 从
阅读更多...
深入理解Mysql OnlineDDL的算法

深入理解Mysql OnlineDDL的算法

《深入理解MysqlOnlineDDL的算法》本文主要介绍了讲解MysqlOnlineDDL的算法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小... 目录一、Online DDL 是什么？二、Online DDL 的三种主要算法2.1COPY（复制法）
阅读更多...
mysql8.0.43使用InnoDB Cluster配置主从复制

mysql8.0.43使用InnoDB Cluster配置主从复制

《mysql8.0.43使用InnoDBCluster配置主从复制》本文主要介绍了mysql8.0.43使用InnoDBCluster配置主从复制,文中通过示例代码介绍的非常详细,对大家的学习或者... 目录1、配置Hosts解析(所有服务器都要执行)2、安装mysql shell(所有服务器都要执行)3、
阅读更多...
k8s中实现mysql主备过程详解

k8s中实现mysql主备过程详解

《k8s中实现mysql主备过程详解》文章讲解了在K8s中使用StatefulSet部署MySQL主备架构,包含NFS安装、storageClass配置、MySQL部署及同步检查步骤,确保主备数据一致... 目录一、k8s中实现mysql主备1.1 环境信息1.2 部署nfs-provisioner1.2.
阅读更多...
MySQL中VARCHAR和TEXT的区别小结

MySQL中VARCHAR和TEXT的区别小结

《MySQL中VARCHAR和TEXT的区别小结》MySQL中VARCHAR和TEXT用于存储字符串,VARCHAR可变长度存储在行内,适合短文本；TEXT存储在溢出页,适合大文本,下面就来具体的了解... 目录一、VARCHAR 和 TEXT 基本介绍1. VARCHAR2. TEXT二、VARCHAR
阅读更多...
MySQL中C接口的实现

MySQL中C接口的实现

《MySQL中C接口的实现》本节内容介绍使用C/C++访问数据库,包括对数据库的增删查改操作,主要是学习一些接口的调用,具有一定的参考价值,感兴趣的可以了解一下... 目录准备mysql库使用mysql库编译文件官方API文档对象的创建和关闭链接数据库下达sql指令select语句前言：本节内容介绍使用C/
阅读更多...
mybatis直接执行完整sql及踩坑解决

mybatis直接执行完整sql及踩坑解决

《mybatis直接执行完整sql及踩坑解决》MyBatis可通过select标签执行动态SQL,DQL用ListLinkedHashMap接收结果,DML用int处理,注意防御SQL注入,优先使用#... 目录myBATiFBNZQs直接执行完整sql及踩坑select语句采用count、insert、u
阅读更多...
MySQL之搜索引擎使用解读

MySQL之搜索引擎使用解读

《MySQL之搜索引擎使用解读》MySQL存储引擎是数据存储和管理的核心组件,不同引擎（如InnoDB、MyISAM）采用不同机制,InnoDB支持事务与行锁,适合高并发场景；MyISAM不支持事务,... 目录mysql的存储引擎是什么MySQL存储引擎的功能MySQL的存储引擎的分类查看存储引擎1.命令
阅读更多...
C#利用Free Spire.XLS for .NET复制Excel工作表

C#利用Free Spire.XLS for .NET复制Excel工作表

《C#利用FreeSpire.XLSfor.NET复制Excel工作表》在日常的.NET开发中,我们经常需要操作Excel文件,本文将详细介绍C#如何使用FreeSpire.XLSfor.NET... 目录1. 环境准备2. 核心功能3. android示例代码3.1 在同一工作簿内复制工作表3.2 在不同
阅读更多...
一文详解MySQL索引(六张图彻底搞懂)

一文详解MySQL索引(六张图彻底搞懂)

《一文详解MySQL索引(六张图彻底搞懂)》MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度,：本文主要介绍MySQL索引的相关资料,文中通过代码介绍的... 目录一、什么是索引？为什么需要索引？二、索引该用哪种数据结构？1. 哈希表2. 跳表3. 二叉排序树4.
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2