知数堂-罗小波-MySQL数据一致性
如果无法正常显示，请先停止浏览器的去广告插件。
1. MySQL数据一致性
杭州沃趣科技股份有限公司
2015.11.03
Hangzhou WOQU Technology Co., Ltd.
2. 目录
01 MySQL 崩溃恢复安全性
02 MySQL复制原理及异步、semi-sync复制
03 MySQL 主备复制如何保证数据一致性
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
3. 目录
01 MySQL 崩溃恢复安全性
02 MySQL复制原理及异步、semi-sync复制
03 MySQL 主备复制如何保证数据一致性
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
4. Innodb崩溃恢复安全性如何保证
实现事务的原子性（记录着数据变更前的记录，用亍回滚没有提交的事务）
实现事务的持久性，和Undo Log相反，Redo Log记录的是发生新的修改
的数据。恢复时可以根据 Redo Log的内容，将所有数据恢复到最新的状态
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
5. undo+redo简单的事务执行过程和特点
假设表中有A、B两个数据，值分别为1、2，事务执行update时的简单过程如下：
begin,
事务开始
undo log
记录A=1
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
redo log
记录A=3
undo log
记录B=2
redo log
记录B=4
redo log
把A、B修改落盘
commit,
提交事务
6. undo+redo简单的崩溃恢复过程
如何使用redo log、undo log简单崩溃恢复如下：
checkpoint是什么？
主要是为了加快crash 恢复的速度
checkpoint点 prepare状态
redo log undo log
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
change buffer
merge,undo purge
7. undo+redo简单的崩溃恢复过程
用checkpoint标记数据落盘到哪个位置
了，对于checkpoint之前的部分，
不需要再用redo log恢复，因为数据已经
落盘了，只需要应用checkpoint点之后
的部分.
prepare状态之前的事务，会直接回滚，
prepare状态的事务，如果binlog已经落盘，
则重新提交，否则回滚
checkpoint点 prepare状态
redo log undo log
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
change buffer
merge,undo purge
8. 控制redo log如何刷新的参数
主库参数优化：
Innodb_flush_log_at_trx_commit=1
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
9. Double write
double write 作用？避免部分写
double write 组成？内存和磁盘各两个1M组成
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
10. 目录
01 MySQL 崩溃恢复安全性
02 MySQL复制原理及异步、semi-sync复制
03 MySQL 主备复制如何保证数据一致性
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
11. MySQL的复制的应用场景
1 • 利用从库做读能力提升(读写分离)
2 • 利用从库做master故障的接管(故障切换)
3 • 利用从库做备份减少对业务的影响
4 • 利用从库做升级(升级MySQL版本或者升级业务数据库结构)
5 • 利用从库做特殊的SQL统计(如count,group by,sum)
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
12. MySQL 如何实现复制
Binlog Dump
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
13. 二阶段提交
sync-binlog=1
log-bin=mysql-bin
开启binary log
Innodb_support_xa=1
多线程并发执行提交事务，按照事务
的先后顺序写入binlog。
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
14. 三阶段提交
MySQL 5.6 引入BLGC（Binary Log Group Commit）
那么事务提交过程简化为：
存储引擎（InnoDB) Prepare ----> 数据库上层(Binary Log) Flush Stage ----> Sync Stage ----> 调存储引擎（InnoDB）
Commit stage
binlog_order_commits #控制事务的提交顺序，ON为和binlog的写入顺序一致，OFF为事务并行进行；默认为ON
sync_binlog=1 #在三阶段提交中，这个代表每次刷新一个队列的binlog到磁盘
binlog_max_flush_queue_time #控制在Flush stage中的等待时间，让Flush队列在此阶段多等待一些时间来增加这一组事务队列的数量使
该队列到Sync阶段可以一次fysnc()更多的事务
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
15. 三阶段提交
MySQL 5.6 引入BLGC（Binary Log Group Commit）
那么事务提交过程简化为：
存储引擎（InnoDB) Prepare ----> 数据库上层(Binary Log) Flush Stage ----> Sync Stage ----> 调存储引擎（InnoDB）
Commit stage
MySQL 5.7 Parallel replication基于主库的Binary Log Group Commit：
使用binlog_group_commit_sync_delay=N 和binlog_group_commit_sync_no_delay_count=N代替binlog_max_flush_queue_time
表示MySQL等待binlog_group_commit_sync_delay毫秒直到达到binlog_group_commit_sync_no_delay_count事务个数时，将进行一
次组提交
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
16. MySQL binlog的格式发展
MySQL5.1.5 MySQL5.1.5 MySQL5.1.8
之前 及其之后 及其之后
• 只支持statement • 新增支持row格 • 新增支持mixed
格式 式 格式
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
17. MySQL Binary log三种格式的区别
Mixed=statement+row：
mysql默认采用statement格式进行二进制日志文件的记录，但是在一些情况下会使用row格式，可能使用row格式的情况有：
1）表的存储引擎为NDB，这时对表的DML操作都会以row格式记录
2）使用了uuid(),user(),current_user(),found_rows(),row_count()等不确定函数
3）使用了insert delay语句
4）使用了用户自定义函数UDF
5）使用了临时表
Statement：
记录的是逻辑SQL
优点是日志量小
缺点是执行一些不确定的函数（如uuid(),now()）会出
现主从数据不一致
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
Row：
记录的不再是简单的SQL语句了(DDL还是记录的SQL)，
而是记录的表的行更改的情况
优点是解决了statement格式下主从数据不一致的问题，
所有数据都可以安全地复制
缺点是日志量大，影响从库日志的复制时间
18. Binary log格式-statement格式记录的内容
19. Binary log格式-row格式记录的内容
20. MySQL复制原理
Binlog Dump
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
1. Master节点配置
［mysqld］
log-bin=mysql-bin
server-id=1
2. Slave节点配置
［mysqld］
server-id=2
21. •Slave 查看复制详情show slave status\G
Mysql>show slave
status\G
Slave_IO_Running:
YES
Slave_SQL_Runnin
g:YES
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
22. 场景一：Master主库正常关机
从库重连间隔参数：master_connect_retry=10
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
23. 场景二：Master主库异常
MASTER
SLAVE
Internet
slave_net_timeout
master_connect_retry
master_retry_count
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
默认值3600秒
默认值60秒，change
master语句可设置
默认值86400次，change
master语句可设置
24. 官方半同步复制原理
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
25. 官方半同步复制原理
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
26. 5.7增强半同步原理
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
27. 开启semi-sync主备复制
主库Master安装插件semisync_master.so并配置my.cnf:
mysql> install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1 second
从库slave安装semisync_slave.so插件并配置my.cnf:
mysql> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
[mysqld]
rpl_semi_sync_slave_enabled=1
查看当前semi-sync变量设置：
mysql> SHOW VARIABLES LIKE ‘rpl_semi_sync%’；
查看当前semi-sync状态信息：
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
PS: 如果从库已经启动了异步复制，那么在配置了semi-sync之后，备库需要先stop slave io_thread;start slave
io_thread;然后再使用参数set global rpl_semi_sync_slave_enabled=1;重新打开半同步
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
28. 目录
01 MySQL 崩溃恢复安全性
02 MySQL复制原理及异步、semi-sync复制
03 MySQL 主备复制如何保证数据一致性
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
29. 从库崩溃恢复之后，如何知道从主库的哪个位置开始复制？
master.info
记录主机master连接信息、IO线程
读取到的当前主机Binary log文件名
和日志偏移量
master_info_repository=FILE时这些
信息记录到master.info文件中
relay_log.info
记录本地SQL 线程已经执行到的中
继日志名称和主库对应的Binary log
文件名、偏移量
relay_log_info_repository=FILE时这
些信息记录到relay_log.info文件中
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
30. IO线程和SQL线程的信息保存在文件中如何保证不丢失
双sync参数设置为1，又会导致从库性能差，且因为binlog
event的更新与保存这个位置之间并不是原子操作，所以就算
设置为1还是可能丢失这个位置
保存文件，崩溃时可能并
没有落盘，导致丢失
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
31. IO线程和SQL线程的信息保存在innodb表中如何保证不丢失
relay_log_recovery=ON
保存table，innodb表，可
利用innodb崩溃恢复特性
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
对于SQL线程信息保存在table中，mysql自身做了两个优化：
1、slave_relay_log_info表的update事务插入合并到SQL线程
执行binlog的事务中，通过这个优化可以做到实时更新SQL线程
位置，并且保证了两者之间是一个原子操作。
2、innodb本身支持group commit，更新slave_relay_log_info
也适用
32. MySQL数据一致性--回顾
innodb存储引擎自身通过redo log，
undo log实现数据的崩溃恢复安全性
，但是无法避免单点故障问题
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
33. MySQL数据一致性--回顾
如果不能容忍主库挂掉丢失数据的风险，建议使用5.7的semi-sync复制，但是性能可能下降的比较厉害
MASTER
SLAVE
基于binlog逻辑日志复制
log-bin=mysql-bin #主库打开写binlog功能
server-id = 1 #复制架构中全局唯一
binlog_format=row #row格式能保证主库执行类型row()函
数时，只把真正的变更复制到从库
innodb_flush_log_at_trx_commit=1 #保证redo 实时落盘
sync_binlog=1 #保证binlog实时落盘
innodb_support_xa=1 #保证binlog的写入顺序与事务提交
顺序一致
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
server-id = 2 #复制架构中全局唯一
relay_log_info_repository=TABLE #SQL线程对应主库的位置
可以实时更新到mysql.slave_relay_log_info中
relay_log_recovery=ON #从库崩溃恢复时，以表
mysql.slave_relay_log_info中保存的SQL线程位置为准，重新
读主库并生成新的relay log文件
master_info_repository=TABLE #设置了
relay_log_recovery=ON之后，这个参数可以保持默认
34. 提问&答疑
关亍知数堂培训
•
•
•
•
知数堂已开办2年多，现有学员超400多人
行业知名资深老师言传身教，教学质量可靠
提供各行业MySQL解决方案及数据库服务
致力推广互联网技术及其他优秀开源技术
全新MySQL DBA课程第八期已开课
全新Python运维开发班第二期8.27开课
QQ群：529671799
杭州沃趣科技股份有限公司
Hangzhou WOQU Technology Co., Ltd.
34
Hangzhou WOQU Technology Co., Ltd.
www.woqutech.com
0571 - 87770835