数据库4-MGR

最新推荐文章于 2025-11-10 12:46:51 发布

原创最新推荐文章于 2025-11-10 12:46:51 发布 · 541 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #服务器

本文档详细介绍了如何在三台服务器上搭建MySQL 8.0的多主复制集群，包括环境清理、主机名和主机间免密配置、数据库安装、配置文件编辑、复制组用户创建及集群启动等步骤。通过此过程，可以实现MySQL数据库的高可用性和数据同步。

版本

[root@server ~]# mysql --version

mysql Ver 8.0.26 for Linux on x86_64 (Source distribution)

环境清理

yum remove mysql-server -y

rm -rf /etc/my.cnf.d/

rm -rf /var/lib/mysql/

rm -rf /var/log/mysql/

========================================================

准备：多台机器（三台为例）

修改主机名

修改/etc/hosts

关闭和禁用防火墙

关闭和禁用SELinux

生成密钥对

传输密钥对

验证免密登陆

修改主机名

        [root@server ~]# hostnamectl set-hostname 名字

修改/etc/hosts

        [root@server ~]# vim /etc/hosts

        127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

        ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

        192.168.110.130 server.example.com server

        192.168.110.131 node1.example.com node1

        192.168.110.132 node2.example.com node2

关闭和禁用防火墙

        [root@server ~]# systemctl stop firewalld.service

关闭和禁用SELinux

        [root@server ~]# setenforce 0

生成密钥对

位置： ~/.ssh

传输密钥对

验证免密登陆

=======================================================

安装数据库：每个机器都做

mount /dev/sr0 /mnt

yum install mysql-server -y

systemctl start mysqld

systemctl stop mysqld（必须关闭mysql，否则后期报错）

==========================================================

编写配置文件：

vim /etc/my.cnf.d/mysql-server.cnf

追加内容：（更改三处）

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

#server_id确保每个机器不一样

server_id=1

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= "server:33061"

group_replication_group_seeds= "server:33061,node1:33061,node2:33061"

group_replication_bootstrap_group=off

启动数据库服务

[root@mgr01 ~]# systemctl start mysqld

[root@mgr01 ~]# mysql -uroot -p

mysql> use mysql;

#创建复制组的用户

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;

#复制用户凭据到复制组通道

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Test@1234' FOR CHANNEL 'group_replication_recovery';

#查看复制组插件是否装载

mysql> SHOW PLUGINS;

如果有以下内容则表示已装载

group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL

#启动复制组

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;

#查看复制组

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)

==================================================================

第二台的相关配置

vim /etc/my.cnf.d/mysql-server.cnf

追加内容：（更改两处）

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

#server_id确保每个机器不一样

server_id=2

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= "node1:33061"

group_replication_group_seeds= "server:33061,node1:33061,node2:33061"

group_replication_bootstrap_group=off

启动连接mysql服务器

[root@mgr02 my.cnf.d]# systemctl start mysqld

[root@mgr02 my.cnf.d]# mysql -uroot -p

#切换数据库

mysql> use mysql;

#创建复制组用户

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;

#复制用户凭据到复制组通道

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Test@1234' FOR CHANNEL 'group_replication_recovery';

#查看复制组插件是否装载

mysql> SHOW PLUGINS;

如果有以下内容则表示已装载

group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL

#启动复制组

mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='Test@1234';

#查看复制组

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