MySQL

文章作为学习笔记，文章内容来自 “极客时间”专栏《MySQL实战45讲》和 “中华石杉”专栏《从零开始带你成为MySQL实战优化高手》，如有侵权，请告知，必将及时删除。

MySQL 架构及 SQL 执行流程

当我们发送一条 SQL 给 MySQL 的时候

首先 MySQL 的网络连接会监听到 SQL 语句，然后交给 SQL接口 来处理，SQL接口 再将 SQL 交给 SQL解析器 来将 SQL 解析成程序认识的语言

然后再交给 查询优化器 来分析可以达到目的的多种查询路径，然后选择一个最优的查询路径

然后 执行器 就会根据 查询优化器 生成的一套执行计划，不停的调用 存储引擎 的各种接口去完成 SQL 语句的执行计划

MySQL 的 Buffer Pool

Buffer Pool

InnoDB 中有一个非常重要的组件，就是 缓冲池（Buffer Pool），这里面会有很多数据，用于优化在查询时候的效率。

比如当我们在做数据库更新操作的时候，有一条sql

1	update user set name = 'lisi' where id = 1;

InnoDB 在执行这个语句的时候会先去 缓冲池 中去查找，如果不存在，会先从磁盘中将数据拿到 缓冲池 中进行操作

这个 Buffer Pool 默认的大小是 128M，实际生产环境下肯定是不够用的，需要做一定的配置

MySQL将数据抽象出来了一个 数据页 的概念，也就是说我们的磁盘文件中会有很多的数据页（默认大小 16KB），每一页存放很多行数据

当我们操作一行数据的时候，MySQL 会找到这行数据所在的数据页，然后从磁盘中将整个数据页的数据直接加载到 Buffer Pool 中

也就是说 Buffer Pool 中存放的是一个一个的数据页（通常叫做 缓存页，默认大小也是 16KB）

Buffer Pool 中有描述数据块、缓存页、free链表、flush链表、lru链表…

描述数据块，记录数据页所属的空间、数据页编号、这个缓存页在 Buffer Pool 中的地址等等，和 缓存页 一一对应
缓存页，存储从磁盘加载进来的数据页
哈希表结构，如何知道一个数据页有没有被缓存？就是通过这个哈希表；表空间号+数据页号，作为一个key，然后缓存页的地址作为value。
free 链表，用来存放空闲缓存页的描述数据块信息
flush链表，用来存放修改过的需要刷盘的缓存页的描述数据块信息
LRU链表，用来实现 LRU 算法，头部存放的是最近使用的缓存页描述数据块，尾部存放的是最少使用的缓存页描述数据块

chunk机制

Buffer Pool 是由很多 chunk 组成的，他的大小是 innodb_buffer_pool_chunk_size 参数控制的，默认值就是 128MB。

每个 Buffer Pool 里的每个 chunk 里就是一系列的描述数据块和缓存页，每个 Buffer Pool 里的多个 chunk 共享一套 free、flush、lru 这些链表

基于 chunk 机制，就可以支持动态调整 Buffer Pool 大小了

MySQL中的 LRU

LRU链表 会被拆分为两个部分，一部分是热数据，一部分是冷数据。比例是由 innodb_old_blocks_pct 参数控制，默认是 37，也就是说冷数据占比37%

数据页第一次被加载到缓存的时候，会被放在冷数据区域的链表头部

什么时候被放入热数据区域？

有个参数 innodb_old_blocks_time ，默认值是 1000，也就是 1000ms。意思是当数据页加载到缓存页之后，在1000ms之后再访问这个缓存页，才会被移动到热数据区域的链表头部

LRU链表的热数据区域是如何进行优化的？

热数据区域里的缓存页可能是经常被访问的，所以这么频繁的进行移动是不是性能也并不是太好？

所以说，LRU链表的热数据区域的访问规则被优化了一下，即你只有在热数据区域的后3/4部分的缓存页被访问了，才会给你移动到链表头部去

