阮胜昌的技术记录站-LinuxMySQL数据库运维

Configuring Group Replication in Single-Primary Mode

1.查看停止当前的mysql服务：
#service mysqld stop


2.查看grouprep.cnf启动配置文件：
[mysqld1]


# Server configuration
datadir=/var/lib/mysql1
port=3311
socket=/var/lib/mysql1/mysql.sock




# General Replication configuration
server-id=1
user=mysql
binlog-format=ROW
binlog-checksum=NONE
log-bin=mysql1-bin
relay-log=mysql1-relay-bin
log-slave-updates=ON
log-error=mysql1
report-host=localhost
report-port=3311
relay-log-recovery=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
gtid-mode=ON
enforce-gtid-consistency

# Group Replication configuration
transaction-write-set-extraction=XXHASH64
loose-group-replication-single-primary-mode=TRUE
loose-group-replication-group-name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
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,127.0.0.1:24904"
loose-group-replication-bootstrap-group=OFF

[mysqld2]


# Server configuration
datadir=/var/lib/mysql2
port=3312
socket=/var/lib/mysql2/mysql.sock

# General Replication configuration
server-id=2
user=mysql
binlog-format=ROW
binlog-checksum=NONE
log-bin=mysql2-bin
relay-log=mysql2-relay-bin
log-slave-updates=ON
log-error=mysql2
report-host=localhost
report-port=3312
relay-log-recovery=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
gtid-mode=ON
enforce-gtid-consistency

# Group Replication configuration
transaction-write-set-extraction=XXHASH64
loose-group-replication-single-primary-mode=TRUE
loose-group-replication-group-name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group-replication-start-on-boot=OFF
loose-group-replication-local-address="127.0.0.1:24902"
loose-group-replication-group-seeds="127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903,127.0.0.1:24904"
loose-group-replication-bootstrap-group=OFF

[mysqld3]


# Server configuration
datadir=/var/lib/mysql3
port=3313
socket=/var/lib/mysql3/mysql.sock


# General Replication configuration
server-id=3
user=mysql
binlog-format=ROW
binlog-checksum=NONE
log-bin=mysql3-bin
relay-log=mysql3-relay-bin
log-slave-updates=ON
log-error=mysql3
report-host=localhost
report-port=3313
relay-log-recovery=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
gtid-mode=ON
enforce-gtid-consistency


# Group Replication configuration
transaction-write-set-extraction=XXHASH64
loose-group-replication-single-primary-mode=TRUE
loose-group-replication-group-name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group-replication-start-on-boot=OFF
loose-group-replication-local-address="127.0.0.1:24903"
loose-group-replication-group-seeds="127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903,127.0.0.1:24904"
loose-group-replication-bootstrap-group=OFF


[mysqld4]


# Server configuration
datadir=/var/lib/mysql4
port=3314
socket=/var/lib/mysql4/mysql.sock


# General Replication configuration
server-id=4
user=mysql
binlog-format=ROW
binlog-checksum=NONE
log-bin=mysql4-bin
relay-log=mysql4-relay-bin
log-slave-updates=ON
log-error=mysql4
report-host=localhost
report-port=3314
relay-log-recovery=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
gtid-mode=ON
enforce-gtid-consistency


# Group Replication configuration
transaction-write-set-extraction=XXHASH64
loose-group-replication-single-primary-mode=TRUE
loose-group-replication-group-name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group-replication-start-on-boot=OFF
loose-group-replication-local-address="127.0.0.1:24904"
loose-group-replication-group-seeds="127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903,127.0.0.1:24904"
loose-group-replication-bootstrap-group=OFF


3.查看组复制相关的文件：
[root@node232 group_Replication]# pwd
/server/shell_scripts/group_Replication
[root@node232 group_Replication]# ls
grouprep-add.sql  grouprep.cnf  grouprep-setup.sh
[root@node232 group_Replication]#


4.查看组复制初始化文件，用于初始化多实例环境:
 #! /bin/bash


echo "** Stopping existing servers 1-4"
mysqld_multi --defaults-file=/server/shell_scripts/replication/repl.cnf --user=root stop 1-4


echo "** Removing existing servers 1-4..."
rm -rf /var/lib/mysql1
rm -rf /var/lib/mysql2
rm -rf /var/lib/mysql3
rm -rf /var/lib/mysql4
echo
echo "== Done"
echo


