知数堂-罗小波-MySQL数据一致性

2020-02-23 290浏览

  • 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