change buffer

当需要更新一个数据页的时候，如果数据页在内存中就直接更新，如果这个数据页不在内存中，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在什么时候查询这个数据页的时候，再将数据读入内存，然后执行 change buffer 中与这个页的 merge 操作（除了访问这个数据页会出发 merge 外，系统也有后台线程定期 merge。数据库正常关闭的过程中，也会merge）。

需要说明的是：

虽然名字叫 change buffer，实际上它是可以持久化的数据。也就是说， change buffer 也会被写入到磁盘上

什么条件下可以使用 change buffer？

唯一索引的更新不能使用（因为需要必须将数据读入内存来判断是否已存在）

只有普通索引可以使用

change buffer 的使用场景：

对于写多读少的业务场景，使用效果最好。比如账单类、日志类的系统

change buffer用的是 buffer pool 里的内存，因此不能无限增大。可通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为50的时候，表示 change buffer 的大小最多只能占 buffer pool 的50%。

MySQL 的预读机制

哪些情况下会触发MySQL的预读机制？

有一个参数是 innodb_read_ahead_threshold，他的默认值是 56，意思就是如果顺序的访问了一个区里的多个数据页，访问的数据页的数量超过了这个阈值，此时就会触发预读机制，把下一个相邻区中的所有数据页都加载到缓存里去
有一个参数是 innodb_random_read_ahead，他的默认值是 OFF，如果Buffer Pool里缓存了一个区里的13个连续的数据页，而且这些数据页都是比较频繁会被访问的，此时就会直接触发预读机制，把这个区里的其他的数据页都加载到缓存里去

MySQL 的 undo log

假如这条数据的 name 原来的值是 zhangsan，现在要更改为 lisi

因为 InnoDB 是支持事务的，有事务就必然会有回滚，那如果需要回滚，就需要保存修改之前的数据状态

所以在执行这条语句之前，会先把原来的值 zhangsan 和 id = 1 这些信息保存到一个地方，这个地方就是 undo 日志文件

MySQL 的 redo log

redo log

所谓的 redo log，就是记录下来你对数据做了什么修改，比如对“id=1这行记录修改了name字段的值为 lisi”，这就是一个日志

这段日志最开始是存在内存中的 Redo Log Buffer 中

因为 InnoDB 在修改数据的时候，是先修改的 缓冲池 中的数据，那么如果在修改完以后还没来得及刷入磁盘，就会造成数据的丢失

那么 redo log 就是来避免这种情况的

当我们提交事务的时候，会根据一定策略把 redo log 从 Redo Log Buffer 中刷入到磁盘中，这个策略是通过 innodb_flush_log_at_trx_commit 来配置的

0 提交事务的时候，不会把 Redo Log Buffer 中的日志刷入磁盘。此时提交事务后宕机了，结果造成数据丢失
1 提交事务的时候，必须把 Redo Log Buffer 中的日志刷入磁盘。此时提交事务后宕机了，那么 MySQL 重启的时候会根据 redo log 来恢复之前的修改
2 提交事务的时候，把 Redo Log Buffer 中的日志写入磁盘对应的 os cache 缓存中，InnoDB 有一个后台线程，每隔 1 秒，就会把 Redo Log Buffer 中的日志，调用 write 写到文件系统的 os cache，然后调用 fsync 持久化到磁盘。（所以一个没有提交的事务的 redo log，也是可能被持久化到磁盘的）

所以如果想要保证数据不丢失，通常建议设置为 1

redo log 的日志文件默认都会写入到一个目录中的文件里，这个目录可以通过 show variables like ‘datadir’ 来查看

可以通过 innodb_log_group_home_dir 参数来设置这个目录的

通过 innodb_log_file_size 可以指定每个 redo log 文件的大小，默认是 48MB

通过 innodb_log_files_in_group 可以指定日志文件的数量，默认就 2 个。如果第二个页写满了，就会覆盖第一个来写

