企业级数据库集群[Mysql]——————mysql的全同步复制（组复制）

最新推荐文章于 2024-09-14 19:55:29 发布

mango_kid

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量214

点赞数

分类专栏：数据库文章标签：数据库 mysql 运维 linux

本文链接：https://blog.csdn.net/mango_kid/article/details/108415952

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

文章目录

1.了解mysql的全同步复制
2. 搭建集群

1.了解mysql的全同步复制

MySQL组复制是MySQL 5.7.17开始引入的新功能，为主从复制实现高可用功能

1）组复制模型

它支持单主模型和多主模型两种工作方式(默认是单主模型)
单主模型：从复制组中众多个MySQL节点中自动选举一个master节点，只有master节点可以写，其他节点自动设置为read only，当master节点故障时，会自动选举一个新的master节点，选举成功后，它将设置为可写，其他slave将指向这个新的master。
多主模型：复制组中的任何一个节点都可以写，因此没有master和slave的概念，只要突然故障的节点数量不太多，这个多主模型就能继续可用。

2）组复制原理

复制组由多个 server成员构成，并且组中的每个 server 成员可以独立地执行事务
但所有读写（RW）事务只有在冲突检测成功后才会提交。
只读（RO）事务不需要在冲突检测，可以立即提交。

换句话说，对于任何 RW 事务，提交操作并不是由始发 server 单向决定的，而是由组来决定是否提交。准确地说，在始发 server 上，当事务准备好提交时，该 server 会广播写入值（已改变的行）和对应的写入集（已更新的行的唯一标识符）。然后会为该事务建立一个全局的顺序。最终，这意味着所有 server 成员以相同的顺序接收同一组事务。因此，所有 server 成员以相同的顺序应用相同的更改，以确保组内一致。

组复制能够根据在一组 server 中复制系统的状态来创建具有冗余的容错系统。因此，只要它不是全部或多数 server 发生故障，即使有一些 server 故障，系统仍然可用，最多只是性能和可伸缩性降低，但它仍然可用。server 故障是孤立并且独立的。它们由组成员服务来监控，组成员服务依赖于分布式故障检测系统，其能够在任何 server 自愿地或由于意外停止而离开组时发出信号。

总之，MySQL 组复制提供了高可用性，高弹性，可靠的 MySQL 服务

3）组复制的缺点：

但是组复制的效率很低

当master节点写数据的时候，会等待所有的slave节点完成数据的复制，然后才继续往下进行
组复制的每一个节点都可能是slave

2. 搭建集群

实验准备：

我们准备三台mysql服务器来进行实验

2.1 配置server1为组的发起者

1）查看uuid：

[root@server1 ~]# cd /var/lib/mysql
[root@server1 mysql]# cat auto.cnf 
[auto]
server-uuid=513e6d8e-ef42-11ea-8fe7-5254000fd593

2）编辑配置文件：

[root@server1 mysql]# vim /etc/my.cnf
# 最后添加
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=binlog
binlog_format=ROW


transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="513e6d8e-ef42-11ea-8fe7-5254000fd593"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="172.25.5.11:33061"
loose-group_replication_group_seeds="172.25.5.11:33061,172.25.5.12:33061,172.25.5.10:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.5.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF

3）开启mysql服务：

[root@server1 mysql]# systemctl restart mysqld

4）清空之前实验的mysql数据：

[root@server1 mysql]# cd /var/lib/mysql
[root@server1 mysql]# systemctl stop mysqld
[root@server1 mysql]# rm -fr *
[root@server1 mysql]# systemctl restart mysqld

5)登录数据库使用新生成登录密码：

[root@server1 mysql]# cat /var/log/mysqld.log | grep password
2020-09-05T08:47:15.103729Z 1 [Note] A temporary password is generated for root@localhost: mosejekXR9%X
[root@server1 mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.24-log

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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>

6）修改数据库密码：

mysql> alter user root@localhost identified by "Server+147";
Query OK, 0 rows affected (0.00 sec)

7）关闭二进制日志：

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

8）创建从节点用户：

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Yan+123kou';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES; # 刷新用户授权表
Query OK, 0 rows affected (0.00 sec)

8）开启二进制日志：

SET SQL_LOG_BIN=1;

9）配置主用户：

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Yan+123kou' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.04 sec)

10）安装组复制插件，开启组复制：

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.04 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=ON; 组复制激活
Query OK, 0 rows affected (0.01 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.86 sec)

11）关闭组复制激活，查看组的状态，当前只有一个节点在线：

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST         | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| group_replication_applier | 65b6e846-ef54-11ea-baf8-5254000fd593 | server1.example.com |        3306 | ONLINE       |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
1 row in set (0.00 sec)

12）建立一个数据库test，建表写入数据：方便一会测试

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> USE test;
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.00 sec)

