《大话数据库》-SQL语句执行时，底层究竟做了什么小动作？

《大话数据库》-SQL语句执行时，底层究竟做了什么小动作？

前言

大家好，我是Taoye，试图用玩世不恭过的态度对待生活的Coder。

现如今我们已然进入了大数据时代，无论是业内还是业外的朋友，相信都有听说过数据库这个名词。数据是一个项目的精华，也扮演着为企业创造价值的重要角色，一个较为完善的公司一般都会有专门的DBA来管理数据库，以便更好的为用户服务。

互联网的发展速度之快，以致大量的APP应用涌入用户的视野，在大多数APP中都会有“推荐”这一板块，而这个板块功能的核心正是基于用户以往的数据记录而实现的。再如《死亡笔记》中L·Lawliet这一角色所提到的大数定律，在众多繁杂的数据中必然存在着某种规律，偶然中必然包含着某种必然的发生。不管是我们提到的大数定律，还是最近火热的大数据亦或其他领域都离不开大量数据的支持。

如上，我们可初步体会到数据的重要性，而要想更好的管理数据，则避免不了与数据库打交道。对于数据库而言，操作数据库的用户就相当于一位老板，我们需要向数据库发出命令，然后期望数据库给我们返回想要的结果。

我们可以想象一下这么一个场景，老板给Taoye发布了这么一个任务：“Taoye啊，你作为一位专业的板砖工，我现在需要你在一小时的之内将工地的转搬回来。”是的，老板发布任务只注重结果和效率，而不在意你板砖的过程。我们在执行SQL语句的时候也是如此，一般只关心执行的结果和效率是否满足用户的需求。

最近，Taoye重新把之前学习数据库时候所记录的笔记复习了一下，然后又系统性的拜读了丁奇大大的《MySQL实战45讲》中的内容，所以想要把MySQL系列的知识内容单独整理出来，在进行自我提高的同时，也希望能给予大家一点帮助。

MySQL的体系结构

我们要想系统性的学习MySQL数据库，首先不得不了解MySQL的体系结构。在MySQL中，主要是由什么功能模块组成？每一个功能模块在SQL语句执行的时候分别扮演了一个什么样的角色？MySQL的强大之处在哪，竟会受到如此之多的开发者的青睐？这些都是我们每一位学习MySQL的朋友必须了解甚至掌握的内容，以下便是MySQL数据库的体系结构及其执行流程：

从上图，我们可以看出MySQL整体上主要分为了Server层和存储引擎层两个部分。

在Server层内部又包括连接器、缓存、分析器、优化器、执行器等功能部件，主要负责了MySQL的大多数核心服务功能，比如存储过程、函数、触发器、视图等。

而存储引擎层主要的是负责数据的存储和提取，在MySQL中可支持MyISAM、InnoDB、Memory等多种存储引擎，其中最常见的是InnoDB和MyISAM，这也是我们在学习存储引擎时候的一个重点。在MySQL 5.5.5版本之前默认使用的是MyISAM，而在此版本之后默认采用的是InnoDB存储引擎。我们在实际创建数据表的时候，也可以通过ENGINE来指定使用的存储引擎，如下所示。我们可以对tb_comment数据表指定使用MyISAM存储引擎：

1CREATE TABLE `tb_comment` (
2  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
3  `content` varchar(255) DEFAULT NULL,
4  `user_name` varchar(255) DEFAULT NULL,
5  `openid` varchar(255) DEFAULT NULL,
6  `comment_time` int(11) DEFAULT NULL
7  PRIMARY KEY (`id`) USING BTREE
8) ENGINE=MyISAM AUTO_INCREMENT=175 DEFAULT CHARSET=utf8;

MyISAM和InnoDB这两种存储引擎最主要的区别是事务以及锁机制：

• InnoDB支持事务，而MyISAM不支持事务

• InnoDB一般采用的是行锁，锁的粒度小，开销大，锁表慢，但是在高并发场景下性能更好。而MyISAM采用的是表锁，特征与行锁相反。

