数据库运维
记录DBA学习成长历程
当前位置：MySQL学习笔记 > MySQL > 正文

19 MySQL复制

2023-10-27 分类：MySQL 阅读(1338)
文章目录

1 简介

复制是指：将主库的DDL、DML等操作通过binlog日志，传输到复制服务器（副本），副本进行回放这些日志，从而使得从库和主库数据保持<近似>同步的工作模式。
复制架构:
（1）传统：1主1从、1主多从、级联主从、双主
（2）演变：(增强)半同步、过滤、延时、GTID、MTS(多SQL线程并发回放relaylog)
（3）新型：多源复制（5.7+支持）
（4）MGR：组复制，5.7.17+支持、8.0增强（WS:WriteSets）
复制主要应用场景：
1. 备份
2. 高可用
3. 读写分离
4. "分布式"架构
5. 迁移升级

2 Replication前提

1 2台以上的MySQL实例(同版本、同平台),具备不同的server_id,server_uuid，时间同步。
2 主库"开启binlog, 创建复制用户。
3 ”补课”： 备份主库数据，恢复到从库.
  mysqldump
  pxb
  Clone Plugin
4 告知从库复制的信息：change master to user,password, ip ,port, filename、pos(gtid)
5 让他干活 ： 启动线程 start slave---》 IO SQL
6 查看复制状态

3 主从复制构建方法

3.1 准备初始环境

# 三台虚拟机
10.0.0.51 db01
10.0.0.52 db02
10.0.0.53 db03
防火墙关闭

