本文共 8861 字,大约阅读时间需要 29 分钟。
组复制模型:
MySQL组复制是MySQL 5.7.17开始引入的新功能,为主从复制实现高可用功能。它支持单主模型和多主模型两种工作方式(默认是单主模型)。
单主模型:从复制组中众多个MySQL节点中自动选举一个master节点,只有master节点可以写,其他节点自动设置为read only。当master节点故障时,会自动选举一个新的master节点,选举成功后,它将设置为可写,其他slave将指向这个新的master。
多主模型:复制组中的任何一个节点都可以写,因此没有master和slave的概念,只要突然故障的节点数量不太多,这个多主模型就能继续可用。
组复制原理:
复制组由多个 server成员构成,并且组中的每个 server 成员可以独立地执行事务。但所有读写(RW)事务只有在冲突检测成功后才会提交。只读(RO)事务不需要在冲突检测,可以立即提交。
换句话说,对于任何 RW 事务,提交操作并不是由始发 server 单向决定的,而是由组来决定是否提交。准确地说,在始发 server 上,当事务准备好提交时,该 server 会广播写入值(已改变的行)和对应的写入集(已更新的行的唯一标识符)。然后会为该事务建立一个全局的顺序。最终,这意味着所有 server 成员以相同的顺序接收同一组事务。因此,所有 server 成员以相同的顺序应用相同的更改,以确保组内一致。
组复制使您能够根据在一组 server 中复制系统的状态来创建具有冗余的容错系统。因此,只要它不是全部或多数 server 发生故障,即使有一些 server 故障,系统仍然可用,最多只是性能和可伸缩性降低,但它仍然可用。server 故障是孤立并且独立的。它们由组成员服务来监控,组成员服务依赖于分布式故障检测系统,其能够在任何 server 自愿地或由于意外停止而离开组时发出信号。
总之,MySQL 组复制提供了高可用性,高弹性,可靠的 MySQL 服务。
主机 | IP | 角色 |
---|---|---|
server3 | 172.25.19.3 | master |
server4 | 172.25.19.4 | slave |
server5 | 172.25.19.5 | slave |
[root@server3 ~]# systemctl stop mysqld[root@server3 ~]# cd /var/lib/mysql[root@server3 mysql]# rm -fr *
[root@server3 mysql]# uuidgen946ae8b3-5ae6-43fb-af97-3a0d82ec6e12
[root@server3 mysql]# vim /etc/my.cnfserver_id=1gtid_mode=ONenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONlog_bin=binlogbinlog_format=ROWtransaction_write_set_extraction=XXHASH64 ###指示Server必须为每个事务收集写集合,并使用XXHASH64哈希算法将其编码为散列loose-group_replication_group_name="946ae8b3-5ae6-43fb-af97-3a0d82ec6e12" ##告知插件,正在加入或创建的组要命名,一般写UUIDloose-group_replication_start_on_boot=off ##指示插件在 server 启动时不自动启动组复制。loose-group_replication_local_address="172.25.19.3:24901" ##告诉插件使用 IP 地址 本地主机,端口 24901 用于接受来自组中其他成员的传入连接。loose-group_replication_group_seeds="172.25.19.3:24901,172.25.19.4:24901,172.25.19.5:24901"loose-group_replication_bootstrap_group=off #配置是否自动引导组 loose-group_replication_ip_whitelist="127.0.0.1,172.25.19.0/24" # 用户白名单loose-group_replication_enforce_update_everywhere_checks=ON #多主模式下为多主更新启用或禁用严格一致性检查。 loose-group_replication_single_primary_mode=OFF #设置组自动选择一个 server 来处理读/写工作。
[root@server3 mysql]# systemctl start mysqld[root@server3 mysql]# grep password /var/log/mysqld.log ##过滤初始密码,在最后面[root@server3 mysql]# mysql -pEnter password: ##初始化密码 mysql> alter user root@localhost identified by 'HUIyange+32'; ##修改密码Query OK, 0 rows affected (0.02 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+4 rows in set (0.00 sec)
mysql> SET SQL_LOG_BIN=0; #禁用二进制日志Query OK, 0 rows affected (0.00 sec)mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'HUIyange+32';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)##一旦配置了用户,使用CHANGE MASTER TO语句将服务器配置为在下一次需要从其他成员恢复状态时使用group_replication_recovery复制通道的给定凭证。发出以下命令,用创建用户时使用的值替换rpl_user和密码。mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='HUIyange+32' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.23 sec)mysql> INSTALL PLUGIN group_replication SONAME #安装组复制插件'group_replication.so';Query OK, 0 rows affected (0.85 sec)mysql> SET GLOBAL group_replication_bootstrap_group=ON; ##master上要先打开,等打开组复制之后再开启(slave上不用进行) Query OK, 0 rows affected (0.00 sec)mysql> START GROUP_REPLICATION; ##打开组复制Query OK, 0 rows affected (2.88 sec)mysql> SET GLOBAL group_replication_bootstrap_group=OFF;Query OK, 0 rows affected (0.00 sec)
mysql> CREATE DATABASE test;Query OK, 1 row affected (0.37 sec)mysql> USE test;Database changedmysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);Query OK, 0 rows affected (0.59 sec)mysql> INSERT INTO t1 VALUES (1, 'Luis');Query OK, 1 row affected (0.43 sec)mysql> SELECT * FROM t1;+----+------+| c1 | c2 |+----+------+| 1 | Luis |+----+------+1 row in set (0.00 sec)
[root@server4 ~]# systemctl stop mysqld[root@server4 ~]# cd /var/lib/mysql[root@server4 mysql]# rm -fr
[root@server4 ~]# vim /etc/my.cnfserver_id=2 ##修改idgtid_mode=ONenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONlog_bin=binlogbinlog_format=ROWtransaction_write_set_extraction=XXHASH64loose-group_replication_group_name="946ae8b3-5ae6-43fb-af97-3a0d82ec6e12"loose-group_replication_start_on_boot=offloose-group_replication_local_address="172.25.19.4:24901" ##修改本机iploose-group_replication_group_seeds="172.25.19.3:24901,172.25.19.4:24901,172.25.19.5:24901"loose-group_replication_bootstrap_group=offloose-group_replication_ip_whitelist="127.0.0.1,172.25.19.0/24"loose-group_replication_enforce_update_everywhere_checks=ONloose-group_replication_single_primary_mode=OFF
[root@server4 ~]# systemctl start mysqld[root@server3 mysql]# grep password /var/log/mysqld.log[root@server4 ~]# mysql -pEnter password: mysql> alter user root@localhost identified by 'HUIyange+32'; ##修改密码Query OK, 0 rows affected (0.46 sec)mysql> SET SQL_LOG_BIN=0;Query OK, 0 rows affected (0.00 sec)mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'HUIyange+32';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)mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='HUIyange+32' FOR CHANNEL 'group_replication_recovery';Query OK, 0 rows affected, 2 warnings (0.68 sec)mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';Query OK, 0 rows affected (0.68 sec)mysql> reset master; ##slave要先reset,才能开启组复制成功,否则会报错Query OK, 0 rows affected (0.44 sec)mysql> START GROUP_REPLICATION;Query OK, 0 rows affected (6.28 sec)
此时在server3上查看状态
然后在server4上,可以看到server3上刚才创建的数据表
[root@server5 ~]# vim /etc/my.cnfserver_id=3 ##修改idgtid_mode=ONenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONlog_bin=binlogbinlog_format=ROWtransaction_write_set_extraction=XXHASH64loose-group_replication_group_name="946ae8b3-5ae6-43fb-af97-3a0d82ec6e12"loose-group_replication_start_on_boot=offloose-group_replication_local_address="172.25.19.5:24901" ##修改本机IPloose-group_replication_group_seeds="172.25.19.3:24901,172.25.19.4:24901,172.25.19.5:24901"loose-group_replication_bootstrap_group=offloose-group_replication_ip_whitelist="127.0.0.1,172.25.19.0/24"loose-group_replication_enforce_update_everywhere_checks=ONloose-group_replication_single_primary_mode=OFF[root@server5 mysql]# systemctl start mysqld[root@server5 mysql]# grep password /var/log/mysqld.log[root@server5 mysql]# mysql -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.24-logCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> alter user root@localhost identified by 'HUIyange+32';Query OK, 0 rows affected (0.04 sec)mysql> SET SQL_LOG_BIN=0;Query OK, 0 rows affected (0.00 sec)mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'HUIyange+32';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)mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='HUIyange+32' FOR CHANNEL 'group_replication_recovery';Query OK, 0 rows affected, 2 warnings (0.29 sec)mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';Query OK, 0 rows affected (0.36 sec)mysql> reset master;Query OK, 0 rows affected (0.45 sec)mysql> START GROUP_REPLICATION;Query OK, 0 rows affected (3.88 sec)
mysql> use test;Database changedmysql> INSERT INTO t1 VALUES (2,'hui');Query OK, 1 row affected (0.73 sec)
mysql> use test;Database changedmysql> INSERT INTO t1 VALUES (3,'hyg');Query OK, 1 row affected (0.52 sec)
转载地址:http://eghrn.baihongyu.com/