echo "** Initializing servers 1-4..."
echo "*** SERVER 1"
mkdir -p /var/lib/mysql1
mysqld --no-defaults --initialize-insecure --user=mysql --datadir=/var/lib/mysql1
echo
echo "*** SERVER 2"
mkdir -p /var/lib/mysql2
mysqld --no-defaults --initialize-insecure --user=mysql --datadir=/var/lib/mysql2
echo
echo "*** SERVER 3"
mkdir -p /var/lib/mysql3
mysqld --no-defaults --initialize-insecure --user=mysql --datadir=/var/lib/mysql3
echo
echo "*** SERVER 4"
mkdir -p /var/lib/mysql4
mysqld --no-defaults --initialize-insecure --user=mysql --datadir=/var/lib/mysql4
echo
echo "== Done"
echo


5.执行grouprep-setup.sh脚本文件，初始化多实例环境：
[root@node232 group_Replication]# /bin/bash /server/shell_scripts/group_Replication/grouprep-setup.sh


6.启动多实例环境中的第一个实例：
[root@node232 group_Replication]# mysqld_multi --defaults-file=/server/shell_scripts/group_Replication/grouprep.cnf start 1


7.通过mysql客户端连接到端口号为3311的第一个实例
[root@node232 group_Replication]# mysql -uroot -p -h127.0.0.1 -P3311


8.使用mysqluuid()函数为复制组创建唯一标识符。 将生成的标识符复制到剪贴板中。
root@localhost[(none)]>select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 57e95d05-e1d9-11eb-b91a-000c29cb49a4 |
+--------------------------------------+
1 row in set (0.01 sec)


9.退出客户端：
root@localhost[(none)]>exit
Bye


10.将复制的标识符粘贴到loose-group-replication-group-name配置文件grouprep.cnf中
将grouprep.cnf文件中的loose-group-replication-group-name的值，全部修改成57e95d05-e1d9-11eb-b91a-000c29cb49a4
[mysqld1]
loose-group-replication-group-name="57e95d05-e1d9-11eb-b91a-000c29cb49a4"


[mysqld2]
loose-group-replication-group-name="57e95d05-e1d9-11eb-b91a-000c29cb49a4"


[mysqld3]
loose-group-replication-group-name="57e95d05-e1d9-11eb-b91a-000c29cb49a4"


[mysqld4]
loose-group-replication-group-name="57e95d05-e1d9-11eb-b91a-000c29cb49a4"


11.停止3311端口的第一个数据库实例：
[root@node232 group_Replication]# mysqladmin -uroot -p  -h127.0.0.1 -P3311 shutdown
或者：mysqld_multi  --defaults-file=/server/shell_scripts/group_Replication/grouprep.cnf  stop 1


12.启动多实例服务1-4
[root@node232 group_Replication]# mysqld_multi --defaults-file=/server/shell_scripts/group_Replication/grouprep.cnf start 1-4


13.连接到第一个实例，端口号为3311，并配置提示符：
root@localhost[(none)]>prompt 1>
PROMPT set to '1>'
1>


14.在第一个实例，端口号为3311，关闭二进制日志
1>set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)


15. 在第一个实例中，创建一个名为repl,密码Rscpass123.的新用户，并授予该用户 对服务器中所有数据库和表的复制专用权限。
1>create user 'repl'@'127.0.0.1' identified by 'Rscpass123.';
Query OK, 0 rows affected (0.00 sec)


1>grant replication slave on *.* to 'repl'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)


16.打开二进制日志
1>set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)


17. 通过以下的命令，来保存组复制恢复通道的repl用户凭据:
1>CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='Rscpass123.' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)


18.在server1中加载组复制组件：
1>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.05 sec)




19.验证组复制组件是否正确安装：
1>show plugins;
+----------------------------+--------+--------------------+----------------------+---------+
| Name                       | Status | Type               | Library              | License |
+----------------------------+--------+--------------------+----------------------+---------+
| binlog                     | ACTIVE | STORAGE ENGINE     | NULL                 | GPL     |
| group_replication          | ACTIVE | GROUP REPLICATION  | group_replication.so | GPL     |
+----------------------------+--------+--------------------+----------------------+---------+


20.通过设置全局设置来引导服务器1上的复制组 将group_replication_bootstrap_groupsystem 参数配置为 ON
1>set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)




21.开启组复制：
1>start group_replication;
Query OK, 0 rows affected (1.36 sec)


22.配置全局参数group_replication_bootstrap_group的值为OFF
1>set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)


23.查询Performance Schema 库的 replication_group_member表，以验证 复制组存在，并且该服务器1是该组的成员
1>select * from performance_schema.replication_group_members\G;
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 9ffbf924-e1d8-11eb-a2d6-000c29cb49a4
MEMBER_HOST: localhost
MEMBER_PORT: 3311
MEMBER_STATE: ONLINE
1 row in set (0.00 sec)
ERROR:
No query specified


