MySQL学习笔记10——日志

一、日志

日志在发现错误、数据复制、数据恢复、操作审计，以及确保数据的永久性和一致性等方面，都有着不可替代的作用，对提升你的数据库应用的开发能力至关重要。

MySQL的日志种类非常多，包括通用查询日志、慢查询日志、错误日志等帮助我们快速定位错误；二进制日志、中继日志、重做日志和回滚日志等可以帮助我们找回由于误操作而丢失的数据。

1、通用查询日志

通用查询日志记录了所有用户的连接开始时间和截止时间，以及发给MySQL数据库服务器的所有SQL指令。通用查询日志可以帮助我们了解操作发生的具体时间和操作的细节，对找出异常发生的原因极其关键。

下面具体介绍一下控制通用查询日志的系统变量。通过这些变量，我们会更清楚怎么控制通用查询日志的开启和关闭，以及保存日志的文件是哪个。

SHOW VARIABLES LIKE '%general%';

在这个查询的结果中，有2点需要我们注意一下。

1. 系统变量general _log 的值是OFF，表示通用查询日志处于关闭状态。这个参数的默认值是关闭的，通用查询日志开启时会消耗系统资源并且占用磁盘空间。我们可以通过手动修改变量的值，在需要的时候开启日志。
2. 通用查询日志文件的名称是DESKTOP-QH9B5HQ.log。这样我们就知道在哪里可以查看通用查询日志的内容了。

（1）开启通用查询日志

我们可以通过设置系统变量的值，来开启通用查询日志，并且指定通用查询日志的文件夹和文件名为"D:\mytest.log" 。这个操作如下:

-- 开启通用查询日志
SET GLOBAL general_log='ON';
-- 制定通用查询日志的文件夹和文件名为"D:\mytest.log"
SET @@global.general_log_file = 'D:\mytest.log'
-- 查询通用查询日志的状态，看是否操作成果
SHOW VARIABLES LIKE '%general%';

（2）查看通用查询日志

通用查询日志都是文本型数据，可以用记事本打开。

（3）删除通用查询日志

当用户对数据库的操作比较频繁时，通用查询日志文件会不断变大。为了节省磁盘空间，我们可以移除旧的日志文件，创建新的日志文件，来对通用查询日志文件进行维护。

步骤如下：

1. 关闭通用查询日志

SET GLOBAL general_log='OFF';

2. 把通用查询日志文件"D:\mytest.log" 移至备份文件夹，空出磁盘D的空间。
3. 开启通用查询日志
   这个时候，你会发现，MySQL 已经给我们准备好了一个新的通用查询日志文件"D:lmytest.log"，并且记录了我们第一个查询的语句。

2、慢查询日志

慢查询日志是用来记录执行时间超过指定时长的查询。它的主要作用是帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。

当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。

慢查询日志是由MySQL的配置文件进行控制的。在MySQL的安装目录中(C:\ProgramData\MySQL\MySQL Server 8.0)， 我们可以找到MySQL的配置文件"my.ini" 。这个文件是一个文本格式的文件， 可以直接用记事本打开来阅读。

3、错误日志

错误日志记录了MySQL服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等。当我们的数据库服务器发生系统故障时，错误日志是发现问题、解决故障的首选。

错误日志默认是开启的。我们可以在MySQL的配置文件"my.ini" 中配置它。

4、二进制日志

二进制日志主要记录数据库的更新事件，比如创建数据表、更新表中的数据、数据更新所花费的时长等信息。通过这些信息，我们可以再现数据更新操作的全过程。而且，由于日志的延续性和时效性，我们还可以利用日志，完成无损失的数据恢复和主从服务器之间的数据同步。

可以说，二进制日志是进行数据恢复和数据复制的利器。操作二进制日志，主要包括查看、刷新二进制日志，用二进制日志恢复数据，以及删除二进制日志。

（1）查看二进制日志

查看二进制日志主要有3种情况，分别是查看当前正在写入的二进制日志、查看所有的二进制日志和查看二进制日志中的所有数据更新事件。

查看当前正在写入的二进制日志的SQL语句是:

SHOW MASTER STATUS;

查看所有的二进制日志的SQL语句是:

SHOW BINARY LOGS;

查看二进制日志中所有数据更新事件的SQL语句是:

SHOW BINLOG EVENTS IN 二进制文件名;

（2）刷新二进制日志

刷新二进制日志的SQL语句是:

FLUSH BINARY LOGS;

这条语句的意思是，关闭服务器正在写入的二进制日志文件，并重新打开一个新文件，文件名的后缀在现有的基础上加1。

（3）用二进制日志恢复数据

我们可以用mysqlbinlog工具进行数据恢复:

mysqlbinlog -start-positon=xxx -end-position-yyy 二进制文件名| mysql -u 用户 -p 

这条命令的意思是，执行二进制日志中从位置xxx开始，到yyy截止的所有数据更新操作。这里的截止位置也可以不写，意思是从位置xx开始，执行二进制文件中的所有数据更新操作。

（4）删除二进制文件

RESET MASTER;
SHOW BINARY LOGS; 
-- 删除比指定二进制日志文件编 号小的所有二进制日志文件:
PURGE MASTER LOGS TO 'GJTECH-PC-bin.000005' ;

5、中继日志

中继日志只在主从服务器架构的从服务器上存在。

从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的白志文件中，这个从服务器本地的日志文件就叫中继日志。

然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。

6、回滚日志

回滚日志的作用是进行事务回滚。

当事务执行的时候，回滚日志中记录了事务中每次数据更新前的状态。当事务需要回滚的时候，可以通过读取回滚日志，恢复到指定的位置。

另一方面，回滚日志也可以让其他的事务读取到这个事务对数据更改之前的值，从而确保了其他事务可以不受这个事务修改数据的影响。

7、重做日志

重做日志是存储在磁盘上的一种日志文件，主要有2个作用。

1. 在系统遇到故障的恢复过程中，可以修复被未完成的事务修改的数据。
2. MySQL为了提高数据存取的效率，减少磁盘操作的频率，对数据的更新操作不会立即写到磁盘上，而是把数据更新先保存在内存中，积累到一定程度，再集中进行磁盘读写操作。
   这样就存在一个问题：一旦出现宕机或者停电等异常情况，内存中保存的数据更新操作可能会丢失。这个时候就可以通过读取重做日志中记录的数据更新操作，把没来得及写到磁盘上的数据更新写到磁盘上，确保数据的完整性。