MySQL主备一致

MySQL 主从复制

主从复制原理

主库将数据库中数据的变化写入到 binlog

在从库上通过change master命令，设置主库的IP、端口、用户名、密码，以及要从哪个位置开始请求binlog，这个位置包含文件名和日志偏移量。
在从库上执行start slave命令，这时候从库会启动两个线程，就是图中的io_thread和sql_thread。其中io_thread负责与主库建立连接。
主库校验完用户名、密码后，创建一个 binlog dump 线程，开始按照从库传过来的位置来发送 binlog ，从库中的 I/O 线程负责接收
从库的 I/O 线程将接收的 binlog 写入到 relay log 中。
从库的 SQL 线程读取 relay log 同步数据本地（也就是再执行一遍 SQL ）。

基于位点的主备切换

把节点B设置成节点A’的从库的时候，需要执行一条change master命令：

CHANGE MASTER TO 
MASTER_HOST=$host_name  #IP
MASTER_PORT=$port       #端口
MASTER_USER=$user_name  #用户名
MASTER_PASSWORD=$password  #密码
MASTER_LOG_FILE=$master_log_name  #主库对应的文件名
MASTER_LOG_POS=$master_log_pos  #日志偏移量，从master_log_pos这个位置的日志继续同步

从库B要切换的时候，就需要先经过“找同步位点”这个逻辑。

考虑到切换过程中不能丢数据，所以我们找位点的时候，总是要找一个“稍微往前”的，然后再通过判断跳过那些在从库B上已经执行过的事务。

一种取同步位点的方法是这样的：

等待新主库A’把中转日志（relay log）全部同步完成；
在A’上执行show master status命令，得到当前A’上最新的File 和 Position；
取原主库A故障的时刻T；
用mysqlbinlog工具解析A’的File，得到T时刻的位点。

1	mysqlbinlog File --stop-datetime=T --start-datetime=T

假设在T这个时刻，主库A已经执行完成了一个insert 语句插入了一行数据R，并且已经将binlog传给了A’和B，然后在传完的瞬间主库A的主机就掉电了。

那么，这时候系统的状态是这样的：

在从库B上，由于同步了binlog， R这一行已经存在；
在新主库A’上， R这一行也已经存在，日志是写在123这个位置之后的；
我们在从库B上执行change master命令，指向A’的File文件的123位置，就会把插入R这一行数据的binlog又同步到从库B去执行。

这时候，从库B的同步线程就会报告 Duplicate entry ‘id_of_R’ for key ‘PRIMARY’ 错误，提示出现了主键冲突，然后停止同步。

切换任务时，要主动跳过错误，有两种方法：

一种做法是，主动跳过一个事务。跳过命令的写法是：

1 2	set global sql_slave_skip_counter=1; start slave;

因为切换过程中，可能会不止重复执行一个事务，所以我们需要在从库B刚开始接到新主库A’时，持续观察，每次碰到这些错误就停下来，执行一次跳过命令，直到不再出现停下来的情况，以此来跳过可能涉及的所有事务。

另外一种方式是，通过设置slave_skip_errors参数，直接设置跳过指定的错误。

在执行主备切换时，有这么两类错误，是经常会遇到的：

1062错误是插入数据时唯一键冲突；
1032错误是删除数据时找不到行。

因此，我们可以把slave_skip_errors 设置为 “1032,1062”，这样中间碰到这两个错误时就直接跳过。

记住等到主备间的同步关系建立完成，并稳定执行一段时间之后，我们还需要把这个参数设置为空，以免之后真的出现了主从数据不一致，也跳过了。

GTID

GTID的全称是Global Transaction Identifier，也就是全局事务ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。它由两部分组成，格式是：

1	GTID=server_uuid:gno

其中：

server_uuid是一个实例第一次启动时自动生成的，是一个全局唯一的值；
gno是一个整数，初始值是1，每次提交事务的时候分配给这个事务，并加1。

GTID模式启动方式：启动时加上参数 gtid_mode=on和enforce_gtid_consistency=on

关于GTID的理解

假设，现在这个实例X是另外一个实例Y的从库，并且此时在实例Y上执行了下面这条插入语句：

1	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集合中。

再执行start slave命令让同步线程执行起来的时候，虽然实例X上还是会继续执行实例Y传过来的事务，但是由于“aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10”已经存在于实例X的GTID集合中了，所以实例X就会直接跳过这个事务，也就不会再出现主键冲突的错误。

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参数，已经不需要指定了。

我们在实例B上执行start slave命令，取binlog的逻辑是这样的：

实例B指定主库A’，基于主备协议建立连接。
实例B把set_b发给主库A’。
实例A’算出set_a与set_b的差集，也就是所有存在于set_a，但是不存在于set_b的GITD的集合，判断A’本地是否包含了这个差集需要的所有binlog事务。
a. 如果不包含，表示A’已经把实例B需要的binlog给删掉了，直接返回错误；
b. 如果确认全部包含，A’从自己的binlog文件里面，找出第一个不在set_b的事务，发给B；
之后就从这个事务开始，往后读文件，按顺序取binlog发给B去执行。

