457-MySQL(日志)

墨蓝 2022-09-07 04:00 369阅读 0赞

**在之前，我们已经讲了MySQL事务日志：redo log 和 undo log**（是InnoDB engine存储引擎生成的）  
作为一个mysql client 来发起一个连接请求  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]  
**这章我们要讲的MySQL日志是在MySQL server上的**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]  
不管更改哪个存储引擎，这些日志都是有的。  
plugin engine 插件式的存储引擎，可以更换  
**这个MySQL日志主要分为4类：**  
**错误日志  
查询日志  
二进制日志  
慢查询日志**

## MySQL日志 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 4]

**打开my.ini,在后面加上上面的参数，保存后重启mysql服务就行了。**  
![在这里插入图片描述][c36814aaa055442daf0fb8fb5efcebe5.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 5]  
开启log-bin=mysql-bin的时候还要加上 server-id=1(当前MySQL的身份）

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 6]  
**在linux root下重启mysqld服务：service mysqld restart**

## 错误日志 ##

**错误日志**是 MySQL 中最重要的日志之一，它记录了当 **mysqld** 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息（corddump，error，异常exception）。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。  
mysqld 使用错误日志名 **host\_name.err**(host\_name 为主机名) 并默认在参数 DATADIR(数据目录)指定的目录中写入日志文件。

## 查询日志 ##

所有的SQL都记录下来。  
**查询日志记录了客户端的所有语句**。由于上线项目sql特别多，开启查询日志IO太多导致MySQL效率低，**只有在调试时才开启**，比如通过查看sql发现热点数据进行缓存。  
![在这里插入图片描述][a57af5f649114958a6348015d2229f51.png]

## 二进制日志 ##

（除select外）  
二进制日志(**BINLOG**)**记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言) 语句**，但是不包括数据查询语句。语句以“事件”的形式保存，它描述了数据的更改过程。 **此日志对于灾难时的数据恢复起着极其重要的作用。**  
两个重要的应用场景：**主从复制、数据恢复**  
**从库读主库的binlog，把binlog的所有操作像拍电影一样在从库上演一下。**  
查看binlog：mysql> show binary logs;  
通过**mysqlbinlog**工具（mysql原生自带的工具）可以快速解析大量的**binlog日志文件**，如：  
![在这里插入图片描述][0dc09dcd541a49ed8235a571efc7d84c.png]  
![在这里插入图片描述][e87501fbd51b4649ae74bdb2784d6238.png]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 7]

## binlog演示 ##

我们先刷新一下日志。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 8]  
然后我们进行一些操作  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 9]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 10]  
我们发现日志的filesize和刚才的不一样了。  
如果我们直接cat日志的话，会发现不是明文，无法直接查看。  
我们要通过  
**mysqlbinlog**

![在这里插入图片描述][a2e98156cbc64f5096652966ffad5baf.png]  
![在这里插入图片描述][a2947d2827704b90b24809256be90734.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 11]  
@代表的是字段。详细地记录了对数据库的更改。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 12]

**at 数字，指的是位置，区间，相当于当前事件在binlog发生的记录的位置，进行回放的方式进行数据恢复。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 13]  
我们现在创建一个数据库，并且添加表和数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 14]  
假如现在有人把库删除了

![在这里插入图片描述][8bef80ca006b426591c7cc56596d9f02.png]  
相当于这个库的所有表的所有数据都没有了。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 15]  
这些操作都会记录在binlog里面。理论上来说，可以从binlog把丢失的数据恢复出来。本身恢复过程中产生的日志也要记录在binlog中，毕竟是修改的操作。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 16]  
**在配置binlog的时候，我们还配置了：**

日志的过期时间：7天。7天之前的数据怎么恢复？MySQL数据的恢复靠的是SQL的数据备份（每天或者每几天备份1次）加上binlog的数据恢复。  
binlog只恢复上一次数据备份以后新增加的数据产生问题丢失，可以通过binlog恢复，之前的数据通过数据备份恢复（在SQL的文件脚本中），在~/data.sql里面，在MySQL上用source执行，就可以把之前备份的数据恢复到mysql这个库表中。备份之后新增加的数据通过binlog恢复就可以了。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 17]  
也就是说，我很明白的知道数据恢复最多恢复到这个000003文件就可以了。  
![在这里插入图片描述][ab282aaa8330469b98cce4ba0f2966aa.png]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 18]  
把775记住。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 19]  
在1410被删库了。  
![在这里插入图片描述][6213debd1bd243c6ba85af294fa2cdfc.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 20]  
mytest出来了！  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 21]  
**stop datatime  
stop position**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 22]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 23]

## 慢查询日志 ##