关于MySQL的事务和锁机制，这里只是简单的提一下，具体的细节我们后面聊。下面我们将MySQL的体系结构中每一个功能模块单独的分离开来，依次看看每一个功能模块所体现出的作用。

连接器

我们要想正常的操作数据库，首先需要经过连接器这道大门。在正式引出连接器之前，各位看官不妨来看看下面一个例子：

金主大大成天担心自己财产的安全，必然会将自己的money存储在银行金库中。某一天金主大大要想取出一部分的财产来维持公司的运营，而银行为了保障金库中money的安全性，金主必然要进行身份核验以及一系列防盗系统的检测。

在MySQL数据库中，连接器的作用其实就类似于上面的身份校验以及防盗系统，主要是负责与客户端建立连接、权限校验、维持和管理连接。我们要想操作数据库，首先需要通过账号、密码等信息来连接数据库，假如我们想要以root账户、密码为666666来连接192.168.31.100:3307的MySQL服务，则可以执行以下命令：

1mysql -h 192.168.31.100 -P 3307 -u root -p 666666
2
3# 如果是本地连接，则执行如下，密码也可回车后输入
4mysql -u root -p 666666

当数据匹配成功时，连接器就能允许用户与数据库建立连接。此外，连接器还需要验证该用户是否有权限对数据表进行操作，这个时候连接器会去权限表中查询连接用户的权限，只有在具有操作权限的前提下才能操作数据表。如果我们在与数据库已经建立连接的前提下，但是不对数据库进行任何操作，这个时候连接就会处于空闲状态，我们可以通过show processlist命令来查看已经建立的连接数和处于空闲状态的连接，其中command字段为sleep表示连接空闲：

1mysql> show processlist;
2+----+------+-----------------+------+---------+------+----------+------------------+
3| Id | User | Host            | db   | Command | Time | State    | Info             |
4+----+------+-----------------+------+---------+------+----------+------------------+
5|  3 | root | localhost:53372 | NULL | Sleep   |   28 |          | NULL             |
6|  4 | root | localhost:53378 | NULL | Query   |    0 | starting | show processlist |
7+----+------+-----------------+------+---------+------+----------+------------------+
8

如果连接长期处于空闲状态而不做任何操作，当超过一定时间时，就会自动断开连接，而这个时间阈值主要是通过wait_timeout属性来决定的，默认是28800，即8小时。show variables like '%wait_timeout%'"可查看时间阈值，set @@session.wait_timeout=xxx可修改当前会话下的时间阈值，具体操作如下：

 1mysql> set @@session.wait_timeout=30000;
 2Query OK, 0 rows affected (0.00 sec)
 3
 4mysql> show variables like "%wait_timeout%";
 5+--------------------------+----------+
 6| Variable_name            | Value    |
 7+--------------------------+----------+
 8| innodb_lock_wait_timeout | 50       |
 9| lock_wait_timeout        | 31536000 |
10| wait_timeout             | 30000    |
11+--------------------------+----------+
123 rows in set, 1 warning (0.00 sec)

缓存

缓存这个概念，学习过《计算机组成原理》或是其他相关课程的朋友应该并不陌生，缓存一般使用的是SRAM（静态随机存储器）技术实现的，相较于DRAM（动态随机存储器）而言，它最主要的优势在于速度快，能够大大提高数据的查询效率。

关于缓存，我们可以来做一个简答的计算题：
假设查询一次缓存需要1s，查询一次内存需要10s，缓存命中的概率为90%，一位用户想要查询100次，则使用缓存和不使用缓存的平均查询时间是多少？

可见，根据局部性原理，缓存的存在是可以大大提高数据的查询效率的。

由上方的执行流程图，我们也可以知道，客户端通过连接器与MySQL服务器建立连接之后，这个时候就会来到缓存中查询用户所需的数据。假设用户向MySQL发出以下一条查询语句：

1mysql> select * from tb_comment where id=1;

MySQL收到用户发出的请求之后，就会先到缓存中看看之前是否有执行过相同的语句，而且之前执行的结果会以key-value键值对的形式直接进行存储。假如之前有执行过这条命令，则直接从缓存中取出数据并反馈给用户，如果没有执行过，则会继续走 分析器 -> 优化器 -> 执行器 -> 存储引擎这条链路。所以说，缓存的命中可以省去后面一系列操作所消耗的时间，这也是缓存提高查询效率的原因。