其实，这个逻辑里面包含了一个设计思想：在基于GTID的主备关系里，系统认为只要建立主备关系，就必须保证主库发给备库的日志是完整的。因此，如果实例B需要的日志已经不存在，A’就拒绝把日志发给B。

你在GTID模式下设置主从关系的时候，从库执行start slave命令后，主库发现需要的binlog已经被删除掉了，导致主备创建不成功。这种情况下，你觉得可以怎么处理呢？

如果业务允许主从不一致的情况，那么可以在主库上先执行show global variables like ‘gtid_purged’，得到主库已经删除的GTID集合，假设是gtid_purged1；然后先在从库上执行reset master，再执行set global gtid_purged =‘gtid_purged1’；最后执行start slave，就会从主库现存的binlog开始同步。binlog缺失的那一部分，数据在从库上就可能会有丢失，造成主从不一致。
如果需要主从数据一致的话，最好还是通过重新搭建从库来做。
如果有其他的从库保留有全量的binlog的话，可以把新的从库先接到这个保留了全量binlog的从库，追上日志以后，如果有需要，再接回主库。
如果binlog有备份的情况，可以先在从库上应用缺失的binlog，然后再执行start slave。

循环复制问题

双M结构下（主备切换过程中），怎么解决循环复制问题？

首先说一下什么是循环复制问题，举个例子说明一下。

业务逻辑在节点A上更新了一条语句，然后再把生成的binlog 发给节点B，节点B执行完这条更新语句后也会生成binlog。（参数log_slave_updates设置为on，表示备库执行relay log后生成binlog）

那么，如果节点A同时是节点B的备库，相当于又把节点B新生成的binlog拿过来执行了一次，然后节点A和B间，会不断地循环执行这个更新语句，也就是循环复制了。

MySQL在binlog中记录了这个命令第一次执行时所在实例的server id。所以它是根据 server id 来解决两个节点间的循环复制问题的：

规定两个库的server id必须不同，如果相同，则它们之间不能设定为主备关系；
一个备库接到binlog并在重放的过程中，生成与原binlog的server id相同的新的binlog；
每个库在收到从自己的主库发过来的日志后，先判断server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

按照这个逻辑，如果我们设置了双M结构，日志的执行流就会变成这样：

从节点A更新的事务，binlog里面记的都是A的server id；
传到节点B执行一次以后，节点B生成的binlog 的server id也是A的server id；
再传回给节点A，A判断到这个server id与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。

什么情况下双M结构会出现循环复制？

在一个主库更新事务后，用命令set global server_id=x修改了server_id。等日志再传回来的时候，发现server_id跟自己的server_id不同，就只能执行了。
trx1是在节点 B执行的，因此binlog上的server_id就是B，binlog传给节点 A，然后A和A’搭建了双M结构，就会出现循环复制。

如果出现了循环复制，可以在A或者A’上，执行如下命令：

1
2
3

stop slave；
CHANGE MASTER TO IGNORE_SERVER_IDS=(server_id_of_B);
start slave;

这样这个节点收到日志后就不会再执行。过一段时间后，再执行下面的命令把这个值改回来。

1
2
3

stop slave；
CHANGE MASTER TO IGNORE_SERVER_IDS=();
start slave;

binlog的三种格式对比

statement格式

statement格式是记录语句到 binlog，因此可能会出现这样一种情况：在主库执行SQL语句的时候，用的是索引a；而在备库执行这条SQL语句的时候，却使用了索引b。假如这是条带有 LIMIT 的 DELETE 语句，从库和主库走不同的索引会导致数据不一致。

row格式

使用 mysqlbinlog 工具查看binlog的内容，这个事务的binlog是从8900这个位置开始的，所以可以用start-position参数来指定从这个位置的日志开始解析。

1	mysqlbinlog -vv data/master.000001 --start-position=8900;

从这个图中，我们可以看到以下几个信息：

server id 1，表示这个事务是在server_id=1的这个库上执行的。
每个event都有CRC32的值，这是因为我把参数binlog_checksum设置成了CRC32。
Table_map event示了接下来要打开的表，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的行，不会有主备删除不同行的问题。

执行delete语句，row格式的binlog也会把被删掉的行的整行信息保存起来。
执行insert语句，row格式下，insert语句的binlog里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。
执行update语句，binlog里面会记录修改前整行的数据和修改后的整行数据。

mixed格式

为什么会有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格式的优点，同时又避免了数据不一致的风险。

binglog恢复数据

用binlog来恢复数据的标准做法是，用 mysqlbinlog工具解析出来，然后把解析结果整个发给MySQL执行。类似下面的命令：

1	mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 \| mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

这个命令的意思是，将 master.000001 文件里面从第2738字节到第2973字节中间这段内容解析出来，放到MySQL去执行。