redo log block

redo log 不是单行单行的写入日志文件的，他是用一个 redo log block 来存放多个单行日志的

一个 redo log block 是 512 字节，这个 redo log block 的 512 字节分为 3 个部分，一个是 12 字节的 header 块头，一个是 496 字节的 body 块体，一个是 4字节的 trailer 块尾

12字节的 header 头又分为了4个部分

包括4个字节的 block no，就是块唯一编号；
2个字节的 data length，就是block里写入了多少字节数据；
2个字节的 first record group。这个是说每个事务都会有多个 redo log，是一个 redo log group，即一组 redo log。那么在这个 block 里的第一组 redo log 的偏移量，就是这2个字节存储的；
4个字节的 checkpoint on

redo log buffer

redo log buffer 里面划分出了N多个空的 redo log block

通过设置 mysql 的 innodb_log_buffer_size 可以指定这个 redo log buffer 的大小，默认的值就是16MB

MySQL 的 binlog

实际上我们之前说的 redo log，他是一种偏向物理性质的重做日志，因为他里面记录的是类似这样的东西，“对哪个数据页中的什么记录，做了个什么修改”。

而且 redo log 本身是属于 InnoDB 存储引擎特有的一个东西。

而 binlog 叫做归档日志，他里面记录的是偏向于逻辑性的日志，类似于“对users表中的id=10的一行数据做了更新操作，更新以后的值是什么”

binlog 不是 InnoDB 存储引擎特有的日志文件，是属于 mysql server 自己的日志文件。

binlog 的刷盘策略是通过 sync_binlog 来配置的

0 提交事务的时候，会把 binlog 写入磁盘对应的 os cache 缓存中。如果机器宕机，会造成日志丢失
1 提交事务的时候，强制把 binlog 写入到磁盘中。
N (N > 1) 表示每次提交事务都只写入 os cache 缓存中，但累积 N 个事务后才 fsync

所以如果想要保证数据不丢失，通常建议设置为 1

针对可容忍的数据丢失的数量，可以根据实际情况将值设置为 N，当然 N 越大，性能越高。比较常见的是将其设置为 100~1000 中的某个数值

当我们把 binlog 写入磁盘文件之后，接着就会完成最终的事务提交，此时会把本次更新对应的 binlog 文件名称和这次更新的 binlog 日志在文件里的位置，都写入到 redo log 日志文件里去，同时在 redo log 日志文件里写入一个 commit 标记。

commit 标记的意义是什么？

他其实是用来保持redo log日志与binlog日志一致的。

比如：

如果 redo log 写入磁盘了，mysql宕机了，怎么办？这个时候因为没有最终的事务 commit 标记在 redo 日志里，所以此次事务可以判定为不成功。

如果 binlog 写入磁盘了，mysql宕机了，怎么办？同理，因为没有 redo log 中的最终 commit 标记，因此此时事务提交也是失败的。

必须是在 redo log 中写入最终的事务 commit 标记了，然后此时事务提交成功，而且 redo log 里有本次更新对应的日志，binlog 里也有本次更新对应的日志，redo log 和 binlog 完全是一致的。

然后通过我们之前的经验而言

一般8核16G的机器部署的MySQL数据库，每秒抗个一两千并发请求是没问题的

对于16核32G的机器部署的MySQL数据库而言，每秒抗个两三千，甚至三四千的并发请求也都是可以的

锁

加锁规则

原则1：加锁的基本单位是 next-key lock。（next-key lock是前开后闭区间）
原则2：查找过程中访问到的对象才会加锁。
优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为 间隙锁。
一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值位置。

MySQL 的生产优化经验

正确告诉 InnoDB 所在主机的 IO 能力

