Blage's Coding
phan
2023-07-03
目录

MySQL一致性与高可用性

# MySQL一致性与高可用性

# 1.MySQL一致性

# 1.1备库设置Readonly

客户端和主库进行连接，并在主库上执行更新操作后，主库会将所有更新都同步到备库中。

一般情况下，备库需要进行设置成readonly只读模式，好处如下：

  • 为了减少主库的压力，一些运营类查询语句会放到备库上查，设置只读可以设置防止误操作。
  • 防止主备切换时出现双写bug，导致主备不一致。
  • 通过readonly可以区分节点是主库还是存库。

只读模式对于拥有super用户权限的用户是无效的，用于备库的同步更新的线程就拥有超级权限。因此备库的更新不会受限于只读模式。

# 1.2主备日志同步流程

主库(A节点)执行事务并更新日志后，通过dump_thread线程专门用于维护主库和备库的长连接。

  1. 备库B通过change master命令设置主库A的IP、用户名、密码，要读取的主库的binlog文件名和位置偏移。
  2. 备库上执行start slave命令后，备库上会启动两个线程：
    • io-thread：负责与主库进行连接。拿到主库的binlog日志后会写到本地文件，也就是中转日志(relaylog)
    • sql-thread：读取中转日志，并解析出日志的SQL命令然后执行。

# 1.3binlog的三种格式

binlog_format：设置binlog的格式。可选参数包含以下三种格式。

# statement

使用以下命令查看binlog日志文件：

/*查看当前binlog的记录位置*/
show master status;
/*根据日志名称查看指定日志文件*/
show binlog events in 'master.000001';
1
2
3
4

得到的binlog内容如下，其中begin和commit之间记录的是SQL语句原文。

  • 第三行：首先use 'test'表示先锁定删除数据表t所在的数据库test，然后接着执行删除命令。
  • 第四行：XID用于redolog与binlog两者之间的定位和标识。
delete from t where a>=4 and t_modified<='2018-11-10' limit 1;
1

💣statement格式存在的问题：考虑如果客户端要执行以上语句，其中t_modified和a字段都有索引，因为limit 1的缘故，该条删除语句分别使用a索引和t_modified索引，定位到的可能不是一条行记录。

也就是说在statement格式下，仅仅根据SQL语句进行备份，如果主库执行时使用了索引a，而备库执行时使用了t_modified，那么就会导致主备不一致的情况。MySQL认为这是不安全的。

# row

借助mysqlbinlog工具解析查看binlog具体内容。其中-vv表示将内容都解析出来，可以看到各个字段的值。

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

row格式下，begin与commit之间记录的是两个event事件：

  • Table_map ：用于说明SQL语句操作的是哪个库哪个表

  • Delete_rows：用于定义删除行为。其中根据binlog_row_images不同，记录的信息也存在些许差别：

    • binlog_row_image = FULL：记录更新之前的记录，以及更新之后的记录。增加binlog文件大小，增加磁盘IO，但从数据恢复角度来说更加安全。

      ### UPDATE `test`.`t2`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='gz' /* STRING(20) meta=65044 nullable=1 is_null=0 */
###   @4=3 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='gz' /* STRING(20) meta=65044 nullable=1 is_null=0 */
###   @3=99 /* INT meta=0 nullable=1 is_null=0 */
      1
      2
      3
      4
      5
      6
      7
      8
      9

    • binlog_row_image = MINIMAL：仅记录该行更新的列字段值信息。相比FULL方式binlog文件更小，但不够安全。

      ### UPDATE `test`.`t2`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
### SET
###   @3=100 /* INT meta=0 nullable=1 is_null=0 */
      1
      2
      3
      4
      5

综上，在row格式下，binlog记录了具体更新操作行的主键id值，从而保证无论是主库还是备库，都能够根据主键id定位到同一行数据。

# mixed