主备延迟问题

与数据同步有关的时间点主要包括以下三个：

主库A执行完成一个事务，写入binlog，我们把这个时刻记为T1;
之后传给备库B，我们把备库B接收完这个binlog的时刻记为T2;
备库B执行完成这个事务，我们把这个时刻记为T3。

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是T3-T1。

在备库上执行show slave status命令，它的返回结果里面会显示seconds_behind_master，用于表示当前备库延迟了多少秒。

如果主备库机器的系统时间设置不一致，会不会导致主备延迟的值不准？

不会的。因为，备库连接到主库的时候，会通过执行SELECT UNIX_TIMESTAMP()函数来获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致，备库在执行seconds_behind_master计算的时候会自动扣掉这个差值。

主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产binlog的速度要慢。

主备延迟来源

有些部署条件下，备库所在机器的性能要比主库所在的机器性能差。

有些人把20个主库放在4台机器上，而把备库集中在一台机器上。更新请求对IOPS的压力，在主库和备库上是无差别的。所以，做这种部署时，一般都会将备库设置为“非双1”的模式。

但是更新过程中也会触发大量的读操作。所以，当备库主机上的多个备库都在争抢资源的时候，就可能会导致主备延迟了。

备库压力大

由于主库直接影响业务，大家使用起来会比较克制，反而忽视了备库的压力控制。结果就是，备库上的查询耗费了大量的CPU资源，影响了同步速度，造成主备延迟。

这种情况，我们一般可以这么处理：

一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。
通过binlog输出到外部系统，比如Hadoop这类系统，让外部系统提供统计类查询的能力。

大事务

有时候主库开启一个大事务，那必须等待主库大事务执行完成才写入 binlog，再传给备库。

比如，一些归档类的数据，平时没有注意删除历史数据，等到空间快满了，业务开发人员要一次性地删掉大量历史数据。同时，又因为要避免在高峰期操作会影响业务（至少有这个意识还是很不错的），所以会在晚上执行这些大量数据的删除操作。但还是建议控制每个事务删除的数据量，分多次删除。

主备切换策略

可靠性优先策略

双M结构下，主备切换的详细过程是这样的：

判断备库B现在的seconds_behind_master，如果小于某个值（比如5秒）继续下一步，否则持续重试这一步；
把主库A改成只读状态，即把readonly设置为true；
判断备库B的seconds_behind_master的值，直到这个值变成0为止；
把备库B改成可读写状态，也就是把readonly 设置为false；
把业务请求切到备库B。

这个切换流程，一般是由专门的HA系统来完成的，我们暂时称之为可靠性优先流程。

这个切换流程中是有不可用时间的。因为在步骤2之后，主库A和备库B都处于readonly状态，也就是说这时系统处于不可写状态，直到步骤5完成后才能恢复。

可用性优先策略

如果不等主备数据同步，直接把连接切到备库B，并且让备库B可以读写，那么系统几乎就没有不可用时间了。

我们把这个切换流程，暂时称作可用性优先流程。这个切换流程的代价，就是可能出现数据不一致的情况。

使用可用性优先策略，且binlog_format=mixed

主库A执行完insert语句，插入了一行数据（4,4），之后开始进行主备切换。
由于主备之间有5秒的延迟，所以备库B还没来得及应用“插入c=4”这个中转日志，就开始接收客户端“插入 c=5”的命令。
备库B插入了一行数据（4,5），并且把这个binlog发给主库A。
备库B执行“插入c=4”这个中转日志，插入了一行数据（5,4）。而直接在备库B执行的“插入c=5”这个语句，传到主库A，就插入了一行新数据（5,5）。

使用可用性优先策略，且binlog_format=row

因为row格式在记录binlog的时候，会记录新插入的行的所有字段值，所以最后只会有一行不一致。而且，两边的主备同步的应用线程会报错duplicate key error并停止。也就是说，这种情况下，备库B的(5,4)和主库A的(5,5)这两行数据，都不会被对方执行。

有没有哪种情况数据的可用性优先级更高呢？

有过这样的一个场景：

有一个库的作用是记录操作日志。这时候，如果数据不一致可以通过binlog来修补，而这个短暂的不一致也不会引发业务问题。
同时，业务系统依赖于这个日志写入逻辑，如果这个库不可写，会导致线上的业务操作无法执行。

另一种使用可靠性优先策略的改进措施就是，让业务逻辑不要依赖于这类日志的写入。也就是说，日志写入这个逻辑模块应该可以降级，比如写到本地文件，或者写到另外一个临时库里面。

MySQL并行复制策略

备库从主库同步数据时，会由一个IO线程接收 relay log，然后有一个 sql_thread 执行 relay log。而MySQL的并行复制，就是将一个 sql_thread 拆分成多个线程，使用一个 coordinator 负责读取中转日志和分发事务，分发到各个 worker 中更新日志。

worker 线程数由 slave_parallel_workers 决定。建议设置在 8~16之间（32核物理机）。

