MYSQL面试系列-04
17.关于redo log和binlog的刷盘机制、redolog、undolog作用、GTID是做什么的?
innodb_flush_log_at_trx_commit 及 sync_binlog 参数意义 双1
17.1 innodb_flush_log_at_trx_commit 该变量定义了 InnoDB 在每次事务提交时,如何处理未刷入(flush)的重做日志信息(redo log)。
它是 InnoDB 确保 ACID 属性中的持久性(Durability)的关键因素。当数据库发生故障,如崩溃或者断电,这项设置可以保护您的数据不会丢失。
redo落盘路径–logbuffer–systemdiskbuffer–disk.
innodb_flush_log_at_trx_commit 的取值可以是 0、1 或 2:
0:log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候,不会主动触发写入磁盘的操作。
1:每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去,该模式为系统默认。
2:每次事务提交时mysql都会把log buffer的数据写入log file,但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作
17.2 sync_binlog 参数来控制数据库的binlog如何刷到磁盘。binlog是mysql数据库级别的,意味着所有数据库引擎都支持binlog.
默认,sync_binlog=0,表示MySQL不控制binlog的刷新,由文件系统自己控制它的缓存的刷新。
如果sync_binlog>0,它指定了两次刷新到磁盘的动作之间间隔多少次二进制日志写操作。
最安全的就是sync_binlog=1了,表示每次事务提交,MySQL都会把binlog刷到binlog文件中。
17.3 redolog、undolog都分别有什么作用?
Redo log:
数据库中的数据变更,需要刷新到磁盘才能保证变更操作不丢失,如果直接把数据文件刷新到磁盘,那么会产生大量的随机io,效率很低。redolog的写入是连续io,所以通过把变更操作记录在redolog中,在事务提交时只需要保证把redolog中的相关记录刷到磁盘即可。变随机io为顺序io,大大的提高了效率。同时,如果发生宕机等情况,可以通过redolog中记录的日志来恢复数据库到一致状态,也能保证数据不丢失,即提高了效率,又能保证数据不丢失。在mysql的物理备份中也会用到redolog,物理备份程序会实时备份redolog。
Undo log:
Undo log中的记录可以用来撤销事务的修改操作(记录数据库修改前的状态),也可以用来支持数据库的并发控制,例如MVCC(多版本并发控制)等机制。
当一个事务回滚时,数据库管理系统会使用Undo log中的记录来恢复事务开始时的状态,把事务做的所有修改操作都撤销,恢复数据库的一致性。此外,Undo log还可以用来支持数据库的快照、闪回等功能。
17.4 什么是GTID
https://blog.csdn.net/shaochenshuo/article/details/54138317
GTID又叫全局事务ID,主要用于主从架构替代原有方案中指定binlog文件名+pos的实现。
注意:
当备库复制出错时,传统的跳过错误的方法是设置sql_slave_skip_counter,然后再START SLAVE。在GTID的主从中无法实现,
需要通过生成一个空事务来跳过错误的事务。
如果开启GTID,理论上最好调小每个binlog文件的最大值,以缩小扫描文件的时间。
主备库设置要都设置为ROW模式,否则执行DML 会导致备库复制中断
18.MySQL - 异步复制,半同步复制,增强半同步复制,组复制
18.1 主从工作原理
SQL语句操作变化存入BinLog日志中
slave上线,连接到master服务器,进行数据同步
dump thread线程把Binlog数据发送到slave中
slave启动之后,创建一个I/O线程,读取master传过来的Binlog内容并写入到Relay log.
slave还会创建一个SQL线程,从Relay log里面读取内容,从Exec_Master_Log_Pos位置开始执行读取到的更新事件,将更新内容写入到slave的db.
18.2 关于并行复制
https://blog.csdn.net/weixin_33738982/article/details/90592958
MySQL一直以来的备库复制都是单线程apply.
改成多线程复制。
备库有两个线程与复制相关:io_thread 负责从主库拿binlog并写到relaylog, sql_thread 负责读relaylog并执行。
多线程的思路就是把sql_thread 变成分发线程,然后由一组worker_thread来负责执行。
几乎所有的并行复制都是这个思路,有不同的,便是sql_thread 的分发策略。
而这些策略里面又分成两类:利用传统binlog格式、修改binlog。
使用传统的binlog格式的几类,由于binlog里面的信息就那些,因此只能按照粒度来分,也就是:按库、按表、按行。
另外有两个策略是修改了binlog格式的,在binlog里面增加了别的信息,用于体现提交分组。
并行复制的实现主要有按表分发策略和按行分发策略
18.3 异步复制
MySQL 默认的复制策略,Master处理事务过程中,将其写入Binlog就会通知Dump thread线程处理,然后完成事务的提交,不会关心是否成功发送到任意一个slave中。
问题:一旦Master 崩溃,发送主从切换将会发送数据不一致性的风险。
18.4半同步复制
Master处理事务过程中,提交完事务后,必须等至少一个Slave将收到的binlog写入relay log返回ack,才能继续执行处理用户的事务。
配置:
rpl_semi_sync_master_wait_point = AFTER_COMMIT (什么时间点开始等ack)
rpl_semi_sync_master_wait_for_slave_count = 1 (最低必须收到多少个slave的ack)
rpl_semi_sync_master_timeout = 100(等待ack的超时时间)
问题:
一旦Ack超时,将退化为异步复制模式,那么异步复制的问题也将发送,性能下降,增多至少一个RTT时间
数据不一致性问题,因为等待ACK的点是Commit之后,此时Master已经完成数据变更,用户已经可以看到最新数据,
当Binlog还未同步到Slave时,发生主从切换,那么此时从库是没有这个最新数据的,用户又看到老数据。
18.5增强半同步复制
增强半同步和半同步不同是,等待ACK时间不同
rpl_semi_sync_master_wait_point = AFTER_SYNC(唯一区别)
半同步的问题是因为等待ACK的点是Commit之后,此时Master已经完成数据变更,用户已经可以看到最新数据,
当Binlog还未同步到Slave时,发生主从切换,那么此时从库是没有这个最新数据的,用户又看到老数据。
增强半同步将等待ACK的点放在提交Commit之前,此时数据还未被提交,外界看不到数据变更,
此时如果发送主从切换,新库依然还是老数据,不存在数据不一致的问题。
18.6组复制
MySQL在引擎层完成Prepare操作写Redo日志之后,会被MySQL的预设Hook拦截进入MGR层
MGR层将事务信息打包通过Paxos协议发送到全部节点上,只要集群中过半节点回复ACK,那么将告诉所有节点数据包同步成功,
然后每个节点开始自己认证(certify)通过就开始写Binlog,提交事务或者写relay log,数据同步,如果认证不通过则rollback
什么是Certify?
在不同服务器上并发执行的事务之间可能存在冲突。这种冲突是通过检查和比较两个不同的并发事务的写入集来检测的,这个过程称为认证
在认证期间,冲突检测是在行级别执行的:如果在不同服务器上执行的两个并发事务更新了同一行,则存在冲突。
冲突解决过程指出,首先排序的事务将在所有服务器上提交,而第二次排序的事务将中止,因此将在原始服务器上回滚并被组中的其他服务器丢弃。
总结:MGR内部实现了分布式数据一致性协议,paxos通过其来保证数据一致性。
问题:
性能不高
TP999升高,吞吐量降低;增大20%~30%响应时间
19 主从延迟的原因及解决方案
19.1:可能导致主从延迟的原因:
1) 主库dml操作并发度过高
我们知道主库是并行处理,但是从库复制过程是串行的,单线程的,所以如果主库dml操作并发度过高会导致从库延迟。
NOTE: mysql 5.6中已经可以实现针对不同库的多线程并行复制了(但是对单库的并发dml造成的延迟还是无能为力),
mysql 5.7中已经实现了针对不同表的并行多线程复制(测试发现主库高并发,从库也未出现延时现象)。
2) 主库执行了大事物
因为 Seconds_Behind_Master=clock_of_slave - last_timestamp_executed_by_SQL_thread - clock_diff_with_master
所以从库在执行主库传递过来的大事物时,我们就会看到延迟一直在增加,该大事物执行完成后延迟现象立即消失
3) 从库的select 操作阻塞主库的ddl操作
为了减轻主库的压力,都会把从库用做查询。如果从库对一个大表进行select操作,在select执行过程中主库对该表执行了ddl操作,
那么传递到从库的ddl操作会被select操作阻塞(Waiting for table metadata lock),从而造成Seconds_Behind_Master值不断变大
4) 从库配置过低
如果从库配置过低,导致sql thread,执行速度跟不上io thread 也会产生延迟,如果从库用来做查询的话,建议从库配置不要低于主库。
##如果io thread跟不上主库binlog产生速度,但是sql thread能够追上io thread,那么虽然此时从库是有延时的,但是Seconds_Behind_Master依然为0
5) 只读实例 MyISAM 引擎表
MyISAM 引擎表读写相互冲突,同一时间读写不能并发操作,且仅支持表级锁。因此表上的长时间查询(比如 SQL 注入)
会阻塞SQL线程应用来自主实例的该表数据变化,导致只读实例数据延迟。
19.2 mysql从库延时原因判断
查找从库延时的具体原因,可以从如下几步下手
1) 多次 show slave status\G; 查看从库状态
多次执行show slave status\G;查看从库状态,如果Exec_Master_Log_Pos不变(Relay_Log_Space可能会变,比如主库 binlog_format=row,且大事务操作为dml???), Seconds_Behind_Master 一直增加,则从库在执行某个大事物。
遇到该种情况,我么可以尝试如下方法来缓解延时问题:
临时设置从库sync_binlog和innodb_flush_log_at_trx_commit 值为0
1)设置从库的sync_binlog为0(之前为1)
set global sync_binlog=0;
2)设置innodb_flush_log_at_trx_commit=0
innodb_flush_log_at_trx_commit 设置为0
因为上面两个参数都是都是global 参数,所以设置后不需要重启sql_thread线程 即可生效
##如果dml操作的innodb 表没有合适主键和索引,则从库进行多次全表扫描
2) 如果看到Slave_SQL_Running_State: Waiting for table metadata lock,则表示从库执行的ddl被select阻塞
遇到该种情况,我们可以先kill掉造成阻塞的select操作
在从库杀掉阻塞ddl操作的会话即可(怎么找到阻塞会话请参考的博客http://blog.csdn.net/shaochenshuo/article/details/51577728)
3) 如果我们看到 Exec_Master_Log_Pos 和Seconds_Behind_Master都在不断变化,那么此时可能是主库dml并发度过高,或者从库配置过低导致
如果排除其他可能原因,并且主库tps值很高,那么我们可以通过如下临时调整尝试缩小主从延时(但是该调整有丢数据的风险,而且有可能也无法缩小主从延时)
遇到该种情况,我们可以尝试如下方法来处理延迟问题
1.3.1 查看是否需要添加索引
我们可以分析从库正在执行的dml,如果可以通过添加索引达到提高处理效率的目的,我们可以暂时给从库上的表添加索引,从而减轻延时现象。
1.3.2 临时设置从库sync_binlog和innodb_flush_log_at_trx_commit 值为0
1)设置从库的sync_binlog为0(之前为1)
set global sync_binlog=0;
2)设置innodb_flush_log_at_trx_commit=0
innodb_flush_log_at_trx_commit 设置为0
因为上面两个参数都是都是global 参数,所以设置后不需要重启sql_thread线程 即可生效
20.Mysql 高可用实现方式及遇到的问题
20.1 MHA主从高可用架构
https://www.bilibili.com/video/BV1hm4y1e7mv?p=113&vd_source=98d4a1bb64fff3d3cace7ee54357de77
监控机制:mysqlping,执行sql命令
Ping
选主机制:GTID高,
数据补偿机制:
ssh连接对比从库的缺失日志,补偿到2个从库,使其能够推进到最新数据
ssh连接不上(服务器已经关机),无法恢复丢失部分数据。
FAILOVER(故障切换):
使用VIP实现应用透明
故障提醒
架构:一主两从、三个物理节点
MHA高可用方案软件构成
Manager软件:选择一个从节点安装
1、监控问题?主机、mysql实例
2、处理问题,需要人为
3、数据补偿---->GTID
Node软件:所有节点都要安装
Manager工具包主要包括以下几个工具:
masterha_check_ssh 检查MHA的SSH配置状况
masterha_check_repl 检查MySQL复制状况
masterha_check_status 检测当前MHA运行状态
masterha_manger 启动MHA
masterha_master_monitor 检测master是否宕机
masterha_master_switch 控制故障转移(自动或者手动)
masterha_conf_host 添加或删除配置的server信息
Node工具包(这些工具通常由MHA Manager的脚本触发,无需人为操作)主要包括以下几个工具:
save_binary_logs 保存和复制master的二进制日志
apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的
slave filter_mysqlbinlog去除不必要的ROLLBACK事件(MHA已不再使用这个工具)
purge_relay_logs 清除中继日志(不会阻塞SQL线程)
注意:
做一次MHA故障切换后,MHA manager需要手动介入启动,才能继续mha工作。
需要手工清楚relay_logs。
20.2 InnodbCluster集群
包含:
mgr集群,数据库同步核心实现
myrouter安装在应用端,实现路由功能
mysqlshell集群管理的功能。
mgr通过类似paxos状态机同步机制实现。
由于整个系列内容还是挺多的,我看了一下50000字左右,所以建议大家直接去资源站免费下载,具体地址:
https://download.csdn.net/download/king01299/89752708
还请大家多好评,收藏,评论,关注,一键三连哦!!!