#清理环境：
pkill mysqld
rm -rf /data/3306/*
mkdir -p /data/3306/data /data/3306/binlog
chown -R mysql.mysql /data/*

# 准备配置文件
主库db01：
mv /etc/my.cnf /tmp
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/binlog/mysql-bin
binlog_format=row
gtid-mode=off
[mysql]
prompt=db01 [\\d]>
EOF
slave1(db02)：
mv /etc/my.cnf /tmp
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/binlog/mysql-bin
binlog_format=row
gtid-mode=off
[mysql]
prompt=db02 [\\d]>
EOF
slave2(db03)：
mv /etc/my.cnf /tmp
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/binlog/mysql-bin
binlog_format=row
gtid-mode=off

[mysql]
prompt=db03 [\\d]>
EOF

初始化数据
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --
datadir=/data/3306/data

启动数据库
/etc/init.d/mysqld start

3.2 通过mysqldump（或者PXB）备份构建传统主从

3.1.1 各节点检查server_id、server_uuid、binlog

mysql -e "select @@server_id; select @@server_uuid;show variables like 'log_bin%';"

3.1.2 主库（51）创建复制用户和远程管理用户

mysql -e "create user repl@'10.0.0.%' identified with mysql_native_password by '123';grant replication slave on *.* to repl@'10.0.0.%';"
mysql -e "create user root@'10.0.0.%' identified with mysql_native_password by
'123';grant all on *.* to root@'10.0.0.%';"
[root@db01 app]# mysql -e "select user,host,plugin from mysql.user;"
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| repl | 10.0.0.% | mysql_native_password |
| root | 10.0.0.% | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |

3.1.3 备份主库数据

[root@db02 data]# mysqldump -uroot -p123 -h 10.0.0.51 -P 3306 -A --master-data=2
--single-transaction -R -E --triggers >/tmp/full.sql
db02 [mysql]>source /tmp/full.sql
课后练习：
通过pxb方式构建主从。

3.1.4 启动主从复制

[root@db02 data]# grep "\--\ CHANGE MASTER" /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1187;
[root@db02 data]# mysql -e \
"CHANGE MASTER TO \
MASTER_HOST='10.0.0.51',\
MASTER_USER='repl', \
MASTER_PASSWORD='123', \
MASTER_PORT=3306, \
MASTER_LOG_FILE='mysql-bin.000002', \
MASTER_LOG_POS=1187, \
MASTER_CONNECT_RETRY=10;"
[root@db02 data]# mysql -e "start slave;"
[root@db02 data]# mysql -e "show slave status \G"|grep "Running:"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3.2 通过Clone-plugin搭建传统和GTID主从

3.2.1 主库操作

mysql -uroot -p123 -e "INSTALL PLUGIN clone SONAME 'mysql_clone.so';create user
test_s@'%' identified by '123';grant backup_admin on *.* to test_s@'%';"

3.2.2 从库操作

mysql -uroot -p123 -e "INSTALL PLUGIN clone SONAME 'mysql_clone.so';create user test_t@'%' identified by '123';grant clone_admin on *.* to test_t@'%';SET GLOBAL
clone_valid_donor_list='10.0.0.51:3306';"
mysql -utest_t -p123 -h10.0.0.52 -P3306 -e "CLONE INSTANCE FROM test_s@'10.0.0.51':3306 IDENTIFIED BY '123';"
[root@db03 data]# mysql -e "SELECT BINLOG_FILE, BINLOG_POSITION FROM
performance_schema.clone_status;"
[root@db03 data]# mysql -e "SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status;"
+------------------+-----------------+
| BINLOG_FILE | BINLOG_POSITION |
+------------------+-----------------+
| mysql-bin.000002 | 1714 |
+------------------+-----------------+
注： 如果GTID复制查看:
[root@db03 data]# mysql -e "SELECT @@GLOBAL.GTID_EXECUTED;"

3.2.3 启动主从复制

[root@db03 data]# mysql -e \
"CHANGE MASTER TO \
MASTER_HOST='10.0.0.51',\
MASTER_USER='repl', \
MASTER_PASSWORD='123', \
MASTER_PORT=3306, \
MASTER_LOG_FILE='mysql-bin.000002', \
MASTER_LOG_POS=1714, \
MASTER_CONNECT_RETRY=10;"
[root@db03 data]# mysql -e "start slave;"
[root@db03 data]# mysql -e "show slave status \G"|grep "Running:"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
注： GTID方式
[root@db03 data]# mysql -uroot -p123 -e \
"CHANGE MASTER TO \
MASTER_HOST='10.0.0.51',\
MASTER_USER='repl', \
MASTER_PASSWORD='123', \
MASTER_PORT=3306, \
MASTER_AUTO_POSITION=1;"
[root@db03 data]# mysql -uroot -p123 -e "start slave;"
[root@db03 data]# mysql -uroot -p123 -e "show slave status \G"|grep "Running:"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3.3 传统复制和GTID转换

3.3.1 查看当前状态

[root@db01 app]# mysql -e "select @@enforce_gtid_consistency;"
+----------------------------+
| @@enforce_gtid_consistency |
+----------------------------+
| OFF |
+----------------------------+
[root@db01 app]# mysql -e "select @@gtid_mode;"
+-------------+
| @@gtid_mode |
+-------------+
| OFF |
+-------------+

3.3.2 修改enforce_gtid_consistency

[root@db01 data]# mysql -e "set global enforce_gtid_consistency =warn;"
[root@db02 data]# mysql -e "set global enforce_gtid_consistency =warn;"
[root@db03 data]# mysql -e "set global enforce_gtid_consistency =warn;"
注意：
所有节点均先将其修改为 WARN,同时注意查看日志是否出现警告信息，生产环境想调整为GTID模式时，
需提前一段时间调整此参数，观察一段时间，确定无警告后再调整。
开启后观察数据库日志，只有在无警告的情况下才可以进行下一步的操作。

3.3.3 修改enforce_gtid_consistency = on

mysql -e "set global enforce_gtid_consistency = on;"

3.3.4 修改gtid_mode = off_permissive

mysql -e "set global gtid_mode = off_permissive;"
生成新的事务为匿名事务，同时允许复制的事务为匿名和GTID的。

3.3.5 修改gtid_mode=on_permissive

mysql -e "set global gtid_mode=on_permissive;"
生成新的事务使用GTID，同时允许复制的事务为匿名和GTID的。

3.3.6 各从节点检查剩余事务数

mysql -e "show status like 'ongoing_anonymous_transaction_count';"
表示标记为匿名正在运行的事务数量。置0后执行下一步。
mysql -e "flush logs;"

3.3.7 启用gtid_mode

mysql -e "set global gtid_mode=on;"

3.3.8 从库切换复制模式

mysql -e "stop slave; change master to master_auto_position=1;start slave;"
mysql -e "show slave status \G"

3.3.9 修改配置文件永久生效

gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

4 异步（传统、GTID）复制工作原理

4.1 传统

4.2 GTID在复制中改变

gtid格式 ：
server_uuid:seq
a. MASTER 发生事务,生成GTID,记录binlog。
b. slave 在MI中包含Retrieved_Gtid_Set，Executed_Gtid_Set
c. Master 发送 binlog到SLAVE.
d. slave 接收二进制日志，存储至relaylog，读取gtid信息并设置gtid_next值。
e. slave SQL 回放GTID事务。
   1. 检测本地binlog是否有该GTID
   2. 应用事务，并更新本地binlog(log_slave_updates)
注：
已执行的gtid信息更新至mysql.gtid_executed表中，并定期进行压缩。（reset master 会清空此表）

5 半同步、增强半同步复制工作原理

5.1 2PC 及 group commit

#1 2PC机制
客户端下发commit命令，此时进入真正的两阶段提交，两阶段提交分为prepare和commit两个阶段
begin; xxxxx commit;
## prepare阶段：
prepare分为binlog prepare和innobase redo prepare,其中binlog prepare几乎不做操
作，innobase prepare会更新事务状态。
# commit阶段：
commit阶段被分为了三个阶段，分别是flush，sync，commit。其中 flush操作会进行线程binlog
cache的文件写入，再将用户binlog cache刷新到文件；sync操作负责binlog的落盘；commit操作负责
更新server层的最大事务提交数量(和并行复制相关),然后innobase 再次更新事务状态。提交结束。
#2 group commit流程：
## FLUSH 阶段：
1) 持有Lock_log mutex [leader持有，follower等待]
2) 获取队列中的一组binlog(队列中的所有事务)
3) 将binlog buffer到OS cache
4) 通知dump线程dump binlog
## SYNC阶段
1) 释放Lock_log mutex，持有Lock_sync mutex[leader持有，follower等待]
2) 将一组binlog 落盘(sync动作，最耗时，假设sync_binlog为1)
## COMMIT阶段
1) 释放Lock_sync mutex，持有Lock_commit mutex[leader持有，follower等待]
2) 遍历队列中的事务，逐一进行innodb commit
3) 释放Lock_commit mutex
4) 唤醒队列中等待的线程
配套参数：
binlog_group_commit_sync_no_delay_count=N
binlog_group_commit_sync_delay=M
begin;
a
b
c
commit; ----> 拆解成2个步骤： prepare commit

5.2 半同步复制(after_commit)

AFTER_COMMIT: The master writes each transaction to its binary log and the
slave, syncs the binary log, and commits the transaction to the storage engine.
The master waits for slave acknowledgment of transaction receipt after the
commit. Upon receiving acknowledgment, the master returns a result to the
client, which then can proceed.

5.3 增强半同步(after_sync)

AFTER_SYNC (the default): The master writes each transaction to its binary log
and the slave, and syncs the binary log to disk. The master waits for slave
acknowledgment of transaction receipt after the sync. Upon receiving
acknowledgment, the master commits the transaction to the storage engine and
returns a result to the client, which then can proceed.

6 主从复制故障监控、原因分析、处理方案

6.1 主库监控

[root@db01 data]# mysql -e "show processlist" |grep "Dump"
7 repl db01:34640 NULL Binlog Dump 5355 Master has sent all binlog to slave; waiting for more updates NULL
9 repl db01:34642 NULL Binlog Dump 687 Master has sent all binlog to slave; waiting for more updates NULL
[root@db01 data]# mysql -e "show slave hosts;"
+-----------+----------------+------+-----------+-------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID|
+-----------+----------------+------+-----------+---------------------+
| 53 | 10.0.0.53:3306 | 3306 | 51 | af1c4368-c31a-11ea-9078-000c29a5e781 |
| 52 | 10.0.0.52:3306 | 3306 | 51 | ac703788-c31a-11ea-a322-000c29f2d9fe |
+-----------+----------------+------+-----------+-----------------------------+

6.2 从库监控

6.2.1 主库连接信息、binlog位置信息(mi)

[root@db01 data]# mysql -e "show slave status \G"
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 341

6.2.2 从库中relay-log的回放信息 (ri)

Relay_Log_File: db02-relay-bin.000004
Relay_Log_Pos: 458
Relay_Master_Log_File: mysql-bin.000006
Exec_Master_Log_Pos: 341

6.2.3 线程监控信息：主要用来排查主从故障

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

6.2.4 过滤复制相关信息

Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:

6.2.5 落后于主库的秒数(主从延时时间)

Seconds_Behind_Master: 0

6.2.6 延时从库状态信息

SQL_Delay: 0
SQL_Remaining_Delay: NULL

6.2.7 GTID复制信息

Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0

6.2.8 一堆pos 功能（5.7版本以前比较关注）

（1） IO 已经获取到的主库Binlog的位置点（master.info）
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 444
作用： 新一轮复制的起点。
（2） SQL 回放到的relaylog位置点。(relay-log.info)
Relay_Log_File: db01-relay-bin.000006
Relay_Log_Pos: 320
作用： SQL回放的起点
（3）SQL回放的realylog位置点，对应的主库binlog的位置点(relay-log.info)
Relay_Master_Log_File: mysql-bin.000001
Exec_Master_Log_Pos: 600
作用： 计算主从复制延时日志量。

6.3 主从故障分析及处理

6.3.1 如何监控

[root@db01 data]# mysql -S /tmp/mysql3308.sock -e "show slave status \G"
Slave_IO_Running: Yes # IO线程工作状态： YES、NO、Connecting
Slave_SQL_Running: Yes # SQL线程工作状态：YES、NO
Last_IO_Errno: 0 # IO故障代码：2003,1045,1040,1593,1236...
Last_IO_Error: # IO线程报错详细信息
Last_SQL_Errno: 0 # SQL故障代码：1008，1007，1032，1062..
Last_SQL_Error: # IO线程报错详细信息

6.3.2 IO线程故障

连接主库失败

user,password,IP,Port,plugin
主库无法连接：网络、宕机、防护墙、最大连接数上限
故障模拟：
(1)主库宕机
systemctl stop mysqld3307
show slave status\G
还原:
systemctl start mysqld3307
mysql -S /tmp/mysql3308.sock -e "start slave;"
(2) 模拟用户密码错误
mysql -e "alter user repl@'10.0.0.%' identified by '11212'"
mysql -e "start slave; show slave status\G "
还原：
mysql -e "alter user repl@'10.0.0.%' identified by '123'"
mysql -e "start slave; show slave status\G "
(3) 连接数上限
mysql -e " set global max_connections=2;"
mysql
mysql
mysql -e "stop slave; start slave; show slave status\G "
还原：
mysql -e " set global max_connections=200;"
mysql -e "stop slave; start slave; show slave status\G "
通用排查方法：
[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P 3300
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.51' (111)
[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.52 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.52' (113)
[root@db01 ~]# mysql -urepla -p123 -h 10.0.0.51 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repla'@'db01' (using password: YES)
[root@db01 ~]# mysql -urepl -p1123 -h 10.0.0.51 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl'@'db01' (using password: YES)

主从信息重复

主从的server_id、Server_uuid、相同。
版本不兼容（低到高可以、高到低不行、跨太多版本也不行）。
# server_id 故障重现：
[root@db01 ~]# mysql -S /tmp/mysql3307.sock
mysql> set global server_id=8;
[root@db01 ~]# mysql -S /tmp/mysql3307.sock
mysql> select @@server_id;
[root@db01 ~]# mysql -S /tmp/mysql3308.sock
mysql> stop slave;start slave;show slave status;
回退：
[root@db01 ~]# mysql -S /tmp/mysql3307.sock
mysql> set global server_id=7;
[root@db01 ~]# mysql -S /tmp/mysql3307.sock
mysql> select @@server_id;
[root@db01 ~]# mysql -S /tmp/mysql3308.sock
mysql> start slave;show slave status;

日志信息错误丢失

# 主库日志损坏故障重现：
[root@db01 ~]# mysql -S /tmp/mysql3307.sock
mysql> reset master;
[root@db01 ~]# mysql -S /tmp/mysql3308.sock
mysql> start slave;show slave status;
测试环境处理方法（主从的数据当前是一致的）：
[root@db01 ~]# mysql -S /tmp/mysql3308.sock
# 将所有线程停止。
mysql> stop slave;
# 将从库复制信息清空（master.info,relay-log.info清空，show slave status看不到信息了）
mysql> reset slave all;
mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
mysql> start slave;
生产中需要额外考虑什么情景？
需要重构主从：
1 备份恢复.
2 change master to ; start slave

6.3.3 SQL线程故障

SQL线程主要工作： 回放relaylog中的日志事件，可以理解为后台执行SQL语句。

realy-log 损坏

处理方法： 重构。
方法1： 备份主库+change master to + start slave;
方法2： 找到问题点+ change master + start slave;
思路： 如何找到问题位置点。
1. 找到SQL已经回放到什么位置了。
SQL回放的realylog位置点，对应的主库binlog的位置点(relay-log.info)
Relay_Log_File: db01-relay-bin.000006
Relay_Log_Pos: 320
----》
2. 找到主库相应位置点：
Relay_Master_Log_File: mysql-bin.000001
Exec_Master_Log_Pos: 600
3. change master to mysql-bin.000001 600

回放日志出问题

（1）主从节点配置不一样： 平台、版本、参数、SQL_MODE调整成一致。
（2）修改的对象不存在（库、表、用户）
    从库被写入了。 双主架构。异步方式主从，导致数据不一致。
（3）创建的对象已存在（库、表、用户、约束冲突）
    从库被写入了。 双主架构。
方法一：部分场景可以使用，只要保证数据以主库为准即可使用。
stop slave;
set global sql_slave_skip_counter = 1;
#将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;
方法二：不推荐
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突
可以配合以下工具：
建议业务低估期，停业务，使用校验工具，手工同步数据，然后再跳过错误。
pt-table-checksum pt-table-sync

GTID 处理错误方式

查看监控信息:
Last_SQL_Error: Error 'Can't create database 'oldboy'; database exists' on
query. Default database: 'oldboy'. Query: 'create database oldboy'
Retrieved_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-3
Executed_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-2,
7ca4a2b7-4aae-11e9-859d-000c298720f6:1
注入空事物的方法：
stop slave;
set gtid_next='99279e1e-61b7-11e9-a9fc-000c2928f5dd:3';
begin;commit;
set gtid_next='AUTOMATIC';
这里的xxxxx:N 也就是你的slave sql thread报错的GTID，或者说是你想要跳过的GTID。
最好的解决方案：重新构建主从环境
d. 总结： SQL线程故障规避方法
1. 从库只读 ，读写分离中间件。
2. 不使用双主结构。PXC、MGR替代。
3. 半同步、增强半同步复制等，或者PXC、MGR替代。
4. 使用pt相关工具校验主从数据，并同步。

6.4 主从延时分析及处理

6.4.1 监控方法

方法一： 有没有延时
Seconds_Behind_Master: 0
get master clock===> ts ---> diff_ts=?
slave sql apply= s_ts?
master binlog event= m_ts?
s_ts-m_ts-diff_ts===> Seconds_Behind_Master
0 -10 -10 ====> 0
5 -10 > -5 ====> 0
导致显示不准确的情况：
1. 时间被修改
2. 如果长时间事务不提交，瞬间变大 ，瞬间归零的现象。
3. MTS回放也会导致Seconds_Behind_Master
方法二： 定位卡在那里了。
主库：
mysql> show master status ;
+------------------+----------+--------------+------------------+----------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------+
| mysql-bin.000004 | 151847 | | ||
+------------------+----------+--------------+------------------+----------+
1 row in set (0.00 sec)
从库：
[root@db01 data]# mysql -e "show slave status \G"|grep "Master_Log"
已经拿到的主库日志量(master.info)：判断传输有没有延时
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 151847
已经执行的主库日志(relay-log.info): 判断回放有没有延时
Relay_Master_Log_File: mysql-bin.000004
Exec_Master_Log_Pos: 141847
计算主从复制延时日志量。
还可以使用GTID判定方法。

方法三：
pt-heartbeat

6.4.2 导致延时的主要原因

主库方面

（1） binlog记录不及时。
mysql> select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
| 1 |
+---------------+
参数说明：
1 ： 每次事务提交都立即刷新binlog到磁盘。
0 ： 由操作系统决定，什么刷新磁盘。
（2） DUMP线程串行工作。
大事务、并发事务高、DDL
解决办法：
5.6版本加入GTID复制模式，但手工配置。DUMP在传输日志时可以并发。
5.7版本GTID做了增强，不手工开启也自动维护匿名的GTID信息。
（3）怎么判断是主库导致的延时？
主库：
mysql> show master status ;
从库：
mysql -S /tmp/mysql3308.sock -uroot -p123 -e "show slave status \G"|grep "Master_Log"

从库方面

# IO线程：
从库IO比较慢。relay 落地慢。可以将realy放到 SSD
# SQL 线程： 串行回放。
主库可以并行事务，从库SQL线程串行回放。
所以：并发事务高、大事务、DDL
解决方法：
5.6 版本：开启GTID后，可以多SQL线程，只能针对不同的库的事务进行并行SQL恢复。
5.7 版本：做了增强，基于逻辑时钟的并行回放。MTS。
LAST_COMMIT(BINLOG_GROUP_COMMIT)
SEQ_NUM.
5.7 的从库并发配置方法。
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
master_info_repository=TABLE
relay_log_info_repository=TABLE

处理建议

MTS+GC
RC
锁等待、死锁
减少大事务
DDL ---> gh-ost
网络优质

7 多SQL线程复制MTS和 MGR Writesets

7.1 5.7+ 版本MTS

为什么会有 MTS和 WS ？
主库执行1000个事务，1000有GC机制，一次性投递到从库。
从库SQL线程默认就1个。只能串行回放relay-log。
5.6 版本
加入多SQL线程回放机制。由一个管理线程，自动分配workers.
按照database为基准，进行分配workers.
MySQL 5.7 :
利用Group Commit(LOGICAL_CLOCK),检测事务之间是否冲突。
引入了新的并行回放类型， 由参数 slave_parallel_type决定，默认值DATABASE将会采用5.6版本中的SCHEMA级别的并行回放，设置为LOGICAL_CLOCK 则会采用基于Group Commit的并行回放，同一个Group内的事务将会在Slave上并行回放。last_commit=0 1 2 3 4 6
为了标记事务所属的组，MySQL 5.7 版本在产生 Binlog日志时会有两个特殊的值记录在BinlogEvent中， last_committed 和 sequence_number , 其中 last_committed 指的是该事务提交时，上一个事务提交的编号，sequence_number 是事务提交的序列号，在一个Binlog文件内单调递增。如果两个事务的 last_committed 值一致，这两个事务就是在一个组内提交的。
mysqlbinlog mysql-bin.0000006 | grep last_committed
server_id 51 XXX GTID last_committed=0 sequence_numer=1
server_id 51 XXX GTID last_committed=0 sequence_numer=2
server_id 51 XXX GTID last_committed=0 sequence_numer=3
server_id 51 XXX GTID last_committed=0 sequence_numer=4
注意： 同一个Session中的事件不能并发回放
session1:
TX1
create table t1;
TX2
begin;
insert into t1 values
commit;
TX3
create table t2 ;

7.2 5.7.22 / 8.0 版本 MGR WS

7.2.1 核心参数

binlog_transaction_dependency_tracking = WRITESET #COMMIT_ORDER
transaction_write_set_extraction = XXHASH64

7.2.2 参数介绍

MySQL 中引入参数 binlog_transaction_depandency_tracking 用于控制如何决定事务的依赖关系。该值有三个选项：默认的 COMMIT_ORDERE 表示继续使用5.7中的基于组提交的方式决定事务的依赖关系；WRITESET 表示使用写集合来决定事务的依赖关系；还有一个选项 WRITESET_SESSION 表示使用WriteSet 来决定事务的依赖关系，但是同一个Session内的事务不会有相同的 last_committed 值。

7.2.3 WS实现原理

在代码实现上，MySQL采用一个 vector<uint64> 的变量存储已经提交的事务的HASH值，所有已经提交的事务的所修改的主键和非空的 UniqueKey 的值经过HASH后与该vector中的值对比，以判断当前提交的事务是否与已经提交的事务更新了同一行，并以此确定依赖关系。该向量的大小由参数binlog_transaction_dependency_history_size 控制，取值范围为 1-1000000 ，初始默认值为25000。 同时有参数 transaction_write_set_extraction 控制检测事务依赖关系时采用的HASH算法，有三个取值 OFF| XXHASH64 | MURMUR32， 如果binlog_transaction_depandency_tracking 取值为 WRITESET 或 WRITESET_SESSION, 那么该值取值不能为OFF，且不能变更。

7.2.4  WriteSet 依赖检测条件

WriteSet 是通过检测两个事务是否更新了相同的记录来判断事务能否并行回放的，因此需要在运行时保存已经提交的事务信息以记录历史事务更新了哪些行。记录历史事务的参数为binlog_transaction_dependency_history_size. 该值越大可以记录更多的已经提交的事务信息，不过需要注意的是，这个值并非指事务大小，而是指追踪的事务更新信息的数量。在开启了 WRITESET 或
WRITESET_SESSION 后，MySQL 按以下的方式标识并记录事务的更新。
insert INTO TEST.T1 VALUES(1,'zhangsan');
insert INTO TEST.T1 VALUES(1,'ls');
如果事务当前更新的行有主键（Primary Key），则将 HASH(DB名，TABLE名，KEY名称，KEY_VALUE1, KEY_VALUE2,.....) 加入到当前事务的 vector write_set 中
如果事务当前更新的行有非空的唯一键 （Unique Key Not NULL）， 同样将 HASH(DB名, TABLE名，KEY名, KEY_VALUE1, ....)加入到当前事务的 write_set 中
如果事务更新的行有外键约束( FOREIGN KEY )且不为空，则将该外键信息与VALUE的HASH加到当前事务的 write_set中
如果事务当前更新的表的主键是其他某个表的外键，并设置当前事务 has_related_foreign_key =true
如果事务更新了某一行且没有任何数据被加入到 write_set 中，则标记当前事务has_missing_key = true
在执行冲突检测的时候，先会检查 has_related_foreign_key 和 has_missing_key ， 如果为true， 则退到 COMMIT_ORDER 模式。否则，会依照事务的 write_set 中的HASH值与已提交的事务的write_set 进行比对，如果没有冲突，则当前事务与最后一个已提交的事务共享相同的 last_commited,否则将从全局已提交的 write_set 中删除那个冲突的事务之前提交的所有write_set，并退化到COMMIT_ORDER 计算last_committed 。 每次计算完事务的 last_committed 值以后，检测当前全局已提交事务的 write_set 是否已经超过了 binlog_transaction_dependency_history_size 设置
的值，如果超过，则清空已提交事务的全局write_set。
从检测条件上看，该特性依赖于主键和唯一索引，如果事务涉及的表中没有主键且没有唯一非空索引，那么将无法从此特性中获得性能的提升。除此之外，还需要将 Binlog 格式设置为 Row 格式。

8 延时从库应用

8.1 传统复制的痛点

只能帮我们解决物理损坏，很难解决逻辑损坏。
可以用备份解决，但是数据量很大，通过备份方式花费很长时间。

8.2 配置方法

mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;
mysql>start slave;
mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL

8.3 恢复思路

主库发生了逻辑损坏（DROP，truncate）时，可以使用延时从库快速恢复数据。
    2小时延时
    10：00 做的drop database A;
1. 及时监控故障： 主库 10:05发现故障，从库此时8:05数据状态
2. 立即将从库的SQL线程关闭。 需要对A业务挂维护页。
3. 停止所有线程。
4. 在延时从。恢复A库数据
方案一：针对无GTID 手工模拟SQL线程工作，直到drop之前位置点。
SQL线程上次执行到的位置------》drop之前
relay.info ----> 分析drop位置点 ---》 截取relaylog日志----》 source

8.4 故障模拟及恢复

故障模拟：
create database delaydb charset utf8mb4;
use delaydb;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
drop database delaydb;
截取日志：
起点: SQL上次执行到的位置点，
    Relay_Log_File: db02-relay-bin.000002
    Relay_Log_Pos: 371
终点： drop 之前
| db02-relay-bin.000002 | 1064 | Gtid | 51 | 1006 | SET@@SESSION.GTID_NEXT= '188be1ed-c84c-11ea-98e7-000c29ea9d83:1469' |
| db02-relay-bin.000002 | 1141 | Query | 51 | 1119 | drop database delaydb /* xid=5339 */
[root@db01 tmp]# mysqlbinlog --start-position=371 --stop-position=1064 /data/3306/data/db02-relay-bin.000002 >/tmp/bin.sql
mysql> reset slave all;
mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql;
mysql> set sql_log_bin=1;
方案二： GTID
db02 [(none)]>stop slave;
db02 [(none)]>show relaylog events in 'db02-relay-bin.000002';
SET @@SESSION.GTID_NEXT= '188be1ed-c84c-11ea-98e7-000c29ea9d83:4'
CHANGE MASTER TO MASTER_DELAY = 0;
db02 [delaydb]>START SLAVE UNTIL SQL_BEFORE_GTIDS = "188be1ed-c84c-11ea-98e7-000c29ea9d83:4";