按道理来讲，缓存的引入应该是相当不错的，而且用户查询次数越多就越能体现缓存的强大，但为什么在MySQL 8.0版本之后直接舍去了缓存部件呢？

在理想情况下，缓存引入确实是非常的完美，但是在数据库中，我们除了查询操作之外，还有更新操作（增、删、改）。每当我们执行过一次更新操作的时候，数据库中的数据就已然发生了改变，而当我们再次发出命令从缓存中取出的数据就不再是用户所期望的数据了。所以对MySQL而言，每当用户进行更新操作时，都会清空一次缓存，然后再次重新缓存新的数据，而这个过程给MySQL带来的压力是很大的，也大大削弱了SQL语句的执行效率。所以说，缓存对于一些查询多，更新少的数据表比较有用，而对那些更新比较频繁的数据表就会适得其反。

如果使用的是MySQL 8.0以下的版本，我们可以根据实际需求来确定是否开启缓存，主要是通过my.cnf配置文件中的query_cache_type来决定的，0代表禁用缓存，1代表开启缓存，2代表根据需要使用缓存。在执行SQL语句的时候可以附带SQL_CACHE和SQL_NO_CACHE来确定执行时是否使用缓存，并且可以通过show status like '%qcache%'命令来查看缓存的整体情况：

 1mysql> select SQL_CACHE * from tb_comment where id=1;
 2mysql> show status like "%qcache%";
 3+-------------------------+---------+
 4| Variable_name           | Value   |
 5+-------------------------+---------+
 6| Qcache_free_blocks      | 1       |
 7| Qcache_free_memory      | 1031872 |
 8| Qcache_hits             | 0       |
 9| Qcache_inserts          | 0       |
10| Qcache_lowmem_prunes    | 0       |
11| Qcache_not_cached       | 10      |
12| Qcache_queries_in_cache | 0       |
13| Qcache_total_blocks     | 1       |
14+-------------------------+---------+
15

分析器

如果在执行查询SQL时没有命中缓存，则需要去数据表中查询用户所需的数据了，也就是开始进入分析器来对用户发出的SQL语句进行分析。

分析的时候主要是包括预处理和解析的过程，在这个阶段会解析SQL语句的语义，并对语句中的一些关键字和非关键字进行提取、解析，并组成一个解析树交给后面的执行器执行。具体的关键词包括但不限定于select/update/delete/or/in/where/group by/having/count/limit等，如果分析到语法或关键词错误，会直接给客户端抛出异常:ERROR:You have an error in your SQL syntax.

比如用户执行：select * from tb_comment where user_id=1，在分析器中就会通过语义规则器将select、from、where等提取和匹配出来，然后进行分析并校验，假如在校验的时候发现tb_comment中并不存在user_id字段，则会报错：Unknown column 'user_id' in 'where clause

1mysql> select * from tb_comment where user_id=1;
2ERROR 1054 (42S22): Unknown column 'user_id' in 'where clause'

优化器

分析器对用户的SQL语义进行分析之后，则说明SQL语句本身是没有任何问题，并且是可以被正常执行的。此时，MySQL已经知道了用户的意图，用户是想查询还是更新，MySQL都心知肚明。

优化器主要是对SQL进行优化，会根据执行计划进行最优的选择来匹配合适的索引，并选择最佳的执行方案。在丁奇《MySQL实战45讲》中提到，一个语句有多表关联（join）的时候，会决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的join：

1mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

• 既可以先从表t1里面取出c=10的记录的ID值，再根据ID值关联到表t2，再判断t2里面d的值是否等于20。

• 也可以先从表t2里面取出d=20的记录的ID值，再根据ID值关联到t1，再判断t1里面c的值是否等于10。

