目录
读写分离简介
what 什么是读写分离?
读写分离,基本的原理是让主数据库处理事务性增、改、删操作(INSERT、UPDATE、DELETE),而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
why 为什么要读写分离呢?
因为数据库的“写”(写10000条数据到oracle可能要3分钟)操作是比较耗时的。
但是数据库的“读”(从oracle读10000条数据可能只要5秒钟)。
所以读写分离,解决的是,数据库的写入,影响了查询的效率。
when 什么时候要读写分离?
数据库不一定要读写分离,如果程序使用数据库较多时,而更新少,查询多的情况下会考虑使用,利用数据库主从同步 。可以减少数据库压力,提高性能。当然,数据库也有其它优化方案。memcache 或是 表折分,或是搜索引擎。都是解决方法。
主从复制与读写分离
在实际的生产环境中,对数据库的读和写都在同一个数据库服务器中,是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此,通过主从复制的方式来同步数据,再通过读写分离来提升数据库的并发负载能力。有点类似于前面我们学习过的rsync,但是不同的是rsync是对磁盘文件做备份,而mysql主从复制是对数据库中的数据、语句做备份。
mysql读写分离
mysq支持的复制类型
1) 基于语句的复制。在服务器上执行sql语句,在从服务器上执行同样的语句,mysql默认采用基于语句的复制,执行效率高。
2) 基于行的复制。把改变的内容复制过去,而不是把命令在从服务器上执行一遍。
3) 混合类型的复制。默认采用基于语句的复制,一旦发现基于语句无法精确复制时,就会采用基于行的复制。
复制的工作过程
1) 在每个事务更新数据完成之前,master在二进制日志记录这些改变。写入二进制日志完成后,master通知存储引擎提交事务。
2) Slave将master的binary log复制到其中继日志。首先slave开始一个工作线程(I/O),I/O线程在master上打开一个普通的连接,然后开始binlog dump process。binlog dump process从master的二进制日志中读取事件,如果已经跟上master,它会睡眠并等待master产生新的事件,I/O线程将这些事件写入中继日志。
3) Sql slave thread(sql从线程)处理该过程的最后一步,sql线程从中继日志读取事件,并重放其中的事件而更新slave数据,使其与master中的数据一致,只要该线程与I/O线程保持一致,中继日志通常会位于os缓存中,所以中继日志的开销很小。
mysql读写分离原理
读写分离就是在主服务器上修改,数据会同步到从服务器,从服务器只能提供读取数据,不能写入,实现备份的同时也实现了数据库性能的优化,以及提升了服务器安全。
常见的Mysql读写分离
基于程序代码内部实现
在代码中根据select 、insert进行路由分类,这类方法也是目前生产环境下应用最广泛的。优点是性能较好,因为程序在代码中实现,不需要增加额外的硬件开支,缺点是需要开发人员来实现,运维人员无从下手。
基于中间代理层实现
代理一般介于应用服务器和数据库服务器之间,代理数据库服务器接收到应用服务器的请求后根据判断后转发到,后端数据库,有以下代表性的程序。
(1)mysql_proxy。mysql_proxy是Mysql的一个开源项目,通过其自带的lua脚本进行sql判断。
(2)Atlas。是由 Qihoo 360, Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上,对其进行了优化,增加了一些新的功能特性。360内部使用Atlas运行的mysql业务,每天承载的读写请求数达几十亿条。支持事物以及存储过程。
(3)Amoeba。由阿里巴巴集团在职员工陈思儒使用序java语言进行开发,阿里巴巴集团将其用户生产环境下,但是他并不支持事物以及存储过程。
经过上述简单的比较,不是所有的应用都能够在基于程序代码中实现读写分离,像一些大型的java应用,如果在程序代码中实现读写分离对代码的改动就较大,所以,像这种应用一般会考虑使用代理层来实现,那么今天就使用Amoeba为例,完成主从复制和读写分离。
mysql主备一致
mysql主备的基本原理
在状态1中,客户端的读写都直接访问节点A,而节点B是A的备库,只是将A的更新都同步过来,到本地执行。这样可以保持节点B和A的数据是相同的。
当需要切换的时候,就切成状态2。这时候客户端读写访问的都是节点B,而节点A是B的备库。
在状态1中,虽然节点B没有被直接访问,但是我依然建议你把节点B(也就是备库)设置成只读(readonly)模式。这样做,有以下几个考虑:
1. 有时候一些运营类的查询语句会被放到备库上去查,设置为只读可以防止误操作;
2. 防止切换逻辑有bug,比如切换过程中出现双写,造成主备不一致;
3. 可以用readonly状态,来判断节点的角色。
你可能会问,我把备库设置成只读了,还怎么跟主库保持同步更新呢?
这个问题,你不用担心。因为readonly设置对超级(super)权限用户是无效的,而用于同步更新的线程,就拥有超级权限。
接下来,我们再看看节点A到B这条线的内部流程是什么样的。图2中画出的就是一个update语句在节点A执行,然后同步到节点B的完整流程图。
图2中,包含了我在上一篇文章中讲到的binlog和redo log的写入机制相关的内容,可以看到:主库接收到客户端的更新请求后,执行内部事务的更新逻辑,同时写binlog。
备库B跟主库A之间维持了一个长连接。主库A内部有一个线程,专门用于服务备库B的这个长连接。一个事务日志同步的完整过程是这样的:
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读取中转日志,解析出日志里的命令,并执行。
这里需要说明,后来由于多线程复制方案的引入,sql_thread演化成为了多个线程,跟我们今天要介绍的原理没有直接关系,暂且不展开。
分析完了这个长连接的逻辑,我们再来看一个问题:binlog里面到底是什么内容,为什么备库拿过去可以直接执行。
binlog的三种格式对比
binlog有两种格式,一种是statement,一种是row。可能你在其他资料上还会看到有第三种格式,叫作mixed,其实它就是前两种格式的混合。
为了便于描述binlog的这三种格式间的区别,我创建了一个表,并初始化几行数据。
mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;
insert into t values(1,1,'2018-11-13');
insert into t values(2,2,'2018-11-12');
insert into t values(3,3,'2018-11-11');
insert into t values(4,4,'2018-11-10');
insert into t values(5,5,'2018-11-09');
如果要在表中删除一行数据的话,我们来看看这个delete语句的binlog是怎么记录的。
注意,下面这个语句包含注释,如果你用MySQL客户端来做这个实验的话,要记得加-c参数,否则客户端会自动去掉注释。
mysql> delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;
当binlog_format=statement时,binlog里面记录的就是SQL语句的原文。你可以用
mysql> show binlog events in 'master.000001';
命令看binlog中的内容。
现在,我们来看一下图3的输出结果。
第一行SET @@SESSION.GTID_NEXT='ANONYMOUS’你可以先忽略,后面文章我们会在介绍主备切换的时候再提到;
第二行是一个BEGIN,跟第四行的commit对应,表示中间是一个事务;
第三行就是真实执行的语句了。可以看到,在真实执行的delete命令之前,还有一个“use‘test’”命令。这条命令不是我们主动执行的,而是MySQL根据当前要操作的表所在的数据库,自行添加的。这样做可以保证日志传到备库去执行的时候,不论当前的工作线程在哪个库里,都能够正确地更新到test库的表t。
use 'test’命令之后的delete 语句,就是我们输入的SQL原文了。可以看到,binlog“忠实”地记录了SQL命令,甚至连注释也一并记录了。
最后一行是一个COMMIT。你可以看到里面写着xid=61。你还记得这个XID是做什么用的吗?
如果记忆模糊了,可以再回顾一下第15篇文章中的相关内容。
为了说明statement 和 row格式的区别,我们来看一下这条delete命令的执行效果图:
可以看到,运行这条delete命令产生了一个warning,原因是当前binlog设置的是statement格式,并且语句中有limit,所以这个命令可能是unsafe的。
为什么这么说呢?这是因为delete 带limit,很可能会出现主备数据不一致的情况。比如上面这个例子:
1. 如果delete语句使用的是索引a,那么会根据索引a找到第一个满足条件的行,也就是说删除的是a=4这一行;
2. 但如果使用的是索引t_modified,那么删除的就是 t_modified='2018-11-09’也就是a=5这一行。
由于statement格式下,记录到binlog里的是语句原文,因此可能会出现这样一种情况:在主库执行这条SQL语句的时候,用的是索引a;而在备库执行这条SQL语句的时候,却使用了索引t_modified。因此,MySQL认为这样写是有风险的。
那么,如果我把binlog的格式改为binlog_format=‘row’, 是不是就没有这个问题了呢?我们先来看看这时候binog中的内容吧。
可以看到,与statement格式的binlog相比,前后的BEGIN和COMMIT是一样的。但是,row格式的binlog里没有了SQL语句的原文,而是替换成了两个event:Table_map和Delete_rows。
1. Table_map event,用于说明接下来要操作的表是test库的表t;
2. Delete_rows event,用于定义删除的行为。
其实,我们通过图5是看不到详细信息的,还需要借助mysqlbinlog工具,用下面这个命令解析和查看binlog中的内容。因为图5中的信息显示,这个事务的binlog是从8900这个位置开始的,所以可以用start-position参数来指定从这个位置的日志开始解析。
mysqlbinlog -vv data/master.000001 --start-position=8900;
从这个图中,我们可以看到以下几个信息:
server id 1,表示这个事务是在server_id=1的这个库上执行的。
每个event都有CRC32的值,这是因为我把参数binlog_checksum设置成了CRC32。
Table_map event跟在图5中看到的相同,显示了接下来要打开的表,map到数字226。现在我们这条SQL语句只操作了一张表,如果要操作多张表呢?每个表都有一个对应的Table_map event、都会map到一个单独的数字,用于区分对不同表的操作。
我们在mysqlbinlog的命令中,使用了-vv参数是为了把内容都解析出来,所以从结果里面可以看到各个字段的值(比如,@1=4、 @2=4这些值)。
binlog_row_image的默认配置是FULL,因此Delete_event里面,包含了删掉的行的所有字段的值。如果把binlog_row_image设置为MINIMAL,则只会记录必要的信息,在这个例子里,就是只会记录id=4这个信息。
最后的Xid event,用于表示事务被正确地提交了。
你可以看到,当binlog_format使用row格式的时候,binlog里面记录了真实删除行的主键id,这样binlog传到备库去的时候,就肯定会删除id=4的行,不会有主备删除不同行的问题。
为什么会有mixed格式的binlog?
基于上面的信息,我们来讨论一个问题:为什么会有mixed这种binlog格式的存在场景?推论过程是这样的:
因为有些statement格式的binlog可能会导致主备不一致,所以要使用row格式。
但row格式的缺点是,很占空间。比如你用一个delete语句删掉10万行数据,用statement的话就是一个SQL语句被记录到binlog中,占用几十个字节的空间。但如果用row格式的binlog,就要把这10万条记录都写到binlog中。这样做,不仅会占用更大的空间,同时写binlog也要耗费IO资源,影响执行速度。
所以,MySQL就取了个折中方案,也就是有了mixed格式的binlog。mixed格式的意思是,MySQL自己会判断这条SQL语句是否可能引起主备不一致,如果有可能,就用row格式,否则就用statement格式。
也就是说,mixed格式可以利用statment格式的优点,同时又避免了数据不一致的风险。
因此,如果你的线上MySQL设置的binlog格式是statement的话,那基本上就可以认为这是一个不合理的设置。你至少应该把binlog的格式设置为mixed。
比如我们这个例子,设置为mixed后,就会记录为row格式;而如果执行的语句去掉limit 1,就会记录为statement格式。
当然我要说的是,现在越来越多的场景要求把MySQL的binlog格式设置成row。这么做的理由有很多,我来给你举一个可以直接看出来的好处:恢复数据。
接下来,我们就分别从delete、insert和update这三种SQL语句的角度,来看看数据恢复的问题。
通过图6你可以看出来,即使我执行的是delete语句,row格式的binlog也会把被删掉的行的整行信息保存起来。所以,如果你在执行完一条delete语句以后,发现删错数据了,可以直接把binlog中记录的delete语句转成insert,把被错删的数据插入回去就可以恢复了。
如果你是执行错了insert语句呢?那就更直接了。row格式下,insert语句的binlog里会记录所有的字段信息,这些信息可以用来精确定位刚刚被插入的那一行。这时,你直接把insert语句转成delete语句,删除掉这被误插入的一行数据就可以了。
如果执行的是update语句的话,binlog里面会记录修改前整行的数据和修改后的整行数据。所以,如果你误执行了update语句的话,只需要把这个event前后的两行信息对调一下,再去数据库里面执行,就能恢复这个更新操作了。
其实,由delete、insert或者update语句导致的数据操作错误,需要恢复到操作之前状态的情况,也时有发生。MariaDB的Flashback工具就是基于上面介绍的原理来回滚数据的。
虽然mixed格式的binlog现在已经用得不多了,但这里我还是要再借用一下mixed格式来说明一个问题,来看一下这条SQL语句:
mysql> insert into t values(10,10, now());
如果我们把binlog格式设置为mixed,你觉得MySQL会把它记录为row格式还是statement格式呢?
先不要着急说结果,我们一起来看一下这条语句执行的效果。
可以看到,MySQL用的居然是statement格式。你一定会奇怪,如果这个binlog过了1分钟才传给备库的话,那主备的数据不就不一致了吗?
接下来,我们再用mysqlbinlog工具来看看:
从图中的结果可以看到,原来binlog在记录event的时候,多记了一条命令:SET TIMESTAMP=1546103491。它用 SET TIMESTAMP命令约定了接下来的now()函数的返回时间。
因此,不论这个binlog是1分钟之后被备库执行,还是3天后用来恢复这个库的备份,这个insert语句插入的行,值都是固定的。也就是说,通过这条SET TIMESTAMP命令,MySQL就确保了主备数据的一致性。
我之前看过有人在重放binlog数据的时候,是这么做的:用mysqlbinlog解析出日志,然后把里面的statement语句直接拷贝出来执行。
你现在知道了,这个方法是有风险的。因为有些语句的执行结果是依赖于上下文命令的,直接执行的结果很可能是错误的。
所以,用binlog来恢复数据的标准做法是,用 mysqlbinlog工具解析出来,然后把解析结果整个发给MySQL执行。类似下面的命令:
mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;
这个命令的意思是,将 master.000001 文件里面从第2738字节到第2973字节中间这段内容解析
出来,放到MySQL去执行。
循环复制问题
通过上面对MySQL中binlog基本内容的理解,你现在可以知道,binlog的特性确保了在备库执行
相同的binlog,可以得到与主库相同的状态。
因此,我们可以认为正常情况下主备的数据是一致的。也就是说,图1中A、B两个节点的内容是
一致的。其实,图1中我画的是M-S结构,但实际生产上使用比较多的是双M结构,也就是图9所
示的主备切换流程。
对比图9和图1,你可以发现,双M结构和M-S结构,其实区别只是多了一条线,即:节点A和B之间总是互为主备关系。这样在切换的时候就不用再修改主备关系。
但是,双M结构还有一个问题需要解决。
业务逻辑在节点A上更新了一条语句,然后再把生成的binlog 发给节点B,节点B执行完这条更新语句后也会生成binlog。(我建议你把参数log_slave_updates设置为on,表示备库执行relay log后生成binlog)。
那么,如果节点A同时是节点B的备库,相当于又把节点B新生成的binlog拿过来执行了一次,然后节点A和B间,会不断地循环执行这个更新语句,也就是循环复制了。这个要怎么解决呢?
从上面的图6中可以看到,MySQL在binlog中记录了这个命令第一次执行时所在实例的server id。因此,我们可以用下面的逻辑,来解决两个节点间的循环复制的问题:
1. 规定两个库的server id必须不同,如果相同,则它们之间不能设定为主备关系;
2. 一个备库接到binlog并在重放的过程中,生成与原binlog的server id相同的新的binlog;
3. 每个库在收到从自己的主库发过来的日志后,先判断server id,如果跟自己的相同,表示这个日志是自己生成的,就直接丢弃这个日志。
按照这个逻辑,如果我们设置了双M结构,日志的执行流就会变成这样:
1. 从节点A更新的事务,binlog里面记的都是A的server id;
2. 传到节点B执行一次以后,节点B生成的binlog 的server id也是A的server id;
3. 再传回给节点A,A判断到这个server id与自己的相同,就不会再处理这个日志。所以,死循环在这里就断掉了。
主备延迟
主备延迟简介
主备切换可能是一个主动运维动作,比如软件升级、主库所在机器按计划下线等,也可能是被动操作,比如主库所在机器掉电。
接下来,我们先一起看看主动切换的场景。
在介绍主动切换流程的详细步骤之前,我要先跟你说明一个概念,即“同步延迟”。与数据同步有关的时间点主要包括以下三个:
1. 主库A执行完成一个事务,写入binlog,我们把这个时刻记为T1;
2. 之后传给备库B,我们把备库B接收完这个binlog的时刻记为T2;
3. 备库B执行完成这个事务,我们把这个时刻记为T3。
所谓主备延迟,就是同一个事务,在备库执行完成的时间和主库执行完成的时间之间的差值,也就是T3-T1。
你可以在备库上执行show slave status命令,它的返回结果里面会显示seconds_behind_master,用于表示当前备库延迟了多少秒。
seconds_behind_master的计算方法是这样的:
1. 每个事务的binlog 里面都有一个时间字段,用于记录主库上写入的时间;
2. 备库取出当前正在执行的事务的时间字段的值,计算它与当前系统时间的差值,得到seconds_behind_master。
可以看到,其实seconds_behind_master这个参数计算的就是T3-T1。所以,我们可以用seconds_behind_master来作为主备延迟的值,这个值的时间精度是秒。
你可能会问,如果主备库机器的系统时间设置不一致,会不会导致主备延迟的值不准?
其实不会的。因为,备库连接到主库的时候,会通过执行SELECT UNIX_TIMESTAMP()函数来获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致,备库在执行seconds_behind_master计算的时候会自动扣掉这个差值。
需要说明的是,在网络正常的时候,日志从主库传给备库所需的时间是很短的,即T2-T1的值是非常小的。也就是说,网络正常情况下,主备延迟的主要来源是备库接收完binlog和执行完这个事务之间的时间差。
所以说,主备延迟最直接的表现是,备库消费中转日志(relay log)的速度,比主库生产binlog的速度要慢。接下来,我就和你一起分析下,这可能是由哪些原因导致的。
主备延迟的来源
首先,有些部署条件下,备库所在机器的性能要比主库所在的机器性能差。
一般情况下,有人这么部署时的想法是,反正备库没有请求,所以可以用差一点儿的机器。或者,他们会把20个主库放在4台机器上,而把备库集中在一台机器上。
其实我们都知道,更新请求对IOPS的压力,在主库和备库上是无差别的。所以,做这种部署时,一般都会将备库设置为“非双1”的模式。
但实际上,更新过程中也会触发大量的读操作。所以,当备库主机上的多个备库都在争抢资源的时候,就可能会导致主备延迟了。
当然,这种部署现在比较少了。因为主备可能发生切换,备库随时可能变成主库,所以主备库选用相同规格的机器,并且做对称部署,是现在比较常见的情况。
追问1:但是,做了对称部署以后,还可能会有延迟。这是为什么呢?
这就是第二种常见的可能了,即备库的压力大。一般的想法是,主库既然提供了写能力,那么备库可以提供一些读能力。或者一些运营后台需要的分析语句,不能影响正常业务,所以只能在备库上跑。
我真就见过不少这样的情况。由于主库直接影响业务,大家使用起来会比较克制,反而忽视了备库的压力控制。结果就是,备库上的查询耗费了大量的CPU资源,影响了同步速度,造成主备延迟。
这种情况,我们一般可以这么处理:
1. 一主多从。除了备库外,可以多接几个从库,让这些从库来分担读的压力。
2. 通过binlog输出到外部系统,比如Hadoop这类系统,让外部系统提供统计类查询的能力。
其中,一主多从的方式大都会被采用。因为作为数据库系统,还必须保证有定期全量备份的能力。而从库,就很适合用来做备份。
追问2:采用了一主多从,保证备库的压力不会超过主库,还有什么情况可能导致主备延迟吗?
这就是第三种可能了,即大事务。
大事务这种情况很好理解。因为主库上必须等事务执行完成才会写入binlog,再传给备库。所以,如果一个主库上的语句执行10分钟,那这个事务很可能就会导致从库延迟10分钟。
不知道你所在公司的DBA有没有跟你这么说过:不要一次性地用delete语句删除太多数据。其实,这就是一个典型的大事务场景。
比如,一些归档类的数据,平时没有注意删除历史数据,等到空间快满了,业务开发人员要一次性地删掉大量历史数据。同时,又因为要避免在高峰期操作会影响业务(至少有这个意识还是很不错的),所以会在晚上执行这些大量数据的删除操作。
结果,负责的DBA同学半夜就会收到延迟报警。然后,DBA团队就要求你后续再删除数据的时候,要控制每个事务删除的数据量,分成多次删除。
另一种典型的大事务场景,就是大表DDL。这个场景,我在前面的文章中介绍过。处理方案就是,计划内的DDL,建议使用ghost方案。
追问3:如果主库上也不做大事务了,还有什么原因会导致主备延迟吗?
造成主备延迟还有一个大方向的原因,就是备库的并行复制能力。
备注:这里需要说明一下,从库和备库在概念上其实差不多。在我们这里,为了方便描述,我把会在HA过程中被选成新主库的,称为备库,其他的称为从库。
由于主备延迟的存在,所以在主备切换的时候,就相应的有不同的策略。
可靠性优先策略
在图1的双M结构下,从状态1到状态2切换的详细过程是这样的:
1. 判断备库B现在的seconds_behind_master,如果小于某个值(比如5秒)继续下一步,否则持续重试这一步;
2. 把主库A改成只读状态,即把readonly设置为true;
3. 判断备库B的seconds_behind_master的值,直到这个值变成0为止;
4. 把备库B改成可读写状态,也就是把readonly 设置为false;
5. 把业务请求切到备库B。
这个切换流程,一般是由专门的HA系统来完成的,我们暂时称之为可靠性优先流程。
备注:图中的SBM,是seconds_behind_master参数的简写。
可以看到,这个切换流程中是有不可用时间的。因为在步骤2之后,主库A和备库B都处于readonly状态,也就是说这时系统处于不可写状态,直到步骤5完成后才能恢复。
在这个不可用状态中,比较耗费时间的是步骤3,可能需要耗费好几秒的时间。这也是为什么需要在步骤1先做判断,确保seconds_behind_master的值足够小。
试想如果一开始主备延迟就长达30分钟,而不先做判断直接切换的话,系统的不可用时间就会长达30分钟,这种情况一般业务都是不可接受的。
当然,系统的不可用时间,是由这个数据可靠性优先的策略决定的。你也可以选择可用性优先的策略,来把这个不可用时间几乎降为0。
可用性优先策略
如果我强行把步骤4、5调整到最开始执行,也就是说不等主备数据同步,直接把连接切到备库B,并且让备库B可以读写,那么系统几乎就没有不可用时间了。
我们把这个切换流程,暂时称作可用性优先流程。这个切换流程的代价,就是可能出现数据不一致的情况。
接下来,我就和你分享一个可用性优先流程产生数据不一致的例子。假设有一个表 t:
mysql> CREATE TABLE `t` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`c` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(c) values(1),(2),(3);
这个表定义了一个自增主键id,初始化数据后,主库和备库上都是3行数据。接下来,业务人员
要继续在表t上执行两条插入语句的命令,依次是:
insert into t(c) values(4);
insert into t(c) values(5);
假设,现在主库上其他的数据表有大量的更新,导致主备延迟达到5秒。在插入一条c=4的语句后,发起了主备切换。
图是可用性优先策略,且binlog_format=mixed时的切换流程和数据结果。
现在,我们一起分析下这个切换流程:
1. 步骤2中,主库A执行完insert语句,插入了一行数据(4,4),之后开始进行主备切换。
2. 步骤3中,由于主备之间有5秒的延迟,所以备库B还没来得及应用“插入c=4”这个中转日志,就开始接收客户端“插入 c=5”的命令。
3. 步骤4中,备库B插入了一行数据(4,5),并且把这个binlog发给主库A。
4. 步骤5中,备库B执行“插入c=4”这个中转日志,插入了一行数据(5,4)。而直接在备库B执行的“插入c=5”这个语句,传到主库A,就插入了一行新数据(5,5)。
最后的结果就是,主库A和备库B上出现了两行不一致的数据。可以看到,这个数据不一致,是由可用性优先流程导致的。
那么,如果我还是用可用性优先策略,但设置binlog_format=row,情况又会怎样呢?
因为row格式在记录binlog的时候,会记录新插入的行的所有字段值,所以最后只会有一行不一致。而且,两边的主备同步的应用线程会报错duplicate key error并停止。也就是说,这种情况下,备库B的(5,4)和主库A的(5,5)这两行数据,都不会被对方执行。
图中我画出了详细过程,你可以自己再分析一下。
从上面的分析中,你可以看到一些结论:
1. 使用row格式的binlog时,数据不一致的问题更容易被发现。而使用mixed或者statement格式的binlog时,数据很可能悄悄地就不一致了。如果你过了很久才发现数据不一致的问题,很可能这时的数据不一致已经不可查,或者连带造成了更多的数据逻辑不一致。
2. 主备切换的可用性优先策略会导致数据不一致。因此,大多数情况下,我都建议你使用可靠性优先策略。毕竟对数据服务来说的话,数据的可靠性一般还是要优于可用性的。
但事无绝对,有没有哪种情况数据的可用性优先级更高呢?
答案是,有的。
我曾经碰到过这样的一个场景:
有一个库的作用是记录操作日志。这时候,如果数据不一致可以通过binlog来修补,而这个短暂的不一致也不会引发业务问题。
同时,业务系统依赖于这个日志写入逻辑,如果这个库不可写,会导致线上的业务操作无法执行。
这时候,你可能就需要选择先强行切换,事后再补数据的策略。
当然,事后复盘的时候,我们想到了一个改进措施就是,让业务逻辑不要依赖于这类日志的写入。也就是说,日志写入这个逻辑模块应该可以降级,比如写到本地文件,或者写到另外一个临时库里面。
这样的话,这种场景就又可以使用可靠性优先策略了。
接下来我们再看看,按照可靠性优先的思路,异常切换会是什么效果?
假设,主库A和备库B间的主备延迟是30分钟,这时候主库A掉电了,HA系统要切换B作为主库。我们在主动切换的时候,可以等到主备延迟小于5秒的时候再启动切换,但这时候已经别无选择了。
采用可靠性优先策略的话,你就必须得等到备库B的seconds_behind_master=0之后,才能切换。但现在的情况比刚刚更严重,并不是系统只读、不可写的问题了,而是系统处于完全不可用的状态。因为,主库A掉电后,我们的连接还没有切到备库B。
你可能会问,那能不能直接切换到备库B,但是保持B只读呢?
这样也不行。
因为,这段时间内,中转日志还没有应用完成,如果直接发起主备切换,客户端查询看不到之前执行完成的事务,会认为有“数据丢失”。
虽然随着中转日志的继续应用,这些数据会恢复回来,但是对于一些业务来说,查询到“暂时丢失数据的状态”也是不能被接受的。
聊到这里你就知道了,在满足数据可靠性的前提下,MySQL高可用系统的可用性,是依赖于主备延迟的。延迟的时间越小,在主库故障的时候,服务恢复需要的时间就越短,可用性就越高。
备库并行复制能力
谈到主备的并行复制能力,我们要关注的是图中黑色的两个箭头。一个箭头代表了客户端写入主库,另一箭头代表的是备库上sql_thread执行中转日志(relay log)。如果用箭头的粗细来代表并行度的话,那么真实情况就如图1所示,第一个箭头要明显粗于第二个箭头。
在主库上,影响并发度的原因就是各种锁了。由于InnoDB引擎支持行锁,除了所有并发事务都在更新同一行(热点行)这种极端场景外,它对业务并发度的支持还是很友好的。所以,你在性能测试的时候会发现,并发压测线程32就比单线程时,总体吞吐量高。
而日志在备库上的执行,就是图中备库上sql_thread更新数据(DATA)的逻辑。如果是用单线程的话,就会导致备库应用日志不够快,造成主备延迟。
在官方的5.6版本之前,MySQL只支持单线程复制,由此在主库并发高、TPS高时就会出现严重的主备延迟问题。
从单线程复制到最新版本的多线程复制,中间的演化经历了好几个版本。接下来,我就跟你说说MySQL多线程复制的演进过程。
其实说到底,所有的多线程复制机制,都是要把图1中只有一个线程的sql_thread,拆成多个线程,也就是都符合下面的这个模型:
图2中,coordinator就是原来的sql_thread, 不过现在它不再直接更新数据了,只负责读取中转日志和分发事务。真正更新日志的,变成了worker线程。而work线程的个数,就是由参数slave_parallel_workers决定的。根据我的经验,把这个值设置为8~16之间最好(32核物理机的情况),毕竟备库还有可能要提供读查询,不能把CPU都吃光了。
接下来,你需要先思考一个问题:事务能不能按照轮询的方式分发给各个worker,也就是第一个事务分给worker_1,第二个事务发给worker_2呢?
其实是不行的。因为,事务被分发给worker以后,不同的worker就独立执行了。但是,由于CPU的调度策略,很可能第二个事务最终比第一个事务先执行。而如果这时候刚好这两个事务更新的是同一行,也就意味着,同一行上的两个事务,在主库和备库上的执行顺序相反,会导致主备不一致的问题。
接下来,请你再设想一下另外一个问题:同一个事务的多个更新语句,能不能分给不同的worker来执行呢?
答案是,也不行。举个例子,一个事务更新了表t1和表t2中的各一行,如果这两条更新语句被分到不同worker的话,虽然最终的结果是主备一致的,但如果表t1执行完成的瞬间,备库上有一个查询,就会看到这个事务“更新了一半的结果”,破坏了事务逻辑的隔离性。
所以,coordinator在分发的时候,需要满足以下这两个基本要求:
1. 不能造成更新覆盖。这就要求更新同一行的两个事务,必须被分发到同一个worker中。
2. 同一个事务不能被拆开,必须放到同一个worker中。
各个版本的多线程复制,都遵循了这两条基本原则。接下来,我们就看看各个版本的并行复制策略。
MySQL 5.5版本的并行复制策略
官方MySQL 5.5版本是不支持并行复制的。但是,在2012年的时候,我自己服务的业务出现了严重的主备延迟,原因就是备库只有单线程复制。然后,我就先后写了两个版本的并行策略。
这里,我给你介绍一下这两个版本的并行策略,即按表分发策略和按行分发策略,以帮助你理解MySQL官方版本并行复制策略的迭代。
按表分发策略
按表分发事务的基本思路是,如果两个事务更新不同的表,它们就可以并行。因为数据是存储在表里的,所以按表分发,可以保证两个worker不会更新同一行。
当然,如果有跨表的事务,还是要把两张表放在一起考虑的。如图3所示,就是按表分发的规则。
可以看到,每个worker线程对应一个hash表,用于保存当前正在这个worker的“执行队列”里的事务所涉及的表。hash表的key是“库名.表名”,value是一个数字,表示队列中有多少个事务修改这个表。
在有事务分配给worker时,事务里面涉及的表会被加到对应的hash表中。worker执行完成后,这个表会被从hash表中去掉。
图3中,hash_table_1表示,现在worker_1的“待执行事务队列”里,有4个事务涉及到db1.t1表,有1个事务涉及到db2.t2表;hash_table_2表示,现在worker_2中有一个事务会更新到表t3的数据。
假设在图中的情况下,coordinator从中转日志中读入一个新事务T,这个事务修改的行涉及到表t1和t3。
现在我们用事务T的分配流程,来看一下分配规则。
1. 由于事务T中涉及修改表t1,而worker_1队列中有事务在修改表t1,事务T和队列中的某个事务要修改同一个表的数据,这种情况我们说事务T和worker_1是冲突的。
2. 按照这个逻辑,顺序判断事务T和每个worker队列的冲突关系,会发现事务T跟worker_2也冲突。
3. 事务T跟多于一个worker冲突,coordinator线程就进入等待。
4. 每个worker继续执行,同时修改hash_table。假设hash_table_2里面涉及到修改表t3的事务先执行完成,就会从hash_table_2中把db1.t3这一项去掉。
5. 这样coordinator会发现跟事务T冲突的worker只有worker_1了,因此就把它分配给worker_1。
6. coordinator继续读下一个中转日志,继续分配事务。
也就是说,每个事务在分发的时候,跟所有worker的冲突关系包括以下三种情况:
1. 如果跟所有worker都不冲突,coordinator线程就会把这个事务分配给最空闲的woker;
2. 如果跟多于一个worker冲突,coordinator线程就进入等待状态,直到和这个事务存在冲突关系的worker只剩下1个;
3. 如果只跟一个worker冲突,coordinator线程就会把这个事务分配给这个存在冲突关系的worker。
这个按表分发的方案,在多个表负载均匀的场景里应用效果很好。但是,如果碰到热点表,比如所有的更新事务都会涉及到某一个表的时候,所有事务都会被分配到同一个worker中,就变成单线程复制了。
按行分发策略
要解决热点表的并行复制问题,就需要一个按行并行复制的方案。按行复制的核心思路是:如果两个事务没有更新相同的行,它们在备库上可以并行执行。显然,这个模式要求binlog格式必须是row。
这时候,我们判断一个事务T和worker是否冲突,用的就规则就不是“修改同一个表”,而是“修改同一行”。
按行复制和按表复制的数据结构差不多,也是为每个worker,分配一个hash表。只是要实现按行分发,这时候的key,就必须是“库名+表名+唯一键的值”。
但是,这个“唯一键”只有主键id还是不够的,我们还需要考虑下面这种场景,表t1中除了主键,还有唯一索引a:
可以看到,这两个事务要更新的行的主键值不同,但是如果它们被分到不同的worker,就有可能session B的语句先执行。这时候id=1的行的a的值还是1,就会报唯一键冲突。
因此,基于行的策略,事务hash表中还需要考虑唯一键,即key应该是“库名+表名+索引a的名字+a的值”。
比如,在上面这个例子中,我要在表t1上执行update t1 set a=1 where id=2语句,在binlog里面记录了整行的数据修改前各个字段的值,和修改后各个字段的值。
因此,coordinator在解析这个语句的binlog的时候,这个事务的hash表就有三个项:
1. key=hash_func(db1+t1+“PRIMARY”+2), value=2; 这里value=2是因为修改前后的行id值不变,出现了两次。
2. key=hash_func(db1+t1+“a”+2), value=1,表示会影响到这个表a=2的行。
3. key=hash_func(db1+t1+“a”+1), value=1,表示会影响到这个表a=1的行。
可见,相比于按表并行分发策略,按行并行策略在决定线程分发的时候,需要消耗更多的计算资源。你可能也发现了,这两个方案其实都有一些约束条件:
1. 要能够从binlog里面解析出表名、主键值和唯一索引的值。也就是说,主库的binlog格式必须是row;
2. 表必须有主键;
3. 不能有外键。表上如果有外键,级联更新的行不会记录在binlog中,这样冲突检测就不准确。
但,好在这三条约束规则,本来就是DBA之前要求业务开发人员必须遵守的线上使用规范,所以这两个并行复制策略在应用上也没有碰到什么麻烦。
对比按表分发和按行分发这两个方案的话,按行分发策略的并行度更高。不过,如果是要操作很多行的大事务的话,按行分发的策略有两个问题:
1. 耗费内存。比如一个语句要删除100万行数据,这时候hash表就要记录100万个项。
2. 耗费CPU。解析binlog,然后计算hash值,对于大事务,这个成本还是很高的。
所以,我在实现这个策略的时候会设置一个阈值,单个事务如果超过设置的行数阈值(比如,如果单个事务更新的行数超过10万行),就暂时退化为单线程模式,退化过程的逻辑大概是这样的:
1. coordinator暂时先hold住这个事务;
2. 等待所有worker都执行完成,变成空队列;
3. coordinator直接执行这个事务;
4. 恢复并行模式。
读到这里,你可能会感到奇怪,这两个策略又没有被合到官方,我为什么要介绍这么详细呢?其实,介绍这两个策略的目的是抛砖引玉,方便你理解后面要介绍的社区版本策略。
MySQL 5.6版本的并行复制策略
官方MySQL5.6版本,支持了并行复制,只是支持的粒度是按库并行。理解了上面介绍的按表分发策略和按行分发策略,你就理解了,用于决定分发策略的hash表里,key就是数据库名。
这个策略的并行效果,取决于压力模型。如果在主库上有多个DB,并且各个DB的压力均衡,使用这个策略的效果会很好。
相比于按表和按行分发,这个策略有两个优势:
1. 构造hash值的时候很快,只需要库名;而且一个实例上DB数也不会很多,不会出现需要构造100万个项这种情况。
2. 不要求binlog的格式。因为statement格式的binlog也可以很容易拿到库名。
但是,如果你的主库上的表都放在同一个DB里面,这个策略就没有效果了;或者如果不同DB的热点不同,比如一个是业务逻辑库,一个是系统配置库,那也起不到并行的效果。
理论上你可以创建不同的DB,把相同热度的表均匀分到这些不同的DB中,强行使用这个策略。
不过据我所知,由于需要特地移动数据,这个策略用得并不多。
MariaDB的并行复制策略
redo log组提交(group commit)优化, 而MariaDB的并行复制策略利用的就是这个特性:
1. 能够在同一组里提交的事务,一定不会修改同一行;
2. 主库上可以并行执行的事务,备库上也一定是可以并行执行的。
在实现上,MariaDB是这么做的:
1. 在一组里面一起提交的事务,有一个相同的commit_id,下一组就是commit_id+1;
2. commit_id直接写到binlog里面;
3. 传到备库应用的时候,相同commit_id的事务分发到多个worker执行;
4. 这一组全部执行完成后,coordinator再去取下一批。
当时,这个策略出来的时候是相当惊艳的。因为,之前业界的思路都是在“分析binlog,并拆分到worker”上。而MariaDB的这个策略,目标是“模拟主库的并行模式”。
但是,这个策略有一个问题,它并没有实现“真正的模拟主库并发度”这个目标。在主库上,一组事务在commit的时候,下一组事务是同时处于“执行中”状态的。
如图5所示,假设了三组事务在主库的执行情况,你可以看到在trx1、trx2和trx3提交的时候,trx4、trx5和trx6是在执行的。这样,在第一组事务提交完成的时候,下一组事务很快就会进入commit状态。
而按照MariaDB的并行复制策略,备库上的执行效果如图6所示。
可以看到,在备库上执行的时候,要等第一组事务完全执行完成后,第二组事务才能开始执行,这样系统的吞吐量就不够。
另外,这个方案很容易被大事务拖后腿。假设trx2是一个超大事务,那么在备库应用的时候,trx1和trx3执行完成后,就只能等trx2完全执行完成,下一组才能开始执行。这段时间,只有一个worker线程在工作,是对资源的浪费。
不过即使如此,这个策略仍然是一个很漂亮的创新。因为,它对原系统的改造非常少,实现也很优雅。
MySQL 5.7的并行复制策略
在MariaDB并行复制实现之后,官方的MySQL5.7版本也提供了类似的功能,由参数slaveparallel-type来控制并行复制策略:
1. 配置为DATABASE,表示使用MySQL 5.6版本的按库并行策略;
2. 配置为 LOGICAL_CLOCK,表示的就是类似MariaDB的策略。不过,MySQL 5.7这个策略,针对并行度做了优化。这个优化的思路也很有趣儿。
你可以先考虑这样一个问题:同时处于“执行状态”的所有事务,是不是可以并行?
答案是,不能。
因为,这里面可能有由于锁冲突而处于锁等待状态的事务。如果这些事务在备库上被分配到不同的worker,就会出现备库跟主库不一致的情况。
而上面提到的MariaDB这个策略的核心,是“所有处于commit”状态的事务可以并行。事务处于commit状态,表示已经通过了锁冲突的检验了。
这时候,你可以再回顾一下两阶段提交,两阶段提交过程图。
其实,不用等到commit阶段,只要能够到达redo log prepare阶段,就表示事务已经通过锁冲突的检验了。
因此,MySQL 5.7并行复制策略的思想是:
1. 同时处于prepare状态的事务,在备库执行时是可以并行的;
2. 处于prepare状态的事务,与处于commit状态的事务之间,在备库执行时也是可以并行的。
binlog的组提交的时候,介绍过两个参数:
1. binlog_group_commit_sync_delay参数,表示延迟多少微秒后才调用fsync;
2. binlog_group_commit_sync_no_delay_count参数,表示累积多少次以后才调用fsync。
这两个参数是用于故意拉长binlog从write到fsync的时间,以此减少binlog的写盘次数。在MySQL 5.7的并行复制策略里,它们可以用来制造更多的“同时处于prepare阶段的事务”。这样就增加了备库复制的并行度。
也就是说,这两个参数,既可以“故意”让主库提交得慢些,又可以让备库执行得快些。在MySQL5.7处理备库延迟的时候,可以考虑调整这两个参数值,来达到提升备库复制并发度的目的。
MySQL 5.7.22的并行复制策略
在2018年4月份发布的MySQL 5.7.22版本里,MySQL增加了一个新的并行复制策略,基于WRITESET的并行复制。
相应地,新增了一个参数binlog-transaction-dependency-tracking,用来控制是否启用这个新策略。这个参数的可选值有以下三种。
1. COMMIT_ORDER,表示的就是前面介绍的,根据同时进入prepare和commit来判断是否可以并行的策略。
2. WRITESET,表示的是对于事务涉及更新的每一行,计算出这一行的hash值,组成集合writeset。如果两个事务没有操作相同的行,也就是说它们的writeset没有交集,就可以并行。
3. WRITESET_SESSION,是在WRITESET的基础上多了一个约束,即在主库上同一个线程先后执行的两个事务,在备库执行的时候,要保证相同的先后顺序。
当然为了唯一标识,这个hash值是通过“库名+表名+索引名+值”计算出来的。如果一个表上除了有主键索引外,还有其他唯一索引,那么对于每个唯一索引,insert语句对应的writeset就要多增加一个hash值。
你可能看出来了,这跟我们前面介绍的基于MySQL 5.5版本的按行分发的策略是差不多的。不过,MySQL官方的这个实现还是有很大的优势:
1. writeset是在主库生成后直接写入到binlog里面的,这样在备库执行的时候,不需要解析binlog内容(event里的行数据),节省了很多计算量;
2. 不需要把整个事务的binlog都扫一遍才能决定分发到哪个worker,更省内存;
3. 由于备库的分发策略不依赖于binlog内容,所以binlog是statement格式也是可以的。
因此,MySQL 5.7.22的并行复制策略在通用性上还是有保证的。
当然,对于“表上没主键”和“外键约束”的场景,WRITESET策略也是没法并行的,也会暂时退化为单线程模型。
一主多从切换
一个基本的一主多从结构。
图中,虚线箭头表示的是主备关系,也就是A和A’互为主备, 从库B、C、D指向的是主库A。一主多从的设置,一般用于读写分离,主库负责所有的写入和一部分读,其他的读请求则由从库分担。
今天我们要讨论的就是,在一主多从架构下,主库故障后的主备切换问题。
如图所示,就是主库发生故障,主备切换后的结果。
相比于一主一备的切换流程,一主多从结构在切换完成后,A’会成为新的主库,从库B、C、D也要改接到A’。正是由于多了从库B、C、D重新指向的这个过程,所以主备切换的复杂性也相应增加了。
接下来,我们再一起看看一个切换系统会怎么完成一主多从的主备切换过程。
基于位点的主备切换
这里,我们需要先来回顾一个知识点。
当我们把节点B设置成节点A’的从库的时候,需要执行一条change master命令:
CHANGE MASTER TO
MASTER_HOST=$host_name
MASTER_PORT=$port
MASTER_USER=$user_name
MASTER_PASSWORD=$password
MASTER_LOG_FILE=$master_log_name
MASTER_LOG_POS=$master_log_pos
这条命令有这么6个参数:
MASTER_HOST、MASTER_PORT、MASTER_USER和MASTER_PASSWORD四个参数,分别代表了主库A’的IP、端口、用户名和密码。
最后两个参数MASTER_LOG_FILE和MASTER_LOG_POS表示,要从主库的master_log_name文件的master_log_pos这个位置的日志继续同步。而这个位置就是我们所说的同步位点,也就是主库对应的文件名和日志偏移量。
那么,这里就有一个问题了,节点B要设置成A’的从库,就要执行change master命令,就不可避免地要设置位点的这两个参数,但是这两个参数到底应该怎么设置呢?
原来节点B是A的从库,本地记录的也是A的位点。但是相同的日志,A的位点和A’的位点是不同的。因此,从库B要切换的时候,就需要先经过“找同步位点”这个逻辑。
这个位点很难精确取到,只能取一个大概位置。为什么这么说呢?
我来和你分析一下看看这个位点一般是怎么获取到的,你就清楚其中不精确的原因了。考虑到切换过程中不能丢数据,所以我们找位点的时候,总是要找一个“稍微往前”的,然后再通过判断跳过那些在从库B上已经执行过的事务。
一种取同步位点的方法是这样的:
1. 等待新主库A’把中转日志(relay log)全部同步完成;
2. 在A’上执行show master status命令,得到当前A’上最新的File 和 Position;
3. 取原主库A故障的时刻T;
4. 用mysqlbinlog工具解析A’的File,得到T时刻的位点。
mysqlbinlog File --stop-datetime=T --start-datetime=T
图中,end_log_pos后面的值“123”,表示的就是A’这个实例,在T时刻写入新的binlog的位置。
然后,我们就可以把123这个值作为$master_log_pos ,用在节点B的change master命令里。
当然这个值并不精确。为什么呢?
你可以设想有这么一种情况,假设在T这个时刻,主库A已经执行完成了一个insert 语句插入了一行数据R,并且已经将binlog传给了A’和B,然后在传完的瞬间主库A的主机就掉电了。
那么,这时候系统的状态是这样的:
1. 在从库B上,由于同步了binlog, R这一行已经存在;
2. 在新主库A’上, R这一行也已经存在,日志是写在123这个位置之后的;
3. 我们在从库B上执行change master命令,指向A’的File文件的123位置,就会把插入R这一行数据的binlog又同步到从库B去执行。
这时候,从库B的同步线程就会报告 Duplicate entry ‘id_of_R’ for key ‘PRIMARY’ 错误,提示出现了主键冲突,然后停止同步。
所以,通常情况下,我们在切换任务的时候,要先主动跳过这些错误,有两种常用的方法。
一种做法是,主动跳过一个事务。跳过命令的写法是:
set global sql_slave_skip_counter=1;
start slave;
因为切换过程中,可能会不止重复执行一个事务,所以我们需要在从库B刚开始接到新主库A’时,持续观察,每次碰到这些错误就停下来,执行一次跳过命令,直到不再出现停下来的情况,以此来跳过可能涉及的所有事务。
另外一种方式是,通过设置slave_skip_errors参数,直接设置跳过指定的错误。
在执行主备切换时,有这么两类错误,是经常会遇到的:
1062错误是插入数据时唯一键冲突;
1032错误是删除数据时找不到行。
因此,我们可以把slave_skip_errors 设置为 “1032,1062”,这样中间碰到这两个错误时就直接跳过。
这里需要注意的是,这种直接跳过指定错误的方法,针对的是主备切换时,由于找不到精确的同步位点,所以只能采用这种方法来创建从库和新主库的主备关系。
这个背景是,我们很清楚在主备切换过程中,直接跳过1032和1062这两类错误是无损的,所以才可以这么设置slave_skip_errors参数。等到主备间的同步关系建立完成,并稳定执行一段时间之后,我们还需要把这个参数设置为空,以免之后真的出现了主从数据不一致,也跳过了。
GTID
通过sql_slave_skip_counter跳过事务和通过slave_skip_errors忽略错误的方法,虽然都最终可以建立从库B和新主库A’的主备关系,但这两种操作都很复杂,而且容易出错。所以,MySQL 5.6版本引入了GTID,彻底解决了这个困难。
那么,GTID到底是什么意思,又是如何解决找同步位点这个问题呢?现在,我就和你简单介绍一下。
GTID的全称是Global Transaction Identifier,也就是全局事务ID,是一个事务在提交的时候生成的,是这个事务的唯一标识。它由两部分组成,格式是:
GTID=server_uuid:gno
其中:
server_uuid是一个实例第一次启动时自动生成的,是一个全局唯一的值;
gno是一个整数,初始值是1,每次提交事务的时候分配给这个事务,并加1。
这里我需要和你说明一下,在MySQL的官方文档里,GTID格式是这么定义的:
GTID=source_id:transaction_id
这里的source_id就是server_uuid;而后面的这个transaction_id,我觉得容易造成误导,所以我改成了gno。为什么说使用transaction_id容易造成误解呢?
因为,在MySQL里面我们说transaction_id就是指事务id,事务id是在事务执行过程中分配的,如果这个事务回滚了,事务id也会递增,而gno是在事务提交的时候才会分配。
从效果上看,GTID往往是连续的,因此我们用gno来表示更容易理解。
GTID模式的启动也很简单,我们只需要在启动一个MySQL实例的时候,加上参数gtid_mode=on和enforce_gtid_consistency=on就可以了。
在GTID模式下,每个事务都会跟一个GTID一一对应。这个GTID有两种生成方式,而使用哪种方式取决于session变量gtid_next的值。
1. 如果gtid_next=automatic,代表使用默认值。这时,MySQL就会把server_uuid:gno分配给这个事务。
a. 记录binlog的时候,先记录一行 SET @@SESSION.GTID_NEXT=‘server_uuid:gno’;
b. 把这个GTID加入本实例的GTID集合。
2. 如果gtid_next是一个指定的GTID的值,比如通过set gtid_next='current_gtid’指定为current_gtid,那么就有两种可能:
a. 如果current_gtid已经存在于实例的GTID集合中,接下来执行的这个事务会直接被系统忽略;
b. 如果current_gtid没有存在于实例的GTID集合中,就将这个current_gtid分配给接下来要执行的事务,也就是说系统不需要给这个事务生成新的GTID,因此gno也不用加1。
注意,一个current_gtid只能给一个事务使用。这个事务提交后,如果要执行下一个事务,就要执行set 命令,把gtid_next设置成另外一个gtid或者automatic。
这样,每个MySQL实例都维护了一个GTID集合,用来对应“这个实例执行过的所有事务”。
这样看上去不太容易理解,接下来我就用一个简单的例子,来和你说明GTID的基本用法。
我们在实例X中创建一个表t。
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t values(1,1);
可以看到,事务的BEGIN之前有一条SET @@SESSION.GTID_NEXT命令。这时,如果实例X有从库,那么将CREATE TABLE和insert语句的binlog同步过去执行的话,执行事务之前就会先执行这两个SET命令, 这样被加入从库的GTID集合的,就是图中的这两个GTID。
假设,现在这个实例X是另外一个实例Y的从库,并且此时在实例Y上执行了下面这条插入语句:
insert into t values(1,1);
并且,这条语句在实例Y上的GTID是 “aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10”。
那么,实例X作为Y的从库,就要同步这个事务过来执行,显然会出现主键冲突,导致实例X的同步线程停止。这时,我们应该怎么处理呢?
处理方法就是,你可以执行下面的这个语句序列:
set gtid_next='aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10';
begin;
commit;
set gtid_next=automatic;
start slave;
其中,前三条语句的作用,是通过提交一个空事务,把这个GTID加到实例X的GTID集合中。如图所示,就是执行完这个空事务之后的show master status的结果。
可以看到实例X的Executed_Gtid_set里面,已经加入了这个GTID。
这样,我再执行start slave命令让同步线程执行起来的时候,虽然实例X上还是会继续执行实例Y
传过来的事务,但是由于“aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10”已经存在于实例X的GTID集
合中了,所以实例X就会直接跳过这个事务,也就不会再出现主键冲突的错误。
在上面的这个语句序列中,start slave命令之前还有一句set gtid_next=automatic。这句话的作
用是“恢复GTID的默认分配行为”,也就是说如果之后有新的事务再执行,就还是按照原来的分
配方式,继续分配gno=3。
基于GTID的主备切换
现在,我们已经理解GTID的概念,再一起来看看基于GTID的主备复制的用法。
在GTID模式下,备库B要设置为新主库A’的从库的语法如下:
CHANGE MASTER TO
MASTER_HOST=$host_name
MASTER_PORT=$port
MASTER_USER=$user_name
MASTER_PASSWORD=$password
master_auto_position=1
其中,master_auto_position=1就表示这个主备关系使用的是GTID协议。可以看到,前面让我们头疼不已的MASTER_LOG_FILE和MASTER_LOG_POS参数,已经不需要指定了。
我们把现在这个时刻,实例A’的GTID集合记为set_a,实例B的GTID集合记为set_b。接下来,我们就看看现在的主备切换逻辑。
我们在实例B上执行start slave命令,取binlog的逻辑是这样的:
1. 实例B指定主库A’,基于主备协议建立连接。
2. 实例B把set_b发给主库A’。
3. 实例A’算出set_a与set_b的差集,也就是所有存在于set_a,但是不存在于set_b的GITD的集合,判断A’本地是否包含了这个差集需要的所有binlog事务。
a. 如果不包含,表示A’已经把实例B需要的binlog给删掉了,直接返回错误;
b. 如果确认全部包含,A’从自己的binlog文件里面,找出第一个不在set_b的事务,发给B;
4. 之后就从这个事务开始,往后读文件,按顺序取binlog发给B去执行。
其实,这个逻辑里面包含了一个设计思想:在基于GTID的主备关系里,系统认为只要建立主备关系,就必须保证主库发给备库的日志是完整的。因此,如果实例B需要的日志已经不存在,A’就拒绝把日志发给B。
这跟基于位点的主备协议不同。基于位点的协议,是由备库决定的,备库指定哪个位点,主库就发哪个位点,不做日志的完整性判断。
基于上面的介绍,我们再来看看引入GTID后,一主多从的切换场景下,主备切换是如何实现的。
由于不需要找位点了,所以从库B、C、D只需要分别执行change master命令指向实例A’即可。
其实,严谨地说,主备切换不是不需要找位点了,而是找位点这个工作,在实例A’内部就已经自动完成了。但由于这个工作是自动的,所以对HA系统的开发人员来说,非常友好。
之后这个系统就由新主库A’写入,主库A’的自己生成的binlog中的GTID集合格式是:
server_uuid_of_A’:1-M。
如果之前从库B的GTID集合格式是 server_uuid_of_A:1-N, 那么切换之后GTID集合的格式就变成了server_uuid_of_A:1-N, server_uuid_of_A’:1-M。
当然,主库A’之前也是A的备库,因此主库A’和从库B的GTID集合是一样的。这就达到了我们预期。
GTID和在线DDL
接下来,我再举个例子帮你理解GTID。
我和你提到业务高峰期的慢查询性能问题时,分析到如果是由于索引缺失引起的性能问题,我们可以通过在线加索引来解决。但是,考虑到要避免新增索引对主库性能造成的影响,我们可以先在备库加索引,然后再切换。
当时我说,在双M结构下,备库执行的DDL语句也会传给主库,为了避免传回后对主库造成影响,要通过set sql_log_bin=off关掉binlog。
提出了一个问题:这样操作的话,数据库里面是加了索引,但是binlog并没有记录下这一个更新,是不是会导致数据和日志不一致?
这个问题提得非常好。当时,我在留言的回复中就引用了GTID来说明。今天,我再和你展开说明一下。
假设,这两个互为主备关系的库还是实例X和实例Y,且当前主库是X,并且都打开了GTID模式。这时的主备切换流程可以变成下面这样:
在实例X上执行stop slave。
在实例Y上执行DDL语句。注意,这里并不需要关闭binlog。
执行完成后,查出这个DDL语句对应的GTID,并记为 server_uuid_of_Y:gno。
到实例X上执行以下语句序列:
set GTID_NEXT="server_uuid_of_Y:gno";
begin;
commit;
set gtid_next=automatic;
start slave;
这样做的目的在于,既可以让实例Y的更新有binlog记录,同时也可以确保不会在实例X上执行这条更新。
接下来,执行完主备切换,然后照着上述流程再执行一遍即可。
读写分离过期读
一主多从的结构,其实就是读写分离的基本结构
读写分离的主要目标就是分摊主库的压力。图1中的结构是客户端(client)主动做负载均衡,这种模式下一般会把数据库的连接信息放在客户端的连接层。也就是说,由客户端来选择后端数据库进行查询。
还有一种架构是,在MySQL和客户端之间有一个中间代理层proxy,客户端只连接proxy, 由proxy根据请求类型和上下文决定请求的分发路由。
接下来,我们就看一下客户端直连和带proxy的读写分离架构,各有哪些特点。
1. 客户端直连方案,因为少了一层proxy转发,所以查询性能稍微好一点儿,并且整体架构简单,排查问题更方便。但是这种方案,由于要了解后端部署细节,所以在出现主备切换、库迁移等操作的时候,客户端都会感知到,并且需要调整数据库连接信息。
你可能会觉得这样客户端也太麻烦了,信息大量冗余,架构很丑。其实也未必,一般采用这样的架构,一定会伴随一个负责管理后端的组件,比如Zookeeper,尽量让业务端只专注于业务逻辑开发。
2. 带proxy的架构,对客户端比较友好。客户端不需要关注后端细节,连接维护、后端信息维护等工作,都是由proxy完成的。但这样的话,对后端维护团队的要求会更高。而且,proxy也需要有高可用架构。因此,带proxy架构的整体就相对比较复杂。
理解了这两种方案的优劣,具体选择哪个方案就取决于数据库团队提供的能力了。但目前看,趋势是往带proxy的架构方向发展的。
但是,不论使用哪种架构,你都会碰到我们今天要讨论的问题:由于主从可能存在延迟,客户端执行完一个更新事务后马上发起查询,如果查询选择的是从库的话,就有可能读到刚刚的事务更新之前的状态。
这种“在从库上会读到系统的一个过期状态”的现象,在这篇文章里,我们暂且称之为“过期读”。
前面我们说过了几种可能导致主备延迟的原因,以及对应的优化策略,但是主从延迟还是不能100%避免的。
不论哪种结构,客户端都希望查询从库的数据结果,跟查主库的数据结果是一样的。
接下来,我们就来讨论怎么处理过期读问题。
这里,我先把文章中涉及到的处理过期读的方案汇总在这里,以帮助你更好地理解和掌握全文的知识脉络。这些方案包括:
强制走主库方案;
sleep方案;
判断主备无延迟方案;
配合semi-sync方案;
等主库位点方案;
等GTID方案。
强制走主库方案
强制走主库方案其实就是,将查询请求做分类。通常情况下,我们可以将查询请求分为这么两类:
1. 对于必须要拿到最新结果的请求,强制将其发到主库上。比如,在一个交易平台上,卖家发布商品以后,马上要返回主页面,看商品是否发布成功。那么,这个请求需要拿到最新的结果,就必须走主库。
2. 对于可以读到旧数据的请求,才将其发到从库上。在这个交易平台上,买家来逛商铺页面,就算晚几秒看到最新发布的商品,也是可以接受的。那么,这类请求就可以走从库。你可能会说,这个方案是不是有点畏难和取巧的意思,但其实这个方案是用得最多的。
当然,这个方案最大的问题在于,有时候你会碰到“所有查询都不能是过期读”的需求,比如一些金融类的业务。这样的话,你就要放弃读写分离,所有读写压力都在主库,等同于放弃了扩展性。
因此接下来,我们来讨论的话题是:可以支持读写分离的场景下,有哪些解决过期读的方案,并分析各个方案的优缺点。
Sleep 方案
主库更新后,读从库之前先sleep一下。具体的方案就是,类似于执行一条select sleep(1)命令。
这个方案的假设是,大多数情况下主备延迟在1秒之内,做一个sleep可以有很大概率拿到最新的数据。
这个方案给你的第一感觉,很可能是不靠谱儿,应该不会有人用吧?并且,你还可能会说,直接在发起查询时先执行一条sleep语句,用户体验很不友好啊。
但,这个思路确实可以在一定程度上解决问题。为了看起来更靠谱儿,我们可以换一种方式。以卖家发布商品为例,商品发布后,用Ajax(Asynchronous JavaScript + XML,异步JavaScript和XML)直接把客户端输入的内容作为“新的商品”显示在页面上,而不是真正地去数据库做查询。
这样,卖家就可以通过这个显示,来确认产品已经发布成功了。等到卖家再刷新页面,去查看商品的时候,其实已经过了一段时间,也就达到了sleep的目的,进而也就解决了过期读的问题。
也就是说,这个sleep方案确实解决了类似场景下的过期读问题。但,从严格意义上来说,这个方案存在的问题就是不精确。这个不精确包含了两层意思:
1. 如果这个查询请求本来0.5秒就可以在从库上拿到正确结果,也会等1秒;
2. 如果延迟超过1秒,还是会出现过期读。
看到这里,你是不是有一种“你是不是在逗我”的感觉,这个改进方案虽然可以解决类似Ajax场景下的过期读问题,但还是怎么看都不靠谱儿。别着急,接下来我就和你介绍一些更准确的方案。
判断主备无延迟方案
要确保备库无延迟,通常有三种做法。
我们知道show slave status结果里的seconds_behind_master参数的值,可以用来衡量主备延迟时间的长短。
所以第一种确保主备无延迟的方法是,每次从库执行查询请求前,先判断seconds_behind_master是否已经等于0。如果还不等于0 ,那就必须等到这个参数变为0才能执行查询请求。
seconds_behind_master的单位是秒,如果你觉得精度不够的话,还可以采用对比位点和GTID的方法来确保主备无延迟,也就是我们接下来要说的第二和第三种方法。
如图所示,是一个show slave status结果的部分截图。
现在,我们就通过这个结果,来看看具体如何通过对比位点和GTID来确保主备无延迟。
第二种方法,对比位点确保主备无延迟:
Master_Log_File和Read_Master_Log_Pos,表示的是读到的主库的最新位点;
Relay_Master_Log_File和Exec_Master_Log_Pos,表示的是备库执行的最新位点。
如果Master_Log_File和Relay_Master_Log_File、Read_Master_Log_Pos
Exec_Master_Log_Pos这两组值完全相同,就表示接收到的日志已经同步完成。
第三种方法,对比GTID集合确保主备无延迟:
Auto_Position=1 ,表示这对主备关系使用了GTID协议。
Retrieved_Gtid_Set,是备库收到的所有日志的GTID集合;
Executed_Gtid_Set,是备库所有已经执行完成的GTID集合。
如果这两个集合相同,也表示备库接收到的日志都已经同步完成。
可见,对比位点和对比GTID这两种方法,都要比判断seconds_behind_master是否为0更准确。
在执行查询请求之前,先判断从库是否同步完成的方法,相比于sleep方案,准确度确实提升了不少,但还是没有达到“精确”的程度。为什么这么说呢?
我们现在一起来回顾下,一个事务的binlog在主备库之间的状态:
1. 主库执行完成,写入binlog,并反馈给客户端;
2. binlog被从主库发送给备库,备库收到;
3. 在备库执行binlog完成。
我们上面判断主备无延迟的逻辑,是“备库收到的日志都执行完成了”。但是,从binlog在主备之间状态的分析中,不难看出还有一部分日志,处于客户端已经收到提交确认,而备库还没收到日志的状态。
如图所示就是这样的一个状态。
这时,主库上执行完成了三个事务trx1、trx2和trx3,其中:
1. trx1和trx2已经传到从库,并且已经执行完成了;
2. trx3在主库执行完成,并且已经回复给客户端,但是还没有传到从库中。
如果这时候你在从库B上执行查询请求,按照我们上面的逻辑,从库认为已经没有同步延迟,但还是查不到trx3的。严格地说,就是出现了过期读。
那么,这个问题有没有办法解决呢?
配合semi-sync
要解决这个问题,就要引入半同步复制,也就是semi-sync replication。
semi-sync做了这样的设计:
1. 事务提交的时候,主库把binlog发给从库;
2. 从库收到binlog以后,发回给主库一个ack,表示收到了;
3. 主库收到这个ack以后,才能给客户端返回“事务完成”的确认。
也就是说,如果启用了semi-sync,就表示所有给客户端发送过确认的事务,都确保了备库已经收到了这个日志。
如果主库掉电的时候,有些binlog还来不及发给从库,会不会导致系统数据丢失?
答案是,如果使用的是普通的异步复制模式,就可能会丢失,但semi-sync就可以解决这个问题。
这样,semi-sync配合前面关于位点的判断,就能够确定在从库上执行的查询请求,可以避免过期读。
但是,semi-sync+位点判断的方案,只对一主一备的场景是成立的。在一主多从场景中,主库只要等到一个从库的ack,就开始给客户端返回确认。这时,在从库上执行查询请求,就有两种情况:
1. 如果查询是落在这个响应了ack的从库上,是能够确保读到最新数据;
2. 但如果是查询落到其他从库上,它们可能还没有收到最新的日志,就会产生过期读的问题。
其实,判断同步位点的方案还有另外一个潜在的问题,即:如果在业务更新的高峰期,主库的位点或者GTID集合更新很快,那么上面的两个位点等值判断就会一直不成立,很可能出现从库上迟迟无法响应查询请求的情况。
实际上,回到我们最初的业务逻辑里,当发起一个查询请求以后,我们要得到准确的结果,其实并不需要等到“主备完全同步”。
为什么这么说呢?我们来看一下这个时序图。
图5所示,就是等待位点方案的一个bad case。图中备库B下的虚线框,分别表示relaylog和binlog中的事务。可以看到,图5中从状态1 到状态4,一直处于延迟一个事务的状态。
备库B一直到状态4都和主库A存在延迟,如果用上面必须等到无延迟才能查询的方案,select语句直到状态4都不能被执行。
但是,其实客户端是在发完trx1更新后发起的select语句,我们只需要确保trx1已经执行完成就可以执行select语句了。也就是说,如果在状态3执行查询请求,得到的就是预期结果了。
到这里,我们小结一下,semi-sync配合判断主备无延迟的方案,存在两个问题:
1. 一主多从的时候,在某些从库执行查询请求会存在过期读的现象;
2. 在持续延迟的情况下,可能出现过度等待的问题。
接下来,我要和你介绍的等主库位点方案,就可以解决这两个问题。
等主库位点方案
要理解等主库位点方案,我需要先和你介绍一条命令:
select master_pos_wait(file, pos[, timeout]);
这条命令的逻辑如下:
1. 它是在从库执行的;
2. 参数file和pos指的是主库上的文件名和位置;
3. timeout可选,设置为正整数N表示这个函数最多等待N秒。
这个命令正常返回的结果是一个正整数M,表示从命令开始执行,到应用完file和pos表示的binlog位置,执行了多少事务。
当然,除了正常返回一个正整数M外,这条命令还会返回一些其他结果,包括:
1. 如果执行期间,备库同步线程发生异常,则返回NULL;
2. 如果等待超过N秒,就返回-1;
3. 如果刚开始执行的时候,就发现已经执行过这个位置了,则返回0。
对于图5中先执行trx1,再执行一个查询请求的逻辑,要保证能够查到正确的数据,我们可以使用这个逻辑:
1. trx1事务更新完成后,马上执行show master status得到当前主库执行到的File和Position;
2. 选定一个从库执行查询语句;
3. 在从库上执行select master_pos_wait(File, Position, 1);
4. 如果返回值是>=0的正整数,则在这个从库执行查询语句;
5. 否则,到主库执行查询语句。
我把上面这个流程画出来。
这里我们假设,这条select查询最多在从库上等待1秒。那么,如果1秒内master_pos_wait返回一个大于等于0的整数,就确保了从库上执行的这个查询结果一定包含了trx1的数据。
步骤5到主库执行查询语句,是这类方案常用的退化机制。因为从库的延迟时间不可控,不能无限等待,所以如果等待超时,就应该放弃,然后到主库去查。
你可能会说,如果所有的从库都延迟超过1秒了,那查询压力不就都跑到主库上了吗?确实是这样。
但是,按照我们设定不允许过期读的要求,就只有两种选择,一种是超时放弃,一种是转到主库查询。具体怎么选择,就需要业务开发同学做好限流策略了。
GTID方案
如果你的数据库开启了GTID模式,对应的也有等待GTID的方案。
MySQL中同样提供了一个类似的命令:
select wait_for_executed_gtid_set(gtid_set, 1);
这条命令的逻辑是:
1. 等待,直到这个库执行的事务中包含传入的gtid_set,返回0;
2. 超时返回1。
在前面等位点的方案中,我们执行完事务后,还要主动去主库执行show master status。而MySQL 5.7.6版本开始,允许在执行完更新类事务后,把这个事务的GTID返回给客户端,这样等GTID的方案就可以减少一次查询。
这时,等GTID的执行流程就变成了:
1. trx1事务更新完成后,从返回包直接获取这个事务的GTID,记为gtid1;
2. 选定一个从库执行查询语句;
3. 在从库上执行 select wait_for_executed_gtid_set(gtid1, 1);
4. 如果返回值是0,则在这个从库执行查询语句;
5. 否则,到主库执行查询语句。
跟等主库位点的方案一样,等待超时后是否直接到主库查询,需要业务开发同学来做限流考虑。我把这个流程图画出来。
在上面的第一步中,trx1事务更新完成后,从返回包直接获取这个事务的GTID。问题是,怎么能够让MySQL在执行事务后,返回包中带上GTID呢?
你只需要将参数session_track_gtids设置为OWN_GTID,然后通过API接口mysql_session_track_get_first从返回包解析出GTID的值即可。
在专栏的第一篇文章中,我介绍mysql_reset_connection的时候,评论区有同学留言问这类接口应该怎么使用。
这里我再回答一下。其实,MySQL并没有提供这类接口的SQL用法,是提供给程序的API(https://dev.mysql.com/doc/refman/5.7/en/c-api-functions.html)。
比如,为了让客户端在事务提交后,返回的GITD能够在客户端显示出来,我对MySQL客户端代码做了点修改,如下所示:
当然了,这只是一个例子。你要使用这个方案的时候,还是应该在你的客户端代码中调用mysql_session_track_get_first这个函数。
过期读总结
在今天这篇文章中,我跟你介绍了一主多从做读写分离时,可能碰到过期读的原因,以及几种应对的方案。
这几种方案中,有的方案看上去是做了妥协,有的方案看上去不那么靠谱儿,但都是有实际应用场景的,你需要根据业务需求选择。
即使是最后等待位点和等待GTID这两个方案,虽然看上去比较靠谱儿,但仍然存在需要权衡的情况。如果所有的从库都延迟,那么请求就会全部落到主库上,这时候会不会由于压力突然增大,把主库打挂了呢?
其实,在实际应用中,这几个方案是可以混合使用的。
比如,先在客户端对请求做分类,区分哪些请求可以接受过期读,而哪些请求完全不能接受过期读;然后,对于不能接受过期读的语句,再使用等GTID或等位点的方案。
但话说回来,过期读在本质上是由一写多读导致的。在实际应用中,可能会有别的不需要等待就可以水平扩展的数据库方案,但这往往是用牺牲写性能换来的,也就是需要在读性能和写性能中取权衡。
Mysql丢数据
MySQL数据库层丢数据场景
本节我们主要介绍一下在存储引擎层上是如何会丢数据的。
InnoDB丢数据
InnoDB支持事务,同Oracle类似,事务提交需要写redo、undo。采用日志先行的策略,将数据的变更在内存中完成,并且将事务记录成redo,顺序的写入redo日志中,即表示该事务已经完成,就可以返回给客户已提交的信息。但是实际上被更改的数据还在内存中,并没有刷新到磁盘,即还没有落地,当达到一定的条件,会触发checkpoint,将内存中的数据(page)合并写入到磁盘,这样就减少了离散写、IOPS,提高性能。
在这个过程中,如果服务器宕机了,内存中的数据丢失,当重启后,会通过redo日志进行recovery重做。确保不会丢失数据。因此只要redo能够实时的写入到磁盘,InnoDB就不会丢数据。
先来看一下innodb_flush_log_at_trx_commit这个参数:
0 :每秒 write cache & flush disk
1 :每次commit都 write cache & flush disk
2 :每次commit都 write cache,然后根据innodb_flush_log_at_timeout(默认为1s)时间 flush disk
从这三个配置来看,显然innodb_flush_log_at_trx_commit=1最为安全,因为每次commit都保证redo写入了disk。但是这种方式性能对DML性能来说比较低,在我们的测试中发现,如果设置为2,DML性能要比设置为1高10倍左右。
为什么oracle的实时写要比innodb的实时写性能更好?线程与进程?后面还需要研究
大家可以考虑一下0与2的区别?
在某些DML操作频繁的场景下,库的innodb_flush_log_at_trx_commit需要设置为2,这样就存在丢数据的风险:当服务器出现宕机,重启后进行crash recovery则会丢失innodb_flush_log_at_timeout秒内的数据。
PS:当开启了内部XA事务(默认开启),且开启binlog,情况稍有不一样,后面会进行介绍。
MyISAM丢数据
MyISAM存储引擎在我们的生产中用的并不多,但是系统的数据字典表元数据等都是存储在MyISAM引擎下。
MyISAM不支持事务,且没有data cache,所有DML操作只写到OS cache中,flush disk操作均由OS来完成,因此如果服务器宕机,则这部分数据肯定会丢失。
主从复制不一致
主从复制原理:MySQL主库在事务提交时写binlog,并通过sync_binlog参数来控制binlog刷新到磁盘“落地”,而备库通过IO线程从主库读取binlog,并记录到本地的relay log中,由本地的SQL线程再将relay log的数据应用到本地数据库,如下图所示:
从上图我们可以看到,在主从环境中,增加了binlog,这就增加了环境的复杂性,因此也增加了丢数据以及数据不一致可能。
在分析这些丢数据的可能性之前,我们先了解一下binlog的刷新机制以及MySQL的内部XA事务是如何保证binlog与redo的一致性的。
binlog刷新机制
master写binlog与innodb引擎写redo类似,也有参数控制:sync_binlog
等于0 :表示MySQL不控制binlog的刷新,由文件系统自己控制它的缓存的刷新
大于 0 :表示每sync_binlog次事务提交,MySQL调用文件系统的刷新操作将缓存刷下去
其中最安全的就是=1,表示每次事务提交,MySQL都会把binlog缓存刷下去,这样在掉电等情况下,系统才有可能丢失1个事务的数据。当sync_binlog设置为1,对系统的IO消耗也是非常大的。
内部XA事务原理
MySQL的存储引擎与MySQL服务层之间,或者存储引擎与存储引擎之间的分布式事务,称之为内部XA事务。最为常见的内部XA事务存在与binlog与InnoDB存储引擎之间。在事务提交时,先写二进制日志,再写InnoDB存储引起的redo日志。对于这个操作要求必须是原子的,即需要保证两者同时写入,内部XA事务机制就是保证两者的同时写入。
XA事务的大致流程:
1. 事务提交后,InnoDB存储引擎会先做一个PREPARE操作,将事务的XID写入到redo日志中。
2. 写binlog日志
3. 再将该事务的commit信息写到redo log中
如果在步骤1和步骤2失败的情况下,整个事务会回滚;如果在步骤3失败的情况下,MySQL数据库在重启后会先检查准备的UXID事务是否已经提交,若没有,则在存储引擎层再进行一次提交操作。这样就保证了redo与binlog的一致性,防止丢数据。
master库写redo、binlog不实时丢数据的场景
上面我们介绍了MySQL的内部XA事务流程,但是这个流程并不是天衣无缝的,redo的ib_logfile与binlog日志如果被设置非实时flush,就有可能存在丢数据的情况。
1. redo的trx_prepare未写入,但binlog写入,造成从库数据量比主库多。
2. redo的trx_prepare与commit都写入了,但是binlog未写入,造成从库数据量比主库少。
从目前来看,只能牺牲性能去换取数据的安全性,必须要设置redo和binlog为实时刷盘,如果对性能要求很高,则考虑使用SSD。
slave库写redo、binlog不实时丢数据的场景
master正常,但是slave出现异常的情况下宕机,这个时候会出现什么样的情况呢?如果数据丢失,slave的SQL线程还会重新应用吗?这个我们需要先了解SQL线程的机制。slave读取master的binlog日志后,需要落地3个文件:relay log、relay log info、master info:
relay log:即读取过来的master的binlog,内容与格式与master的binlog一致
relay log info:记录SQL Thread应用的relay log的位置、文件号等信息
master info:记录IO Thread读取master的binlog的位置、文件号、延迟等信息
因此如果当这3个文件如果不及时落地,则主机crash后会导致数据的不一致。
1.在MySQL 5.6.2之前,slave记录的master信息以及slave应用binlog的信息存放在文件中,即master.info与relay-log.info。在5.6.2版本之后,允许记录到table中,参数设置如下:
master-info-repository = TABLE
relay-log-info-repository = TABLE
对应的表分别为mysql.slave_master_info与mysql.slave_relay_log_info,且这两个表均为innodb引擎表。
2.master info与relay info还有3个参数控制刷新:
sync_relay_log:默认为10000,即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新,交由OS的cache控制。
sync_master_info:若master-info-repository为FILE,当设置为0,则每次sync_master_info事件都会刷新到磁盘,默认为10000次刷新到磁盘;若master-info-repository为TABLE,当设置为0,则表不做任何更新,设置为1,则每次事件会更新表 #默认为10000
sync_relay_log_info:若relay_log_info_repository为FILE,当设置为0,交由OS刷新磁盘,默认为10000次刷新到磁盘;若relay_log_info_repository为TABLE,且为INNODB存储,则无论为任何值,则都每次evnet都会更新表。
3.建议参数设置如下:
sync_relay_log = 1
sync_master_info = 1
sync_relay_log_info = 1
master-info-repository = TABLE
relay-log-info-repository = TABLE
当这样设置,导致调用fsync()/fdatasync()随着master的事务的增加而增加,且若slave的binlog和redo也实时刷新的话,会带来很严重的IO性能瓶颈。
master宕机后无法及时恢复造成的数据丢失
当master出现故障后,binlog未及时传到slave,或者各个slave收到的binlog不一致。且master无法在第一时间恢复,这个时候怎么办?
如果master不切换,则整个数据库只能只读,影响应用的运行。
如果将别的slave提升为新的master,那么原master未来得及传到slave的binlog的数据则会丢失,并且还涉及到下面2个问题。
各个slave之间接收到的binlog不一致,如果强制拉起一个slave,则slave之间数据会不一致。
原master恢复正常后,由于新的master日志丢弃了部分原master的binlog日志,这些多出来的binlog日志怎么处理,重新搭建环境?
对于上面出现的问题,一种方法是确保binlog传到从库,或者说保证主库的binlog有多个拷贝。第二种方法就是允许数据丢失,制定一定的策略,保证最小化丢失数据。
确保binlog全部传到从库
方案一:使用semi sync(半同步)方式,事务提交后,必须要传到slave,事务才能算结束。对性能影响很大,依赖网络适合小tps系统。
方案二:双写binlog,通过DBDR OS层的文件系统复制到备机,或者使用共享盘保存binlog日志。
方案三:在数据层做文章,比如保证数据库写成功后,再异步队列的方式写一份,部分业务可以借助设计和数据流解决。
保证数据最小化丢失
上面的方案设计及架构比较复杂,如果能容忍数据的丢失,可以考虑使用淘宝的TMHA复制管理工具。
当master宕机后,TMHA会选择一个binlog接收最大的slave作为master。当原master宕机恢复后,通过binlog的逆向应用,把原master上多执行的事务回退掉。
主库锁表与主从复制
主从同步是通过binlog进行的,从库有两个线程,一个负责接受binlog日志,一个负责解析日志将数据写入库中。所以主从同步一般是有一定的延时的。
至于读写锁的问题,写锁是排他的,读锁可以多次获得。在Innodb中,锁分为表锁、行锁和间隙锁,具体看你的操作,如果一个插入操作需要锁表,而这时有查询锁住了该表中的一行,自然是需要等待的。
主从复制,正常来说从库是不允许除复制进程以外的写操作的,在从库进行查询操作是不会阻塞复制的写进程的,当然如果你非要手动去锁从库的数据,主库同步到被锁的记录时在从库上是会等待锁的,所以要严格控制从库的写动作,除管理员以外全部只读
从库复制获取的是binlog,而binlog是主库提交写数据生成的binlog,主库锁表就是没有提交数据就没有生成对应的binlog,所以主库锁表跟主从复制没有因果关系,不会导致从库延迟