coordinator在分发的时候，需要满足以下这两个基本要求：

不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个worker中。
同一个事务不能被拆开，必须放到同一个worker中。

按表分发策略（MySQL 5.5）

每个worker线程对应一个hash表，用于保存当前正在这个worker的“执行队列”里的事务所涉及的表。hash表的key是“库名.表名”，value是一个数字，表示队列中有多少个事务修改这个表。

在有事务分配给worker时，事务里面涉及的表会被加到对应的hash表中。worker执行完成后，这个表会被从hash表中去掉。

如果一个事务T修改表t1和t2，但是已知 worker1中涉及到修改表 t1 的操作，worker2 中涉及到修改表 t2 的操作，因此事务 T 和 worker1、worker2 均存在冲突。coordinator线程就进入等待，等到只存在一个冲突的worker后，就可以让事务T加入worker了。

按行分发策略(MySQL 5.5)

如果两个事务没有更新相同的行，它们在备库上可以并行执行。

为每个worker，分配一个hash表。只是要实现按行分发，这时候的key，就必须是“库名+表名+索引a的名字+a的值”。

在实现这个策略的时候会设置一个阈值，单个事务如果超过设置的行数阈值（比如，如果单个事务更新的行数超过10万行），就暂时退化为单线程模式。

按行分发策略和按表分发策略其实都有一些约束条件：

要能够从binlog里面解析出表名、主键值和唯一索引的值。也就是说，主库的binlog格式必须是row；
表必须有主键；
不能有外键。表上如果有外键，级联更新的行不会记录在binlog中，这样冲突检测就不准确。

按库分发策略(MySQL 5.6)

用于决定分发策略的hash表里，key就是数据库名。

这个策略的并行效果，取决于压力模型。如果在主库上有多个DB，并且各个DB的压力均衡，使用这个策略的效果会很好。

相比于按表和按行分发，这个策略有两个优势：

构造hash值的时候很快，只需要库名；而且一个实例上DB数也不会很多，不会出现需要构造100万个项这种情况。
不要求binlog的格式。因为statement格式的binlog也可以很容易拿到库名。

但是，如果你的主库上的表都放在同一个DB里面，这个策略就没有效果了；或者如果不同DB的热点不同，比如一个是业务逻辑库，一个是系统配置库，那也起不到并行的效果。

MySQL 5.7的并行复制策略

由参数slave-parallel-type来控制并行复制策略：

配置为DATABASE，表示使用MySQL 5.6版本的按库并行策略；
配置为 LOGICAL_CLOCK，表示的就是类似MariaDB的策略。

MySQL 5.7并行复制策略的思想是：

同时处于prepare状态的事务，在备库执行时是可以并行的；
处于prepare状态的事务，与处于commit状态的事务之间，在备库执行时也是可以并行的。

有两个参数：

binlog_group_commit_sync_delay参数，表示延迟多少微秒后才调用fsync;
binlog_group_commit_sync_no_delay_count参数，表示累积多少次以后才调用fsync。

这两个参数是用于故意拉长binlog从write到fsync的时间，以此减少binlog的写盘次数。在MySQL 5.7的并行复制策略里，它们可以用来制造更多的“同时处于prepare阶段的事务”。这样就增加了备库复制的并行度。

也就是说，这两个参数，既可以“故意”让主库提交得慢些，又可以让备库执行得快些。在MySQL 5.7处理备库延迟的时候，可以考虑调整这两个参数值，来达到提升备库复制并发度的目的。

基于WRITESET的并行复制策略

相应地，新增了一个参数binlog-transaction-dependency-tracking，用来控制是否启用这个新策略。这个参数的可选值有以下三种。

COMMIT_ORDER，表示的就是前面介绍的，根据同时进入prepare和commit来判断是否可以并行的策略。
WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的hash值，组成集合writeset。如果两个事务没有操作相同的行，也就是说它们的writeset没有交集，就可以并行。
WRITESET_SESSION，是在WRITESET的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序。

当然为了唯一标识，这个hash值是通过“库名+表名+索引名+值”计算出来的。如果一个表上除了有主键索引外，还有其他唯一索引，那么对于每个唯一索引，insert语句对应的writeset就要多增加一个hash值。

你可能看出来了，这跟我们前面介绍的基于MySQL 5.5版本的按行分发的策略是差不多的。不过，MySQL官方的这个实现还是有很大的优势：

writeset是在主库生成后直接写入到binlog里面的，这样在备库执行的时候，不需要解析binlog内容（event里的行数据），节省了很多计算量；
不需要把整个事务的binlog都扫一遍才能决定分发到哪个worker，更省内存；
由于备库的分发策略不依赖于binlog内容，所以binlog是statement格式也是可以的

MariaDB的并行复制策略

MariaDB的并行复制策略利用的是redo log组提交(group commit)优化：

能够在同一组里提交的事务，一定不会修改同一行；
主库上可以并行执行的事务，备库上也一定是可以并行执行的。