对比上面两种格式的binlog，不难分析出存在以下优缺点：

  • statement格式的binlog文件更小，因为只需要花费几十个字节存储一个SQL语句。但是在备份同步时，可能会出现主备不一致的情况。
  • row格式的binlog虽然解决了主备不一致的问题，但binlog文件较大浪费空间，耗费IO资源。假设一个delete语句删除十万行数据，那么binlog文件中就需要记载十万个行记录的信息(至少包含主键id)。

mixed：这种格式相当于以上两种方案的折中。对于一条SQL语句，如果不会造成主备不一致，那么就会转化为statement格式；否则就用row格式。

# 增删改场景下binlog格式选择

结论：一般来说主要使用row格式。因为row格式有一个很大的好处：恢复数据。

row格式中记载了更新前和更新后行记录的所有信息，因此删错了就在日志中改为插入；插错了就在日志中改为删除；对于更新操作只需要将event事件更新前后的记录信息对调即可。

insert into t values(10,10,now());
1

这条SQL语句需要插入当前时间，因此备库根据binlog的SQL语句同步数据时，肯定会由于存在时间间隔而导致主从不一致的情况。因此这种情况下有两种解决方案：

  • 使用row格式不会出现主备不一致的情况，直接根据该条行记录的列信息进行同步和备份。

  • 使用mixed格式的情况下，MySQL虽然会选择statenment格式，但是会加上时间戳信息，保证now()返回的是一个设定好的时间。

    set TIMESTAMP=1546103491;
insert into t values(10,10,now());
    1
    2

# 如何根据binlog来恢复数据

statement格式的SQL语句在执行时，有时候依赖于binlog的上下文命令。因此使用binlog恢复数据时，需要将解析结果整行整段一起执行。

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

上述命令是指将master.000001文件2738字节到2973字节中间这段内容进行解析和重放，然后放到MySQL执行。

# 1.4循环复制问题

实际生产过程中，采用的是双M结构(双主节点)。

  • 不存在绝对的主节点或者从节点。节点之间互为主备关系。
  • 如果客户端在A节点进行更新操作，那么B节点就作为从节点将A节点内容同步到自己节点上。反之在B节点上进行更新，那么A节点就需要同步更新。

log_slave_updates ：设置为on，表示备库执行relaylog之后会生成binlog。

循环复制问题

A节点执行完更新语句将binlog传给备库B，B节点执行更新语句的同时自身也会生成新的binlog文件，因此这时候A节点相当于”备库“，又轮到A节点同步...这样就会不断循环执行同一条更新语句。

MySQL对于每条命令，第一次执行时都会在binlog中记下所在的server id，并且将这条日志在传给备库时，备库并不会修改。而每个库的server id都是不同的，所以可以根据server id来中止恶行循环。备库在执行relaylog时，生成的binlog的server id需要与relaylog的id保持一致。

整个过程如下：

  1. A节点执行更新操作，并生成binlog，通过后台线程传给B节点
  2. B节点收到后，执行relaylog，并生成新的binlog，此时该binlog中的更新操作的server id仍然为A节点的id
  3. A节点作为B的”从节点“，拿到B传过来的binlog，发现里面的server id就是自己的server id。因此不再执行该日志的更新操作。

然而这个方法也还是存在一定的缺点，当节点数大于2时，备库之间发送的binlog日志同样会造成死循环。也就是说发送binlog进行同步的节点只能由主库发起，”备库“与”备库“之间不能发起同步。

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

从节点可以执行以上语句，当前从节点受到日志更新后，主节点还是一开始的主节点，从而中止日志传播。

# 2.MySQL高可用性

主备延迟

主库A执行完一个事务写入binlog，计为时刻T1；备库B收到更新binlog并执行完这个更新事务，计为时刻T2；

主备延迟就是主库执行完成的时间与备库完成同步的时间之差，也就是T2-T1。

在备库执行以下SQL语句查看备库延迟seconds_behind_master 的值。忽略网络因素，主备延迟的来源主要是备库消费relaylog的这段时间。

