MySQL的逻辑架构和二进制日志的操作

1. mysql逻辑分层

  SQL语句在执行的过程中经历了什么？要解答这个问题，就要学习一下mysql的架构知识了，同时学习它，我们还可以更好的理解mysql是如何工作的。有如下图：
  对于上面这幅图，我们可以理解成sql语句的执行到返回结果之间经历的种种关卡。对此我们可以分为三层，客户端层(可以是PHP，Java程序这些，也就是说，只要能连接数据库，并且能够操作数据库的软件我们就叫客户端)，server层(mysql的核心部分)和存储引擎层。那么我们就对其来一一介绍吧！
  1）连接器：连接器是专门来为客户端的请求做连接处理的，比如建立连接，获取权限等。对应的指令如下：

mysql -h 主机名 -u 用户名 -p密码

这个指令我们再熟悉不过了，不就是连接指令嘛，其中，指令中的mysql就是bin目录下的mysql.exe工具，也就是说，我们如果想跟远程mysql连接，就要借助该工具，在指令中的体现就是一开始写上mysql，后面才是写-h这些，写完后，回车，客户端就会先跟服务器进行TCP的三次握手，然后再把你的指令发送到指定的服务器那里去，找到之后，对方的门卫(一个线程)连接器就会把你拦下来进行盘问，看看你的用户名和密码有没有错(也可以看你的SSL证书)，如果有，不好意思，连接不成功，如果没有，ok，我就会去mysql系统数据库里面的权限表中看看你拥有哪些权限，比如你有没有写的权限之类的，注意该权限对本次连接是长期影响的，就算你中途修改了权限，对本次连接也不会有什么影响，除非你重新连接了。好，如果权限也知道了，最后也没啥问题，门卫就会放你进去，表示连接成功了，那我们是不是就可以开始对数据库进行增删改的操作了呀。所以连接器主要负责安全和权限的认证。我们可以通过以下命令来查看当前有多少与服务器建立连接，如下：

show processlist;

  2）查询缓存(mysql8.0之后删去)：连接成功后，如果我们执行的是select语句，那么它就会优先去查询缓存中找，但前提是看看你是否有该权限，没问题就继续看看是否有对应的这条sql语句的缓存，在这个缓存当中，它里面的数据是按照key-value对进行保存的，key就为查询的sql语句，value为结果值。所以它是根据key来进行定位的，如果定位到了，就会直接把结果返回给客户端，如果没被定位到，就说明没找到，没找到就进入下一步啰。
  大多数情况下是不建议用缓存的，因为缓存很容易失效，一张表一旦有更新，这个表上所有的查询缓存都会被清空。所以对于频繁的更新，命中率会非常的低。
  3）分析器(analyzer)：如果查询缓存没被命中，就会来到分析器，来到分析器，首先会做词法分析，比如会提取你的sql语句(分析器拿到你的sql语句是个字符串)的关键字进行识别，看看有没有写错之类的，并且看看你是做何种操作，是增，还是删，或者有没有涉及到分组，排序等，同时还会判断你的字段名和表名是否存在等。如果没问题，就会进入语法分析，看看你在语法上有没有问题。
  4）优化器(optimizer)：如果上面没问题了，就会进入优化器，优化器拿到你的sql，就会分析你的sql写的好不好，会不会影响性能之类的，如果会，那么优化器就会对你的sql优化成它眼中最好的sql，但只做参考。
  5）执行器：首先，在执行之前，它会判断我们是否有操作权限，如果有，则继续往下走，开始调用下方的引擎接口来获得结果。引擎有很多，那么它在调用的时候会根据我们那个表格设置时对应的那个引擎，去调用存储引擎层里相应的接口。拿到结果后，如果是查询，还会看看我们是否开启了缓存，如果开启了，就会按照kv对进行保存，那么我们怎么看看是否开启了缓存呢？如下：
如果要想开启，我们可以打开my.ini文件，注意，它有几个参数，分别是0，1，2。如果为0，说明不开启，为1，说明开启，那么为2呢，就表示按需求缓存，那么怎么按需求缓存，比如如下语句：

select SQL_CACHE * from 表名;

