mysql实战45讲（23-26）

mysql实战45讲学习笔记

23 mysql保证数据不丢失
23.1，binlog的写入
逻辑：事务执行过程中，先把日志写到binlog cache中，事务提交的时候，再把cache写到binlog文件中。
注意：事务不管多大，都一次性写入，binlog cache是私有的，每个线程有自己的cache，但是公用一份binlog文件。参数binlog_cache_size控制单个线程cache的大小。超过就存到磁盘。
写到cache和写到磁盘的时机，由sync_binlog参数控制：
sync_binlog = 0，表示只写cache不写磁盘
sync_binlog = 1 表示每次提交事务都执行写磁盘
sync_binlog = n(n>1)，表示每次提交只写cache，当积累了n个事务后再一起写磁盘。
n一般设置100-1000，不能无限大，因为当主机发生异常重启，会丢失最近n个事务的binlog日志。
23.2 redolog的写入
逻辑：先写到redolog buffer中，再更新到磁盘。不需要每次写buffer，就立即写磁盘。
1）存到redo log buffer中
2）写到磁盘，但是还没持久化
3）持久化磁盘
控制策略由innodb_flush_log_at_trx_commit控制：
0:只存buffer
1:每次提交就直接持久化到磁盘
2:每次提交都只是写到磁盘，但不持久化
持久化策略：
1）Innodb有一个后台线程，每隔一秒，就会把buffer中日志，调用wirte写到磁盘，然后调用fsync持久化到磁盘。
2）buffer占用空间即将达到innodb_log_buffer_size一半的时候
3）并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。
优化I/0瓶颈：
1）设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
2）将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
3）将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。

 

24 主备一致（数据备份）

24.1 原理：备库通过从主库拿到binlog，从binlog中获取命令执行备份

流程：

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 读取中转日志，解析出日志里的命令，并执行。

24.2 binlog格式

2.1 statement格式

可以看到记录的sql是跟我们执行的是一样的。这样有一个隐患：在主库上执行这条sql的时候，用的索引是a,但是在备库上执行这个sql的时候却使用了b索引，这样其实会导致主备不一致的情况。

2.2 row格式

 Table_map event，用于说明接下来要操作的表是 test 库的表 t;Delete_rows event，用于定义删除的行为。

格式为row的时候日志里记录了真实删除行的主键id，这样在备库上就是执行删除id的行，不会导致主备不一致的情况。

缺点：占用空间。

2.3 mixed格式

因为statement会导致主备不一致，而row又占空间，所以出现mixed：mysql自己会判断这个sql是否会可能导致主备不一致，，如果会，就用row，如果不会就用statement

但是还是建议线上设置称row，因为这样保证了一点：恢复数据

用 binlog 来恢复数据的标准做法是，用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行。类似下面的命令：
mysqlbinlog master.000001  --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

24.3 循环复制

当主备库都允许写入的话，就会出现这种情况，主库的binlog给备库用了，备库执行了binlog里的语句，也生成了一个binlog，然后给主库，这样就会出现循环复制的问题，

解决：

1）规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；

2）一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；

3）每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

 

25 mysql高可用策略

1，主备延迟

备库复制主库的时间，肯定是在主库执行之后，所以主备有个时间查，在某些原因下，备库消费中转日志的速度，比主库生产binlog的速度要慢。

1.1延迟来源

备库机器差，硬件原因

备库压力大，因为有些备库是要有读的能力的，产生压力，解决：多个从库分散读压力。

大事务：一个事务里执行东西太多，事务提交很慢，解决：多次提交，一个事务处理的语句少一些。

大表的DDL。

2，主备切换策略

2.1 可靠性优先策略

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

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

3）判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；

4）把备库 B 改成可读写状态，也就是把 readonly 设置为 false；

5）把业务请求切到备库 B。

缺点：可用性不高，因为如果延迟时间很久，几十分钟甚至更久，切换库的时间也会更久，因为需要等待复制好再切换。

2.2 可用性优先策略

将4，5步骤提前为1，2

缺点：会导致数据不一致的问题，binlog格式为mixed(statement)时数据不一致不明显，可能当时发现不了，binlog格式为row时，如果数据不一致就容易被发现。

总结：优先使用可靠性策略，因为保证数据性是第一。

 

26 备库并行复制能力