show slave status;
1

# 2.1主备延迟的来源

  • 备库所在的机器性能较差

备库设置”非双1“的模式下，更新操作导致的大量IOPS，因此备库在”烂机器“上抢占CPU和内存资源执行同步更新事务，导致更大的主备延迟。

  • 备库压力大

为了不直接影响业务，个别查询语句往往在备库上执行，而这些查询语句也会耗费大量CPU资源，从而影响新进来的同步更新速度。

解决方案：①一主多从：一个备库不行那就搞多几个备库，让多个备库分担查询压力。

  • 大事务

一个大事务如果主库执行需要十分钟，那么从库执行时也至少需要十分钟。从而造成较大的主备延迟。

因此需要尽可能避免大事务操作，可以将数据分批处理。

# 2.2可靠性优先策略

当有新的更新操作到来后，整个主备切换流程如下：

  1. 首先判断备库B的主备延迟seconds_behind_master是否小于设定的阈值，如果不小于则轮询重试这一步。
  2. 将主库A设置为只读模式。——此时整个数据库直到步骤5处于短暂的不可用状态。
  3. 判断备库B的seconds_behind_master是否等于0，如果不等于0则持续轮询。
  4. 将备库B设置为可读写模式。
  5. 把业务请求切到备库B,完成主从切换。

整个过程最耗时的地方是步骤3，因此这就是为什么存在主备延迟double check的原因：

  • 第一遍轮询主备延迟原因在于，步骤三耗时比较慢，如果进来时的主备延迟就很大，那么很早就关闭主库A的权限会导致整个系统不可用的时间大大延长。
  • 第二遍轮询的目的在于保证可靠性，等待整个备库B执行完所有的事务，完成同步。

显然可靠性策略能够保证主备库数据的一致性，但是需要花费较大时间等待备库完成同步。

# 2.3可用性优先策略

在可用性优先策略中，将上述步骤4、5提前执行。即先切换，后补数据实现同步：

  1. 将备库B设置为可读写模式
  2. 把所有业务请求切到备库B上，完成主从切换。
  3. 同上步骤一到三，备库B执行主库A传过来的更新日志，实现同步。

可用性策略能够保证系统以较低的时延实现主备切换，速度快，但缺点在于容易出现主备数据不一致。

# 2.4主库断电场景

场景：主库突然断电或者宕机，并且当前主备延迟是30分钟。

这种情况下显然只能采用可用性优先策略，因为如果不立马将当前备库切换到主库，就会导致整个系统不可用；可即使是切换到了备库B，主备延迟有30分钟那么大，这意味着30分钟内更新的数据用户是查询不到的，对个别业务场景而言，这种状态也是不能接受的。

总而言之，在保证数据可靠性优先的条件下，MySQL的可用性依赖于主备延迟。延迟越小，主库出故障后恢复服务所需要的时间越短，可用性越高。

# 3.MySQL并行复制

问题：在高并发场景下，大量用户请求会分别打到主库和备库。对主库来说因为InnoDB支持更细粒度的行锁，因此主库对业务并发度的支持还是比较友好的；而对于从库，尤其是sql线程执行中转日志更新DATA时，如果使用单线程，就会产生较大的主备延迟，备库永远追不上主库。

解决问题的核心在于，将单线程sql_thread拆成多线程进行复制。具体来说原来的sql_thread会被拆掉分成单个coordinator线程和多个worker线程。其中coordinator负责读取中转日志和分发事务，而不同worker的执行顺序不能控制，所以分发时需要遵循以下原则：

  • 同一个完整的事务不能被拆开，必须放到同一个worker中。
  • 同一行的不同事务必须分发到同一个worker，从而控制执行顺序，保证主备一致。

要想迅速标识出表名和行名，显然哈希表是一个很好的选择，因为它的key具有唯一性。下面介绍以下两种策略：