9 过滤复制

# 主库实现
binlog_do_db 白名单
binlog_ignore_db 黑名单
说明： 是否记录binlog日志来控制过滤。
# 从库实现 ******
实现方法：
IO线程不做限制。
SQL线程回放时，选择性回放。
参数：
replicate_do_db=world
replicate_do_db=oldboy
replicate_ignore_db=
replicate_do_table=world.city
replicate_ignore_table=
replicate_wild_do_table=world.t*
replicate_wild_ignore_table=
配置方法：
方法一：
修改配置文件并重启
vim /etc/my.cnf
replicate_do_db=oldguo
replicate_do_db=oldboy
方法二：
STOP SLAVE SQL_THREAD;
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (oldguo, oldboy);
START SLAVE SQL_THREAD;

10 多元复制（MSR）

10.1 架构

10.2 主机角色

10.3 配置过程

10.3.1 GTID环境准备

(1) 清理环境
pkill mysqld
rm -rf /data/3306/*
\mv /etc/my.cnf /tmp

(2) 创建需要的目录
mkdir -p /data/3306/data /data/3306/binlog
chown -R mysql.mysql /data

(3) 准备配置文件
# db01
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
server_id=6
socket=/tmp/mysql.sock
log_bin=/data/3306/binlog/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
master_info_repository=TABLE
relay_log_info_repository=TABLE
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db01 [\\d]>
socket=/tmp/mysql.sock
EOF

# db02
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
server_id=7
socket=/tmp/mysql.sock
log_bin=/data/3306/binlog/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
master_info_repository=TABLE
relay_log_info_repository=TABLE
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db02 [\\d]>
socket=/tmp/mysql.sock
EOF

# db03
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
server_id=8
socket=/tmp/mysql.sock
log_bin=/data/3306/binlog/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
master_info_repository=TABLE
relay_log_info_repository=TABLE
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db03 [\\d]>
socket=/tmp/mysql.sock
EOF

(4) 初始化数据
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --
datadir=/data/3306/data
(5) 启动数据库
/etc/init.d/mysqld start
(6) 构建主从
# 1. 创建复制用户(主节点)
set sql_log_bin=0;
create user repl@'10.0.0.%' identified with mysql_native_password by '123';
grant replication slave on *.* to repl@'10.0.0.%' ;
set sql_log_bin=1;

10.3.2 配置多源复制

CHANGE MASTER TO MASTER_HOST='10.0.0.51',MASTER_USER='repl',
MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1 FOR CHANNEL 'Master_1';
CHANGE MASTER TO MASTER_HOST='10.0.0.52',MASTER_USER='repl',
MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1 FOR CHANNEL 'Master_2';
start slave for CHANNEL 'Master_1';
start slave for CHANNEL 'Master_2';

10.3.3 多源复制监控

db03 [(none)]>SHOW SLAVE STATUS FOR CHANNEL 'Master_1'\G
db03 [(none)]>SHOW SLAVE STATUS FOR CHANNEL 'Master_2'\G
db03 [performance_schema]>select * from
performance_schema.replication_connection_configuration\G
SELECT * FROM performance_schema.replication_connection_status WHERE
CHANNEL_NAME='master_1'\G
db03 [performance_schema]>select * from
performance_schema.replication_applier_status_by_worker;

10.3.4 多源复制配置过滤

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.%') FOR CHANNEL "master_1";
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db2.%') FOR CHANNEL "master_2";

11 MGR组复制引入

11.1 背景

5.7.17+以后，出现的组复制技术。MySQL Group Replication。
为了复制环境中，数据一致性考虑、多写的场景研发的技术。

11.2 架构原理

在2N+1个节点组成的单主模式组复制集群中，主库上一个事务提交时，会将事务修改记录相关的信息和事务产生的BINLOG事件打包生成一个写集(WRITE SET)，将写集发送给所有节点，并通过至少N个节点投票通过才能事务提交成功。
在事务执行期间，会将：
1、事务操作生成的map event/query event/dml event等写入BINLOG CACHE中(内存)
2、将Write Set写入到Rpl_transaction_write_set_ctx中(内存)
在事务提交时，具体在MYSQL_BIN_LOG::prepare之后，但是在MYSQL_BIN_LOG::ordered_commit之前，即事务相关的BINLOG Event还在BINLOG CACHE没有写入到BINLOG FILE前，将BINLOG CACHE中和Rpl_transaction_write_set_ctx中的数据进行处理并写入到transaction_msg中，由gcs_module负责发送transaction_msg到各个节点，等待各节点进行事务认证。

由于transaction_msg中包含BINLOG信息，并在事务认证期间发送给MGR各节点，因此无需等待主节点的BINLOG落盘后再发送给备用节点。
每个MGR群集中的节点上，都存在IO线程和SQL线程，IO线程会解析transaction_msg获取到BINLOGEVENT并保存到RELAY LOG中，再由SQL线程执行重放到辅助节点上。

总结：
single-primary:
begin;
update t1 set name=‘zs’ where id=10;
commit;

1. 主库发生新的事务，事务执行期间，会将事务binlog 刷到binlog cache。
2. 生成write_set ,包含binlog日志+表、库hash值。写入Rpl_transaction_write_set_ctx。
3. MYSQL_BIN_LOG::prepare之后，在MYSQL_BIN_LOG::ordered_commit之前
   通过gcs_module将所有的Rpl_transaction_write_set_ctx，以transaction_msg传输给各个节点。
4. 通过certify验证（Paxos）,通过投票机制，判断此次事务是否满足半数以上节点通过（不冲突）。
5. 主库binlog flush && sync disk
6. 此时，各个从库开始回放relaylog。

11.3 MGR构建过程

11.3.1 清理环境

pkill mysqld
rm -rf /data/3306/*
\mv /etc/my.cnf /etc/my.cnf.sin

11.3.2 创建目录

mkdir -p /data/3306/data
mkdir -p /data/3306/binlog
chown -R mysql.mysql /data/*

11.3.3 配置文件

[root@db01 data]# cat /proc/sys/kernel/random/uuid
ca842376-1c50-42ac-bb57-a5adc7da7a12

# db01：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql/
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
skip_name_resolve
master_info_repository=TABLE
relay_log_info_repository=TABLE
report_host=10.0.0.51
report_port=3306
socket=/tmp/mysql.sock
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ca842376-1c50-42ac-bb57-a5adc7da7a12"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "10.0.0.51:33061"
loose-group_replication_group_seeds=
"10.0.0.51:33061,10.0.0.52:33062,10.0.0.53:33063"
loose-group_replication_bootstrap_group=OFF
[mysql]
prompt=db01 [\\d]>
EOF

#db02：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql/
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
skip_name_resolve
master_info_repository=TABLE
relay_log_info_repository=TABLE
report_host=10.0.0.52
report_port=3306
socket=/tmp/mysql.sock
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ca842376-1c50-42ac-bb57-a5adc7da7a12"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "10.0.0.52:33062"
loose-group_replication_group_seeds=
"10.0.0.51:33061,10.0.0.52:33062,10.0.0.53:33063"
loose-group_replication_bootstrap_group=OFF
[mysql]
prompt=db02 [\\d]>
EOF

# db03 ：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql/
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
skip_name_resolve
master_info_repository=TABLE
relay_log_info_repository=TABLE
report_host=10.0.0.53
report_port=3306
socket=/tmp/mysql.sock
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ca842376-1c50-42ac-bb57-a5adc7da7a12"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "10.0.0.53:33063"
loose-group_replication_group_seeds=
"10.0.0.51:33061,10.0.0.52:33062,10.0.0.53:33063"
loose-group_replication_bootstrap_group=OFF
[mysql]
prompt=db03 [\\d]>
EOF

11.3.4 初始化数据

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data

11.3.5 启动数据库

/etc/init.d/mysqld start

11.3.6 组复制部分，配置文件介绍

// group_replication变量使用的loose-前缀是指示Server启用时尚未加载复制插件也将继续启动
##指示Server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列
transaction_write_set_extraction = XXHASH64
##表示将加入或者创建的复制组命名为01e5fb97-be64-41f7-bafd-3afc7a6ab555
##可自定义(通过cat /proc/sys/kernel/random/uuid)
loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555"
##设置为Server启动时不自动启动组复制
loose-group_replication_start_on_boot=off
##绑定本地的192.168.29.128及33061端口接受其他组成员的连接，IP地址必须为其他组成员可正常访问
loose-group_replication_local_address="192.168.29.128:33061"
##本行为告诉服务器当服务器加入组时，应当连接到
##这些种子服务器进行配置。本设置可以不是全部的组成员服务地址。
loosegroup_replication_group_seeds="192.168.29.128:33061,192.168.29.128:33062,192.168.29.128:33063"
##配置是否自动引导组
loose-group_replication_bootstrap_group = off
##配置白名单，默认情况下只允许192.168.29.128连接到复制组，如果是其他IP则需要配置。
loose-group_replication_ip_whitelist=”10.30.0.0/16,10.31.0..0/16,10.27.0.0/16″

11.3.7 设置本地root用户密码和密码插件（所有节点）

mysql -S /tmp/mysql.sock -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH
mysql_native_password BY '123';"

11.3.8 安装MGR插件（所有节点）

mysql -uroot -p123 -S /tmp/mysql.sock -e "INSTALL PLUGIN group_replication
SONAME 'group_replication.so';"

11.3.9 设置复制账号（所有节点）

[root@db03 83306]# mysql -uroot -p123 -S /tmp/mysql.sock
SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY '123';
CREATE USER repl@'localhost' IDENTIFIED BY '123';
CREATE USER repl@'127.0.0.1' IDENTIFIED BY '123';
GRANT REPLICATION SLAVE,replication client ON *.* TO repl@'%';
grant replication slave,replication client on *.* to repl@'localhost' ;
grant replication slave,replication client on *.* to repl@'127.0.0.1' ;
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

11.3.10 启动MGR单主模式

# 启动MGR，在主库(10.0.0.51)上执行
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;
SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------
-+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST| MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------
-+-------------+--------------+-------------+----------------+
| group_replication_applier | 5e46b750-c834-11ea-ab1a-000c29ea9d83 | 10.0.0.51| 65535 | ONLINE | PRIMARY | 8.0.20 |
+---------------------------+--------------------------------------+------------
-+-------------+--------------+-------------+----------------+
# 其他节点加入MGR，在从库上执行
reset master;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123' FOR CHANNEL
'group_replication_recovery';
START GROUP_REPLICATION;
# 查看MGR组信息
db03 [(none)]>SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------
-+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST| MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------
-+-------------+--------------+-------------+----------------+
| group_replication_applier | 60aec22c-c83d-11ea-9560-000c29a5e781 | 10.0.0.53
| 65535 | ONLINE | SECONDARY | 8.0.20 |
| group_replication_applier | 627fde7d-c83d-11ea-9a66-000c29f2d9fe | 10.0.0.52
| 3306 | ONLINE | SECONDARY | 8.0.20 |
| group_replication_applier | 7c612d78-c83d-11ea-b17b-000c29ea9d83 | 10.0.0.51
| 3306 | ONLINE | PRIMARY | 8.0.20 |
+---------------------------+--------------------------------------+------------
-+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
// 可以看到，3个节点状态为online，并且主节点为192.168.56.101，只有主节点可以写入，其他节点只
读，MGR单主模式搭建成功。
如果需要重置，那么需要执行如下命令：
STOP GROUP_REPLICATION;
reset master;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123' FOR CHANNEL
'group_replication_recovery';
start GROUP_REPLICATION;

11.3.11 切换到多主模式

MGR切换模式需要重新启动组复制，因些需要在所有节点上先关闭组复制，设置group_replication_single_primary_mode=OFF 等参数，再启动组复制。
1、该模式启用需设置两个参数
group_replication_single_primary_mode=0 #这个参数很好理解，就是关闭单master模式
group_replication_enforce_update_everywhere_checks=1 #这个参数设置多主模式下各个节点
严格一致性检查
================
db02 [(none)]>stop GROUP_REPLICATION;
db02 [(none)]>set global group_replication_single_primary_mode=OFF;
db02 [(none)]>set global group_replication_enforce_update_everywhere_checks=1;
db03 [(none)]>stop GROUP_REPLICATION;
db03 [(none)]>set global group_replication_single_primary_mode=OFF;
db03 [(none)]>set global group_replication_enforce_update_everywhere_checks=1;
select @@group_replication_single_primary_mode,@@group_replication_enforce_update_every where_checks;
===============
2、 默认启动的都是单master模式，其他节点都设置了read_only、super_read_only这两个参数，需要修改这两个配置
===========
db03 [(none)]>set global read_only=0;
db03 [(none)]>set global super_read_only=0;
===========
3、 完成上面的配置后就可以执行多点写入了，多点写入会存在冲突检查，这耗损性能挺大的，官方建议采用
网络分区功能，在程序端把相同的业务定位到同一节点，尽量减少冲突发生几率。
# 停止组复制(所有节点执行)：
stop group_replication;
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;
# 随便选择某个节点执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
# 其他节点执行
START GROUP_REPLICATION;
# 查看组信息，所有节点的 MEMBER_ROLE 都为 PRIMARY，
SELECT * FROM performance_schema.replication_group_members;

11.3.12 切回单主模式

# 所有节点执行
stop group_replication;
set global group_replication_enforce_update_everywhere_checks=OFF;
set global group_replication_single_primary_mode=ON;
# 主节点执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
# 从节点执行
START GROUP_REPLICATION;
# 查看MGR组信息，SELECT * FROM performance_schema.replication_group_members;
补充：
SELECT group_replication_set_as_primary('7e0649b3-ded7-11eb-9dd0-005056bef803');
SELECT group_replication_switch_to_single_primary_mode();
SELECT group_replication_switch_to_multi_primary_mode();

11.4 MGR运维

11.4.1 MGR监控

SELECT * FROM performance_schema.replication_group_members;

11.4.2 MGR故障处理

## 宕掉主节点测试。
db02 [(none)]>SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------
-+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST| MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------
-+-------------+--------------+-------------+----------------+
| group_replication_applier | 60aec22c-c83d-11ea-9560-000c29a5e781 | 10.0.0.53| 65535 | ONLINE | PRIMARY | 8.0.20 |
| group_replication_applier | 627fde7d-c83d-11ea-9a66-000c29f2d9fe | 10.0.0.52| 3306 | ONLINE | SECONDARY | 8.0.20 |
+---------------------------+--------------------------------------+------------
-+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

## 启动主节点
db02 [(none)]>SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------
-+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST| MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------
-+-------------+--------------+-------------+----------------+
| group_replication_applier | 60aec22c-c83d-11ea-9560-000c29a5e781 | 10.0.0.53| 65535 | ONLINE | PRIMARY | 8.0.20 |
| group_replication_applier | 627fde7d-c83d-11ea-9a66-000c29f2d9fe | 10.0.0.52| 3306 | ONLINE | SECONDARY | 8.0.20 |
| group_replication_applier | 7c612d78-c83d-11ea-b17b-000c29ea9d83 | 10.0.0.51| 3306 | ONLINE | SECONDARY | 8.0.20 |
+---------------------------+--------------------------------------+------------
-+-------------+--------------+-------------+-----

11.4.3 MGR添加删除节点

# 通过clone plugin 添加新的节点
1. 初始化新节点
pkill mysqld
rm -rf /data/3306/*
\mv /etc/my.cnf /etc/my.cnf.sin
mkdir -p /data/3306/data
mkdir -p /data/3306/binlog
chown -R mysql.mysql /data/*

cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql/
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
port=3306
skip_name_resolve
master_info_repository=TABLE
relay_log_info_repository=TABLE
report_host=10.0.0.51
report_port=3306
socket=/tmp/mysql.sock
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ca842376-1c50-42ac-bb57-a5adc7da7a12"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address= "10.0.0.54:33061"
loose-group_replication_group_seeds=
"10.0.0.51:33061,10.0.0.52:33062,10.0.0.53:33063"
loose-group_replication_bootstrap_group=OFF
[mysql]
prompt=db01 [\\d]>
EOF

2. 初始化数据
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --
datadir=/data/3306/data

3. 启动数据库
/etc/init.d/mysqld start

4. 设置本地root用户密码和密码插件（所有节点）
mysql -S /tmp/mysql.sock -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH
mysql_native_password BY '123';"

5. 安装MGR插件（所有节点）
mysql -uroot -p123 -S /tmp/mysql.sock -e "INSTALL PLUGIN group_replication
SONAME 'group_replication.so';"

6. clone plugin 克隆主库数据
# 主库：
[root@db01 ~]# mysql -uroot -p123 -S /tmp/mysql.sock -e "INSTALL PLUGIN clone SONAME 'mysql_clone.so';create user test@'%' identified by '123';grant backup_admin on *.* to test@'%';"
# 从库：
mysql -uroot -p123 -S /tmp/mysql.sock -e "INSTALL PLUGIN clone SONAME 'mysql_clone.so';create user test1@'%' identified by '123';grant clone_admin on *.* to test1@'%';SET GLOBAL clone_valid_donor_list='10.0.0.52:3306';"
mysql -utest1 -p123 -h10.0.0.51 -P3306 -e "CLONE INSTANCE FROM test@'10.0.0.52':3306 IDENTIFIED BY '123';"
# 加入集群
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;
# 查看MGR组信息
SELECT * FROM performance_schema.replication_group_members;

11.4.4 MGR的限制

1. 仅支持innodb存储引擎
MGR集群中，只支持innodb存储引擎，能够创建非innodb引擎的表，但是无法写入数据，向非innodb表写数据直接报错。
2. 表必须有主键，或者非Null的唯一键
MGR集群中，只支持innodb引擎的表，并且该表必须有显式的主键，或者非Null的唯一键，否则即使能够创建表，也无法向表中写入数据。
3. 网络限制
MGR 组通信引擎目前仅支持IPv4网络，并且对节点间的网络性能要求较高，低延迟、高带宽的网络是部署MGR集群的基础。
4. MGR忽略表锁和命名锁，在MGR中lock tables、unlock tables、get_lock、release_lock等这些表锁和命名锁将被忽略。
5. MGR多主模式中，默认不支持 SERIALIZABLE 隔离级别。建议使用RC。
6. 多主模式下，对同一个对象进行并发的有冲突的ddl和dml操作导致这种冲突在部分成员节点中无法检测到，最终可能导致数据不一致。
7. 多主模式下，不支持级联约束的外键，可能造成有冲突的操作无法检测。
8. 不支持超大事务。
9. 多主模式下可能导致死锁，比如select ...for update在不同节点执行，由于多节点锁无法共享，很容易导致死锁。
10.不支持复制过滤，如果有节点设置了复制过滤，将影响节点间决议的达成。
11. MGR最多支持9个节点，大于9个节点，将拒绝新节点的加入。

11.5 MGR在8.0版本读写一致性保证

MGR相对于半同步复制，在relay log前增加了冲突检查协调，但是binlog回放仍然可能延时，也就是跟我们熟悉的半同步复制存在SQL线程的回放延迟情况类似。当然关于SQL线程回放慢的原因，跟半同步也类似，比如大事务！！
所以MGR并不是全同步方案，关于如何处理一致性读写的问题，MySQL 在8.0.14版本中加入了“读写一致性”特性，并引入了参数：group_replication_consistency，下面将对读写一致性的相关参数及不同应用场景进行详细说明。

11.6 参数group_replication_consistency的说明

EVENTUAL 默认值(最终一致性)，开启事务（T2），事务执行前不会等待先序事务（T1）的回放完成，也不会影响后序事务等待该事务回放完成。

BEFORE （本地强一致性）开启事务（T2），在开始前首先要等待先序事务（T1）的回放完成，确保此事务将在最新的数据上执行。

AFTER（全局强一致性），开启事务（T1），只有等该事务回放完成。其他后序事务（T2）才开始执行，这样所有后序事务都会读取包含其更改的数据库状态，而不管它们在哪个成员上执行。

BEFORE_AND_AFTER 开启事务（T2），需要等待前序事务的回放完成（T1）；同时后序事务（T3）等待该事务的回放完成；

BEFORE_ON_PRIMARY_FAILOVER,在发生切换时，连到新主的事务会被阻塞，等待先序提交的事务回放完成；这样确保在故障切换时客户端都能读取到主服务器上的最新数据，保证了一致性group_replication_consistency 参数可以用法SESSION，GLOBAL去进行更改。
官方说明请参考：
https://dev.mysql.com/doc/refman/8.0/en/group-replication-options.html

11.7 MGR读写一致性的优缺点

官方引入的MGR读写一致性既有它自身的天然优势，也不可避免的存在相应的不足，其优缺点如下：
优点：MGR配合中间件，比如读写分离功能的中间件，在MGR单主模式下，可以根据业务场景进行读写分离，不用担心会产生延迟，充分利用了MGR主节点以外的节点。
缺点：使用读写一致性会对性能有极大影响，尤其是网络环境不稳定的场景下。
在实际应用中需要大家因地制宜，根据实际情况选择最适配的方案

11.8 MGR读写一致性的方案

针对不同应用场景应当如何选择MGR读写一致性的相关方式，官方提供了几个参数以及与其相对应的应用场景：
AFTER
适用场景1：写少读多的场景进行读写分离，担心读取到过期事务，可选择AFTER。
适用场景2：只读为主的集群，有RW的事务需要保证提交的事务能被其他后序事务读到最新读数据，可选择AFTER。
BEFORE
适用场景1：应用大量写入数据，偶尔进行读取一致性数据，应当选择BEFORE。
适用场景2：有特定事务需要读写一致性，以便对敏感数据操作时，始终读取最新的数据；应当选择BEFORE。
BEFORE_AND_AFTER
适用场景：有一个读为主的集群，有RW的事务既要保证读到最新的数据，又要保证这个事务提交后，被其他后序事务读到；在这种情况下可选择BEFORE_AND_AFTER。
http://www.itdks.com/Course/detail?id=117532
赞(0)
MySQL学习笔记 » 19 MySQL复制

近期文章

友情链接