这两种执行方式的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用较佳的方案来执行。为了更好的理解丁奇大大的例子，我们可以对其进行类别：我们要想在中国上海找到年龄在20-30岁之间的人。第一种方案是先锁定上海，然后再查询年龄；而第二种是先在全国查询年龄区间在20-30的人，然后再锁定上海。至于哪种方案较优，相信各位看官都明白吧。

当然了，优化器对SQL进行优化的地方不仅仅局限于以上场景。在我们创建联合索引，并对数据表进行查询的时候，优化器同样可能会对其进行优化。且看下面一个例子：

1mysql> create index oid_ctime_pid on tb_comment(openid, comment_time, problem_id);
2mysql> explain select * from tb_comment where openid="1" and problem_id=401 and comment_time=3;
3+----+-------------+------------+------+---------------+---------------+---------+-------------------+------+-----------------------+
4| id | select_type | table      | type | possible_keys | key           | key_len | ref               | rows | Extra                 |
5+----+-------------+------------+------+---------------+---------------+---------+-------------------+------+-----------------------+
6|  1 | SIMPLE      | tb_comment | ref  | oid_ctime_pid | oid_ctime_pid | 778     | const,const,const |    1 | Using index condition |
7+----+-------------+------------+------+---------------+---------------+---------+-------------------+------+-----------------------+

在上面，我们首先根据tb_comment数据表中的openid、comment_time、problem_id建立一个组合索引oid_ctime_pid，然后根据索引字段进行查询。然而在查询的时候，我们有意地将筛选的字段顺序打乱，与索引字段的创建顺序不一致。（索引创建字段顺序：openid、comment_time、problem_id，查询条件字段顺序：openid、problem_id、comment_time）但是我们采用explain执行计划检索的时候发现key属性为oid_ctime_pid，换句话讲，即使我们不遵循最左匹配原则，此时依然是走了oid_ctime_pid组合索引的。之所以能够达到这样的效果，正式因为优化器的存在，在其内部对查询条件的顺序进行了优化，从而利用索引提高了数据的查询效率。

注意：以上内容提前涉及到了数据库当中索引的概念，索引是非常非常重要的，我们日后会单独的详细讲讲索引。另外，还有一点值得注意的是，虽然说优化器在一定程度上能够优化SQL，但是这种优化程度仅仅是MySQL所认为的一个较佳状态，而不一定能够满足DBA所需要的标准。

执行器

OK，MySQL已经对用户发出的SQL语句进行了分析，也进行了优化，现在就要进入执行阶段来执行SQL语句了。

执行器在对SQL语句进行执行的时候，会根据表的引擎定义，去使用这个引擎所提供的接口，这些接口都是引擎中已经定义好的，执行器直接通过“拿来主义”进行调用即可。引擎还有一个名字叫做“表处理器”，这个名字应该说更能体现出它所存在的意义。

我们同样以一条简单的SQL语句来说明下这个执行过程：

1mysql > select * from tb_comment where openid=123;

假设这张表采用的是InnoDB存储引擎，则首先InnoDB存储引擎接口会去查询的表数据中的第一行，对该行数据中的openid字段所对应的值进行判断，假如该值为123，则将这行数据加入到结果集当中，否则引擎接口继续查询下一行，如此不断循环重复相同的判断逻辑，直至对数据表中最后一行的数据判断结束为止。最后，将结果集当中的所有满足条件的数据反馈给用户。

以上便是执行器在查询操作时候的大体功能，但是在进行更新操作（增、删、改）的时候，执行器还会将具体的操作记录到binlog日志当中，另外，update会采取两阶段的提交方式，记录到redolog中，也就是我们接下来所要讲的MySQL日志系统。

MySQL的日志系统

日志日志，在生活中，顾名思义就是记录生活的点点滴滴，把自己的内心世界记录下来，更好的诠释自己当时写日志时候的心情感受。待到将来的某一天，我们回翻之前所写的日志时，我们能够瞬间回忆起自己身边所发生的事以及内心世界。

