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的JDBC编程详解

MySQL的JDBC编程详解

《MySQL的JDBC编程详解》：本文主要介绍MySQL的JDBC编程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录前言一、前置知识1. 引入依赖2. 认识 url二、JDBC 操作流程1. JDBC 的写操作2. JDBC 的读操作总结前言本文介绍了mysq
阅读更多...
java.sql.SQLTransientConnectionException连接超时异常原因及解决方案

java.sql.SQLTransientConnectionException连接超时异常原因及解决方案

《java.sql.SQLTransientConnectionException连接超时异常原因及解决方案》：本文主要介绍java.sql.SQLTransientConnectionExcep... 目录一、引言二、异常信息分析三、可能的原因3.1 连接池配置不合理3.2 数据库负载过高3.3 连接泄漏
阅读更多...
Linux下MySQL数据库定时备份脚本与Crontab配置教学

Linux下MySQL数据库定时备份脚本与Crontab配置教学

《Linux下MySQL数据库定时备份脚本与Crontab配置教学》在生产环境中,数据库是核心资产之一,定期备份数据库可以有效防止意外数据丢失,本文将分享一份MySQL定时备份脚本,并讲解如何通过cr... 目录备份脚本详解脚本功能说明授权与可执行权限使用 Crontab 定时执行编辑 Crontab添加定
阅读更多...
MySQL中On duplicate key update的实现示例

MySQL中On duplicate key update的实现示例

《MySQL中Onduplicatekeyupdate的实现示例》ONDUPLICATEKEYUPDATE是一种MySQL的语法,它在插入新数据时,如果遇到唯一键冲突,则会执行更新操作,而不是抛... 目录1/ ON DUPLICATE KEY UPDATE的简介2/ ON DUPLICATE KEY UP
阅读更多...
MySQL分库分表的实践示例

MySQL分库分表的实践示例

《MySQL分库分表的实践示例》MySQL分库分表适用于数据量大或并发压力高的场景,核心技术包括水平/垂直分片和分库,需应对分布式事务、跨库查询等挑战,通过中间件和解决方案实现,最佳实践为合理策略、备... 目录一、分库分表的触发条件1.1 数据量阈值1.2 并发压力二、分库分表的核心技术模块2.1 水平分
阅读更多...
Python与MySQL实现数据库实时同步的详细步骤

Python与MySQL实现数据库实时同步的详细步骤

《Python与MySQL实现数据库实时同步的详细步骤》在日常开发中,数据同步是一项常见的需求,本篇文章将使用Python和MySQL来实现数据库实时同步,我们将围绕数据变更捕获、数据处理和数据写入这... 目录前言摘要概述：数据同步方案1. 基本思路2. mysql Binlog 简介实现步骤与代码示例1
阅读更多...
C#文件复制异常："未能找到文件"的解决方案与预防措施

C#文件复制异常："未能找到文件"的解决方案与预防措施

《C#文件复制异常：未能找到文件的解决方案与预防措施》在C#开发中,文件操作是基础中的基础,但有时最基础的File.Copy()方法也会抛出令人困惑的异常,当targetFilePath设置为D:2... 目录一个看似简单的文件操作问题问题重现与错误分析错误代码示例错误信息根本原因分析全面解决方案1. 确保
阅读更多...
使用shardingsphere实现mysql数据库分片方式

使用shardingsphere实现mysql数据库分片方式

《使用shardingsphere实现mysql数据库分片方式》本文介绍如何使用ShardingSphere-JDBC在SpringBoot中实现MySQL水平分库,涵盖分片策略、路由算法及零侵入配置... 目录一、ShardingSphere 简介1.1 对比1.2 核心概念1.3 Sharding-Sp
阅读更多...
MySQL 表空却 ibd 文件过大的问题及解决方法

MySQL 表空却 ibd 文件过大的问题及解决方法

《MySQL表空却ibd文件过大的问题及解决方法》本文给大家介绍MySQL表空却ibd文件过大的问题及解决方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考... 目录一、问题背景：表空却 “吃满” 磁盘的怪事二、问题复现：一步步编程还原异常场景1. 准备测试源表与数据
阅读更多...
Mac电脑如何通过 IntelliJ IDEA 远程连接 MySQL

Mac电脑如何通过 IntelliJ IDEA 远程连接 MySQL

《Mac电脑如何通过IntelliJIDEA远程连接MySQL》本文详解Mac通过IntelliJIDEA远程连接MySQL的步骤,本文通过图文并茂的形式给大家介绍的非常详细,感兴趣的朋友跟... 目录MAC电脑通过 IntelliJ IDEA 远程连接 mysql 的详细教程一、前缀条件确认二、打开 ID
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2