一、日志系统
redo log(重做日志):
当有一条记录需要更新的时候,InnoDB 引擎就会先把记录写到 redo log里面,并更新内存,同时InnoDB 引擎会在适当的时候,将这个操作记录更新到磁盘里面。
WAL(Write-Ahead Logging):关键点即先写日志,再写磁盘。将磁盘的随机写变成了顺序写。
WAL 机制主要得益于两个方面:
1、redo log 和 binlog 都是顺序写,磁盘的顺序写比随机写速度要快
2、组提交机制,可以大幅度降低磁盘的 IOPS 消耗。
crash safe:
1、有了 redo log,InnoDB 就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为 crash-safe。
2、innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候,表示每次事务的 redo log 都直接持久化到磁盘。
redo log保证了事务ACID的特性
redo log 的写入机制:
1、InnoDB 有一个后台线程,每隔 1 秒,就会把 redo log buffer 中的日志,调用 write 写到文件系统的 page cache,然后调用 fsync 持久化到磁盘。
2、redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候,后台线程会主动写盘。
3、并行的事务提交的时候,顺带将这个事务的 redo log buffer 持久化到磁盘。
binlog(归档日志)
由Server层实现,记录的是语句的原始逻辑,比如给ID=1的行的状态设置成1,追加写,上个文件写完回切换成下一个文件,类似滚动日志。
sync_binlog 这个参数设置成 1 的时候,表示每次事务的 binlog 都持久化到磁盘。
binlog的写入逻辑
1、事务执行过程中,先把日志写到 binlog cache,事务提交的时候,再把 binlog cache 写到 磁盘上的binlog 文件中。
2、调用 fsync 持久化。
binlog 是不能“被打断的”。一个事务的 binlog 必须连续写,因此要整个事务完成后,再一起写到文件里。
binlog和redolog的不同点有哪些:
1、redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用。
2、redo log 是物理日志,记录的是“在某个数据页上做了什么修改”;binlog 是逻辑日志,记录的是这个语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1 ”。
3、redo log 是循环写的,空间固定会用完;binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。
两阶段提交:
为了解决redo log和binlog不一致问题的,这里的不一致是指redo log写成功binlog写失败了,由于恢复是根据binlog恢复的,这样恢复的时候就会少一条更新操作,导致和线上库的数据不一致。具体而言,两阶段是指prepare阶段和commit阶段,写完redo log进入prepare阶段,写完binlog进入commit阶段。
脏页:内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。
InnoDB 刷脏页的控制策略:
1、设置 innodb_io_capacity 参数,建议设置成磁盘的 IOPS。
2、关注脏页比例,不要让它经常接近 75%。脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的。
3、如果使用的是 SSD,把 innodb_flush_neighbors 的值设置成 0,因为这时候 IOPS 往往不是瓶颈,而“只刷自己”,就能更快地执行完必要的刷脏页操作,减少 SQL 语句响应时间。
MySQL 的“双 1”配置:
sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说,一个事务完整提交前,需要等待两次刷盘,一次是 redo log(prepare 阶段),一次是 binlog。
二、主备相关
将备库设置成只读(readonly)模式, readonly 设置对超级 (super) 权限用户是无效的,而用于同步更新的线程,就拥有超级权限。
一个事务日志同步的完整过程:
1、在备库 B 上通过 change master 命令,设置主库 A 的 IP、端口、用户名、密码,以及要从哪个位置开始请求 binlog,这个位置包含文件名和日志偏移量。
2、在备库 B 上执行 start slave 命令,这时候备库会启动两个线程,就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
3、主库 A 校验完用户名、密码后,开始按照备库 B 传过来的位置,从本地读取 binlog,发给 B。
4、备库 B 拿到 binlog 后,写到本地文件,称为中转日志(relay log)。
5、sql_thread 读取中转日志,解析出日志里的命令,并执行。
binlog 的三种格式对比:
statement:当 binlog_format=statement 时,binlog 里面记录的就是 SQL 语句的原文
row:当 binlog_format=statement 时,便于delete、insert 和 update后的数据恢复
mixed:statement与row格式的混合
实际生产上使用比较多的是双 M 结构,即:节点 A 和 B 之间总是互为主备关系。这样在切换的时候就不用再修改主备关系。
如何解决循环复制问题:
1、规定两个库的 server id 必须不同,如果相同,则它们之间不能设定为主备关系;
2、一个备库接到 binlog 并在重放的过程中,生成与原 binlog 的 server id 相同的新的 binlog;
3、每个库在收到从自己的主库发过来的日志后,先判断 server id,如果跟自己的相同,表示这个日志是自己生成的,就直接丢弃这个日志。
主备延迟的来源:
1、有些部署条件下,备库所在机器的性能要比主库所在的机器性能差。
配置”双1“,redo log和binlog都只write fs page cache
2、备库的压力大
一主多从。除了备库外,可以多接几个从库,让这些从库来分担读的压力。(多从库适合做全量备份)
通过 binlog 输出到外部系统,比如 Hadoop 这类系统,让外部系统提供统计类查询的能力。
3、大事务。如:大表 DDL
尽量减少大事务,比如delete操作,使用limit分批删除,可以防止大事务也可以减少锁的范围。
4、备库的并行复制能力
主备延迟对应策略 :
- 可靠性优先策略
1、判断备库 B 现在的 seconds_behind_master如果小于某个值(比如 5 秒)继续下一步,否则持续重试这一步
2、把主库 A 改成只读状态,即把 readonly 设置为 true
3、判断备库 B 的 seconds_behind_master的值,直到这个值变成 0 为止
4、把备库 B 改成可读写状态,也就是把 readonly 设置为 false
5、把业务请求切到备库 B
- 可用性优先策略
出现的问题:在双m,且binlog_format=mixed,会导致主备数据不一致,使用使用 row 格式的 binlog 时,数据不一致的问题更容易发现,因为binlog row会记录字段的所有值。
——整理自丁奇老师专栏《MySQL实战45讲》