首页 > 数据库 >MYSQL面试系列-04

MYSQL面试系列-04

时间:2024-09-16 08:54:18浏览次数:12  
标签:主库 binlog log 04 sync 面试 thread MYSQL 从库

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
还请大家多好评,收藏,评论,关注,一键三连哦!!!

标签:主库,binlog,log,04,sync,面试,thread,MYSQL,从库
From: https://blog.csdn.net/king01299/article/details/142298485

相关文章

  • MySQL锁机制揭秘:从行锁到表锁,共享锁到排他锁,悲观锁到乐观锁的全面解读
    MySQL有哪些锁1、按照锁的粒度划分行锁是最低粒度的的锁,锁住指定行的数据,加锁的开销较大,加锁较慢,可能会出现死锁的情况,锁的竞争度会较低,并发度相对较高。但是如果where条件里的字段没有加索引,则加的行锁会自动升级为表锁,因为行锁是基于索引去进行操作的,所以想要加行锁,就......
  • 一文看完MySQL 9.0新特性!
    本文总结自MySQL8.4以来,在MySQL9.0中新增、废弃、更改和删除的内容。MySQL9.0中新增或更改的功能。1MySQL9.0新特性1VECTOR类型支持MySQL9.0支持VECTOR列类型。向量是一个数据结构,它由条目列表(4字节浮点值)组成,可以表示为二进制字符串值或列表格式字符串。VECT......
  • mysql学习教程,从入门到精通,TOP 和MySQL LIMIT 子句(15)
    1、TOP和MySQLLIMIT子句内容在SQL中,不同的数据库系统对于限制查询结果的数量有不同的实现方式。TOP关键字主要用于SQLServer和Access数据库中,而LIMIT子句则主要用于MySQL、PostgreSQL(通过LIMIT/OFFSET语法)、SQLite等数据库中。下面将分别详细介绍这两个功能......
  • 左叶子之和-404
    题目描述给定二叉树的根节点root,返回所有左叶子之和。解题思路这里我才用的是前序遍历,我们在遍历的时候因为是要手机左叶子节点,所以我们就不能等到遍历当前节点的时候再去做判断,应该遍历到一个节点的时候就对其下一个节点的左右子树进行判断,这样才能确保我们得到的是我们的左......
  • MySQL间隙锁,next-key锁
    间隙锁间隙锁是对索引记录之间的间隙的锁,或者是对第一个索引记录之前或最后一个索引记录之后的间隙的锁。例如,SELECTc1FROMtWHEREc1BETWEEN10and20FORUPDATE;阻止其他事务将的值插入15到列中t.c1,无论列中是否已经存在任何此类值,因为该范围内所有现有值之间的......
  • Ubuntu 18.04搭建RISCV和QEMU环境
    Ubuntu18.04搭建RISCV和QEMU环境  原文:https://blog.csdn.net/Eng_ingLi/article/details/135285200 前言因为公司项目代码需要在RISCV环境下测试,因为没有硬件实体,所以在Ubuntu18.04上搭建了riscv-gnu-toolchain+QEMU模拟器环境。安装riscv-gnu-toolchainriscv......
  • JVM四种垃圾回收算法以及G1垃圾回收器(面试)
    JVM垃圾回收算法标记清除算法:标记清除算法将垃圾回收分为两个阶段:标记阶段和清除阶段。在标记阶段通过根节点,标记所有从根节点开始的对象。然后,在清除阶段,清除所有未被标记的对象适用场合:存活对象较多的场景下比较高效缺点:容易产生内存碎片复制算法:从根节点进行扫描,......
  • MySQL练手题--体育馆的人流量(困难)
    一、准备工作CreatetableIfNotExistsStadium(idint,visit_dateDATENULL,peopleint);TruncatetableStadium;insertintoStadium(id,visit_date,people)values('1','2017-01-01','10');insertintoStadium(id,visit_date,......
  • 【Java面试】第十一天
    ......
  • mysql事务
    MySQL事务是数据库管理系统(DBMS)中的一项关键功能,确保一系列数据库操作作为一个整体被执行,且具有原子性、一致性、隔离性和持久性(ACID)的特性。事务处理机制可以帮助开发者确保数据的完整性和一致性,特别是在出现错误或并发操作时。1.事务的四大特性(ACID)原子性......