也就是，当它执行到SQL_CACHE的时候就会把你当前这条查询语句缓存起来，没有就不缓存。相应的，你的my.ini就得开启它，增加这一行，query_cache_type=2，然后重启即可。然后，才是返回给客户端，以后，如果还是查相同的sql语句，就会直接在缓存中拿，避免了后面要到存储引擎里面进行的磁盘IO。同时要注意，我前面说的，更新会把你缓存中的都清了，所以，对于更新比较频繁的，不建议开启缓存。
  6）存储引擎(Storage engine)：不同的存储引擎，对于数据在磁盘上的组织格式，存放形式就有所不同。我们知道，mysql的数据其实就是存放在磁盘上的一个文件，这是我们最初的理解，但对于存储引擎而言，以Innodb存储引擎为例，它在磁盘上的存储就有这样的两个文件，如下：
也就是以.ibd结尾的和以.frm结尾的两个文件，这两个文件中.frm文件是存放跟列相关的信息，而.ibd存放的才是数据，aaa就是表名。那其它的存储引擎比如myisam就不是这样只了，而是对应的有三个文件，比Innodb多了一个，不管怎样，文件都是来保存数据的，只是在不同的存储引擎下所体现出来的形式不一样而已，当然，更为详细的后面再说。那么最后存储引擎接到执行器的命令，就会去它对应的文件(也可以是日志)里查找，看看有没有你要查找的内容，有的话就会返回给执行器，大概就是这个意思。
  同时要注意架构图上的存储引擎层是一种可插拔式的存储引擎，因为该存储引擎层中包含了mysql所支持的全部存储引擎，你可以在建表的时候自由选择(在一个库中可以建不同存储引擎的表，注意存储引擎是针对表的，不针对库)，你选择哪一个，就是按那种的方式进行存储数据的。可以简单的理解存储引擎就是来帮我们管理数据，查询数据的。

2. mysql日志操作

  日志我们可以分为很多种，如下：
  1）查询日志：将我所执行的select命令保存下来，如果查询多，那么该日志就会非常大，所以不建议开启。
  2）慢查询日志：对于某些执行慢的sql语句记录下来，有助于后期我们排查解决，当然了，有时候慢也不一定是sql本身的问题，也有可以是内存不足导致查询慢的。
  3）错误日志：可以记录启动mysql，关闭mysql所产生的错误，也包括mysql复制过程中的相关信息。
  4）事务日志：事务一旦提交，那么事务提交的结果就会记录到事务日志里(记录到日志之后，才会将提交的事务保存在数据库中)。
  5）二进制日志(bin log)：又叫归档日志，它是记录mysql中所有数据的改变情况的，比如你写了一条创建表的语句，一旦执行成功，它就会自动的把你的那个创表语句保存到二进制日志里，也就是说，它会原封不动的把你写的语句保存到二进制日志里。所以，如果别人拿到我们的二进制日志，就会把我们数据库里的内容全部还原过来了。该日志主要用于主从复制。
  6）Innodb日志(redo log)：又叫重做日志文件，该日志是Innodb引擎独有的。跟bin log(逻辑日志)相比，它属于物理日志。
  现在我们重点要讲的日志是二进制日志，二进制日志呢是存在server层级别的，是所有引擎共有的，所以下面我们将介绍二进制日志的相关操作，以及如果发生数据丢失我们该怎么恢复过来。

查看二进制日志是否启动

show variables like "log_bin";

  如果为OFF，说明当前二进制日志是没有开启的，那么该如何开启呢？我们通过修改配置文件的方法来开启它，也就是my.ini文件，打开它，在[mysqld]下加上这么一句话，如下：

log bin=aa  #注意这个aa表示日志的文件名，如果不指定，就会以当前的主机名来命名

  这样就行了，保存退出，然后重启服务，再看一下，如下：
  完美，成功开启。然后打开你的data目录，它会默认在你指定的datadir下创建这两个文件，如下：
  后缀为000001的文件为日志文件，而后缀为index的是索引文件，所谓的索引文件就是记录了所有的二进制文件，我们打开看一下就明白了，如下：
  现在是000001，往后如果有下一个日志文件的话，就是000002，以此类推，相应的，索引文件也会自动的记录下来。比如，我们来重新生成一个新的日志文件，如下命令：

flush logs;

  看到没有，不信可以自己试一下。当然也可以用命令查看，如下：

show master logs;  

或：

show binary logs;

  注意，现在我们使用的日志就是我们刚刚flush出来的新日志，也就是aa.000002这个文件，那好，如果我对mysql做了一系列操作，会不会自动的就记录在aa.000002这个日志文件呢？要证明它，就要看它的文件大小，上图，当前的文件大小是不是156？那么我们就来进行创库，创表吧！如下：   然后再次查看，如下：   确实是增了，那么我们能不能打开看一看呢？因为它是二进制文件，所以，我们不能直接打开，得借助mysql给我们提供的工具才能打开，它就是mysqlbinlog，如下： 所以如下(注意要退出(exit)命令行)：

