首页 > 数据库 >MySQL 8.0.27 集群搭建——基于MGR方式的一主两从架构

MySQL 8.0.27 集群搭建——基于MGR方式的一主两从架构

时间：2025-01-11 10:49:39浏览次数：1  
标签：8.0 27 group replication MySQL 827MGR MGR Master mysql

数据库：MySQL 8.0.27
安装包：mysql-8.0.27-1.el7.x86_64.rpm-bundle.tar
系统：rhel 7.3 64位

 

1、准备工作
说明：1.1至1.3均需在三台服务器执行.
1.1、修改hosts文件
--在hosts中设置hostname与IP映射绑定关系.
[root@MySQL-827MGR-Master ~]# cat <<EOF>>/etc/hosts
192.168.133.118 MySQL-827MGR-Master
192.168.133.119 MySQL-827MGR-Slave01
192.168.133.120 MySQL-827MGR-Slave02
EOF

1.2、关闭防火墙
[root@MySQL-827MGR-Master ~]# systemctl stop firewalld
[root@MySQL-827MGR-Master ~]# systemctl disable firewalld
[root@MySQL-827MGR-Master ~]# systemctl status firewalld

1.3、禁用selinux
[root@MySQL-827MGR-Master ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

说明：修改selinux后需重启生效.
2、安装MySQL
说明：2.1至2.3在三台服务器均执行.
2.1、确认MySQL安装情况
--查看并卸载mysql
[root@MySQL-827MGR-Master lib64]# rpm -qa|grep mysql
mysql-community-common-8.0.27-1.el7.x86_64

说明：若有安装旧版MySQL，参考如下卸载.
--卸载mysql.
[root@MySQL-827MGR-Master ~]# rpm -e mysql-community-common-8.0.27-1.el7.x86_64 --nodeps

--查看并卸载mariadb
[root@MySQL-827MGR-Master ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.52-1.el7.x86_64
[root@MySQL-827MGR-Master ~]# rpm -e mariadb-libs-5.5.52-1.el7.x86_64 --nodeps
[root@MySQL-827MGR-Master ~]# rpm -qa|grep mariadb

2.2、安装MySQL
--创建目录、上传并解压安装包
[root@MySQL-827MGR-Master ~]# mkdir -p /usr/local/mysql
sftp> cd /usr/local/mysql
sftp> lcd F:\installmedium\mysql\MySQL-8.0.27
sftp> put mysql-8.0.27-1.el7.x86_64.rpm-bundle.tar
[root@MySQL-827MGR-Master ~]# cd /usr/local/mysql
[root@MySQL-827MGR-Master mysql]# tar -xvf mysql-8.0.27-1.el7.x86_64.rpm-bundle.tar

--安装mysql 8.0.27 common、libs、client、server
[root@MySQL-827MGR-Master mysql]# rpm -ivh mysql-community-common-8.0.27-1.el7.x86_64.rpm --nodeps --force
[root@MySQL-827MGR-Master mysql]# rpm -ivh mysql-community-libs-8.0.27-1.el7.x86_64.rpm --nodeps --force
[root@MySQL-827MGR-Master mysql]# rpm -ivh mysql-community-client-8.0.27-1.el7.x86_64.rpm --nodeps --force
[root@MySQL-827MGR-Master mysql]# rpm -ivh mysql-community-server-8.0.27-1.el7.x86_64.rpm --nodeps --force

--检查安装情况
[root@MySQL-827MGR-Master mysql]# rpm -qa | grep mysql
mysql-community-common-8.0.27-1.el7.x86_64
mysql-community-server-8.0.27-1.el7.x86_64
mysql-community-client-8.0.27-1.el7.x86_64
mysql-community-libs-8.0.27-1.el7.x86_64

2.3、启动MySQL
--初始化和配置MySQL
[root@MySQL-827MGR-Master mysql]# mysqld --initialize
[root@MySQL-827MGR-Master mysql]# chown -R mysql:mysql /var/lib/mysql

--启动MySQL
[root@MySQL-827MGR-Master mysql]# systemctl start mysqld.service
--设置自启动
[root@MySQL-827MGR-Master mysql]# systemctl enable mysqld.service

3、相关配置
说明：该步骤三台服务器均执行.
--修改mysql初始密码
[root@MySQL-827MGR-Master mysql]# cat /var/log/mysqld.log | grep password
2023-08-12T01:46:22.747404Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Fis/C0Rcqw4&

[root@MySQL-827MGR-Master mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'mysql_4U';
Query OK, 0 rows affected (0.01 sec)

--创建远程访问用户
mysql> create user 'root'@'%' identified with mysql_native_password by 'mysql_4U';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

--相关目录
mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

mysql> select @@basedir;
+-----------+
| @@basedir |
+-----------+
| /usr/     |
+-----------+
1 row in set (0.00 sec)

4、MySQL相关命令
启动MySQL
systemctl start mysqld.service
停止MySQL
systemctl stop mysqld.service
重启MySQL
systemctl restart mysqld.service
查看MySQL状态
systemctl status mysqld.service
设置MySQL自启动
systemctl enable mysqld.sercice
查看MySQL是否自启动
systemctl list-unit-files|grep mysqld.service

5、创建MGR集群
说明：以下搭建基于MGR方式的MySQL集群.
5.1、修改配置文件
5.1.1、主节点
[root@MySQL-827MGR-Master ~]# cp /etc/my.cnf /etc/my.cnf.bak`date +%Y%m%d%H%M%S`
[root@MySQL-827MGR-Master ~]# vi /etc/my.cnf
按如下修改：
[mysqld]
basedir=/usr
datadir=/var/lib/mysql
server_id=1    
socket=/var/lib/mysql/mysql.sock
log-error=/var/lib/mysql/mysqld.log
pid-file=/var/lib/mysql/mysqld.pid

binlog_checksum=NONE
gtid_mode=ON
enforce_gtid_consistency=ON
default_authentication_plugin=mysql_native_password
loose-group_replication_recovery_get_public_key=on
loose-group_replication_recovery_use_ssl=on
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="192.168.133.118:33061"
loose-group_replication_group_seeds="192.168.133.118:33061,192.168.133.119:33061,192.168.133.120:33061"
loose-group_replication_bootstrap_group=OFF

[mysql]
prompt= "Master[\\d]> "

--重启主节点
[root@MySQL-827MGR-Master ~]# systemctl restart mysqld.service

5.1.2、从节点1
[root@MySQL-827MGR-Slave01 ~]# cp /etc/my.cnf /etc/my.cnf.bak`date +%Y%m%d%H%M%S`
[root@MySQL-827MGR-Slave01 ~]# vi /etc/my.cnf
按如下修改：
[mysqld]
basedir=/usr
datadir=/var/lib/mysql  
server_id=2    
socket=/var/lib/mysql/mysql.sock
log-error=/var/lib/mysql/mysqld.log
pid-file=/var/lib/mysql/mysqld.pid

binlog_checksum=NONE
gtid_mode=ON
enforce_gtid_consistency=ON
default_authentication_plugin=mysql_native_password
loose-group_replication_recovery_get_public_key=on
loose-group_replication_recovery_use_ssl=on
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="192.168.133.119:33061"
loose-group_replication_group_seeds="192.168.133.118:33061,192.168.133.119:33061,192.168.133.120:33061"
loose-group_replication_bootstrap_group=OFF

[mysql]
prompt="Slave01[\\d]> "

--重启从节点1
[root@MySQL-827MGR-Slave01 ~]# systemctl restart mysqld.service

5.1.3、从节点2
[root@MySQL-827MGR-Slave02 ~]# cp /etc/my.cnf /etc/my.cnf.bak`date +%Y%m%d%H%M%S`
[root@MySQL-827MGR-Slave02 ~]# vi /etc/my.cnf
按如下修改：
[mysqld]
basedir=/usr
datadir=/var/lib/mysql 
server_id=3 
socket=/var/lib/mysql/mysql.sock
log-error=/var/lib/mysql/mysqld.log
pid-file=/var/lib/mysql/mysqld.pid

binlog_checksum=NONE
gtid_mode=ON
enforce_gtid_consistency=ON
default_authentication_plugin=mysql_native_password
#loose-group_replication_recovery_get_public_key=on
loose-group_replication_recovery_use_ssl=on
loose-group_replication_group_name="bbbbbbbb-bbbb-cccc-dddd-eeeeeeeeeeee"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="192.168.133.120:33061"
loose-group_replication_group_seeds="192.168.133.118:33061,192.168.133.119:33061,192.168.133.120:33061"
loose-group_replication_bootstrap_group=OFF
transaction_write_set_extraction=XXHASH64

[mysql]
prompt="Slave02[\\d]> "

--重启从节点2
[root@MySQL-827MGR-Slave02 ~]# systemctl restart mysqld.service

5.1.4、参数说明
#定义用户事务期间哈希写入提取的算法，组复制模式下必须设置为XXHASH64
transaction_write_set_extraction=XXHASH64
#确定组复制恢复时是否应该应用SSL，通常设置为"开"，但默认设置为"关"
loose-group_replication_recovery_use_ssl=ON
#该服务器的实例所在复制组的名称，必须是有效UUID，所有节点必须相同
loose-group_replication_group_name="28a779f2-a8f0-4621-8d7b-f8b54b03fc5d"
#确定服务器是否应该在服务器启动期间启动组复制
loose-group_replication_start_on_boot=OFF
#为复制组中其它成员提供网络地址，此处端口号避免使用3306，否则会冲突
loose-group_replication_local_address= "192.168.133.118:33061"
#用于建立新成员到组的连接组成员列表，该列表指定为由分隔号间隔的组成员网络地址列表
loose-group_replication_group_seeds= "192.168.133.118:33061,192.168.133.119:33061,192.168.133.120:33061"
#配置此服务器为引导组，该选项必须仅在一台服务器上设置，且仅当第一次启动组或重新启动整个组时，成功引导组启动后，将此选项设置为关闭
loose-group_replication_bootstrap_group=OFF
#使用mysql_native_password密码策略，防止navicat连不上mysql8
default_authentication_plugin=mysql_native_password
#设置mysql插件所在目录，因为MGR基于插件，所以必须设置插件路径
plugin_dir=/usr/lib64/mysql/plugin
#此参数决定primary节点到secondary节点的请求是否基于RSA密匙对的密码交换所需的公匙
loose-group_replication_recovery_get_public_key=on

5.2、创建复制账号
说明：三个节点均执行.
[root@MySQL-827MGR-Master ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Master[(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

Master[(none)]> create user 'repl'@'%' identified by 'repl@12345';
Query OK, 0 rows affected (0.01 sec)

Master[(none)]> grant replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.01 sec)

Master[(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Master[(none)]> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

说明：复制账号repl可自定义，需保证各节点复制账号一致.

5.3、安装mgr插件
说明：5.3.1至5.3.2三个节点均执行.
5.3.1、升级openssl
--将openssl-1.0.1升级到openssl-1.0.2
sftp> lcd F:\package
sftp> cd /root
sftp> put openssl-libs-1.0.2k-19.el7.x86_64.rpm
[root@MySQL-827MGR-Master ~]# rpm -ivh openssl-libs-1.0.2k-19.el7.x86_64.rpm --force
5.3.2、安装插件
[root@MySQL-827MGR-Master ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Master[(none)]> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (12.84 sec)

说明：此处第一次install插件会报如下异常.exit后重新登录数据库install会成功.

ERROR 1126 (HY000): Can't open shared library '/usr/lib64/mysql/plugin/group_replication.so' (errno: 2 /usr/lib64/mysql/plugin/group_replication.so: symbol X509_check_ip_asc, version libcrypto.so.10 not defined in file libcrypto.so)

5.4、主节点开启组复制
Master[(none)]> set global group_replication_bootstrap_group=on;
Query OK, 0 rows affected (0.00 sec)

Master[(none)]> start group_replication;
Query OK, 0 rows affected (1.15 sec)

Master[(none)]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST         | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 9259a41b-3985-11ee-a5e4-000c299ea627 | MySQL-827MGR-Master |        3306 | ONLINE       | PRIMARY     | 8.0.27         | XCom                       |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)

Master[(none)]> set global group_replication_bootstrap_group=off;
Query OK, 0 rows affected (0.00 sec)

5.5、从节点开启组复制
说明：以下在两个从节点执行.
Slave01[(none)]> reset master;
Query OK, 0 rows affected (0.02 sec)

Slave01[(none)]> change master to master_user="repl",master_password="repl@12345" for channel 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.00 sec)

Slave01[(none)]> start group_replication;
Query OK, 0 rows affected (4.78 sec)

6、集群状态确认
Master[(none)]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST          | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 5d4170a4-3987-11ee-af25-0050563ccee2 | MySQL-827MGR-Slave02 |        3306 | ONLINE       | SECONDARY   | 8.0.27         | XCom                       |
| group_replication_applier | 9259a41b-3985-11ee-a5e4-000c299ea627 | MySQL-827MGR-Master  |        3306 | ONLINE       | PRIMARY     | 8.0.27         | XCom                       |
| group_replication_applier | 9e1ab21c-3986-11ee-ad9a-0050562d243d | MySQL-827MGR-Slave01 |        3306 | ONLINE       | SECONDARY   | 8.0.27         | XCom                       |
+---------------------------+--------------------------------------+----------------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)

说明：如上所示，MGR集群搭建成功.

参考文档：https://www.cnblogs.com/nothingonyou/p/12145348.html

 

标签：8.0,27,group,replication,MySQL,827MGR,MGR,Master,mysql
From： https://www.cnblogs.com/shujuyr/p/18566877

相关文章

  • VMware ESXi 8.0U3c macOS Unlocker & OEM BIOS Huawei (华为) 定制版
    VMwareESXi8.0U3cmacOSUnlocker&OEMBIOSHuawei(华为)定制版ESXi8.0U3c标准版，Dell(戴尔)、HPE(慧与)、Lenovo(联想)、Inspur(浪潮)、Cisco(思科)、Hitachi(日立)、Fujitsu(富士通)、NEC(日电)、Huawei(华为)、xFusion(超聚变)OEM定制版请访问原文链接：htt......
  • 2024.12.27（MyBatis知识点）
    编写实体类编写对象配置文件xxxMapper.xml1234567编写SqlMapConfig.xml核心配置文件<!--加载properties文件--><propertiesresource="jdbc.properties"></properties><settings><settingname="lazyLoadTriggerMethods"value......
  • 2024.11.27（总结专家的经验）
    第一步，成为互联网时代合格的测试工程师。如果你是入行不满3年的测试工程师，一定对此有迫切需求。此时，你必须具有快速学习的能力，能迅速掌握被测软件的业务功能与内部架构，并在此基础上运用各种测试方法，尽可能多地发现潜在缺陷，并能够在已知缺陷的基础上进一步发现相关的连带缺陷。从......
  • JS-27 字符串方法_split()
    split方法按照给定规则分割字符串，返回一个由分割出来的子字符串组成的数组'it|sxt|zifuchuan'.split('|')//["it","sxt","zifuchuan"]如果分割规则为空字符串，则返回数组的成员是原字符串的每一个字符。'a|b|c'.split('')//["a","|","b&q......
  • C#实战|人员管理系统[27]:双击列表显示详细人员信息
    哈喽，你好啊，我是雷工！前面实现了通过编号查询详细的人员信息，但使用中可能不知道人员编号，需要先通过所属组织查询人员列表，根据列表中的编号查询详细的人员信息；这时使用起来不太友好，输入编号比较麻烦，是否可以优化一下，在查询列表中双击某行，直接弹出显示该行对应人员的详细信息界面......
  • ubuntu 18.04下neovim手动添加treesitter支持(c语言为例)
    环境准备rustcurl--proto'=https'--tlsv1.2-sSfhttps://sh.rustup.rs|shnode.jshttps://nodejs.org/dist/v16.20.2/node-v16.20.2-linux-x64.tar.xzneovimhttps://github.com/neovim/neovim-releases/releases/download/v0.10.3/nvim-linux64.tar.g......
  • Rocky Linux 9.5 安装 MySQL 8.0
    RockyLinux9.5安装MySQL8.0RockyLinux9.5 [root@netkiller~]#dnfinstall-ymysql-server[root@netkiller~]#systemctlenablemysqldCreatedsymlink/etc/systemd/system/multi-user.target.wants/mysqld.service→/usr/lib/systemd/system/mysqld.ser......
  • 基于.NET8.0实现RabbbitMQ的Publish/Subscribe发布订阅以及死信队列
    本文github源码附上：https://github.com/yangshuqi1201/RabbitMQ.Core【前言】RabbitMQ提供了五种消息模型，分别是简单模型、工作队列模型、发布/订阅模型、路由模型和主题模型。‌‌简单模型（Simple）‌：在这种模式下，一个生产者将消息发送到一个队列，只有一个消费者监听并处理该队......
  • VMware ESXi 8.0U3c macOS Unlocker & OEM BIOS 标准版和厂商定制版，已适配主流品牌服
    VMwareESXi8.0U3cmacOSUnlocker&OEMBIOS标准版和厂商定制版ESXi8.0U3c标准版，Dell(戴尔)、HPE(慧与)、Lenovo(联想)、Inspur(浪潮)、Cisco(思科)、Hitachi(日立)、Fujitsu(富士通)、NEC(日电)定制版、Huawei(华为)OEM定制版请访问原文链接：https://sy......
  • 第一天 / 704. 二分查找 / 27. 移除元素 / 977. 有序数组的平方
    704.二分查找左闭右闭classSolution{public:intsearch(vector<int>&nums,inttarget){intleft=0;intright=nums.size()-1;//定义target在左闭右闭的区间里，[left,right]while(left<=right){......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99