在5.6版本之前，mysql只支持单线程复制，由此在主库并发高，tps高时就会出现严重的主备延迟问题。

所以需要多线程复制。但是多线程分发需要满足两个条件：

1）不能造成更新覆盖，这就要求更新同一行的两个事务，必需分发到同一个worker中

2）同一个事务不能拆开，必需放在同一个worker中

1，按表分发策略

思路：如果两个事务更新不同的表，他们就可以并行，因为数据是存储在表里的，所以按表分发，保证数据正确

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

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

每个事务在分发的时候，跟所有worler的冲突关系包括以下三种情况：

1）如果跟所有 worker 都不冲突，coordinator 线程就会把这个事务分配给最空闲的 woker;

2）如果跟多于一个 worker 冲突，coordinator 线程就进入等待状态，直到和这个事务存在冲突关系的 worker 只剩下 1 个；

3）如果只跟一个 worker 冲突，coordinator 线程就会把这个事务分配给这个存在冲突关系的 worker。

优缺点：在多个表负载均匀的场景里应用效果很好，但是碰到热点表(就是操作频繁的表),所有的事务操作都只操作这个表，就会被分发到一个worker线程中，就又成了单线程复制了。

2，按行分发策略

思路：如果两个事务没有更新相同的行，他们在备库上就可以并行执行，显然这个模式的binlog的格式必需是row。

按行复制和按表渎职的数据结构差不多，也是为每个worker分配一个hash表，只是要实现按行分发，这个时候的key必需是“库名+表名+主键的值”。当然，还要考虑唯一键。

因为要计算每一行的hash值，所以相比按表并行的分发策略，按行需要更多的计算资源。

两种的共同点：

1）要能够从binlog里解析出来表名，主键，唯一索引的值，也就是说，主库的binlog格式必需是row.

2）表必需有主键

3）不能有外键，表如果有外键，记联更新的行不会记录在binlog中，这样就不行的。

3，按库分发策略（5.6版本的）

思路：跟按表和按行差不多，只是这里是按库来分的。

优势：1）构造 hash 值的时候很快，只需要库名；而且一个实例上 DB 数也不会很多，不会出现需要构造 100 万个项这种情况。

2）不要求 binlog 的格式。因为 statement 格式的 binlog 也可以很容易拿到库名。

缺点：如果主库的表都放在一个库里，那这个就没效果了，颗粒度有点大。

4，MariaDB并行策略

特性：

1）能够在同一组里提交的事务，一定不会修改同一行。

2）主库上可以并行执行的事务，备库上也一定是可以并行执行的。

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

1）在一组里面一起提交的事务，有一个相同的 commit_id，下一组就是 commit_id+1；

2）commit_id 直接写到 binlog 里面；

3）传到备库应用的时候，相同 commit_id 的事务分发到多个 worker 执行；

4）这一组全部执行完成后，coordinator 再去取下一批。

缺点：在主库上，一组事务在commit的时候，下一组事务是同时处于执行中的状态的，但是在备库上，要等第一组事务完全执行完成后，第二组事务才开始执行。

5，5.7版本的并行策略

参数slave-parallel-type控制：

1）配置为DATABASE，表示使用MySQL 5.6 版本的按库并行策略；

2）配置为LOGICAL_CLOCK，表示就用类似MariaDB的策略，5.7做了一些优化

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

1）同时处于 prepare 状态的事务，在备库执行时是可以并行的；

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

6，5.7.22并行策略

基于 WRITESET 的并行复制

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

1）COMMIT_ORDER，表示的就是前面介绍的，根据同时进入 prepare 和 commit 来判断是否可以并行的策略。

2）WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的 hash 值，组成集合 writeset。如果两个事务没有操作相同的行，也就是说它们的 writeset 没有交集，就可以并行。

3）WRITESET_SESSION，是在 WRITESET 的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序。

可以看出这和按行分发类似，优点是：

1）writeset 是在主库生成后直接写入到 binlog 里面的，这样在备库执行的时候，不需要解析 binlog 内容（event 里的行数据），节省了很多计算量；

2）不需要把整个事务的 binlog 都扫一遍才能决定分发到哪个 worker，更省内存；

3）由于备库的分发策略不依赖于 binlog 内容，所以 binlog 是 statement 格式也是可以的。