###MGR单主搭建流程###
##每个节点都需要创建用户###
mysql> grant replication slave on *.* to 'repl'@'192.168.1.%' identified by '12345678';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
#MGR1 配置文件
[mysqld]
user=mysql
server_id=1
basedir=/usr/local/mysql3306
datadir=/data/3306/data
port=3306
log_error=/data/3306/data/mysql-error.log
log-bin=/data/3306/data/mysql-binlog
relay-log=/data/3306/data/mysql-relaylog
gtid_mode=on
report_host=10.0.0.159
enforce_gtid_consistency=ON
binlog_checksum=NONE
master_info_repository=TABLE
relay_log_info_repository=TABLE
log-slave-updates=on
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="05153196-c2ab-43ae-b355-b9832eacf0b2"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="10.0.0.159:33061"
loose-group_replication_group_seeds="10.0.0.159:33061,10.0.0.215:33062,10.0.0.139:33063"
loose-group_replication_bootstrap_group=off
#该参数决定启用单主模式(on)还是多主模式(off),默认单主模式
loose-group_replication_single_primary_mode=ON
# 在多主模式下部署时,将检查语句以确保它们与该模式兼容。在多主模式下部署组复制时,将进行以下检查:
# --如果事务是在SERIALIZABLE隔离级别下执行的,则在与组同步时,其提交将失败。
# --如果事务是针对具有具有级联约束的外键的表执行的,则该事务在与组同步时将无法提交。
# 这些检查可以通过设置选项来禁用 group_replication_enforce_update_everywhere_checks 到FALSE。在单主要模式下部署时,此选项必须设置为FALSE。
loose-group_replication_enforce_update_everywhere_checks= false
#启动mysqld
/bin/sh /usr/local/mysql3306/bin/mysqld_safe --defaults-file=/data/3306/my.cnf
#安装组复制插件
install PLUGIN group_replication SONAME 'group_replication.so';
#查看组复制插件是否启用
show plugins;
#构建组复制集群信息
change master to master_user='repl',master_password='123' for channel 'group_replication_recovery';
#开启组复制引导
set global group_replication_bootstrap_group=on;
start group_replication;
#关闭组复制引导
set global group_replication_bootstrap_group=off;
#查看master节点状态
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 7cbd3eaa-380f-11ee-8647-fa163e1057f8 | 10.0.0.159 | 3306 | ONLINE | PRIMARY | 8.0.32 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)
###其他节点加入MGR###
从库配置文件
[mysqld]
user=mysql
server_id=2 修改server-id
basedir=/usr/local/mysql3306
datadir=/data/3306/data
port=3306
log_error=/data/3306/data/mysql-error.log
log-bin=/data/3306/data/mysql-binlog
relay-log=/data/3306/data/mysql-relaylog
gtid_mode=on
enforce_gtid_consistency=ON
binlog_checksum=NONE
master_info_repository=TABLE
relay_log_info_repository=TABLE
log-slave-updates=on
report_host=10.0.0.215
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="05153196-c2ab-43ae-b355-b9832eacf0b2"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="10.0.0.215:33062" 每个机器这里需要修改为当前ip:port 可以理解为mgr的port,非3306
loose-group_replication_group_seeds="10.0.0.159:33061,10.0.0.215:33062,10.0.0.139:33063"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=ON
#loose-group_replication_enforce_update_everywhere_checks= TRUE
#加入组集群
change master to master_user='repl',master_password='123' for channel 'group_replication_recovery';
start group_replication; #注意:这里如果之前是主从架构,从库需要先reset slave;否则报错
#验证节点role信息
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 7cbd3eaa-380f-11ee-8647-fa163e1057f8 | 10.0.0.159 | 3306 | ONLINE | PRIMARY | 8.0.32 | XCom |
| group_replication_applier | efb54f57-380e-11ee-9f2e-fa163e5f2954 | 10.0.0.215 | 3306 | ONLINE | SECONDARY | 8.0.32 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
2 rows in set (0.00 sec)
failover | 优点 | 缺点 | |
MHA | 1.选举有相对更全数据的slave 2.尝试连接master,从master保存binlog 3.应用差异的relay log给其他从节点 4.再将从master保存的binlog发送给其他节点数据补全 5.提升选举的slave提升为master节点 6.slave连接新的master 7.重新启动MHA | 1.支持普通主从和GTID | 1.如果master机器宕机,可能无法保存binlog 2.MHA只监控了master的状态,没有检测slave,需要手动去写脚本检测 |
MGR | MGR基于paxos协议,实现组复制,保证数据一致性,内置 故障检测和自动选主功能,只要组中一半以上节点正常,mgr 就可以正常工作,当客户端发起一个更新事务时,该事务先在本地执行, 执行完成之后就要发起对事务的提交操作。在还没有真正提交之前, 需要将产生的复制写集广播出去,复制到其它成员。如果冲突检测成功, 组内决定该事务可以提交,其它成员可以应用,否则就回滚。 | 1.使用简单,管理方便 2.数据强一致性,基于paxos协议 3.支持单主和多主,最多支持9节点 | 1.仅支持innodb引擎 2.必须GTID,且日志格式为row模式 3.必须指定显示主键,非隐式主键 |