2.2 配置server2

1）server1将自己的配置文件发送给server2，server2进行修改：

[root@server1 mysql]# scp /etc/my.cnf root@172.25.5.12:/etc/
The authenticity of host '172.25.5.12 (172.25.5.12)' can't be established.
ECDSA key fingerprint is 7a:47:50:81:e2:b5:bf:17:99:f0:e2:ba:39:b7:35:2d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.25.5.12' (ECDSA) to the list of known hosts.
root@172.25.5.12's password: 
my.cnf 

[root@server2 ~]# vim /etc/my.cnf

server_id=2 # 修改为自己的id
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=binlog
binlog_format=ROW


transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="513e6d8e-ef42-11ea-8fe7-5254000fd593" # 使用相同的uuid
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="172.25.5.12:33061" # 使用自己的ip
loose-group_replication_group_seeds="172.25.5.11:33061,172.25.5.12:33061,172.25.5.10:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.5.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF

2）删除server2上之前的mysql数据：

[root@server2 ~]# systemctl stop mysqld
[root@server2 ~]# cd /var/lib/mysql
[root@server2 mysql]# ls
auto.cnf    client-cert.pem  ibdata1      master.info         private_key.pem  server-cert.pem         slave-relay-bin.000005  testfile
ca-key.pem  client-key.pem   ib_logfile0  mysql               public_key.pem   server-key.pem          slave-relay-bin.index   testfiles
ca.pem      ib_buffer_pool   ib_logfile1  performance_schema  relay-log.info   slave-relay-bin.000004  sys
[root@server2 mysql]# rm -fr *

3）重新启动mysql服务：

[root@server2 mysql]# systemctl restart  mysqld

4）查看mysql的新密码，进行登录修改密码：

[root@server2 mysql]# cat /var/log/mysqld.log | grep password
2020-09-05T09:08:54.638368Z 1 [Note] A temporary password is generated for root@localhost: hNndN%zob2+j
[root@server2 mysql]# mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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 by "Server+147";
Query OK, 0 rows affected (0.00 sec)

5）关闭二进制日志，建立从节点用户，再次开启二进制日志：

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Yan+123kou';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

6）修改主节点信息：

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Yan+123kou' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.08 sec)

7）下载全同步配置服务，激活复制服务，开启组同步：

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.04 sec)

mysql> set global group_replication_allow_local_disjoint_gtids_join=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (6.75 sec)

8）server2也已经添加进去了：

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST         | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| group_replication_applier | 65b6e846-ef54-11ea-baf8-5254000fd593 | server1.example.com |        3306 | ONLINE       |
| group_replication_applier | 6c4bde80-ef57-11ea-943a-525400019149 | server2.example.com |        3306 | RECOVERING   |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
2 rows in set (0.00 sec)

2.3 配置server3

1）server2将自己的配置文件发送给server3，server3进行修改：

[root@server1 mysql]# scp /etc/my.cnf root@172.25.5.10:/etc/
root@172.25.5.10's password: 
my.cnf

server_id=3
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=binlog
binlog_format=ROW


transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="513e6d8e-ef42-11ea-8fe7-5254000fd593"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="172.25.5.10:33061"
loose-group_replication_group_seeds="172.25.5.11:33061,172.25.5.12:33061,172.25.5.10:33061"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.5.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF

2）启动mysql服务，修改root用户的密码：

[root@server3 mysql]# systemctl start mysqld
[root@server3 mysql]#  cat /var/log/mysqld.log | grep password
2020-09-05T09:40:09.146500Z 1 [Note] A temporary password is generated for root@localhost: KWY.WLgaj7aa
[root@server3 mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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 by "Server+147";
Query OK, 0 rows affected (0.00 sec)

3）关闭二进制日志，建立从节点用户，再次开启二进制日志：

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Yan+123kou';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

4）修改主节点信息：

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Yan+123kou' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.08 sec)

5）下载全同步配置服务，激活复制服务，开启组同步：

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.04 sec)

mysql> set global group_replication_allow_local_disjoint_gtids_join=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (6.75 sec)

6）server3也已经添加进去了：

mysql>  SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST         | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| group_replication_applier | 65b6e846-ef54-11ea-baf8-5254000fd593 | server1.example.com |        3306 | ONLINE       |
| group_replication_applier | 6c4bde80-ef57-11ea-943a-525400019149 | server2.example.com |        3306 | RECOVERING   |
| group_replication_applier | c9973fad-ef5b-11ea-afa5-525400c4b8ec | server3.example.com |        3306 | RECOVERING   |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
3 rows in set (0.00 sec)