前言
在生产环境中,为了满足安全性,高可用性以及高并发等方面的需求,基本上采用的MySQL数据库架构都是MHA、MGR等,最低也得是一主一从的架构,搭配自动切换脚本,实现故障自动切换。
上述架构都是通过集群主从复制(Master-Slave)的方式来同步数据。
MySQL集群简单架构图:
说到主从同步,离不开binlog这个东西,先从binlog说起。
binlog
binlog是什么?有什么作用?
binlog是一个二进制文件,主要记录所有数据库表结构变更(例如CREATE、ALTER TABLE…)以及表数据修改(INSERT、UPDATE、DELETE…)的所有操作。
binlog 是 mysql 的逻辑日志,并且由 Server层进行记录,使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。在实际应用中, binlog 的主要使用场景有四个:
- 恢复(recovery):某些数据的恢复需要二进制日志。例如,在一个数据库全备文件恢复后,用户可以通过二进制日志进行point-in-time的恢复。
- 复制(replication):其原理与恢复类似,通过复制和执行二进制日志使一台远程的MySQL数据库(一般称为slave或者standby)与一台MySQL数据库(一般称为master或者primary)进行实时同步。
- 审计(audit):用户可以通过二进制日志中的信息来进行审计,判断是否有对数据库进行注入攻击。
- 用于数据备份,在数据库备份文件生成后,binlog保存了数据库备份后的详细信息,以便下一次备份能从备份点开始。
除了上面介绍的几个作用外,binlog对于事务存储引擎的崩溃恢复也有非常重要的作用。在开启binlog的情况下,为了保证binlog与redo的一致性,MySQL将采用事务的两阶段提交协议。
当MySQL系统发生崩溃时,事务在存储引擎内部的状态可能为prepared和commit两种。
对于prepared状态的事务,是进行提交操作还是进行回滚操作,这时需要参考binlog:如果事务在binlog中存在,那么将其提交;如果不在binlog中存在,那么将其回滚,这样就保证了数据在主库和从库之间的一致性。
日志格式
binlog 日志有三种格式,分别为 STATMENT 、 ROW 和 MIXED,日志格式通过 binlog-format 指定。详情如下:
- STATEMENTStatement 模式只记录执行的 SQL,不需要记录每一行数据的变化,因此极大的减少了 binlog 的日志量,避免了大量的 IO 操作,提升了系统的性能。但是,正是由于 Statement 模式只记录 SQL,而如果一些 SQL 中包含了函数,那么可能会出现执行结果不一致的情况。比如说 uuid() 函数,每次执行的时候都会生成一个随机字符串,在 master 中记录了 uuid,当同步到 slave 之后,再次执行,就获取到另外一个结果了。
- ROW从 MySQL5.1.5 版本开始,binlog 引入了 Row 格式,Row 格式不记录 SQL 语句上下文相关信息,仅仅只需要记录某一条记录被修改成什么样子了。Row 格式的日志内容会非常清楚的记录下每一行数据修改的细节,这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。不过 Row 格式也有一个很大的问题,那就是日志量太大了,特别是批量 update、整表 delete、alter 表等操作,由于要记录每一行数据的变化,此时会产生大量的日志,大量的日志也会带来 IO 性能问题。现在对于ROW格式的二进制日志基本是标配了,主要是因为它的优势远远大于缺点。并且由于ROW格式记录行数据,所以可以基于这种模式做一些DBA工具,比如数据恢复,不同数据库之间数据同步等。
- MIXED从 MySQL5.1.8 版开始,MySQL 又推出了 Mixed 格式,这种格式实际上就是 Statement 与 Row 的结合。在 Mixed 模式下,系统会自动判断该用 Statement 还是 Row:一般的语句修改使用 Statement 格式保存 binlog;对于一些 Statement 无法准确完成主从复制的操作,则采用 Row 格式保存 binlog。Mixed 模式中,MySQL 会根据执行的每一条具体的 SQL 语句来区别对待记录的日志格式,也就是在 Statement 和 Row 之间选择一种。
重要参数:sync_binlog
在MySQL 5.7之前版本默认情况下,二进制日志并不是在每次写的时候同步的磁盘(用户可以理解为缓冲写)。因此,当数据库所在的操作系统发生宕机时,可能会有最后一部分数据没有写入二进制文件中,这会给恢复和复制带来问题。参数sync_binlog=[N]中的N表示每提交多少个事务就进行binlog刷新到磁盘。如果将N设为1,即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志,每次事务提交时就会刷新binlog到磁盘;sync_binlog为0表示刷新binlog时间点由操作系统自身来决定,操作系统自身会每隔一段时间就会刷新缓存数据到磁盘;
如果使用Innodb存储引擎进行复制,并且想得到最大的高可用性,需要将此值设置为1。不过该值为1时,确时会对数据库IO系统带来一定的开销。但是,即使将sync_binlog设为1,还是会有一种情况导致问题的发生。当使用InnoDB存储引擎时,在一个事务发出COMMIT动作之前,由于sync_binlog为1,因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志,但是提交还没有发生,并且此时发生了宕机,那么在MySQL数据库下次启动时,由于COMMIT操作并没有发生,这个事务会被回滚掉。但是二进制日志已经记录了该事务信息,不能被回滚。
对于这个问题,MySQL使用了两阶段提交来解决的,简单说就是对于已经写入到binlog文件的事务一定会提交成功, 而没有写入到binlog文件的事务就会进行回滚,从而保证二进制日志和InnoDB存储引擎数据文件的一致性,保证主从复制的安全。
主从复制原理
mysql主从复制需要三个线程:master(binlog dump thread)、slave(I/O thread 、SQL thread)
复制基本过程(面试常考):
- 主库写入数据并且生成binlog文件。该过程中MySQL将事务串行的写入二进制日志,依赖binlog dump线程。
- 在事件写入二进制日志完成后,master通知存储引擎提交事务。
- 从库服务器上的IO线程连接Master服务器,请求从执行binlog日志文件中的指定位置开始读取binlog至从库。
- 主库接收到从库的IO线程请求后,其上复制的IO线程会根据Slave的请求信息分批读取binlog文件然后返回给从库的IO线程。
- Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后,会将binlog日志内容依次写到Slave端自身的Relay Log(即中继日志)文件的最末端,并将新的binlog文件名和位置记录到master-info文件中,以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容。
- 从库服务器的SQL线程会实时监测到本地Relay Log中新增了日志内容,然后把RelayLog中的日志翻译成SQL并且按照顺序执行SQL来更新从库的数据。
- 从库在relay-log.info中记录当前应用中继日志的文件名和位置点以便下一次数据复制。
并行复制
在MySQL 5.6版本之前,Slave服务器上有两个线程I/O线程和SQL线程。
I/O线程负责接收二进制日志,SQL线程进行回放二进制日志。如果在MySQL5.6版本开启并行复制功能,那么SQL线程就变为了coordinator线程,coordinator线程主要负责以上两部分的内容。
这意味着coordinator线程并不是仅将日志发送给worker线程,自己也可以回放日志,但是所有可以并行的操作交付由worker线程完成。
coordinator线程与worker是典型的生产者与消费者模型。
不过到MySQL 5.7才可称为真正的并行复制,这其中最为主要的原因就是slave服务器的回放与主机是一致的即master服务器上是怎么并行执行的slave上就怎样进行并行回放。不再有库的并行复制限制,对于二进制日志格式也无特殊的要求。
为了兼容MySQL 5.6基于库的并行复制,5.7引入了新的变量slave-parallel-type,其可以配置的值有:
- DATABASE:默认值,基于库的并行复制方式
- LOGICAL_CLOCK:基于组提交的并行复制方式
下面分别介绍下两种并行复制方式按库并行
每个 worker 线程对应一个 hash 表,用于保存当前正在这个worker的执行队列里的事务所涉及到的库。其中hash表里的key是数据库名,用于决定分发策略。该策略的优点是构建hash值快,只需要库名,同时对于binlog的格式没有要求。
但这个策略的效果,只有在主库上存在多个DB,且各个DB的压力均衡的情况下,这个策略效果好。因此,对于主库上的表都放在同一个DB或者不同DB的热点不同,则起不到多大效果
组提交优化
该特性如下:
- 能够同一组里提交的事务,定不会修改同一行;
- 主库上可以并行执行的事务,从库上也一定可以并行执行。
详细内容可以去官网看看:https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html
主从延迟
主从延迟是怎么回事?
根据前面主从复制的原理可以看出,两者之间是存在一定时间的数据不一致,也就是所谓的主从延迟。我们来看下导致主从延迟的时间点:
- 主库 A 执行完成一个事务,写入 binlog,该时刻记为T1.
- 传给从库B,从库接受完这个binlog的时刻记为T2.
- 从库B执行完这个事务,该时刻记为T3.
那么所谓主从延迟,就是同一个事务,从库执行完成的时间和主库执行完成的时间之间的差值,即T3-T1。我们也可以通过在从库执行show slave status,返回结果会显示seconds_behind_master,表示当前从库延迟了多少秒。seconds_behind_master如何计算的?
- 每一个事务的binlog都有一个时间字段,用于记录主库上写入的时间
- 从库取出当前正在执行的事务的时间字段,跟当前系统的时间进行相减,得到的就是seconds_behind_master,也就是前面所描述的T3-T1。
主从延迟原因
为什么会主从延迟?
正常情况下,如果网络不延迟,那么日志从主库传给从库的时间是相当短,所以T2-T1可以基本忽略。
最直接的影响就是从库消费中转日志(relaylog)的时间段,而造成原因一般是以下几种:
1、从库的机器性能比主库要差
比如将20台主库放在4台机器,把从库放在一台机器。这个时候进行更新操作,由于更新时会触发大量读操作,导致从库机器上的多个从库争夺资源,导致主从延迟。
不过,目前大部分部署都是采取主从使用相同规格的机器部署。
2、从库的压力大
按照正常的策略,读写分离,主库提供写能力,从库提供读能力。将进行大量查询放在从库上,结果导致从库上耗费了大量的CPU资源,进而影响了同步速度,造成主从延迟。
对于这种情况,可以通过一主多从,分担读压力;也可以采取binlog输出到外部系统,比如Hadoop,让外部系统提供查询能力。
3、大事务的执行
一旦执行大事务,那么主库必须要等到事务完成之后才会写入binlog。比如主库执行了一条insert … select非常大的插入操作,该操作产生了近几百G的binlog文件传输到只读节点,进而导致了只读节点出现应用binlog延迟。
因此,DBA经常会提醒开发,不要一次性地试用delete语句删除大量数据,尽可能控制数量,分批进行。
4、主库的DDL(alter、drop、create)
只读节点与主库的DDL同步是串行进行,如果DDL操作在主库执行时间很长,那么从库也会消耗同样的时间,比如在主库对一张500W的表添加一个字段耗费了10分钟,那么从节点上也会耗费10分钟。
从节点上有一个执行时间非常长的的查询正在执行,那么这个查询会堵塞来自主库的DDL,表被锁,直到查询结束为止,进而导致了从节点的数据延迟。
5、锁冲突
锁冲突问题也可能导致从节点的SQL线程执行慢,比如从机上有一些select …. for update的SQL,或者使用了MyISAM引擎等。
6、从库的复制能力
一般场景中,因偶然情况导致从库延迟了几分钟,都会在从库恢复之后追上主库。但若是从库执行速度低于主库,且主库持续具有压力,就会导致长时间主从延迟,很有可能就是从库复制能力的问题。
从库上的执行,即sql_thread更新逻辑,在5.6版本之前,是只支持单线程,那么在主库并发高、TPS高时,就会出现较大的主从延迟。
因此,MySQL自5.7版本后就已经支持并行复制了。可以在从服务上设置 slave_parallel_workers为一个大于0的数,然后把slave_parallel_type参数设置为LOGICAL_CLOCK,这就可以了。
怎么减少主从延迟
主从同步问题永远都是一致性和性能的权衡,得看实际的应用场景,若想要减少主从延迟的时间,可以采取下面的办法:
- 降低多线程大事务并发的概率,优化业务逻辑
- 优化SQL,避免慢SQL,减少批量操作,建议写脚本以update-sleep这样的形式完成。
- 提高从库机器的配置,减少主库写binlog和从库读binlog的效率差。
- 尽量采用短的链路,也就是主库和从库服务器的距离尽量要短,提升端口带宽,减少binlog传输的网络延时。
- 实时性要求的业务读强制走主库,从库只做灾备,备份。
主从延迟案例分析
问题背景:
有一套主从每到凌晨就出现延迟现象,需要排查原因。首先查看master库每天凌晨有什么操作:分析binlog日志
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000204 > mysql-bin.000204.sql
日志在凌晨有大量的delete操作
#191126 0:08:35 server id 1 end_log_pos 1073744234 CRC32 0x11bf4f5d Table_map: `user`.`table_name` mapped to number 4012691
# at 1073744234
#191126 0:08:35 server id 1 end_log_pos 1073744393 CRC32 0xa3229214 Delete_rows: table id 4012691 flags: STMT_END_F
### DELETE FROM `user`.`table_name`
### WHERE
### @1=259121
### @2='2019-11-25'
### @3=1
### @4=1
### @5=0
### @6='2019-11-25'
### @7='08:30'
### @8='2019-11-25'
### @9='17:30'
### @10=540
### @11=''
### @12=''
### @13=NULL
### @14=''
### @15=''
### @16=NULL
### @17=0
### @18=0
### @19=0
### @20=0
### @21=0
### @22=540
### @23=0
### @24=0
### @25=0
### @26=0
### @27=0
### @28='{}'
# at 1073744393
#191126 0:08:35 server id 1 end_log_pos 1073744424 CRC32 0x5a03e7aa Xid = 25909247548
判断为大量的delete操作产出大量的binlog日志,slave应用不过来。
一般而言,slave相对master延迟较大,其根本原因就是slave上的复制线程没办法真正做到并发。简单说就是在master上是并发模式(以InnoDB引擎为主)完成事务提交的,而在slave上,复制线程只有一个sql thread用于binlog的apply,所以slave在高并发时会远落后master。
查看slave复制方式:
mysql> show variables like"%slave_parallel%";
+-------------------------------------------+----------------------+
| Variable_name | Value |
+-------------------------------------------+----------------------+
| slave_parallel_type | DATABASE |
| slave_parallel_workers | 0 |
+-------------------------------------------+----------------------+
2 rows in set (0.00 sec)
当前的复制类型是 DATABASE,也就是统一数据库下只有一个线程进行复制,不能并行复制。当前并行工作的进程数是 0.配置从服务器上的多线程并行复制的参数(此处为实现多线程复制的重要参数)在数据库配置文件 my.cnf中设置
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16 #16为设置的并发线程个数,之后根据项目对数据传输的具体要求再更改
#一个schema下,slave_parallel_workers中的worker线程并发执行relay log中主库提交的事务
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
注:变量slave-parallel-type可以有两个值DATABASE 为默认值,意为基于库的并行复制方式;LOGICAL_CLOCK:基于组提交的并行复制方式
更多精彩内容,关注我们▼▼