在实现上，MariaDB是这么做的：

在一组里面一起提交的事务，有一个相同的commit_id，下一组就是commit_id+1；
commit_id直接写到binlog里面；
传到备库应用的时候，相同commit_id的事务分发到多个worker执行；
这一组全部执行完成后，coordinator再去取下一批。

但是，这个策略有一个问题，它并没有实现“真正的模拟主库并发度”这个目标。在主库上，一组事务在commit的时候，下一组事务是同时处于“执行中”状态的。在备库上执行的时候，要等第一组事务完全执行完成后，第二组事务才能开始执行，这样系统的吞吐量就不够。

假设一个MySQL 5.7.22版本的主库，单线程插入了很多数据，过了3个小时后，我们要给这个主库搭建一个相同版本的备库。

这时候，你为了更快地让备库追上主库，要开并行复制。在binlog-transaction-dependency-tracking参数的COMMIT_ORDER、WRITESET和WRITE_SESSION这三个取值中，你会选择哪一个呢？

你选择的原因是什么？如果设置另外两个参数，你认为会出现什么现象呢？

应该将这个参数设置为WRITESET。

由于主库是单线程压力模式，所以每个事务的commit_id都不同，那么设置为COMMIT_ORDER模式的话，从库也只能单线程执行。

同样地，由于WRITESET_SESSION模式要求在备库应用日志的时候，同一个线程的日志必须与主库上执行的先后顺序相同，也会导致主库单线程压力模式下退化成单线程复制。

所以，应该将binlog-transaction-dependency-tracking 设置为WRITESET。

处理过期读方案

尽管知道了主备延迟的原因和对应的优化策略，主从延迟仍不是能100%避免的。从库仍有可能读到系统的过期状态，那么接下来我们可以讨论如何处理过期读问题。

强制走主库

通常情况下，可以把请求分为两类：

对于必须要拿到最新结果的请求，强制将其发到主库上。
对于可以读到旧数据的请求，才将其发到从库上。

Sleep 方案

主库更新后，读从库之前先sleep一下。具体的方案就是，类似于执行一条select sleep(1)命令。

这个方案的假设是，大多数情况下主备延迟在1秒之内，做一个sleep可以有很大概率拿到最新的数据。

判断主备无延迟方案

执行 show slave status。

观察 seconds_behind_master 字段。每次从库执行查询请求前，先判断seconds_behind_master是否已经等于0。如果还不等于0 ，那就必须等到这个参数变为0才能执行查询请求。
对比位点确保主备无延迟：
- 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集合。
如果这两个集合相同，也表示备库接收到的日志都已经同步完成。

配合 semi-sync

semi-sync做了这样的设计：

事务提交的时候，主库把binlog发给从库；
从库收到binlog以后，发回给主库一个ack，表示收到了；
主库收到这个ack以后，才能给客户端返回“事务完成”的确认。

也就是说，如果启用了semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志。

但是，semi-sync+位点判断的方案，只对一主一备的场景是成立的。在一主多从场景中，主库只要等到一个从库的ack，就开始给客户端返回确认。这时，在从库上执行查询请求，就有两种情况：

如果查询是落在这个响应了ack的从库上，是能够确保读到最新数据；
但如果是查询落到其他从库上，它们可能还没有收到最新的日志，就会产生过期读的问题。

其实，判断同步位点的方案还有另外一个潜在的问题，即：如果在业务更新的高峰期，主库的位点或者GTID集合更新很快，那么上面的两个位点等值判断就会一直不成立，很可能出现从库上迟迟无法响应查询请求的情况。

等主库位点方案

1	select master_pos_wait(file, pos[, timeout]);

这条命令的逻辑如下：

它是在从库执行的；
参数file和pos指的是主库上的文件名和位置；
timeout可选，设置为正整数N表示这个函数最多等待N秒。

这个命令正常返回的结果是一个正整数M，表示从命令开始执行，到应用完file和pos表示的binlog位置，执行了多少事务。

这里我们假设，这条select查询最多在从库上等待1秒。那么，如果1秒内master_pos_wait返回一个大于等于0的整数，就确保了从库上执行的这个查询结果一定包含了trx1的数据。

若返回一个小于0的整数，则到主库执行查询语句，是这类方案常用的退化机制。因为从库的延迟时间不可控，不能无限等待，所以如果等待超时，就应该放弃，然后到主库去查。

GTID 方案

1	select wait_for_executed_gtid_set(gtid_set, 1);

这条命令的逻辑是：

等待，直到这个库执行的事务中包含传入的gtid_set，返回0；
超时返回1。

而MySQL 5.7.6版本开始，允许在执行完更新类事务后，把这个事务的GTID返回给客户端，这样等GTID的方案就可以减少一次查询。

这时，等GTID的执行流程就变成了：

trx1事务更新完成后，从返回包直接获取这个事务的GTID，记为gtid1；
选定一个从库执行查询语句；
在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)；
如果返回值是0，则在这个从库执行查询语句；
否则，到主库执行查询语句。

