MySQL集群——主从复制进阶

项目环境：

主机名	ip地址	操作系统	mysql版本
mysql-master	192.168.83.200	rhel7.9	源码安装mysql8.0.40
mysql-slave	192.168.83.129	rhel7.9	源码安装mysql8.0.40

一、延时同步

1、概念

通过人为配置从库和主库延时N小时可以实现延时同步，延时同步可以解决数据库故障出现的数据丢失问题（物理损坏如直接使用rm删除数据库数据和逻辑损坏如使用drop命令删除数据库）。

2、基本配置

#1、停止从库复制数据
mysql> stop replica;#2、修改延时同步时间
mysql> CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 300;#3、查看配置信息
mysql> show replica status\G
SQL_Delay: 300                # 延时时间
SQL_Remaining_Delay: 273      # 延时同步剩余时间# 关闭从库的sql线程
mysql> stop slave sql_thread;  

3、主库误操作模拟

主库创建库和表

mysql> create databases test1
use test1 
create table student(id int,name varchar(20),age int);
insert student values(1,"宋骁",15);
drop database test1;

方法一：从库停止复制数据，解除从库身份

#解除从库身份
mysql> stop replica;
mysql> reset replica all;

方法二：找到误删前relay log的起点和终点，备份恢复数据

#1、在sql线程延迟300秒时间内发现误删除数据库则立马停止从库的sql线程
mysql> stop replica sql_thread;#2、找到误删前数据的起点和终点
mysql> show replica status\GRelay_Log_File: slave1-relay-bin.000002Relay_Log_Pos: 1608  #当前读取的relay日志的位置
#3、输入以下命令，找到误删数据的位置
mysql> show relaylog events in 'mysql-slave1-relay-bin.000002';

# 查看数据信息
[root@mysql-slave1 ~]# mysqlbinlog --start-position=1608  --stop-position=2763 /data/mysql/mysql-slave1-relay-bin.000002 | less#3、将数据进行备份
[root@mysql-slave1 ~]# mysqlbinlog --start-position=1608  --stop-position=2763 /data/mysql/mysql-slave1-relay-bin.000002 > /tmp/mysql.backup#4、恢复误删除的数据库
# 方法一
[root@mysql-slave1 ~]# mysql -uroot -p123 < /tmp/mysql.backup
# 方法二
[root@mysql-slave1 ~]# mysql -uroot -p -e 'source /tmp/mysql.backup'#5、查看数据是否恢复
mysql> select * from test1.student;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | 宋骁   |   15 |
+------+--------+------+#3）锁表设置只读
#为后面备份准备，注意生产环境要提前申请停机时间；
mysql> flush tables with read lock;
#提示：如果超过设置时间不操作会自动解锁。
mysql> show variables like '%timeout%';
#测试锁表后是否可以创建数据库：
mysql> create database test;[root@mysql-slave1 ~]# mysqldump -uroot -p123 --databases test1 > /root/huifu.backup[root@mysql-slave1 ~]# scp /root/huifu.backup root@192.168.83.200:/rootmysql> create user 'rep'@'%' identified  by 'rep123';
mysql> grant  replication slave on *.* to 'rep'@'%';

4、主变从，从变主，恢复主库数据

从库配置

#1、停掉复制数据
mysql> stop replica;#2、清空从库信息
mysql> reset replica;#3、锁表设置只读
# 为后面备份准备，注意生产环境要提前申请停机时间；
mysql> flush tables with read lock;
# 提示：如果超过设置时间不操作会自动解锁。
mysql> show variables like '%timeout%';#4、备份文件
[root@mysql-slave1 ~]# mysqldump -uroot -p123 --databases test1 > /root/huifu.backup#5、上传之前主库删除内容
[root@mysql-slave1 ~]# scp /root/huifu.backup root@192.168.83.200:/root
huifu.backup                                           100% 2135     3.4MB/s   00:00 #6、建立同步账号
mysql> create user rep@'%' identified  by 'rep123';
mysql> grant  replication slave on *.* to 'rep'@'%';#7、查看从库状态，即当前日志文件名和二进制日志偏移量
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |     3752 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

主库配置

