MySQL Group Replication 单机多实例安装配置

最新推荐文章于 2025-12-01 02:11:37 发布

原创最新推荐文章于 2025-12-01 02:11:37 发布 · 266 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

mysql 同时被 3 个专栏收录

5 篇文章

订阅专栏

group

4 篇文章

订阅专栏

replication

4 篇文章

订阅专栏

本文详细介绍如何通过具体步骤创建并配置MySQL组复制环境，包括数据库实例的初始化、节点配置及启动、用户权限设置、组复制插件的安装与启用等。

1、创建三个数据库实例

mkdir /data/mysql/sock
mkdir –p /data/mysql/data/{s1,s2,s3}
mkdir /data/mysql/cnf
mkdir –p /data/mysql/logs/{s1,s2,s3}
cd /data/mysql
chown -R mysql:mysql *
cd /usr/local/mysql/
chown -R mysql:mysql *
bin/mysqld --initialize-insecure --basedir=/usr/local/mysql --datadir=/data/mysql/data/s1
bin/mysqld --initialize-insecure --basedir=/usr/local/mysql --datadir=/data/mysql/data/s2
bin/mysqld --initialize-insecure --basedir=/usr/local/mysql --datadir=/data/mysql/data/s3

2、配置第一个节点

[mysqld]
#Server Configuration
basedir=/usr/local/mysql
datadir=/data/mysql/data/s1
port = 24801
socket = /data/mysql/sock/s1.sock

#Replication Framework
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=/data/mysql/logs/s1/binlog
binlog_format=ROW 
relay-log=/data/mysql/logs/s1/relay-bin

#Group Replication
transaction_write_set_extraction=XXHASH64
loose- group_replication_group_name="b4668cea-d7ca-11e6-86b5-18a99b76310d"       
loose- group_replication_start_on_boot=off
loose- group_replication_local_address= "127.0.0.1:24901"
loose- group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"
loose- group_replication_bootstrap_group= off
loose- group_replication_single_primary_mode=FALSE
loose- group_replication_enforce_update_everywhere_checks= TRUE

参数配置中除了如下参数在各节点不一样外，其他配置都一样：datadir、port、socket、server_id、log_bin、relay-log、loose- group_replication_local_address

3、启动第一个节点

cd /usr/local/mysql/
sudo chown -R mysql:mysql *
ln -s /usr/local/mysql/bin/mysql /usr/bin
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/cnf/s1.cnf

4、登录第一个节点

mysql -h127.0.0.1 -P24801 -uroot --skip-password
#登录后修改默认密码
SET PASSWORD = PASSWORD('root');
flush privileges;

5、创建group replication所需要的用户

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

6、开启组复制

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+
| Name                       | Status   | Type               |
+----------------------------+----------+--------------------+
| binlog                     | ACTIVE   | STORAGE ENGINE     |
...
| group_replication          | ACTIVE   | GROUP REPLICATION  |
+----------------------------+----------+--------------------+
45 rows in set (0.02 sec)

7、启动group replication

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
#查看组复制是否启动成功
SELECT * FROM performance_schema.replication_group_members;

8、配置第二个节点（仅列出和s1的不同之处）

datadir=/data/mysql/data/s2
port = 24802
socket = /data/mysql/sock/s2.sock
server_id=2
log_bin=/data/mysql/logs/s2/binlog
relay-log=/data/mysql/logs/s2/relay-bin
loose-group_replication_local_address= "127.0.0.1:24902"

9、启动第二个节点（实例）

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/cnf/s2.cnf

10、登录第二个节点并修改默认密码

mysql -h127.0.0.1 -P24802 -uroot --skip-password
SET PASSWORD = PASSWORD('root');
flush privileges;

11、配置group replication需要的用户

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

12、安装group replication插件

INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Show plugins;
+----------------------------+----------+--------------------+
| Name                       | Status   | Type               |
+----------------------------+----------+--------------------+
| binlog                     | ACTIVE   | STORAGE ENGINE     |
...
| group_replication          | ACTIVE   | GROUP REPLICATION  |
+----------------------------+----------+--------------------+
45 rows in set (0.02 sec)

13、将s2添加到组中

set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;
#查看改group replication情况
SELECT * FROM performance_schema.replication_group_members;

14、添加第三个节点

操作同第二个节点，s3.cnf的参数略有不同
#添加完成查看各节点的运行状态
SELECT * FROM performance_schema.replication_group_members;

参考资料：

卸载mysql：http://www.cnblogs.com/kerrycode/p/4364465.html

MySQL Group Replication: http://mysqlhighavailability.com/mysqlha/gr/doc/getting_started.html