同理，在计算机领域里，日志也是一个相当重要的概念。而MySQL的日志系统会将我们对数据库的修改操作全部自动记录下来，这样就能够通过日志文件随时对误操作的数据进行恢复。说道这里，有了解过Git或是读过Taoye之前写的Git文章的朋友，应该会有点联想，在Git中，我们可以在任意时间点对项目的版本进行恢复（回退），虽然结果与MySQL的日志系统有点类似，但是它们的实现原理是不一样的。关于Git，想了解的读者可以暂且跳转学习：哪些年，我们玩过的Git

redo log（重做日志）和binlog（归档日志）是MySQL当中非常重要的两种日志，尤其是binlog，在我们之后进行数据恢复或是搭建主从的时候会频繁的接触到。

关于对redo log和binlog的理解，丁奇在《MySQL实战45讲》中已经讲解的非常明白了，其中以一种通俗易懂的方式来介绍了晦涩难懂的redo log和binlog。说实话，我记得第一次学习这两种日志的时候，总是对某些小的知识点细节理解的不够透彻，甚至会出现理解偏差的情况，好在后来通过不断重复阅读和查询资料的形式一个个解决了当时的疑问。关于对redo log和binlog基本概念的理解，这里就不再多说了，大家可以去看看《MySQL实战45讲》中该部分的内容，或是阅读：https://www.cnblogs.com/sunshineliulu/p/10905483.html

下面主要是介绍下binlog二进制日志的常见操作，及如何通过binlog搭建主从和进行数据的恢复：

binlog日志的常见操作

配置binlog

在MySQL中，binlog日志默认是处于关闭的状态，我们要想开启并设置二进制日志，就需要修改MySQL的配置文件，即my.cnf，配置完成之后再重启mysql即可生效systemctl restart mysql:

1log-bin = /www/server/data/mysql-bin    # binlog日志的存放路径
2expire_logs_days = 7                    # 日志文件的过期时间，过期之后会自动删除，0表示不删除
3binlog_format = mixed                   # 日志的存储格式
4max_binlog_size = 100M                  # 每个binlog日志文件的大小

• log-bin：binlog日志的存放路径

• expire_logs_days：表示日志文件的过期时间，过期之后MySQL就会自动将日志文件删除，如果设置为0则表示不删除

• binlog_format：表示的是binlog日志文件的存储格式。

该属性的值总共有三种：statement、row和mixed，当将日志格式配置为statement的时候，表示每一次修改数据的SQL语句本身都会记录到binlog日志当中，而row并非记录SQL语句，而是记录被修改的那行数据。mixed格式则是前两总形式的混合，一般对库或表结构发生修改采用的是statement，而数据本身发生修改采用row格式。

• max_binlog_size：每个binlog日志文件的大小，默认是1G。因为binlog日志并不是像redo log那样循环写，而是采用追加的形式记录日志，所以当一个binlog日志文件写满之后，就会进行一次滚动，也就是创建一个新的binlog日志文件用于记录。

以上关于binlog日志的配置只是一部分，其他的配置我们用到的时候再来补充。当该部分配置完成之后，我们就可以通过show variables like命令来查看二进制文件的设置：

 1mysql> show variables like "log_bin%";
 2+---------------------------------+----------------------------------+
 3| Variable_name                   | Value                            |
 4+---------------------------------+----------------------------------+
 5| log_bin                         | ON                               |
 6| log_bin_basename                | /www/server/data/mysql-bin       |
 7| log_bin_index                   | /www/server/data/mysql-bin.index |
 8| log_bin_trust_function_creators | OFF                              |
 9| log_bin_use_v1_row_events       | OFF                              |
10+---------------------------------+----------------------------------+

查看目前所存在的所有二进制日志，以及当前正在使用的二进制日志：

 1mysql> show master logs;
 2+------------------+-----------+
 3| Log_name         | File_size |
 4+------------------+-----------+
 5| mysql-bin.000022 |       120 |
 6| mysql-bin.000023 |     45529 |
 7| mysql-bin.000024 |     24284 |
 8+------------------+-----------+
 9
10mysql> show master status;
11+------------------+----------+--------------+------------------+-------------------+
12| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
13+------------------+----------+--------------+------------------+-------------------+
14| mysql-bin.000028 |      120 |              |                  |                   |
15+------------------+----------+--------------+------------------+-------------------+