#1、重新初始化文件
[root@mysql-master ~]# /etc/init.d/mysqld stop
[root@mysql-master ~]# rm -rf /data/mysql/*   # 删除旧数据
[root@mysql-master ~]# mysqld --initialize --user=mysql
# 获取密码
[root@mysql-master ~]# /etc/init.d/mysqld start
[root@mysql-master ~]# mysqladmin -uroot -p  password  '123'  # 修改密码# 恢复数据到主库
[root@mysql-master ~]# mysql -uroot -p123 < /root/huifu.backup#2、设定从库向主库同步
mysql> change replication source to
SOURCE_HOST='192.168.83.129',
SOURCE_USER='rep',
SOURCE_PASSWORD='rep123',
SOURCE_LOG_FILE='binlog.000001',
SOURCE_LOG_POS=3752,
SOURCE_SSL=1; 
# 开启主从同步
mysql> start replica;
# 检查状态
mysql> show replica status\G
Slave_IO_Running: Yes    # IO线程是否打开
Slave_SQL_Running: Yes   # SQL线程是否打开

二、GTID复制

1、概念

GTID是全局事务ID（global transaction identifier），其保证为每一个提交的事务可以生成一个唯一的ID。当事务提交时，MySQL Server在写binlog的时候，会先写一个类型为GTID_Event的特殊Binlog Event，指定下一个事务的GTID，然后再写事务的Binlog。主从同步时GTID_Event和事务的Binlog 都会传递到从库，从库在执行的时候也是用同样的GTID写binlog，这样主从同步以后，就可通过GTID确定从库同步到的位置了。也就是说，无论是级联情况，还是一主多从情况，都可以通过GTID自动找同步位置，而无需像之前那样通过File_name和File_position找同步位置了。

当master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。 slave端的i/o 线程将变更的binlog写入到本地的relay log中。sql线程从relay log中获取GTID，然后对比slave端 gtid_executed集合，如果GTID存在，说明该GTID的事务已经执行，slave会忽略；如果没有记录，slave就会从relay log中执行该GTID的事务，并将GTID添加到gtid_executed集合中。

2、基本配置

#1、在线切换为GTID复制，假设主从复制正在运行，可以在线切换为GTID复制，此方法不需要重启Mysql实例，非常适合生产环境使用
# 在主master和从slave上执行
mysql> select @@enforce_gtid_consistency;
+----------------------------+
| @@enforce_gtid_consistency |
+----------------------------+
| OFF                        |
+----------------------------+
# 确定GTID模式复制的兼容性：查看日志中是否有警告，如果有警告说明应用和GTID复制不兼容
mysql> set global enforce_gtid_consistency=warn;# 查看日志中是否有警告
[root@mysql-master ~]# tail /data/mysql/mysql-master.err# 启用GTID强一致性检查，防止GTID不兼容的语句导致复制失败
mysql> set global enforce_gtid_consistency=on;#2、将主和从的gtid_mode一步步从OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON
mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| OFF         |
+-------------+
mysql> set global gtid_mode=OFF_PERMISSIVE;
set global gtid_mode=ON_PERMISSIVE;# 在主库和从库上等待所有匿名事务复制完成（该变量为0）
mysql> show status like 'ongoing_anonymous_transaction_count';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0     |
+-------------------------------------+-------+# 启用GTID模式
mysql> set global gtid_mode=ON;# 建议将enforce_gtid_consistency=on和gtid_mode=ON写入配置文件，防止配置丢失；
# 如果需要级联复制，需要设置设置log_slave_updates=on，控制从库是否将主库同步过来的事务记录到自己的 binlog 中（依赖 log_bin=ON才生效）
# 在从slave上重启复制线程，切换到GTID模式
mysql> stop replica;
mysql> CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1;
mysql> start replica;
mysql> show replica status \G
Replica_IO_Running: Yes
Replica_SQL_Running: Yes

3、检测主从是否能够正常通过GTID同步