使用 innodb_io_capacity 参数，这个值建议设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试，下面是测试磁盘随机读写的命令：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
# filename        测试文件名称，通常选择需要测试的盘的data目录
# direct=1        测试过程绕过机器自带的buffer，使测试结果更真实
# iodepth 1       队列深度（我也不知道是啥）
# rw=randwrite    测试随机写的 I/O
# rw=randrw       测试随机读写的 I/O
# ioengine=psync  引擎使用pync方式
# bs=16k          单词io的块文件大小为16k
# size=500M       本次的测试文件大小为500M
# numjobs=10      本次的测试线程为10
# runtime=10      测试时间为10秒，如果不写，则一直将 size 大小的文件分 bs 每次写完为止
# group_reporting 关于现实结果的，汇总每个进程的信息

刷脏页带上了”邻居”

一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时，如果这个脏页旁边的数据页刚好也是脏页，就会把这个”邻居”也带着一起刷掉；而且如果旁边还是脏页，那么这种机制还会蔓延。

在 InnoDB 中 innodb_flush_neighbors 参数就是来控制这个行为的，值为 1 的时候表示开启上述机制；值为 0 表示关闭，只刷自己的数据页

如果是机械硬盘的话，可以开启，这样可以减少很多随机 IO
如果是 SSD 的话，建议关闭，因为这时候 IOPS 往往不是瓶颈，只刷自己，可以减少 SQL 语句的响应时间

在 MySQL 8.0中，innodb_flush_neighbors 参数的默认值已经是 0 了

多个 Buffer Pool 优化并发能力

一般来说，MySQL 默认的规则是，如果你给 Buffer Pool 分配的内存小于1GB，那么最多就只会给你一个 Buffer Pool。

但是如果你的机器内存很大，那么你必然会给 Buffer Pool 分配较大的内存，比如给他个8G内存，那么此时你是同时可以设置多个 Buffer Pool 的，比如：

1
2
3

[server]
innodb_buffer_pool_size = 8589934592
innodb_buffer_pool_instances = 4

总共给了 8G 内存，分了 4 个 Buffer Pool，每个 Buffer Pool 的大小就是 2G，每个 Buffer Pool 有自己的 free、flush、LRU 链表

建议给 Buffer Pool 设置你的机器内存的 50%~60% 左右

如何设置有多少个 Buffer Pool 呢？有一个公式：

buffer pool 总大小=(chunk 大小 * buffer pool 数量)的倍数

假设你的 buffer pool 的数量是16个，那么此时chunk大小 * buffer pool的数量 = 128MB * 16 = 2048MB，然后 buffer pool 总大小如果是 20GB，此时buffer pool 总大小就是 2048MB 的 10倍，这就符合规则了。

Too many connections 问题

往往你在生产环境部署了一个系统，比如数据库系统、消息中间件系统、存储系统、缓存系统之后，都需要调整一下linux的一些内核参数，这个文件句柄的数量是一定要调整的，通常都得设置为 65535

错误信息“ERROR 1040(HY000): Too many connections”，这个时候就是说数据库的连接池里已经有太多的连接了，不能再跟你建立新的连接了！

然后可能会去查看配置文件 my.cnf 中的参数 max_connections。发现明明很大啊？占满了？

这个时候使用命令看一下

1	show variables like 'max_connections'

发现 MySQL 建立的连接远远没有达到上面设置的值，这是为什么呢？

这是因为底层的 linux 操作系统把进程可以打开的文件句柄数限制为了1024了，导致 MySQL最大连接数是 214！

为什么linux的最大文件句柄限制为 1024 的时候，MySQL的最大连接数是 214 呢？

这个其实是 MySQL 源码内部写死的，他在源码中就是有一个计算公式，算下来就是如此罢了！

修改 linux 可以打开的文件句柄数

ulimit -HSn 65535
# 然后使用以下命令查看是否修改成功
cat /etc/security/limits.conf
cat /etc/rc.local

如果都修改好之后，可以在 MySQL 的 my.cnf 里确保 max_connections 参数也调整好了，然后可以重启服务器，然后重启 MySQL，这样的话，linux 的最大文件句柄就会生效了，MySQL 的最大连接数也会生效了。