跟等主库位点的方案一样，等待超时后是否直接到主库查询，需要业务开发同学来做限流考虑。

怎么能够让MySQL在执行事务后，返回包中带上GTID呢？

将参数session_track_gtids设置为OWN_GTID，然后通过API接口mysql_session_track_get_first从返回包解析出GTID的值即可。

实际业务场景中，怎么结合以上几种处理过期读方案？

先在客户端对请求做分类，区分哪些请求可以接受过期读，而哪些请求完全不能接受过期读；然后，对于不能接受过期读的语句，再使用等GTID或等位点的方案。

但话说回来，过期读在本质上是由一写多读导致的。在实际应用中，可能会有别的不需要等待就可以水平扩展的数据库方案，但这往往是用牺牲写性能换来的，也就是需要在读性能和写性能中取权衡。

假设你的系统采用了我们文中介绍的最后一个方案，也就是等GTID的方案，现在你要对主库的一张大表做DDL，可能会出现什么情况呢？为了避免这种情况，你会怎么做呢？

假设，这条语句在主库上要执行10分钟，提交后传到备库就要10分钟（典型的大事务）。那么，在主库DDL之后再提交的事务的GTID，去备库查的时候，就会等10分钟才出现。

这样，这个读写分离机制在这10分钟之内都会超时，然后走主库。

这种预期内的操作，应该在业务低峰期的时候，确保主库能够支持所有业务查询，然后把读请求都切到主库，再在主库上做DDL。等备库延迟追上以后，再把读请求切回备库。

判断主库情况

主备切换有两种场景，一种是主动切换，一种是被动切换。而其中被动切换，往往是因为主库出问题了，由HA系统发起的。

select 1 判断

set global innodb_thread_concurrency=3;

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

 insert into t values(1,1)

innodb_thread_concurrency：控制InnoDB的并发线程上限。一旦并发线程数达到这个值，InnoDB在接收到新请求的时候，就会进入等待状态，直到有线程退出。

同时在执行的语句超过了设置的innodb_thread_concurrency的值，这时候系统其实已经不行了，但是通过select 1来检测系统，会认为系统还是正常的。

查表判断

在系统库（mysql库）里创建一个表，比如命名为health_check，里面只放一行数据，然后定期执行：

1	mysql> select * from mysql.health_check;

更新事务要写binlog，而一旦binlog所在磁盘的空间占用率达到100%，那么所有的更新语句和事务提交的commit语句就都会被堵住。但是，系统这时候还是可以正常读数据的。

更新判断

既然要更新，就要放个有意义的字段，常见做法是放一个timestamp字段，用来表示最后一次执行检测的时间。这条更新语句类似于：

1	mysql> update mysql.health_check set t_modified=now();

为了让主备之间的更新不产生冲突，我们可以在mysql.health_check表上存入多行数据，并用A、B的server_id做主键。