#1、在主上添加表并插入数据
mysql> create database test2;
use test2;
create table s1(id int,name varchar(20),age int,sex varchar(20) default '无');
insert into s1 values(1,"宋骁",18,"男");mysql> show master status \G
*************************** 1. row ***************************File: binlog.000004Position: 1188Binlog_Do_DB: Binlog_Ignore_DB: 
Executed_Gtid_Set: 5cc1b68c-89c7-11f0-bb00-000c29039b8f:1-4#2、查看日志信息
mysql> show binlog events in "binlog.000004"\G
*************************** 3. row ***************************Log_name: binlog.000004Pos: 157Event_type: GtidServer_id: 129
End_log_pos: 234Info: SET @@SESSION.GTID_NEXT= '5cc1b68c-89c7-11f0-bb00-000c29039b8f:1'      # GTID执行事务名称
*************************** 4. row ***************************Log_name: binlog.000004Pos: 234Event_type: QueryServer_id: 129
End_log_pos: 345Info: create database test2 /* xid=295 */
*************************** 16. row ***************************Log_name: binlog.000004Pos: 1157Event_type: XidServer_id: 129
End_log_pos: 1188Info: COMMIT /* xid=309 */    # 事务进行提交#3、查看已经执行过的gtid
mysql> select @@gtid_executed;
+------------------------------------------+
| @@gtid_executed                          |
+------------------------------------------+
| 5cc1b68c-89c7-11f0-bb00-000c29039b8f:1-4 |
+------------------------------------------+#4、在从上查看
mysql> show replica status \G
*************************** 1. row ***************************Replica_IO_State: Waiting for source to send eventSource_Host: 192.168.83.129Source_User: repSource_Port: 3306Connect_Retry: 60Source_Log_File: binlog.000004Read_Source_Log_Pos: 1188Relay_Log_File: mysql-master-relay-bin.000002Relay_Log_Pos: 1398Relay_Source_Log_File: binlog.000004Replica_IO_Running: YesReplica_SQL_Running: Yes#5、查看从上的relaylog日志
mysql> show relaylog events in "mysql-master-relay-bin.000002"\G#在从库上查看数据是否同步
mysql> select * from test2.s1;
+------+--------+------+------+
| id   | name   | age  | sex  |
+------+--------+------+------+
|    1 | 宋骁   |   18  |  男  |
+------+--------+------+------+

三、半同步复制

1、概念

说到半同步复制就不得不提到异步复制、半同步复制、全同步复制。

在早期的Mysql5.5版本之前一直采用的是异步复制方式，异步复制就是主把事务写到binlog日志后并不管从是否接收或者什么时候接收，即主库的事务执行不会管备库的同步进度，如果备库落后而此时主库不幸crash，那么就会导致数据丢失。

在Mysql5.5引入了半同步复制，主库在应答客户端提交的事务前需要保证至少一个从库接收并将事务写到relay log中，即master将写入binlog的事务传递到slave，主库提交事务，master等待slave回复ACK确认已将数据写入relay log，此时master才会将commit OK结果反馈给客户端，如果出现异常没有收到ACK，那么将自动降级为普通的复制，直到异常修复后又会变为半同步复制。

半同步复制的特性：

• 半同步复制必须是在主库和从库两端都开启时才行，从库会在连接到主库时告诉主库它是不是配置了半同步。

• 如果半同步复制在主库端是开启了的，并且至少有一个半同步复制的从库节点，那么此时主库的事务线程在提交时会被阻塞并等待，等待的结果有两种，第一种是至少一个从库节点通知主库自己已经收到了所有这个事务的binlog事件；第二种就是主库一直等待直到超过配置的某一个时间点为止，此种情况下半同步复制将自动关闭，转换为异步复制。

• 从库节点只有在接收到某一个事务的所有binlog并将其写入relay log文件之后才会通知主库。

在mysql5.5-5.6使用after_commit的半同步模式下，客户端事务在存储引擎层提交后，在得到从库确认的过程中，主库宕机了，此时主库在等待slave ACK的时候，虽然没有返回到当前客户端但是事务已经提交，其他客户端会读取到已经提交的事务，如果slave端还没有读到该事务的events，同时主库发生了crash，然后切换到备库，那么之前读到的事务就不见了。如果主库永远启动不了，那么实际上在主库已经成功提交的事务在从库上是找不到的，也就是数据丢失了。

为了解决上述问题，在mysql5.7版本增加了after_sync（无损复制）参数，并将其设置为默认的半同步方式。在mysql5.7.2引入的Loss-less Semi-Synchronous（无损复制）通过添加参数after_sync，使得主库在commit事务之前等待slave的ACK，只有slave回复了ACK，主库才会提交事务，这样就保证了master和slave数据的一致性。

在mysql5.7.17又引入了全同步技术，全同步复制就是当主库提交事务之后，所有的从库节点必须收到并且apply这些事务，然后主库线程才能继续后续操作。