行锁优化

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。

死锁解决

有两种策略

innodb_lock_wait_timeout 设置超时时间，在 InnoDB 中，默认值是 50s。对于在线服务来说，这个等待时间往往是无法接受的。
innodb_deadlock_detect 开启死锁检测，设置为 on，默认就是 on，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。

正常情况下主要采用第二种策略，即开启死锁检测。但是它也是有额外负担的。

可以想象一下这个过程：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

减少死锁的主要方向，就是控制访问相同资源的并发事务量。

SQL 执行计划

type	描述
const	直接可以通过聚簇索引或二级索引+聚簇索引回源，轻松查到你要的数据。二级索引必须是唯一索引，才是属于 const
eq_ref	在连接查询时，针对被驱动表如果基于主键进行等值匹配，那么他的查询方式就是 eq_ref
ref	普通二级索引，不是唯一索引，在执行计划里叫做 ref。如果包含多个列的普通索引，必须是从索引最左侧开始连续多个列都是等值比较才可以是属于 ref 方式。比如 *select from table where name=x and age=x and xx=xx。索引可能是个 KEY(name,age,xx)**。如果你用 name IS NULL 这种语法的话，即使 name 是主键或者唯一索引，还是只能走 ref 方式
ref_or_null	如果针对一个二级索引同时比较了一个值还有限定了 IS NULL，类似于*select from table where name=x or name IS NULL，那么此时在执行计划里就叫做 ref_or_null**
index_merge	有一些特殊场景下针对单表查询可能会基于多个索引提取数据后进行合并，此时查询方式会是 index_merge
range	SQL 里有范围查找，一旦利用索引做了范围筛选，那么这种方式就是 range。比如 *select from table where age>=x and age <=x**
index	比如有一个联合索引 KEY(x1,x2,x3) ，有一个SQL语句是 select x1,x2,x3 from table where x2=xxx。虽然 where 条件中的 x2 不是联合索引的最左侧字段，但是因为查询的字段全部都是联合索引中的字段，所以还是会走索引。针对这种只要遍历二级索引就可以拿到你想要的数据，而不需要回源到聚簇索引的访问方式，就叫做 index 访问方式
all	全表扫描，没有走索引

假设你在执行计划里看到了 const、ref（或ref_or_null）和 range，他们是什么意思？

他们都是基于索引在查询，而且是基于索引树的二分查找和多层跳转来查询，所以性能一般都是很高的，除非你通过索引查出来的数据量太多了
假设你在执行计划里看到了 index

速度就比上面三种要差一些，因为他是遍历二级索引树的叶子节点的方式来执行，那肯定比基于索引树的二分查找要慢多了，但是还是比全表扫描快一些的。

extra	描述
Using index	说明这次查询，仅仅涉及到了一个二级索引，不需要回表。例如 *SELECT FROM t1 WHERE x1 = ‘xxx’**
Using index condition	这叫做索引条件下推，是5.6以后加入的新特性。含义就是存储引擎层根据索引尽可能的过滤数据，然后在返回给服务器层根据where其他条件进行过滤。比如我有这样的联合索引 KEY name_gid_age_index (name, gid, age) , 查询的时候 where name=’taoshihan’ and age=1 , 没有按顺序连续查条件, 后面那个age 就用不到索引，这时候会先按索引筛选出符合 name=’taoshihan’ 条件的索引数据，然后再将索引数据按 age=1 条件来筛选，只有符合条件的索引数据才会进行回表操作。减少了回表的次数。where 中非索引条件是由 engine 层来做的筛选
Using where	说明对一个表扫描，没用到索引，然后 where 里好几个条件，就会告诉你 Using where，或者是用了索引去查找，但是除了索引之外，还需要用其他的字段进行筛选，也会告诉你 Using where
Using join buffer	说明在多表关联的时候，有的时候你的关联条件并不是索引，此时就会用一种叫做 join buffer 的内存技术来提升关联的性能
Using filesort	如果我们排序的时候是没法用到索引的，此时就会基于内存或者磁盘文件来排序，大部分时候得都基于磁盘文件来排序。性能极差
Using temprory	如果我们用 group by、union、distinct 之类的语法的时候，没有利用索引来进行分组聚合，那么就会直接基于临时表来完成，也会有大量的磁盘操作。性能极差