# 3.1按表分发规则

核心思想：不同的事务如果操作同一个数据库的表，那么将这些事务都放到同一个worker进行。

每一个worker对应一个哈希表，用于保存当前worker的执行队列。哈希表的key为数据库名+表名，value代表这个表的执行队列，有多少个事务同时要在这个表上进行操作。

worker冲突

如果当前worker的事务执行队列中存在一个事务A，它与当前事务B操作的都是同一张表，那么则说当前事务与该worker冲突。

规则如下：

  • worker中如果某个表都已经复制更新完毕，执行队列为0，那么则将该表从哈希表中的key删除。
  • 当前事务如果与多个worker存在冲突，那么coordinator线程则进入等待状态。
  • 直到当前只有一个worker与当前事务存在冲突，那么coordinator线程会将该事务分配给该worker，并加入冲突表的执行队列。

💣按表分发存在的问题：按表转发的粒度比较大，如果多个操作同个表的高并发(热点数据)请求打进来，就会导致所有的事务堆积进同一个worker中。从而退化为“单线程复制”。

# 3.2按行分发规则

核心思想：如果两个事务没有操作同一行数据，那么它们可以在不同的worker下并行执行。

注意仅使用主键ID作为key虽然可以将不同行的事务分发到对应的桶，但在“唯一索引”上依然存在时序约束，个别情况下不同的行数据也需要放在同一个worker中顺序执行。因此哈希表的key需要设置多个项：

  • 库名+表名+主键名称+主键字段值
  • 库名+表名+唯一索引字段名称+唯一索引字段值
  • ...

以上多个项都是“且”的关系，也就是说只要有一项存在冲突，那么当前事务就与该worker就存在冲突。既然需要从relaylog中转日志中读取列字段的值，显然binlog格式只能使用row格式。

💣按行分发存在的问题：按行分发虽然并发度更高，但是解析binlog+计算和保存哈希这两个步骤成本较高，消耗较多的资源。

# 3.3其它规则策略

MySQL5.6：按库分发。需要将热度相同的表均摊到不同的数据库当中。

MariaDB：利用组提交里事务之间操作的行数据肯定不会产生冲突的特性，备库每次将“同组”commit_id的所有事务一次性取出来，并直接分发到不同的worker(肯定不会冲突)。缺点在于下一组的所有事务，需要等到所有worker中当前这组事务都更新完，才会开始下一组事务的更新。

MySQL5.7：上述两种策略的融合。核心思想是既然处于prepare阶段的事务就已经通过锁冲突检测了，因此同时处于prepare阶段(以及到commit阶段)的所有事务都是可以并行的。本质上是在两阶段写binlog时，同时添加记录commit_id，备库通过这个id来判断是否可以并行。此外可以通过调节binlog两个参数，利用延迟机制使组提交的效果最好。

MySQL5.7.22：基于WRITESET进行复制。与按行分发的区别在于，它是在主库生成binlog后直接将writeset写进去，因此备库不需要扫描binlog(取出每个行数据)也不需要计算。判断冲突时，只要两个事务之间的writeset没有交集，那么这两个事务就可以分别放入不同的worker并行执行。

5.7.22根据参数binlog-transaction-dependency-tracking设置不同的策略：

  • COMMIT_ORDER：根据同时进入prepare的事务来判断是否可以并行。
  • WRITESET：计算出这行数据的所有哈希值，并组成集合。
  • WRITESET_SESSION：在上面基础上多了一个约束，在主库中同一个线程先后执行的两个事务，在备库执行同步时，需要保证相同的先后顺序(相当于要放在同一个worker)。

# 4.MySQL一主多从下的主备切换

A与A'互为主备库，B和C均为从库。当主库A发生故障时，备库A'会成为新的主库，同时所有从库改接新的主库。

其中从库节点B更换主节点时，需要执行如下change master命令，包括指明新主节点A'的IP、端口、用户名、密码，同时还需要指明同步位点，即主库对应的文件名和日志偏移量。

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
1
2
3
4
5
6
7