2、配置半同步复制

主库的配置

#1、主的配置，加载插件
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
# 查看插件是否加载成功
mysql> show plugins;
# 开启此开关，可写入配置文件
mysql> set global rpl_semi_sync_master_enabled=1;# 设置主等待从回复ACK的超时时间为3秒，默认是10秒，可写入配置文件
mysql> set global rpl_semi_sync_master_timeout=3000;
mysql> show variables like 'rpl_semi_sync_master%';
+---------------------------------------------+------------+
| Variable_name                               | Value      |
+---------------------------------------------+------------+
| rpl_semi_sync_master_enabled                | ON         |  # 半同步复制是否启动
| rpl_semi_sync_master_timeout                | 3000       |
| rpl_semi_sync_master_trace_level            | 32         |
| rpl_semi_sync_master_wait_for_slave_count   | 1          |  # 指主等待几个slave返回同步复制的ACK信号后提交
| rpl_semi_sync_master_wait_no_slave          | ON         |  # 为off时，只要主库发现Rpl_semi_sync_master_clients小于rpl_semi_sync_master_wait_for_slave_count，则半同步立即转为异步模式；为on时，在无事务提交的空闲时间里，即使主库发现Rpl_semi_sync_master_clients小于rpl_semi_sync_master_wait_for_slave_count也不会做任何调整，只要保证在事务超时之前，主库收到大于等于rpl_semi_sync_master_wait_for_slave_count值的ACK应答数量，主库就一直保持在半同步模式，如果在事务提交阶段超时，半同步才会转为异步模式。
| rpl_semi_sync_msater_wait_point             | AFTER_SYNC |
+---------------------------------------------+------------+mysql> show status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     | #支持和注册半同步复制的已连slave数
| Rpl_semi_sync_master_net_avg_wait_time     | 0     | #废弃
| Rpl_semi_sync_master_net_wait_time         | 0     | #废弃
| Rpl_semi_sync_master_net_waits             | 0     | #废弃
| Rpl_semi_sync_master_no_times              | 0     | #master关闭半同步复制的次数
| Rpl_semi_sync_master_no_tx                 | 0     | #master等待超时的次数
| Rpl_semi_sync_master_status                | ON    | #半同步复制是否开启
| Rpl_semi_sync_master_timefunc_failures     | 0     | #调用gettimeofday()等时间函数失败的次数
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     | #master花在每个事务上的平均等待时间
| Rpl_semi_sync_master_tx_wait_time          | 0     | #事务总的提交等待时间
| Rpl_semi_sync_master_tx_waits              | 0     | #master成功提交的次数
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     | #主库event binary位置低于之前等待event的总次数，当事务开始等待回复的顺序与其二进制日志事件的写入顺序不同时就会发生该情况
| Rpl_semi_sync_master_wait_sessions         | 0     | #当前会话等待从库确认接受的个数，可以用来确认分析会话是否存在由于等待从库确认而堵塞的情况
| Rpl_semi_sync_master_yes_tx                | 0     | #master成功接收到slave的回复的次数
+--------------------------------------------+-------+

从库的配置

#1、从的配置
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
mysql> show variables like 'rpl_semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+#2、查看插件是否加载成功
mysql> show plugins;
#3、重启从的IO线程
mysql> STOP REPLICA IO_THREAD;
mysql> START REPLICA IO_THREAD;
mysql> show status like 'rpl_semi%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Rpl_semi_sync_slave_status   |  ON   |
+------------------------------+-------+

3、测试

# 测试
#1、主上创建一张表
mysql> use test2
create table s2(id int);
mysql> show status like 'Rpl_semi_sync_master_yes_tx';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_yes_tx | 1     | # master成功接收到slave的回复的次数
+-----------------------------+-------+#2、模拟从故障
mysql> stop REPLICA io_thread;
mysql> set global rpl_semi_sync_slave_enabled=0;
mysql>  show status like 'rpl_semi_sync_slave_status';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Rpl_semi_sync_slave_status   | OFF   |
+------------------------------+-------+# 在主上创建表，可以看到会等待一个超时时间
mysql> insert into s2 values(2);
Query OK, 1 row affected (3.00 sec)# 发现主关闭了半同步复制
mysql> show status like 'rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF   |
+-----------------------------+-------+