什么是Bin Log
二进制文件,所有对数据行和表进行操作,用于 数据之间的同步和数据的恢复。
对于Bin Log文件的查看和使用 需要使用 mysqlbinlog 命令。
如何使用Bin Log进行数据的恢复
mysqlbinlog [option] filename|mysql –uuser -ppass;
可以指定数据库的时间节点,和数据的范围,有选择的进行恢复。
Bin Log日志写入时期
- 在事务执行过程中,将bin log日志记录到 Binlog Cache中,因为一个事物的BinLog Cache无论多大都必须一次性写入,不能分割,因此系统会为每个线程分配一个Binlog Cache,如果BinLog的内容超出了BinLog Cache的上限,则会将文件暂存到磁盘中
- 仅当事务提交后,线程才会将 Bin log日志从 Binlog Cache中写入到 Page Cache 文件缓存中
- 此时根据 sync_binlog 选项设置,在什么时机内从 Page Cache文件缓存刷入到磁盘中
- 当 sync_binlog = 0时,则为不主动进行刷盘策略,此时如果数据库发生宕机,可能会有大量事务的 binlog文件丢失
- 当 sync_binlog = 1时,则每次提交事务后都会将 binlog文件从 Page Cache中刷入到磁盘中,但此时需要频繁的IO,可能会遇到IO的瓶颈
- 当 sync_binlog = 其他数量时,当 Page Cache 中累计的数量达到了 设置的数量时,才会将日志文件从 Page Cache中刷入到磁盘中。如果此时数据库发生宕机,则会损失一部分数据,但会减少IO的次数,提高性能。
更新时 redoLog和binlog的一致性问题
Redo Log和Bin Log 写入顺序
- 在执行更新语句时,先从磁盘中数据读取到缓冲池中,然后写入 Undo Log,然后进行数据的更新,然后持续写入 Redo Log到Redo Log Buffer中
- 在事务执行完毕后,会开始进入提交事务的环节,此时会先开始 prepare阶段 先写入 Redo Log的写入到 Page Cache中
- 因为事务的崩溃恢复机制需要 Redo Log日志,此时其实只需要 Redo Log 的 Prepare阶段 fsync执行完毕后即可,因此Innodb认为最后Redo Log 的commit阶段 write执行完毕即可,可以不执行 commit的 fsync阶段
在事务过程中,出现MySQL宕机如何判断是提交还是回滚
- 如果Redo Log中包含 commit标志,则说明事务已经完成,此时可以直接提交事务。
- 如果Redo Log中只有完成的 Prepare 阶段的日志,则需要先判断 Bin Log日志是否完整,如果BIn Log日志完整,则提交事务,否则进行事务的回滚操作
- 如何判断bin log是否完整,在日志文件中结尾有标识位,判断BIn Log的结尾处是否包含标识位,则可以判断BIn log文件是否完整
- Redo Log和BIn Log有个共同的字段,事务Id,在崩溃恢复时,会先扫描Redo Log,如果Redo Log中包含 Prepare和 commit的信息,则直接提交,如果仅包含Prepare 则根据事务Id去BIn Log中查询对应的信息
主从同步的原理
- 主从同步是基于BIn Log 日志的同步来实现的,根据BIn Log日志记录的操作,执行后即可达到数据同步的目的。
- 在数据更新事务提交完成后,主库中的数据修改完成,此时该次事物的 Bin Log日志也已经记录在磁盘中。
- 主从同步的实现需要有三个线程执行,首先在主库中会有一个 Log dump的线程,将主库中的磁盘进行发送到从库中。
- 从库中有一个IO线程,接收来自主库的 Log Dump线程的信息,将接收到的信息写入 中继日志 RelayLog。
- 从库中第二个线程用于执行RelayLog,依次执行SQL语句,从而达到数据同步的目的。
主从复制会遇到的问题
主从同步数据丢失
即binlog数据丢失,导致虽然主库上完成了操作,但由于binlog日志丢失,导致从库中的数据是缺失的。解决方案可以将Binlog刷盘的频率参数 sync_binlog = 1 解决此问题,但是这样IO会收到限制,这样可以保证每次事务完成都成功将Binlog日志刷入到磁盘中,也就保证从库获取到的 binlog日志时完整的,保证了数据的完整性。
主从同步导致数据延迟
主从同步的耗时时间点
- Log Dump线程从主库往各个从库之间发送BinLog日志
- IO线程接收到主库的日志并将日志写入中继日志
- SQL线程持续消费中继日志,常常因为慢SQL或对表操作等原因导致主从消费中继日志的速度比生成的慢,这也是主从同步的主要耗时时间段。
如何判断当前主从同步的延迟很大
show slave status参数中 Seconds_Behind_Master 数值比较大
主从同步的方案
异步复制
在主库commit后,直接返回结果,不考虑从库是否读取到数据,效率最高,默认状态
同步复制
在主库commit后,需要等待所有的从库接受完BInlog日志,并且将日志写入到中继日志后,返回ack后,主库才能成功返回。
半同步复制
在主库commit后,只需要有一个从库接受完Binlog日志,并且将日志写入到中继日志后,返回ack,主库即可成功返回。
主从复制的延迟如何解决
主从同步延迟主要的原因
- 网络IO
- 主库负载
- 从库负载
解决方案
- 如果是写后读的方案,shardingphere默认在同一个事务中,有update操作后的select会是同一个数据源,因此则会直接读取主库
- 开启并行复制,但是只是数据库级别的并行,通常情况下数据库实例的压力常常集中在某几个数据库表
- 从库中可以不记录Binlog日志,提高SQL的运行效率
- 因为主要的瓶颈在于从库的SQL执行速率,可以在从库使用比主库更高的配置,从而使得从库的消化速率大于主库
- 对主库进行拆分
- HintManager.getInstance().setMasterRouteOnly(); 设置下一次查询读主库
- 半自动复制
通过mysqldump+binlog进行数据备份和恢复
如何备份
在运行这个命令后,将所有的表结构和数据行转储到SQL文件中,但是会丢失在这个时间节点后新增的命令,因此需要加上-F命令,刷新Binlog文件,后续需要通过binlog文件获取增量的数据。
获取当前使用的binlog文件
删除数据库
根据SQL文件进行全量恢复
但是时间点后的数据没有恢复
获取恢复的时间节点
将Binlog日志转化为文本文件,通过命令搜索确认从什么时间节点开始恢复
利用Binlog文件从指定的时间恢复数据
GTID 全局事务标识符
GTID是全局唯一的事务标识符,可以省去查找binlog中pos的操作。