叶老师MySQL试水题
1、简述MySQL 主从复制原理
2、简述xtrabackup工作原理
3、mysqldump工作原理
4、简述造成MySQL主从数据不一致的因素，有什么解决办法
5、简述MySQL主从同步延迟的原因以及解决办法
6、MySQL数据库负载高的排查和解决方法
7、简要说明一下double write的工作机制
8、MySQL半同步的实现以及原理
9、简单描述MySQL MVCC机制
10、简述下对innodb_flush_log_at_trx_commit的理解
附一份某同学的答案
1、简述MySQL主从复制原理理
通过主库的dump thread, 从库的io thread, sql thread实现数据同步;
主库数据更更新写.入binlog后, dump thread通知slave, slave的io thread会拉取主库binlog,写.入本地relay log, sql thread读取relay log后在本机执.行行以实现数据同步
2、简述xtrabackup工作原理理
mysql的物理理备份工具, 可以支持在线innodb热备
主要备份流程:
复制ibd, ibdata1.文件
flush tables with read lock
复制.frm,myd,myi等.非innodb.文件
show master stauts获取binlog位置
执行unlock tables
停止并复制xtrabackup_log,完成备份
3、mysqldump.工作原理理
mysql自带的,可支持在线逻辑备份的工具
主要备份流程:
执行FLUSH TABLES关闭所有打开的表
FLUSH TABLES WITH READ LOCK
设置为REPEATABLE READ
START TRANSACTION WITH CONSISTENT SNAPSHOT
mysqldump --single-transaction执行的操作
SHOW MASTER STATUS获取binlog位置
mysqldump中的--master-data执行的操作,获取binlog文件与位置
UNLOCK TABLES
4、简述造成MySQL主从数据不一致的因素，有什什么解决办法
为数据安全考虑, 大体解决办法是主备库做双1设置, 启用gtid,将masterinfo,slaveinfo等参数设置到innodb
table(mariadb分支启用gtid就行); 对数据一致性要求较高的可以尝试使用galera cluster
表上缺少主键 新版本中添加强制主键设置, 建表时没主键就报错
误操作导致数据不一致
在备库执行了写入导致不一致, 可以在所有备库设置只读
主库临时设置sqllogbin导致不一致, 执行前做此变量量检查
主从设置了了不不同的复制过滤规则(包含迁移时临时设置,但后面忘记关闭)
保持主从配置尽量一致, 可以添加报警设置
binlog使用了传统的语句句格式,增加了数据不一致的风险
修改为row格式
主从库版本不不同导致
同.一版本要求从库版本>=主库版本
mysql同步到mariadb,注意取消gtid,binlog配置参数及正确选择对等分支
主备库切换导致
存储过程,事件的账号权限调整为inover, 避免未完成切换时数据部分写入备库的情况
注意切换时新主库中event的启.用时间
半同步设置
after_commit,主库异常crash可能会出现事务未同步到备库现象 万.一出现此类情况, 旧主库正常恢复
后, 通过binglog补回数据
after_sync,主库异常crash可能出现事务同步到备库,但.自.己未提交情况 这类情况出现, 相对来说总
比数据丢失更更好, 可根据业务情况确定数据的取舍
5、简述MySQL主从同步延迟的原因以及解决办法
.网络 检查.网络带宽是否占用过高, 可以使.用万兆网
机器.硬件配置 保证从库机器.配置>=主库配置, iops确定很.高时可以考虑使.用ssd top, iostat查看cpu, io是否负载过高锁及大事务
执行大量量删除,更更新或.大表修改会导致备库延时;
可以拆分成.小事务批量量执.行行, .大表修改可以使.用pt.工具(设置chunk及允许备库的延时范围);
降低事务隔离级别为读提交.方式,减少锁(死锁)发生;
老版本中备库有.高并发查询时加索引会阻塞sql线程写.入,导致同步延时,尽量量升级新版本
配置参数
可以根据实际情况调整innodb-flush-log-at-trx-commit=2或0,sync-binlog>=0以提.高io性能;
负载.高的机器.或ssd设备可以尝试关闭.自适应哈希索引;
多线程
传统多线程同步是按库级同步,5.7以后使.用logical clock同步并调整组提交参数,提.高并行处理理性能
6、MySQL数据库负载.高的排查和解决.方法
是否有并发高的全表扫描查询
top,sar,iostat查看系统负载;
show processlist查看有.无异常进程,找到问题语句句explain分析是否有合适的索引;
pt-query-digest分析慢查询.日志
是否存在mdl锁阻塞了了.大量量写.入
mysql可以使.用ps,sys分析, mariadb使.用mdl插件表分析确定
配置参数是否合理理
innodb-flush-log-at-trx-commit,sync-binlog,innodb-buffer-pool-size, innodb-io-capity,innodb AHI
是否不不正确地使.用了了thread_pool
如果是io密集型查询为主的应.用, 则不不适合启.用thread_pool,如果启用,会造成线程池.用滿,.大量量连接
等待现象
系统调.用是否有问题
pt-stalk,gdb/strace/pstack做.日志或系统级的跟踪分析
7、简要说明.一下double write的.工作机制
针对innodb.页.面部分写问题.而设计;
修改的数据先写.入doublewrite(ibdata1中的2M空间),然后写.入数据.文件,写入过程如果异常断电, 恢复时
可从doublewrite中恢复完整.页内容
.支持原.子写的fusion-io设备会.自动关闭doublewrite
8、MySQL半同步的实现以及原理理
确保从库接收完主库发送的binlog并写.入到relay log，然后发送给主库ack，主库返回结果给客户端. 当出现从
库响应超时，主库会切换到异步模式. 通过rplsemisyncmasterwait_point参数, 可以控制主库返回结果前的提
交.方式
AFTER_COMMIT(<5.7默认)
master写入binlog后，先提交事务，然后将binlog数据传到slave并写入relay log, master等待slave返回
ack，收到ack后master执.行行commit
AFTER_SYNC（5.7默认）
master写入binlog，将binlog传到slave并写.入relay log, 然后master收到slave ack后，再执.行行commit。
即使主库挂了了，也能保证在主库上已经提交的事务都同步到了了slave
9、简单描述MySQL MVCC机制
简单说是一种多版本并发控制技术, 处理理并发能力较强,MySQL中更更加适用于repeatable read隔离级别
只能读取在事务开始前已提交的数据,事务开始后,所有其他事务的修改对当前事务都不可见
执行dml语句句时,会记录行的创建,删除时间并为每行记录版本号; 指向当前记录回滚段的undo_log, 用于查
找之前版本;删除标记位,数据提交时执行真正删除
10、简述下对innodbflushlogattrx_commit的理理解
主要作.用是控制innodb事务提交后redolog的写入频率
0: 性能最好, 每次事务提交时不不做操作, 每秒(可通过参数修改刷新频率)log buffer会执行redolog的刷新写入,mysql进程crash时会丢失1s数据
1: 默认值,性能相对最差但最安全, 每次事务提交时, log buffer会执行redolog的刷新写入
2: 每次事务提交时会写入os cache, 每秒执行redolog的刷新写入,os crash时会丢失1s数据