SQL 执行成本

我们先了解一下MySQL里的成本是什么意思，简单来说，跑一个SQL语句，一般成本是两块:

数据如果在磁盘里，你要不要从磁盘里把数据读出来？这个从磁盘读数据到内存就是IO成本，而且MySQL里都是一页一页读的，读一页的成本的约定为 1.0。
拿到数据之后，是不是要对数据做一些运算？比如验证他是否符合搜索条件了，或者是搞一些排序分组之类的事，这些都是耗费CPU资源的，属于CPU成本，一般约定读取和检测一条数据是否符合条件的成本是 0.2。

成本计算

全表扫描成本计算

有多少数据页就得耗费多少IO成本

有多少条数据就要耗费多少CPU成本

通过以下命令可以拿到你的表的统计信息，比如这里可以看到 rows 和 data_length 两个信息，不过对于 innodb 来说，这个 rows 是估计值。

1	show table status like "表名"

rows 就是表里的记录数，data_length 就是表的聚簇索引的字节数大小，此时用 data_length 除以 1024 就是kb为单位的大小，然后再除以 16kb（默认一页的大小），就是有多少页，此时知道数据页的数量和 rows 记录数，就可以计算全表扫描的成本了。

IO成本就是：数据页数量 * 1.0 + 微调值，CPU成本就是：行记录数 * 0.2 + 微调值，他们俩相加，就是一个总的成本值，比如你有数据页100个，记录数有2万条，此时总成本值大致就是 100 + 4000 = 4100，在这个左右。

MySQL 主从架构

异步复制

这种架构有个问题，会丢数据，因为主库是不管从库有没有接收到 binlog 。如果主库一部分 binlog 还没有同步给从库，主库宕机了。此时就会丢数据

所以一般都是使用下面的那种方式来搭建

首先，要确保主库和从库的 server-id 是不同的，这个是必然的，其次就是主库必须打开 binlog 功能，你必须打开 binlog 功能主库才会写 binlog 到本地磁盘

1
2
3

[mysqld]
log-bin=/data/mysql/log/mysql-bin.log
server-id=1

注意，如果mysql目录下无log目录，请先创建log目录

首先在主库上要创建一个用于主从复制的账号：
1
2
3
create user 'backup_user'@'192.168.31.%' identified by 'backup_123';
grant replication slave on *.* to 'backup_user'@'192.168.31.%';
flush privileges;
接着你要考虑一个问题，假设你主库都跑了一段时间了，现在要挂一个从库，那从库总不能把你主库从0开始的所有 binlog都拉一遍吧！这是不对的，此时你就应该在凌晨的时候，在公司里直接让系统对外不可用，说是维护状态，然后对主库和从库做一个数据备份和导入。

可以使用如下的 mysqldump 工具把主库在这个时刻的数据做一个全量备份，但是此时一定是不能允许系统操作主库了，主库的数据此时是不能有变动的。
1
/usr/local/mysql/bin/mysqldump --single-transaction -uroot -proot --master-data=2 -A > backup.sql
mysqldump 工具就在你的 MySQL 安装目录的bin目录下，然后用上述命令就可以对你主库所有的数据都做一个备份，备份会以SQL语句的方式进入指定的backup.sql 文件，只要执行这个 backup.sql 文件，就可以恢复出来跟主库一样的数据

