什么是组复制
MySQL 组复制实现了基于复制协议的多主更新。
复制组由多个 server成员构成,并且组中的每个 server
成员可以独立地执行事务。但所有读 写(RW)事务只有在冲突检测成功后才会提交。只读(RO)事务不需要在冲突检测,可以立即提交。
简单来说,就是同时是主和备
组复制特点
一.特点:
● 高一致性
基于原生复制及 paxos 协议的组复制技术,并以插件的方式提供,提供一致数据安全保证;
● 高容错性
只要不是大多数节点坏掉就可以继续工作,有自动检测机制,当不同节点产生资源争用冲突时,不会出现错误,按照先到者优先原则进行处理,并且内置了自动化脑裂防护机制;
● 高扩展性
节点的新增和移除都是自动的,新节点加入后,会自动从其他节点上同步状态,直到新节点和其他节点保持一致,如果某节点被移除了,其他节点自动更新组信息,自动维护新的组信息;
● 高灵活性
有单主模式和多主模式,单主模式下,会自动选主,所有更新操作都在主上进行; 多主模式下,所有 server都可以同时处理更新操作。
什么样的应用场景适合用组复制
1、弹性的数据库复制环境
组复制可以灵活的增加和减少集群中的数据库实例
2、高可用的数据库环境
组复制允许数据库实例宕机,只要集群中大多数服务器可用,则整个数据库服务可用
3、替代传统主从复制结构的数据库环境
server3 | 172.25.34.4 | master1 |
---|---|---|
server4 | 172.25.34.5 | master2 |
server5 | 172.25.34.6 | master3 |
在进行组复制部署之前,我们生一个uuid,在写入配置文件的loose-group_replication_group_name参数的uuid必须不能和三个主机中的任何一个主机uuid一样
server3上
[root@server3 ~]uuidgen # 随机生成uuid
c36cc649-3f17-11e9-960e-525400cf2a01
也可以直接在已经部署mysql服务的主机上查看uuid
[root@server3 ~]# cd /var/lib/mysql
[root@server3 mysql]# ls
auto.cnf ibdata1 mysql-bin.000002 private_key.pem
ca-key.pem ib_logfile0 mysql-bin.000003 public_key.pem
ca.pem ib_logfile1 mysql-bin.index server-cert.pem
client-cert.pem ibtmp1 mysql.sock server-key.pemgu
client-key.pem mysql mysql.sock.lock sys
ib_buffer_pool mysql-bin.000001 performance_schema westos
[root@server3 mysql]# cat auto.cnf
[auto]
server-uuid=3cd7bcf6-f2ed-11e9-8496-5254006dc583
如果是已经部署mysql的主机,先清除环境,重新初始化,主要就是删除在目录/var/lb/mysql中的文件
注意:在删除文件之前一定要先停掉服务
[root@server3 ~]# cd /var/lib/mysql
[root@server4 mysql]# systemctl stop mysqld
[root@server4 mysql]# ps ax | grep mysqld
3096 pts/0 S+ 0:00 grep --color=auto mysqld
[root@server4 mysql]# rm -fr *
[root@server4 mysql]# systemctl start mysqld ##再次重启服务之后,会重新在该目录生成文件
如果是没有安装过mysql的进行安装
[root@server3 ~]# ls
mysql-community-client-5.7.24-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm
mysql-community-common-5.7.24-1.el7.x86_64.rpm
mysql-community-server-5.7.24-1.el7.x86_64.rpm
mysql-community-libs-5.7.24-1.el7.x86_64.rpm
[root@base4 ~]# yum install -y *
编辑配置文件
[root@server3 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="e3c782ed-8970-4905-a7f8-1d0547d81f0c" # 这是随机生成的,但是一旦设定,uuid就是确定的
loose-group_replication_start_on_boot=off # 开机不启动
loose-group_replication_local_address="172.25.34.4:24901" # 绑定本地的172.25.78.12以及24901端口接受其他组成员的连接
loose-group_replication_group_seeds="172.25.34.4:24901,172.25.34.5:24901,172.25.34.6:24901" # 组复制的成员
loose-group_replication_bootstrap_group=off # 配置不自动引导组
loose-group_replication_ip_whitelist="127.0.0.1,172.25.34.0/24" # 允许网段
loose-group_replication_enforce_update_everywhere_checks=ON # 更新检测
loose-group_replication_single_primary_mode=OFF # 设置组自动选择一个 server 来处理读/写工作。
重启服务 安全初始化
[root@base2 mysql]# systemct restart mysqld # 重启数据库,加载配置文件
[root@base2 mysql]# cat /var/log/mysqld.log | grep password # 获取初始密码
[root@server4 mysql]# mysql_secure_installation
[root@server4 mysql]# mysql -uroot -pWestos+001
进入数据库,授权用户,安装插件,开启组复制
[root@server3 mysql]# mysql -p # 用初始密码登录数据库
Enter password:
mysql> SET SQL_LOG_BIN=0; # 关闭二进制日志,避免在从服务上复制
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Westos+001';
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; # 刷新
mysql> SET SQL_LOG_BIN=1; # 开启日志
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Westos+001' FOR CHANNEL 'group_replication_recovery'; # 配置组用户
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; # 安装plugin插件
mysql> SET GLOBAL group_replication_bootstrap_group=ON; #这句只有base2,在第一次执行引导组的时候执行
mysql> START GROUP_REPLICATION; # 开启组复制
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> SELECT * FROM performance_schema.replication_group_members; # 查看组复制成员
创建个数据库作为测试
> mysql> CREATE DATABASE test: # 创建数据库
> mysql> use test;
> changed mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
> # 创建表 mysql> INSERT INTO t1 VALUES (1, 'Luis'); mysql> SELECT * FROM t1;
server4上
之前步骤重复进行,如果已经安装过mysql的重新安装,初始化,没有安装过mysql进行安装
[root@server4 mysql]# vi /etc/my.cnf
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE #关闭binlog校验
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW #组复制依赖基于行的复制格式
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="c36cc649-3f17-11e9-960e-525400cf2a01" ##可以看/var/lib/mysql/auto.cnf
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.34.5:24901"
loose-group_replication_group_seeds= "172.25.34.4:24901,172.25.34.5:24901,172.25.34.6:24901"
loose-group_replication_bootstrap_group=off ##插件是否自动引导,这个选项一般都要off掉,只需要由发起组复制的节点开启,
并只启动一次,如果是on,下次再启动时,会生成一个同名的组,可能会发生脑裂
loose-group_replication_ip_whitelist="127.0.0.1,172.25.34.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
进入数据库
[root@server4 mysql]# mysql -uroot -pWestos+001
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Westos+001';
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='Westos+001' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.08 sec)
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 | a6f12866-f31e-11e9-b82b-525400babebf | server4 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
server5
和之前server4步骤相同
编辑配置文件
[root@server5 mysql]# vi /etc/my.cnf
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE #关闭binlog校验
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW #组复制依赖基于行的复制格式
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="c36cc649-3f17-11e9-960e-525400cf2a01" ##可以看/var/lib/mysql/auto.cnf
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.34.6:24901"
loose-group_replication_group_seeds= "172.25.34.4:24901,172.25.34.5:24901,172.25.34.6:24901"
loose-group_replication_bootstrap_group=off ##插件是否自动引导,这个选项一般都要off掉,只需要由发起组复制的节点开启,
并只启动一次,如果是on,下次再启动时,会生成一个同名的组,可能会发生脑裂
loose-group_replication_ip_whitelist="127.0.0.1,172.25.34.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
重启服务
[root@base4 ~]# systemctl restart mysqld
进入数据库
[root@server5 ~]# mysql -p
Enter password:
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Westos+001';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Westos+001' FOR CHANNEL 'group_replication_recovery';
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> reset master;
mysql> START GROUP_REPLICATION; # 开启组复制
mysql> SELECT * FROM performance_schema.replication_group_members; # 查看组成员成员状态
mysql> show databases; # 直接查看,把server3上的数据同步成功