九.MySQL是怎么保证主备一致和高可用的

binlog可以用来归档，也可以用来做主备同步，但它的内容是什么样的呢，为什么备库执行了binlog就可以跟主库保持一致了呢？我们今天来探究下这个问题。

主备的基本原理

搭建两个节点A和B。开始时节点B是节点A的备库，备库节点B只读。A上的更新通过binlog同步到B，这样就可以保持节点A和节点B的数据是相同的。当需要切换的时候，就切成状态二，此时客户端读写访问的都是节点B，而节点A是B的备库。

主备同步的具体流程是这样的：备库B跟主库A之间维持了一个长连接，主库执行一个事务后，会写binlog，主库写完后会把binlog发到备库，备库B拿到binlog后，写到本地文件，称为中转日志（relay log）。接下来sql_thread读取中转日志，解析出日志里的命令，并执行。

binlog的三种格式

binlog有三种格式，statement，row和mixed。

STATEMENT

当binlog_format=statement时，binlog里面记录的就是原文。你可以用show binlog events in 'binlog.xxxx查看binlog的内容。

由于statement格式下，记录到binlog里的是语句原文，可能会出现主库和备库执行同一条语句但结果不一样的情况，造成数据不一致。

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000020 |      826 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'binlog.000020';
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                                       |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------+
| binlog.000020 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.21, Binlog ver: 4                                          |
| binlog.000020 | 125 | Previous_gtids |         1 |         156 |                                                                            |
| binlog.000020 | 156 | Anonymous_Gtid |         1 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                       |
| binlog.000020 | 235 | Query          |         1 |         325 | BEGIN                                                                      |
| binlog.000020 | 325 | Query          |         1 |         448 | use `test`; insert into t values(4,4,'2018-11-10')                         |
| binlog.000020 | 448 | Xid            |         1 |         479 | COMMIT /* xid=55 */                                                        |                                                      |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------+
10 rows in set (0.00 sec)

ROW

当binlog_format=row时，binlog里面记录的是对每行的具体操作以及操作的内容。

mysql> show binlog events in 'binlog.000008';
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| binlog.000008 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.21, Binlog ver: 4    |
| binlog.000008 | 125 | Previous_gtids |         1 |         156 |                                      |
| binlog.000008 | 156 | Anonymous_Gtid |         1 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000008 | 235 | Query          |         1 |         318 | BEGIN                                |
| binlog.000008 | 318 | Table_map      |         1 |         368 | table_id: 73 (test.t)                |
| binlog.000008 | 368 | Delete_rows    |         1 |         416 | table_id: 73 flags: STMT_END_F       |
| binlog.000008 | 416 | Xid            |         1 |         447 | COMMIT /* xid=9 */                   |
| binlog.000008 | 447 | Rotate         |         1 |         491 | binlog.000009;pos=4                  |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
8 rows in set (0.14 sec)

mixed

当binlog_format=mixed时，binlog里面的内容既有statement格式，也有row格式。

为什么会有mixed格式的binlog：

• 因为有些statement格式的binlog会导致主备不一致，所以要使用row格式。
• 但row格式的缺点时占用空间。比如你用delete删掉10w行记录，用statement格式的话就是一个语句记录到binlog中，占用几十字节的空间；但如果使用row格式的binlog，就要把这10w条记录都写到binlog中。这样不仅会占用更大空间，同时写binlog也要耗费IO资源，影响执行速度。
• 因此，MySQL就采取了折中的方案，采用mixed格式的binlog。MySQL判断某条语句是否会引起主备不一致，如果可能引起主备不一致，就用row格式，否则就用statement格式。

mixed格式可以利用statement格式的优点，同时避免了数据不一致的风险。

为什么建议把MySQL的binlog设置成ROW格式：

MySQL的binlog设置成ROW格式可以方便恢复数据。

我们分别从insert，delete，update这三种角度看看ROW格式的数据恢复问题。

1.如果执行错了insert语句。row格式下insert记录了所有字段信息，把insert换成delete重新执行一遍就可以了。

2.如果执行错了delete语句。row格式下会把delete的行整行信息保存起来，执行错了把delete变成insert重新执行一遍就可以了。

3.如果执行错了update语句。row格式下update会记录修改前的整行数据和修改后的整行数据，也是可以进行恢复。

MySQL如何保证高可用

在主备关系中，只要主库执行更新生成的所有binlog，都可用传到备库并被正确的执行，备库就能达到和主库一致的状态，这就是最终一致性。MySQL要提供高可用，只有最终一致性是不够的，还需要主备延迟足够小。

所谓主备延迟，就是同一个事务，在备库执行完成的时间T3和主库执行完成的时间T1之间的差值，也就是T3-T1。你可以在备库上执行show slave status命令，它的返回结果seconds_behind_master，用于表示当前备库延迟了多少秒。

主备延迟的来源

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

一般情况下，人们这么部署的想法是，反正备库没有请求，可用用差一点儿的机器。其实我们都知道，更新请求对IOPS的压力，在主库和备库上是无差别的。因为备库机器的性能差，跟不上主库的执行速度，导致主备延迟。部署是应该选择对称部署。

2. 备库的压力大

一般情况下，主库提供了写能力，备库可以提供一些读能力。或者一些分析的脚本，在主库上跑影响业务，这些脚本只能在备库上跑。结果就是，备库上的查询耗费了大量的CPU资源，影响了同步速度，造成主备延迟。

3. 大事务。

因为主库必须等事务全部执行完才能写入binlog，再传给备库。假如一个主库上的语句执行10min，那这个事务很可能导致从库延迟10min。一次性的用delete语句删除太多数据，这就是一个典型的大事务场景。

4. 备库的并行复制能力。

备库用单线程执行relay log，而主库的TPS一直比较高的话，备库的执行binlog的速度一直赶不上主库生成binlog的速度，就会造成主备延迟。

可靠性优先策略

在双M结构下，主备切换的流程是这样的。

1.判断备库现在的seconds_behind_master，如果小于某个值（比如5s）继续进行下一步，否则持续重试这一步。

2.把主库A改成只读状态，即把readonly设置为true。

3.判断备库的seconds_behind_master,直到这个值变为0为止，此时把备库B改成可读写状态，也就是把readonly改成false。

4.最后把业务请求切换到备库B。

可用性优先策略

如果强行把上面的第4，5步调整到最开始执行，不等主备数据同步，直接把业务请求切换到备库B，并且让备库开始读写，那么系统几乎就没有不可用时间了。可用性切换流程，可能出现数据不一致的情况。在实际应用中，建议使用可靠性优先的策略。毕竟保证数据准确，是数据库服务的底线。在这个基础上，通过减少主备延迟，提升系统的可用性。

在满足数据可靠性的前提下，MySQL高可用系统的可用性，是依赖于主备延迟的。延迟的时间越小，在主库故障的时候，服务恢复需要的时间就越短，可用性就越高。