清除binlog日志文件

每当我们重启MySQL服务的时候，都会自动创建一个新的binglog二进制日志文件，并且还会生成mysql-bin.index文件，该文件存储所有二进制日志文件的清单，也就是二进制日志文件的索引。在上面，我们配置在my.cnf中配置binlog的时候，设置了expire_logs_days=7，也就是说超过7天的日志文件会被自动删除。但是如果我们不对binlog日志文件进行处理的时候，大量的binlog文件会占据太多的磁盘空间，从而在一定程度上影响了磁盘的IO性能，所以定期清理binlog日志是很有必要的。

我们既可以清除所有的binlog日志文件，也可以对指定的日志文件进行清除，部分具体操作如下：

 1# 清除mysql-bin.000023之前的所有日志文件
 2mysql> purge master logs to "mysql-bin.000023";
 3# 清除指定时间之前的日志文件
 4mysql> purge master logs before "2020-06-12 12:12:12";
 5# 清除所有的日志文件
 6mysql> reset master;
 7mysql> show master logs;
 8+------------------+-----------+
 9| Log_name         | File_size |
10+------------------+-----------+
11| mysql-bin.000001 |       120 |
12+------------------+-----------+

我们在执行reset master命令的时候，会清除所有的binlog日志，并且会自动重新创建新的二进制日志文件，其其编号为000001开始。

binlog的切换（滚动）

此外，也可以通过flush logs命令实现日志文件的滚动，即会生成的一个新的日志文件作为当前的记录日志，之后对数据库的修改操作都会记录在该日志文件当中：

 1mysql> flush logs;
 2Query OK, 0 rows affected (0.04 sec)
 3
 4mysql> show master logs;
 5+------------------+-----------+
 6| Log_name         | File_size |
 7+------------------+-----------+
 8| mysql-bin.000001 |       167 |
 9| mysql-bin.000002 |       120 |
10+------------------+-----------+
11

binlog日志文件的查看与分析

前面有提到，binlog中有statement、row和mixed三种存储格式，它们的存储内容都是二进制的形式，我们是无法通过常规的方式浏览其内容的，一般可以以命令的形式或是工具来查看并分析二进制日志文件的内容：

 1mysql> create database db_test;
 2# 查看日志文件所记录的事件
 3mysql> show binlog events in  "mysql-bin.000002";
 4+------------------+-----+-------------+-----------+-------------+---------------------------------------+
 5| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
 6+------------------+-----+-------------+-----------+-------------+---------------------------------------+
 7| mysql-bin.000002 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.47-log, Binlog ver: 4 |
 8| mysql-bin.000002 | 120 | Query       |         1 |         223 | create database db_test               |
 9+------------------+-----+-------------+-----------+-------------+---------------------------------------+
10

除了采用show binlog events命令之外，我们还可以使用mysqlbinlog来读取日志文件或转化为自己想要输出的文件形式，这样便于在误操作的情况下对日志文件进行分析。这里需要注意的一点是，在使用mysqlbinlog工具的时候，一般要进入到其所在目录，默认存在于MySQL的bin目录当中，并且在指定具体解析的日志文件时，同样要定位文件所在位置。

1# 直接查看日志文件的内容
2$ ./mysqlbinlog --no-defaults ../../data/mysql-bin.000002 
3
4# 将binlog日志文件内容以txt或log文件输出
5$ ./mysqlbinlog ../../data/mysql-bin.000002 > ./my-log.log
6
7# 将binlog日志文件转化为sql文件
8$ ./mysqlbinlog --base64-output=decode-rows -v ../../data/mysql-bin.000002 > ./my-log.sql

下面我们从0开始建库、建表，并实现数据的增删改操作，来详细分析一下日志文件中所记录的信息：

• 日志滚动、建库db_test、建表tb_test，对数据表执行增删改操作

 1# 日志滚动、建库db_test、建表tb_test
 2mysql> flush logs;
 3mysql> create database db_test;
 4mysql> use db_test;
 5mysql> create table tb_test(id int primary key, age int, username varchar(25));
 6
 7# 对数据表执行增删改操作
 8mysql> insert into tb_test values(1, 1, "me");
 9mysql> update tb_test set username="you" where id=1;