# 4.1基于同步位点的主备切换

同步位点

从库所需要同步的主库对应文件名和日志偏移量

问题：主库A发生故障时，取故障时刻T，然后用工具扫描备库A' 的文件，最后得到在T时刻的位点。然后得到的结果并不精准，因为主库A发生故障后，所有从库和备库并不是绝对静止，可能会存在重复执行事务的情况。

常用两种解决方案如下：

  • 每次从库B如果因执行重复事务出现报错，则执行以下SQL语句主动跳过一个事务，直到不再出现报错为止。

    set global sql_slave_skip_counter=1;
start slave;
    1
    2

  • 设置slave_skip_errors参数，直接跳过指定的错误码。当新的主备关系确立后，稳定一段时间确认新的主从关系后，再开启错误拦截。

# 4.2基于全局事务ID的主备切换

GTID：全局事务ID，用于每个事务的唯一标识。格式为GTID=server_uuid ：gno

gno：每次提交事务时才会分配给这个事务，并加一。因此GTID往往是连续的。

transaction_id：也叫事务ID，只要事务执行了就会分配。即使这个事务回滚了，下一个事务ID也会继续递增。

每一个MySQL数据库实例都会维护一个GTID集合，里面包含该节点执行过的所有事务ID标识。

从节点要使用GTID模式完成主备切换，不需要指定文件名+偏置这两个位点信息，只需要指定使用GTID协议，具体流程如下：

master_auto_position=1
1
  1. 从节点B指定A' 作为主库，开始进行主备切换。
  2. B节点将自己节点上执行过的所有事务集合set_b发送给主库A’ 。
  3. 主库A' 计算出A‘节点与B节点的事务ID集合的差集（即在A’集合但不在B集合的事务ID），并检查A'本地binlog文件的事务是否包含这个差集：
    • 如果不包含，则说明A' 将B需要同步的事务日志给删掉了，返回错误(获取主库已删除gtid_purged，并在从库指定新的GTID同步起点)。
    • 如果全部包含，则从binlog文件中找出第一个不在set_b的事务，作为B节点的同步位点。
  4. A' 节点从这个事务开始，在binlog中读取后续的文档内容，并发送给从库B进行同步。

💡GTID模式的好处：差集计算保证了从库没有执行过的事务日志，主库一定有并且是完整的。否则这两个库之间就不能建立主备关系。

# 4.3GTID与在线DDL

场景：因为索引缺失导致的性能问题，在业务高峰期时对主库通过online DDL添加索引，可能会对主库造成影响。所以最优实践是在备库添加索引后，再进行主备切换。

在互为主备的双M结构下，当前主库A，备库为B；

  1. 首先主库A执行stop slave，停止写入中转日志，暂停同步线程。

  2. 然后在备库B执行DDL语句，此时并不需要关闭binlog(设置 set_log_bin=off)，依旧将DDL语句写到binlog。

  3. 记录下备库B该条DDL语句对应的GTID。

  4. 执行以下语句序列，使主库A的GTID集合中写入DDL对应的GTID，保证主库A此时不会执行DDL影响线上：

    set GTID_NEXT="server_uuid_of_Y:gno";
begin;
commit;
set gtid_next=automatic;
start slave;
    1
    2
    3
    4
    5

  5. 执行主备切换。因为DDL语句对应事务已经被跳过了，因此A库要想添加索引同步，只能再执行一遍上述1-5步骤，手动在备库(先前的主库)中添加对应的索引。

✨通过提交指定GTID的空事务，可以将其它全局事务ID写入当前库的GTID集合当中，从而避免执行或者跳过某些事务。

# 5.MySQL读写分离

过期读

客户端执行完一个更新事务后，立刻发起一个查询(打到从库)，因为存在主备延迟从库还没有完成更新，导致客户端读到的是更新前的旧数据。