mysql> CREATE TABLE `health_check` (
  `id` int(11) NOT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

/* 检测命令 */
insert into mysql.health_check(id, t_modified) values (@@server_id, now()) on duplicate key update t_modified=now();

IO利用率100%表示系统的IO是在工作的，每个请求都有机会获得IO资源，执行自己的任务。而我们的检测使用的update命令，需要的资源很少，所以可能在拿到IO资源的时候就可以提交成功，并且在超时时间N秒未到达之前就返回给了检测系统。

检测系统一看，update命令没有超时，于是就得到了“系统正常”的结论。

之所以会出现这个现象，根本原因是我们上面说的所有方法，都是基于外部检测的。

内部统计

MySQL 5.6版本以后提供的performance_schema库，就在file_summary_by_event_name表里统计了每次IO请求的时间。

如果打开所有的performance_schema项，性能大概会下降10%左右。所以，我建议你只打开自己需要的项进行统计。你可以通过下面的方法打开或者关闭某个具体项的统计。

如果要打开redo log的时间监控，你可以执行这个语句：

1	mysql> update setup_instruments set ENABLED='YES', Timed='YES' where name like '%wait/io/file/innodb/innodb_log_file%';

现在你已经开启了redo log和binlog这两个统计信息，就可以通过MAX_TIMER的值来判断数据库是否出问题了。比如，你可以设定阈值，单次IO请求时间超过200毫秒属于异常，然后使用类似下面这条语句作为检测逻辑。

mysql> select event_name,MAX_TIMER_WAIT  FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') and MAX_TIMER_WAIT>200*1000000000;

发现异常后，取到你需要的信息，再通过下面这条语句：

1	mysql> truncate table performance_schema.file_summary_by_event_name;

把之前的统计信息清空。这样如果后面的监控中，再次出现这个异常，就可以加入监控累积值了。

MySQL性能优化

短连接风暴

MySQL建立连接的过程，成本是很高的。除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。

短连接模型是存在风险的。max_connections参数是用来控制一个MySQL实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的请求，报错提示：“Too many connection”

如果调高max_connections的值呢？

存在一定风险。因为设计max_connections这个参数的目的是想保护MySQL，如果我们把它改得太大，让更多的连接都可以进来，那么系统的负载可能会进一步加大，大量的资源耗费在权限验证等逻辑上，结果可能是适得其反，已经连接的线程拿不到CPU资源去执行业务的SQL请求。

处理掉占着连接不工作的线程

首先 show processlist 查看正在 sleep 的线程。

图中id=4和id=5的两个会话都是Sleep 状态。而要看事务具体状态的话，你可以查information_schema库的innodb_trx表。

trx_mysql_thread_id=4，表示id=4的线程还处在事务中。

可以优先断开事务外空闲太久的连接，再考虑断开事务内空闲太久的连接。

断开连接使用的是kill connection + id的命令，一个客户端处于sleep状态时，它的连接被服务端主动断开后，这个客户端并不会马上知道。直到客户端在发起下一个请求的时候，才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。

减少连接过程的消耗

有的业务代码会在短时间内先大量申请数据库连接做备用，如果现在数据库确认是被连接行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段。

因此可以重启数据库，并使用–skip-grant-tables参数启动。

MySQL 8.0版本里，如果你启用–skip-grant-tables参数，MySQL会默认把 –skip-networking参数打开，表示这时候数据库只能被本地的客户端连接。

慢查询性能问题

索引没有设计好

MySQL 5.6版本以后，创建索引都支持Online DDL了，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行alter table 语句。

比较理想的是能够在备库先执行。假设你现在的服务是一主一备，主库A、备库B，这个方案的大致流程是这样的：

在备库B上执行 set sql_log_bin=off，也就是不写binlog，然后执行alter table 语句加上索引；
执行主备切换；
这时候主库是B，备库是A。在A上执行 set sql_log_bin=off，然后执行alter table 语句加上索引。

这是一个“古老”的DDL方案。平时在做变更的时候，你应该考虑类似gh-ost这样的方案，更加稳妥。

语句没有写好

MySQL 5.7提供了query_rewrite功能，可以把输入的一种语句改写成另外一种模式。

比如，语句被错误地写成了 select * from t where id + 1 = 10000，你可以通过下面的方式，增加一个语句改写规则。

1
2
3

mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");

call query_rewrite.flush_rewrite_rules();

这里，call query_rewrite.flush_rewrite_rules()这个存储过程，是让插入的新规则生效，也就是我们说的“查询重写”。你可以用图中的方法来确认改写规则是否生效。

MySQL选错索引

应急方案就是给这个语句加上force index。

QPS突增问题

有时候由于业务突然出现高峰，或者应用程序bug，导致某个语句的QPS突然暴涨，也可能导致MySQL压力过大，影响服务。

当然，最理想的情况是让业务把这个功能下掉，服务自然就会恢复。而下掉一个功能，如果从数据库端处理的话，对应于不同的背景，有不同的方法可用。

一种是由全新业务的bug导致的。假设你的DB运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。
如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的QPS就会变成0。
如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的SQL语句直接重写成”select 1”返回。

当然，这个操作的风险很高，需要你特别细致。它可能存在两个副作用：

如果别的功能里面也用到了这个SQL语句模板，会有误伤；
很多业务并不是靠这一个语句就能完成逻辑的，所以如果单独把这一个语句以select 1的结果返回的话，可能会导致后面的业务逻辑一起失败。

所以，方案3是用于止血的，跟前面提到的去掉权限验证一样，应该是你所有选项里优先级最低的一个方案。

同时你会发现，其实方案1和2都要依赖于规范的运维体系：虚拟化、白名单机制、业务账号分离。由此可见，更多的准备，往往意味着更稳定的系统。

上线准备

通过下面这个过程，我们可以预先发现问题。

上线前，在测试环境，把慢查询日志（slow log）打开，并且把long_query_time设置成0，确保每个语句都会被记录入慢查询日志；
在测试表里插入模拟线上的数据，做一遍回归测试；
观察慢查询日志里每类语句的输出，特别留意Rows_examined字段是否与预期一致。（我们在前面文章中已经多次用到过Rows_examined方法了，相信你已经动手尝试过了。如果还有不明白的，欢迎给我留言，我们一起讨论）。

不要吝啬这段花在上线前的“额外”时间，因为这会帮你省下很多故障复盘的时间。

如果新增的SQL语句不多，手动跑一下就可以。而如果是新项目的话，或者是修改了原有项目的表结构设计，全量回归测试都是必要的。这时候，你需要工具帮你检查所有的SQL语句的返回结果。比如，你可以使用开源工具pt-query-digest(https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html)。

误删数据

传统的高可用架构是不能预防误删数据的，因为主库的一个drop table命令，会通过binlog传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令。

误删行

如果是使用delete语句误删了数据行，可以用Flashback工具通过闪回把数据恢复回来。

Flashback恢复数据的原理，是修改binlog的内容，拿回原库重放。而能够使用这个方案的前提是，需要确保binlog_format=row 和 binlog_row_image=FULL。

具体恢复数据时，对单个事务做如下处理：

对于insert语句，对应的binlog event类型是Write_rows event，把它改成Delete_rows event即可；
同理，对于delete语句，也是将Delete_rows event改为Write_rows event；
而如果是Update_rows的话，binlog里面记录了数据行修改前和修改后的值，对调这两行的位置即可。

如果误删数据涉及到了多个事务的话，需要将事务的顺序调过来再执行。

恢复数据比较安全的做法，是恢复出一个备份，或者找一个从库作为临时库，在这个临时库上执行这些操作，然后再将确认过的临时库的数据，恢复回主库。

事前预防

把sql_safe_updates参数设置为on。这样一来，如果我们忘记在delete或者update语句中写where条件，或者where条件里面没有包含索引字段的话，这条语句的执行就会报错。
代码上线前，必须经过SQL审计

使用truncate /drop table和drop database命令删除的数据，就没办法通过Flashback来恢复了。这是因为，即使我们配置了binlog_format=row，执行这三个命令时，记录的binlog还是statement格式。binlog里面就只有一个truncate/drop 语句，这些信息是恢复不出数据的。

误删库/表

要想恢复数据，就需要使用全量备份，加增量日志的方式了。这个方案要求线上有定期的全量备份，并且实时备份binlog。

在这两个条件都具备的情况下，假如有人中午12点误删了一个库，恢复数据的流程如下：

取最近一次全量备份，假设这个库是一天一备，上次备份是当天0点；
用备份恢复出一个临时库；
从日志备份里面，取出凌晨0点之后的日志；
把这些日志，除了误删除数据的语句外，全部应用到临时库。

建议

为了加速数据恢复，如果这个临时库上有多个数据库，你可以在使用mysqlbinlog命令时，加上一个–database参数，用来指定误删表所在的库。这样，就避免了在恢复数据时还要应用其他库日志的情况。
在应用日志的时候，需要跳过12点误操作的那个语句的binlog：
- 如果原实例没有使用GTID模式，只能在应用到包含12点的binlog文件的时候，先用–stop-position参数执行到误操作之前的日志，然后再用–start-position从误操作之后的日志继续执行；
- 如果实例使用了GTID模式，就方便多了。假设误操作命令的GTID是gtid1，那么只需要执行set gtid_next=gtid1;begin;commit; 先把这个GTID加到临时实例的GTID集合，之后按顺序执行binlog的时候，就会自动跳过误操作的语句。

加速方法

在用备份恢复出临时实例之后，将这个临时实例设置成线上备库的从库，这样：

在start slave之前，先通过执行
change replication filter replicate_do_table = (tbl_name) 命令，就可以让临时库只同步误操作的表；
这样做也可以用上并行复制技术，来加速整个数据恢复过程。
如果由于时间太久，备库上已经删除了临时实例需要的binlog的话，我们可以从binlog备份系统中找到需要的binlog，再放回备库中。

把之前删掉的binlog放回备库的操作步骤，是这样的：

从备份系统下载master.000005和master.000006这两个文件，放到备库的日志目录下；
打开日志目录下的master.index文件，在文件开头加入两行，内容分别是 “./master.000005”和“./master.000006”;
重启备库，目的是要让备库重新识别这两个日志文件；
现在这个备库上就有了临时库需要的所有binlog了，建立主备关系，就可以正常同步了。

建议将数据恢复功能做成自动化工具，并经常演练，避免线上出问题无法及时恢复数据。

延迟恢复备库

延迟复制的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER_DELAY = N命令，可以指定这个备库持续保持跟主库有N秒的延迟。

比如你把N设置为3600，这就代表了如果主库上有数据被误删了，并且在1小时内发现了这个误操作命令，这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

这样的话，你就随时可以得到一个，只需要最多再追1小时，就可以恢复出数据的临时实例，也就缩短了整个数据恢复需要的时间。

预防误删库/表的方法

账号分离。这样做的目的是，避免写错命令。比如：

我们只给业务开发同学DML权限，而不给truncate/drop权限。而如果业务开发人员有DDL需求的话，也可以通过开发管理系统得到支持。
即使是DBA团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账号。

制定操作规范。这样做的目的，是避免写错要删除的表名。比如：

在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。
改表名的时候，要求给表名加固定的后缀（比如加_to_be_deleted)，然后删除表的动作必须通过管理系统执行。并且，管理系删除表的时候，只能删除固定后缀的表。

rm 删除数据

对于一个有高可用机制的MySQL集群来说，最不怕的就是rm删除数据了。这时，你要做的就是在这个节点上把数据恢复回来，再接入整个集群。

当然了，现在不止是DBA有自动化系统，SA（系统管理员）也有自动化系统，所以也许一个批量下线机器的操作，会让你整个MySQL集群的所有节点都全军覆没。