mysqlbinlog --no-defaults 二进制文件;

以上返回的就是文件内容，只是有点多，没有全部截下来。如果看的不方便，那么就把上面的内容输出到文本文件上，如下：
也就是说，我们要把内容输出到C盘下的aa.log文件上，这样我们看起来就会舒服很多。好，观察文件，我们是不是能够看到创库语句，使用库语句和创表语句呀，如下：

但唯独是不是少了插入语句？找来找去没有，其实并不是没有，而是这条插入语句用base64加密了，所以，我们要重新打开，输入一下，如下命令：

--base64-output=decode-rows -v

加上上面那句话，然后再一次打开aa.log，就能找到了，如下：
如果我们再做一些更新，如下：
再执行mysqlbinlog命令，然后再查看，如下：
虽然这个SQL语句看起来怪怪的，但不管怎样我们知道了，mysql的日志文件确实帮我们记录下来了，除了查询。到这为止，我们再来看一下000002这个文件的大小跟之前相比是不是又变大了，如下：
所以，我们可以做个总结，我们在mysql所做的一切操作，除了查询，都会被自动的记录在mysql的二进制日志里(如果你的语句涉及到rand()，或者变量这些，它也会保存起来)，但有个前提，就是你这条语句必须执行成功。如下：
  我有id为7的那条记录吗，是不是没有？没有怎么能修改成功呢？没修改成功是不是执行就不成功，执行不成功的话二进制日志会帮你记录吗？

查看当前正在使用的二进制文件

  如果你再一次flush logs，表示结束当前日志，重开一个日志，上面也提到过了，我们再试一遍，如下：   当前正在使用的日志就变成了aa.000003的这个文件了。如果mysql重启，那么当前使用的日志还会不会是aa.000003呢？试验一下就知道了，如下：

  所以，导致新日志的产生有两个：第一个，执行flush logs命令，第二个，重启mysql服务器。

清空所有的二进制日志1(从000001开始)

  这样就表示从头开始记录，注意aa.000001里面此时是没有记录的，有的只是它的一些声明信息，那我们不用管。

mysqlbinlog扩展

  随着我们操作的命令多了，那么相应的日志文件里的内容也多了，如果我只想查看某一时间段的日志怎么办？如下命令：

mysqlbinlog 
 --no-defaults 
 --base64-output=decode-rows -v 
 --start-datetime="2021-04-03 15:40:00" 
 --stop-datetime="2021-04-03 16:01:01" 
C:\mysql-8.0.23-winx64\data\aa.000001 > C:/aa.log

  也就是说，我要查看的内容是2021-04-03 15:40分的到16:01:01这时间段里的内容。然后我打开C:/aa.log，因为我在这时间段内添加了一行记录，所以当我打开aa.log应该可以看到我的添加记录，如下：
  我已经证明过了，当我把时间提前一点，就看不到我上面的那条添加记录了。

清空所有的二进制日志2

  如下：
  当前的日志为aa.000005这个日志，如果我想删除000003之前的日志怎么办，如下：

purge binary logs to 'aa.000003';

只记录某个库

  这个好理解，如果有多个库，但是我只想记录某个库的操作记录，那要怎么做？首先，打开我们的my.ini，加上这么一句话，如下：

binlog-do-db=数据库名

  这样就会指定为某一个库的记录，而其它库不会记录。比如，我现在再创建一个库，如下：
  打开000005日志文件是可以看的到创建db2这个指令的，那么我们就打开my.ini，进行配置一下吧，如下：
  然后我们重启一下吧！重启完我们继续往db2里添加数据，看看aa.000006这个日志文件有没有记录，如下：
  再次打开aa.log，如下：
  有看到所谓的添加语句吗？是不是没有，我们可以在db1库里的tb1增加数据，再次验证，这样更加有说服力，如下：
  再次查看aa.log，如下：
  搞定，简直就是perfect。
  注意与之相反的为binlog-ignor-db。