MySQL可以设置慢查询日志，当SQL执行的时间超过我们设定的时间，那么这些SQL就会被记录在慢查询日志当中，然后我们通过查看日志，用explain分析这些SQL的执行计划，来判定为什么效率低下，是没有使用到索引？还是索引本身创建的有问题？或者是索引使用到了，但是由于表的数据量太大，花费的时间就是很长，那么此时我们可以把表分成n个小表，比如订单表按年份分成多个小表等。慢查询日志相关的参数如下所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 24]  
慢查询日志记录了包含所有执行时间超过参数 long\_query\_time（单位：秒）所设置值的 SQL语句的日志，在MySQL上用命令可以查看，如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 25]  
这个值是可以修改的，如下：  
![在这里插入图片描述][f95ec32f4e3b4cbfa0f45caa2e08aded.png]  
现在修改成超过1秒的SQL都会被记录在慢查询日志当中！可以设置为0.01秒，表示10毫秒。  
慢查询日志，默认名称是host\_name-slow.log，存放在MySQL的数据路径下，内容格式显示大致如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 26]  
通过查询慢查询日志，发现项目运行过程中，上面这条SQL语句的执行时间超过了设定的慢查询时间，那么接下来就需要用explain分析一下该SQL的执行计划了，根据具体情况找出SQL和索引该怎么去优化。  
**show profiles命令可有查看sql具体的运行时间，全局变量的名字是：profiling**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]: /images/20220829/98d8a0c260494eec9b4e222d744bce49.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]: /images/20220829/66a73f1582e74ce7938b2cfe473a4676.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]: /images/20220829/78a1c9d58d2e4c42a39238b6057dd675.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]: /images/20220829/514a34c87c9c499db3f5447675d6b3ab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 4]: /images/20220829/2b781a6a03cb4e5aa726231f1c5e218a.png
[c36814aaa055442daf0fb8fb5efcebe5.png]: /images/20220829/dab2272c48214cfca36109599861a9e0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 5]: /images/20220829/dbcffdf1016c4afbbbc2fc72cc23f86f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 6]: /images/20220829/c6bc0fdeccf74c4eaf21d734f13c8ec1.png
[a57af5f649114958a6348015d2229f51.png]: /images/20220829/4e5873732e344f4e9741408914e6be50.png
[0dc09dcd541a49ed8235a571efc7d84c.png]: /images/20220829/60d326f2df4746bdb0ea3acf62c4f501.png
[e87501fbd51b4649ae74bdb2784d6238.png]: /images/20220829/7c270044e84144aaa122a5c838eb4ce7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 7]: /images/20220829/5edf9112b2c84fdab54240d721afd9af.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 8]: /images/20220829/1e05811d3d0c49ceb9f27daad84a8420.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 9]: /images/20220829/737f02ae8ea0477d9622a8fd681febab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 10]: /images/20220829/58c285074d074675b639d8ee1e03e981.png
[a2e98156cbc64f5096652966ffad5baf.png]: /images/20220829/c3810e2185dd4dd688a04ca974eeb33e.png
[a2947d2827704b90b24809256be90734.png]: /images/20220829/ed2c81f316b545f49c611344a0d34942.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 11]: /images/20220829/3cfbe29e22e442abbf92ce0f5d009b15.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 12]: /images/20220829/e7fc6a9f330b42cc8af5f1eef42244c0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 13]: /images/20220829/c64f30748b724a77a0ab4269fc0fac5a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 14]: /images/20220829/1ac3aba98971479384a4868a6afadf39.png
[8bef80ca006b426591c7cc56596d9f02.png]: /images/20220829/d38c2ade0e95491abf24b573fc2b276e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 15]: /images/20220829/c2b539f46e6f48d4930cb6020c3254ec.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 16]: /images/20220829/347163b8b9c84e97883f46b5a1d600b6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 17]: /images/20220829/1f385581b8504bfbaaa2c1a0abe2ba86.png
[ab282aaa8330469b98cce4ba0f2966aa.png]: /images/20220829/49d4b32278284cb78638e037eee54667.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 18]: /images/20220829/6310e04507624609959f80e21c8db768.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 19]: /images/20220829/fc08fc2cec1545d58828c85c9187f974.png
[6213debd1bd243c6ba85af294fa2cdfc.png]: /images/20220829/82c3f84cb596478f969718f50e254df5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 20]: /images/20220829/079d569f01b947b487ab07fa7294c6f7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 21]: /images/20220829/1154645c40bd4d38aba0898adfbe5cb1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 22]: /images/20220829/0e2842c70b5d442ca2361437e8287928.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 23]: /images/20220829/9686233f19d94411b0cd78c75850ab4f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 24]: /images/20220829/c8c2e61a581b4746ba963484ecf7c454.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 25]: /images/20220829/af6b32c3a8544ef3bda83956d664f65d.png
[f95ec32f4e3b4cbfa0f45caa2e08aded.png]: /images/20220829/0fe709ad79534a59912cc3402639ced6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 26]: /images/20220829/0665e46fa23c47c6bcb680a9271dd8b2.png