24.在server1，创建testDB数据库，并包含一个tb1表
1>create database testDB;
Query OK, 1 row affected (0.00 sec)


1>use testDB;
Database changed
1>create table tb1(c1 int primary key,c2 text not null);
Query OK, 0 rows affected (0.02 sec)
1>insert into tb1 values(1,'Group replication.');
Query OK, 1 row affected (0.02 sec)




25通过以下脚本，对server2,server3,server4批量配置组复制功能：
[root@node232 group_Replication]# pwd
/server/shell_scripts/group_Replication
[root@node232 group_Replication]# cat grouprep-add.sql
/*
grouprep-add.sql
This script simplifies adding new instances to the group
for the "Configuring Group Replication in Single Primary
mode activity in the "Group Replication" lesson.
To use it, first create the group with the primary and then:
1. Connect to the server using the mysql client
2. Change the mysql prompt to identify the server
3. SOURCE this file using the following statement:
     SOURCE /labs/grouprep-add.sql;
*/
SET SQL_LOG_BIN=0;
CREATE USER 'repl'@'127.0.0.1' IDENTIFIED BY 'Rscpass123.';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1';
Create user 'memroot'@'127.0.0.1' identified by 'Rscpass123.';
Grant all privileges on *.* to 'root'@'127.0.0.1' identified by 'Rscpass123.' with grant option;
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Rscpass123.' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
START GROUP_REPLICATION;
[root@node232 group_Replication]#


在server2上运行脚本：
[root@node232 group_Replication]# mysql -uroot -p -h127.0.0.1 -P3312
Enter password:
root@localhost[(none)]>prompt 2>
PROMPT set to '2>'
2>source /server/shell_scripts/group_Replication/grouprep-add.sql;


查看server2属于那个组：
2>select * from performance_schema.replication_group_members\G;
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 9ffbf924-e1d8-11eb-a2d6-000c29cb49a4
MEMBER_HOST: localhost
MEMBER_PORT: 3311
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: a2365b2a-e1d8-11eb-a46f-000c29cb49a4
MEMBER_HOST: localhost
MEMBER_PORT: 3312
MEMBER_STATE: ONLINE
2 rows in set (0.00 sec)


查看数据是否同步：
2>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testDB             |
+--------------------+
5 rows in set (0.01 sec)
2>select * from testDB.tb1;
+----+--------------------+
| c1 | c2                 |
+----+--------------------+
|  1 | Group replication. |
+----+--------------------+
1 row in set (0.00 sec)


server3,server4采用上面同样的运行脚本的方法来配置组复制：




26.验证组复制是否运行在单主模式：
2>select @@group_replication_single_primary_mode;
+-----------------------------------------+
| @@group_replication_single_primary_mode |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+
1 row in set (0.00 sec)




27.通过performance schema库的replication_group_members表来查看所有组的成员：
root@localhost[(none)]>select * from performance_schema.replication_group_members\G;
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 9ffbf924-e1d8-11eb-a2d6-000c29cb49a4
MEMBER_HOST: localhost
MEMBER_PORT: 3311
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: a2365b2a-e1d8-11eb-a46f-000c29cb49a4
MEMBER_HOST: localhost
MEMBER_PORT: 3312
MEMBER_STATE: ONLINE
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: a48d962d-e1d8-11eb-a452-000c29cb49a4
MEMBER_HOST: localhost
MEMBER_PORT: 3313
MEMBER_STATE: ONLINE
*************************** 4. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: a6dba605-e1d8-11eb-a5b1-000c29cb49a4
MEMBER_HOST: localhost
MEMBER_PORT: 3314
MEMBER_STATE: ONLINE
4 rows in set (0.00 sec)


ERROR:
No query specified




28.通过下面的查询，确定那个成员是master成员：
2>select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member';
+--------------------------------------+
| variable_value                       |
+--------------------------------------+
| 9ffbf924-e1d8-11eb-a2d6-000c29cb49a4 |
+--------------------------------------+
1 row in set (0.01 sec)


1>select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 9ffbf924-e1d8-11eb-a2d6-000c29cb49a4 |
+--------------------------------------+
1 row in set (0.00 sec)

以上可以确认，server1就是master服务器

29.在非主服务器上试着去插入一条新数据：
2>insert into testDB.tb1 values(2,'Somethind.');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
2>

总结：
在单主模式的复制组配置中：
非主服务器是只读的，不允许直接更新。全部 必须在主服务器上进行更新，并复制到只读服务器上 

微信公众号视频：https://mp.weixin.qq.com/s/zZa8O8dunv_ymfQGOfEnDw