下面简要介绍和分析几种处理“过期读”的方案。

# 方案一、强制走主库

方案：根据不同业务的查询请求来划分，对于强一致性要求的结果，则必须直接走主库；而对于弱一致性要求的查询结果，则可以走从库。

强制走主库放弃了数据库的可扩展性，导致读写压力全部打到主库。但这种方法也是用的最多的。

# 方案二、sleep方案

方案：设定一个休眠时长，读从库之前sleep一下(或者前端设置一个交互界面和按钮)，大多数情况下经过这个时间间隔后，可以拿到最新的数据。

这种方法存在的问题在于，查询前人为设定的延迟时间不好精准确定下来。

# 方案三、判断主备无延迟的方案

  • 根据seconds_behind_master判断

每次执行查询之前先通过show slave status查看从库的主备延迟，如果不等于0，那么一直等直到主备延迟为0才在从库上执行查询操作。

  • 对比位点

如果主库和从库的文件名和更新偏置两组位点值完全相同，那么表示从库已经完全同步完成。

  • 对比GTID

判断主库和从库的GTID集合、以及执行完成的GTID集合。如果主从库两个集合相同，则表示备库已经同步完成。

存在的问题：主库已经执行完毕，并已经向客户端发送了提交确认(还没有生成GTID)，但是此时“备库还没有收到日志”，此时虽然备库查询主备延迟发现是0，但是备库还没有执行更新同步。而这种情况下导致的过期读用上面三种方法均失效。

# 方案四、semi-sync

启用半同步复制后，能够保证所有向客户端发送确认的事务，备库都已经收到该事务的日志。

①解决主库突然断电问题：要么主备都已经收到同步日志，要么客户端没收到直接回滚。保证主从数据一致性。

②解决上述过期读状态：通过semi-sync客户端ack和位点对比判断双重条件，可以保证从库不是过期读。

但是半同步不能解决以下问题：

①一主多从的情况下，主库只要收到一个从库的ack立刻会向客户端发送确认。因此半同步方式不能保证所有从库都处于最新的状态。

②❓在持续延迟的情况，可能出现过度等待。

# 方案五、等主库位点

借助SQL语句，获取从库同步到指定的主库位点所需要的时间。该方法是一个阻塞方法，可以设置超时时间。

下列语句表示从库应用同步完指定的file和pos的binlog位置，一共执行了多少事务。因此方案的核心就是利用该语句的返回结果判断当前从库是否同步完毕。

select master_pos_wait(file, pos[, timeout]);
1
  1. 主库执行更新操作，并立刻执行show master status获取当前主库执行到的file和position。
  2. 选定一个从库执行上述SQL查询语句：select master_pos_wait(File, Position, 1)；
    • 如果返回值是正整数，那么说明该从库已经完成同步，直接在该从库上执行查询语句。
    • 否则到主库执行查询语句。

如果SELECT查询等待超过1s，则退化为方案一到主库上查询。

# 方案六、GTID方案

借助SQL语句，等待从库执行的事务中包含传入指定gtid_set，如果存在gtid则返回0，超时返回1。根据这个值判断当前从库是否完成同步。

不同于方案五，这里获取主库gtid信息可以直接从返回包拿到。通过参数设置+程序API接口。

在大事务+DDL场景下，如果依然选择等GTID方案，会导致要很长时间备库才会同步完成。这种场景下，可以采用以下读写分离方案：

  • 业务低峰期将所有查询请求都切到主库。
  • 在从库执行DDL（可以关闭set_log_bin）
  • 主从切换，所有请求打到新的主库；然后备库进行DDL
  • 当备库的主备延迟降低追上主库后，再将读请求切到备库。
编辑 (opens new window)
#数据库
上次更新: 2023/12/15, 15:49:57
binlog和redolog如何写入磁盘
kill命令

← kill命令→

Theme by Vdoing | Copyright © 2023-2024 blageCoder