10mysql> delete from tb_test where id=1;

• 查看日志文件、将日志文件输出为log、sql文件，以便之后的分析

1$ ./mysqlbinlog --no-defaults ../../data/mysql-bin.000003 
2$ ./mysqlbinlog ../../data/mysql-bin.000003 > ./my-log.log
3$ ./mysqlbinlog --base64-output=decode-rows -v ../../data/mysql-bin.000003 > ./my-log.sql

• 日志分析

下图是Taoye使用Sublime text打开my-log.sql文件时所展现的内容（部分内容略过），从图中可以看到，binlog日志当中已经记录了我们对数据库所做出的所有操作，包括建库、建表、增删改，并且该日志文件是存储在磁盘当中，换句话说，及时我们的MySQL服务宕机了，但是重启之后依然可以根据该文件进行数据的恢复。

根据binlog日志实现数据的恢复

在上面，已经介绍了在实际使用过程中binlog日志的常见操作，其中包括binlog日志的配置、查看、滚动、清除和分析等，接下来就是利用binlog日志实现数据的恢复了。

在一个伸手不见五指的黑夜，Taoye的同事Yetao坐在办公桌前，左手捧着一杯Java（咖啡），右手提着一块板砖。白天不断隐忍了产品经理的折磨之后，此时的他心情非常的愤懑，最终它做出了一个明智的选择：“删库，跑路！”

第二天，产品经理发现数据库已然被Yetao这臭小子清空了，于是把心中的那把火全部洒在Taoye身上，并且命令Taoye在一天之内恢复数据库中所有的数据，否则滚蛋。

Shit，Yetao这臭小子居然做了这么愚蠢的骚操作，居然还让我背锅、擦屁股。好在我的binlog日志玩的贼溜，否则还真的得滚蛋。下面我们来看看在实际的过程中，假如出现了数据的误操作，我们该如何利用binlog二进制日志文件对数据进行恢复？

• 基于单个binlog实现数据的恢复

1# 日志滚动、建库db_test、建表tb_test
2mysql> flush logs;
3mysql> create if not exists database db_test;
4mysql> use db_test;
5mysql> create table if not exists tb_test(id int primary key, age int, username varchar(25));
6
7mysql> insert into tb_test values(1, 1, "taoye");
8mysql> insert into tb_test values(2, 2, "yetao");
9mysql> delete from tb_test where id=1;

首先，我们需要通过以上一些SQL语句来自定一个场景，以便我们实现数据的恢复：flush logs;进行日志文件滚动，使得之后对数据库的操作都能记录在一个新的binlog日志文件当中。之后创建db_test数据库以及tb_test数据表，并在表中的插入两条数据，最后删除id=1所对应的数据。

假如我们最后删除的那一条数据是一个误操作，我们应当如何通过binlog日志文件来恢复该条数据呢？

1.查看以上所有操作被记录在哪一个binlog当中，也就是当前所使用的binlog日志

1mysql> show master status;
2+------------------+----------+--------------+------------------+-------------------+
3| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
4+------------------+----------+--------------+------------------+-------------------+
5| mysql-bin.000002 |     1074 |              |                  |                   |
6+------------------+----------+--------------+------------------+-------------------+

2.由show master status命令我们可以知道，当前使用的是mysql-bin.000002日志，并且以上所有操作都已经被记录在该日志文件当中。对此，我们可以继续使用show binlog events命令来指定该日志文件，查看一下该文件所发生的事件：

1mysql> show binlog events in "mysql-bin.000002";

通过执行以上命令，我们可以得到日志文件所记录每个事件的信息，其中包括日志文件名、起始位置、事件类型、服务id、终止位置、描述信息，其中比较重要的是log_name、pos、end_log_pos、info字段的信息，我们进行数据的恢复也是的根据这些信息来实现的。

3.分析及数据的恢复

既然我们是想对最后的delete语句所删除的数据进行恢复，那么根据上面的执行结果可以发现，倒数第二行中的Info字段的值为：use 'db_test'; delete from tb_test where id=1，也就是说我们的需求是想要得到执行该行之前的数据库状态。