–master-data=2，意思就是说备份SQL文件里，要记录一下此时主库的 binlog 文件和 position 号，这是为主从复制做准备的
接着在从库上执行下面的命令去指定从主库进行复制
1
CHANGE MASTER TO MASTER_HOST='192.168.31.229',MASTER_USER='backup_user',MASTER_PASSWORD='backup_123',MASTER_LOG_FILE='mysqlbin.000015',MASTER_LOG_POS=1689;
在执行上述命令前，打开那个backup.sql就可以看到如下内容：
1
MASTER_LOG_FILE='mysql-bin.000015',MASTER_LOG_POS=1689
然后就把上述内容写入到主从复制的命令里去

接着执行一个开始进行主从复制的命令：
1
start slave
再用
1
show slave status
查看一下主从复制的状态，主要看到 Slave_IO_Running 和 Slave_SQL_Running 都是 Yes 就说明一切正常了，主从开始复制了

半同步复制

半同步复制有两种方式：

AFTER_COMMIT 方式：他不是默认的。意思是说主库写入日志到 binlog，等待 binlog 复制到从库了，主库就提交本地事务，等待从库返回成功的响应后，主库返回提交事务成功的响应给客户端（先提交本地事务，再等待从库响应，再响应客户端）
MySQL 5.7默认的方式：主库把日志写入 binlog，并且复制给从库，然后开始等待从库的响应，从库返回成功给主库后，主库再提交事务，接着返回提交事务成功的响应给客户端（先等待从库响应，再提交本地事务，再响应客户端）

搭建半同步复制也很简单，在之前搭建好异步复制的基础之上，安装一下半同步复制插件就可以了

首先，查看 MySQL 是否支持动态增加插件

1	select @@have_dynamic_loading;

先在主库中安装半同步复制插件，同时还得开启半同步复制功能：

1	install plugin rpl_semi_sync_master soname 'semisync_master.so';set global rpl_semi_sync_master_enabled=on;show plugins;

接着在从库也是安装这个插件以及开启半同步复制功能：

1	install plugin rpl_semi_sync_slave soname 'semisync_slave.so';set global rpl_semi_sync_slave_enabled=on;show plugins;

由于之前配置的复制是异步复制，需要重启从库的IO线程 (如果是全新配置的半同步复制则不需要)：

1	stop slave io_thread;start slave io_thread;

然后在主库上检查一下半同步复制是否正常运行：

1	show global status like '%semi%';

如果看到了 Rpl_semi_sync_master_status 的状态是 ON，那么就可以了

GTID搭建方式

这种方式更加简便

首先在主库进行配置：

1	gtid_mode=onenforce_gtid_consistency=onlog_bin=onserver_id=单独设置一个binlog_format=row

接着在从库进行配置：

1	gtid_mode=onenforce_gtid_consistency=onlog_slave_updates=1server_id=单独设置一个

接着按照之前讲解的步骤在主库创建好用于复制的账号之后，就可以跟之前一样进行操作了，比如在主库 dump 出来一份数据，在从库里导入这份数据，利用mysqldump 备份工具做的导出，备份文件里会有 SET @@GLOBAL.GTID_PURGED=*** 一类的字样，可以照着执行一下就可以了。

接着其余步骤都是跟之前类似的，最后执行一下 show master status，可以看到 executed_gtid_set，里面记录的是执行过的 GTID，接着执行一下 SQL：select * from gtid_executed，可以查询到，对比一下，就会发现对应上了。那么此时就说明开始 GTID 复制了。

那么万一你要是从库的读QPS越来越大，达到了每秒几千，此时你是不是会压力很大？

没关系，这个时候你可以给主库做更多的从库，搭建从库，给他挂到主库上去，每次都在凌晨搞，先让系统停机，对外不使用，数据不更新。接着对主库做个dump，导出数据，到从库导入数据，做一堆配置，然后让从库开始接着某个时间点开始继续从主库复制就可以了，一旦搭建完毕，就等于给主库挂了一个新的从库上去，此时继续放开系统的对外限制，继续使用就可以了，整个过程基本在1小时以内。

Dragon