恢复数据

  恢复数据就是说如果我们误删了数据，该如何恢复，那么，开始第一个案例吧，我们就从aa.000001这个文件开始记录，所以，知道怎么做了吧，如下：
  然后我们创建db3这个库(对了，别忘了修改my.ini的binlog-do-db属性，把它值改为db3，或注释掉。最后重启，重启完继续reset master;)，再创建表，添加数据，这些都是老操作了，如下：
  不用说，我们操作的命令肯定是记录在aa.000001文件里了，现在我们重新再新建一个日志吧，然后再把db3这个库给干掉，如下：
  那么相应的，我们的删库语句就会被记录在aa.000002这个文件中，如果我们想恢复db3这个数据库，那么就要借助我们的aa.000001这个文件了，那么具体怎么操作，如下：
  是不是db3就恢复过来了，命令如下：

mysqlbinlog --no-defaults  C:\mysql-8.0.23-winx64\data\aa.000001|mysql -u root -p

  但是这不会去用，因为它有个缺点，就是如果创表语句和删表语句在同一个日志文件怎么办，我们是不是就要去指定位置去恢复呀，那么我们就做一下，不过在这之前先准备如下数据，我们从头开始：
  请认真看我上面的操作，如果可以了，我将提出两个问题，第一个问题，我上面的修改是误修改，我想撤回怎么办。第二个问题，我把库给删了，我要怎么恢复？
  首先，有一条命令，我们就从这条命令如手，如下：

show binlog events in "C:\\mysql-8.0.23-winx64\\data\\aa.000001";

  上图它所返回给我们的结果很重要，我们不需要全部看懂，能看懂一部分就可以了，那么我们要怎么看呢？首先，第一步，我们要看的是Info字段，因为我们要恢复db3这个数据库，所以找一下，看看有没有create database db3的这条sql语句，是不是有呀，第几行，答案是第4行。第二步：在这一行中我们要看两个字段，分别是Pos和End_log_pos，这两个代表了起始位置和结束位置，简单的理解就是create database db3这条语句在二进制日志文件的起始位置和结束位置，单位是字节，你可以这么想，二进制文件，从字面意思就是一堆的0和1，一个数字就代表一个字节，也就是说，create database db3这条语句转换为0和1，那么无论如何，它是不是必定会有一个起始位置，而上图说它的起始位置是不是233，说白了就是在这个二进制文件的第233个位置处，一直到338这之间的数字就是create database db3这条语句。那么其它的也同理。第三步：找到更新语句，但是注意，像修改，删除，添加它是不会直接就把sql语句给贴出来的，并且他们是被事务给包围着的，这就是更新的特点，与DDL语句是不一样的。那么好，既然它并没有直接就把sql语句给贴出来，那么我们怎么看？很简单，看它的Event_type字段，如下：
  先看Event_type字段，有没有看到Update_rows，直译过来就是修改行，那么相应的，你再仔细看的时候，你是不是就会看到Write_rows和Delete_rows呀，不用我说，你们都知道是什么意思。也就是说于Update_rows这一行的Info字段的值就是一条修改语句，只不过它没有像DDL语句那样直接就贴出来，但是我们是可以猜出来的，怎么猜？还能怎么猜，结合上下文呗，同时我也说了，不管是添加，修改，还是删除都有事务，如上图我用绿框框出来的两个部分，一个是事务开启，一个是事务提交。第四步：到这，我就可以把库跟表，以及表的前三条记录恢复过来了，还记不记得创表语句它的其实位置是多少？是不是233，那么看更新语句，我们应该定位到它的事务开启那里，因为这里的事务开启和事务结束就是针对修改的，好，事务开启是不是BEGIN，往上看，忽略SET @@SESSION.GTID_NEXT= 'ANONYMOUS'语句，是不是就会看到commit呀，这个commit是上一个事务的提交，那好，是对哪个操作的提交？是不是就是对添加语句的提交呀。那么现在我的起始位置已经确定了，它的结束位置是不是就得截取到该添加语句的commit哪一行呀，也就是End_log_pos为1435的那一条，为什么截取到这？你是不是得保证该添加语句能正常的添加呀，你不截取到commit这里，那么这条添加语句它能提交到数据库里吗？是不是就不能呀，那好，分析完毕，结论就是其实位置为233，结束位置为1435，那么命令如下：

mysqlbinlog 
--no-defaults 
--start-position=233 
--stop-position=1435 
C:\mysql-8.0.23-winx64\data\aa.000001|mysql -u root -p

  那么现在就是见证奇迹的时刻了，如下：
  现在库也恢复了，但是还没完，我要通过日志来把id为3的删掉，还是一样的方法，如下：
  因为我要删除，所以我要截取的就是我上图大红框框出来的部分，所以起始位置是1823，结束位置是2030。命令如下：