对此，我们继续往上查找，发现倒数第四行的Info是一个commit，也就是insert事务的提交。至此，我们基本可以确定，我们只需要将数据库恢复到从起始位置到该commit为止即可，这样就完美的跳过了delete操作。而指定数据恢复的位置便是pos和end_log_pos两个字段所决定的，从图中我们就能明白：只需要执行binlog日志文件中pos=4，end_log_pos=848的内容即可恢复。

所以我们可以通过如下方式实现该数据的恢复：

1# 删除之前旧的数据库
2mysql> drop database db_test;
3# 恢复数据
4$ ./mysqlbinlog --start-position 4 --stop-position 848 ../../data/mysql-bin.000002 | mysql -uroot -p;

以上是我们通过mysqlbinlog工具实现数据恢复的一般步骤，在其中指定的了--start-position和stop-position两个参数来确定恢复的起始位置和终止位置。当然，在前面我们也有介绍过将binlog日志转化为sql文件方法，所以我们同样可以基于sql文件来实现数据的恢复，这个比较简单，这里就不多说了。

• 基于多个binlog实现数据的恢复

以上是我们基于单个binlog日志文件实现数据恢复的操作，但是假如我们的数据操作信息被记录到多个binlog日志文件当中，在误操作的情况下应该如何实现日志的恢复呢？

 1mysql> flush logs;
 2mysql> insert into tb_test values(3, 3, "taoye03");
 3mysql> insert into tb_test values(4, 4, "taoye04");
 4mysql> insert into tb_test values(5, 5, "taoye05");
 5
 6mysql> flush logs;
 7mysql> insert into tb_test values(6, 6, "taoye06");
 8msyql> delete from tb_test where id=4;
 9mysql> insert into tb_test values(7, 7, "taoye07");
10
11mysql> flush logs;
12mysql> insert into tb_test values(8, 8, "taoye08");
13mysql drop table tb_test;
14mysql drop database db_test;

在上面我们执行了三次flush logs命令，也就是滚动了三次，并生成了三个日志binlog日志文件。第一个文件中记录了三次insert操作，第二个日志文件中记录了insert、delete、insert，第三个日志记录了insert、删表、删库的操作。假如我们现在的需求是恢复所有delete以及删表删库的操作，我们应该怎么做？

有了前一个例子的铺垫，该数据的恢复应该不难，思路就是：第一个日志文件进行全部恢复，第二个日志文件进行两次部分恢复（以delete为分界）、第三日志文件进行一次部分恢复（即除去删表、删库操作）。

具体的命令操作，大家可以参考删一个例子，基本是一致的，这里就不做过多赘述了。

参考资料：

[1] 丁奇.MySQL实战45讲
[2] 详细分析MySQL事务日志(redo log和undo log)
https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html#auto_id_11
[3] MySQL二进制日志详解
https://www.cnblogs.com/jkin/p/10124182.html

总结

至此，本文基本就结束了。

该文详细讲解了MySQL的体系结构，其中包括连接器、缓存、分析器、优化器和执行器等各个功能部件的作用及所扮演的角色。再者，介绍了MySQL的日志系统，日志系统在MySQL当中是非常重要的，其应用场景主要体现在主从搭建以及数据恢复，在实际生产过程中使用的还是非常频繁的，毕竟谁也保证不了我们的数据库没有出现问题的时候。

这篇文章是《大话数据库》的第一篇，也是从宏观的角度来对MySQL进行一个整体性的分析，这样对后面的事务、索引等知识的理解都是非常有帮助的，我们只有了解了MySQL的底层工作原理，才能在出现问题时直击问题的本质。

认真读到这里的读者，相信都是和Taoye一样怀揣着一颗想要不断提高自己的心，想要成为一位优秀的Coder。原创不易啊，如果本文对各位有所帮助，还请关注+转发+再看，【三连】走一走啊，就当是给Taoye坚持肝文的鼓励和支持了。

《大话数据库》，我们下期再见！

写留言