MySQL---MGR保姆版

一、环境清理：

三台机器都做：
1、修改主机名
2、修改/etc/hosts文件
3、关闭和禁用防火墙
4、关闭和禁用SELinux
5、生成密钥对
6、传输密钥对
7、验证免密登陆

yum remove mysql-server -y
rm -rf /etc/my.cnf.d/
rm -rf /var/lib/mysql/
rm -rf /var/log/mysql/
1
2
3
4

二、安装数据库

三台机器都安装数据库及启动数据库服务器

mount /dev/sr0 /mnt
yum install mysql-server -y
systemctl start mysqld
systemctl stop mysqld
1
2
3
4

三、主从搭建

1、第一台服务器：主服务器

1）编写配置文件：

cd /etc/my.cnf.d/
vim mysql-server.cnf
1
2

2）在其后追加内容：

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= "mgr01:33061"
group_replication_group_seeds= "mgr01:33061,mgr02:33061,mgr03:33061"
group_replication_bootstrap_group=off
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

配置文件编辑完成
3）启动数据库服务

[root@mgr01 ~]# systemctl start mysqld
[root@mgr01 ~]# mysql -uroot -p
mysql> use mysql;
1
2
3

4）创建复制组用户

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;
1
2
3
4
5
6

5）复制用户凭据到复制组通道：

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

6）查看复制组插件是否安装：

mysql> SHOW PLUGINS;

如果有以下内容则表示已装载
 group_replication               | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL   
1
2
3
4

7）启动复制组：

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;
1
2
3

8）查看复制组：

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)
1
2
3
4
5
6
7
8

到此主服务器配置完成！！！

2、俩台从服务器配置（一样）

1）复制第一台服务器的MySQL配置：

[root@mgr02 ~]# cd /etc/my.cnf.d/
[root@mgr02 my.cnf.d]# rm -rf mysql-server.cnf 
[root@mgr02 my.cnf.d]# scp mgr01:/etc/my.cnf.d/mysql-server.cnf .
1
2
3

2）编辑以下俩个相关配置：

server_id=2
group_replication_local_address= "mgr02:33061"
1
2

3）启动MySQL服务器：

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

4）连接服务器：

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

5）切换数据库：

mysql> use mysql;
1

6）创建复制组用户：

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;
1
2
3
4
5
6

7）复制用户凭据到复制组通到：

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

8）查看复制组插件：

mysql> SHOW PLUGINS;

如果有以下内容则表示已装载
 group_replication               | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL
1
2
3
4

9）启动复制组：

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

10）查看复制组：

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)
1
2
3
4
5
6
7
8
9

11）停止复制组：

mysql> stop GROUP_REPLICATION
1