MySQL 日志

MySQL日志种类：通用查询日志、慢查询日志、错误日志、二进制日志、中继日志、重做日志、回滚日志

1.通用查询日志

记录了用户连接时间和截止时间，以及发送给 MySQL 服务器执行的所有 SQL 操作指令，开启查询日志，可以方便还原操作场景以及定位问题

mysql> SHOW VARIABLES LIKE '%general%';
+------------------+---------------+
| Variable_name | Value |
+------------------+---------------+
| general_log | OFF | -- 通用查询日志处于关闭状态，默认状态，开启后会消耗资源和占用存储空间
| general_log_file | GJTECH-PC.log | -- 通用查询日志文件的名称是GJTECH-PC.log
+------------------+---------------+
2 rows in set, 1 warning (0.00 sec)

-- 开启通用查询日志
SET GLOBAL general_log = 'ON';
-- 指定日志存储文件
SET @@global.general_log_file = 'H:\mytest.log';
-- 关闭通用查询日志
SET GLOBAL general_log = 'OFF';

2.慢查询日志

用来记录查询时间超过指定时长的查询。帮助我们记录执行时间长的 SQL 查询，并有针对的进行优化。在配置文件“my.ini”中配置

slow-query-log=1 -- 表示开启慢查询日志，系统将会对慢查询进行记录。
 
slow_query_log_file="GJTECH-PC-slow.log"  -- 表示慢查询日志的名称是"GJTECH-PC-slow.log"。这里没有指定文件夹，默认就是数据目录："C:\ProgramData\MySQL\MySQL Server 8.0\Data"。
 
long_query_time=10  -- 表示慢查询的标准是查询执行时间超过10秒

控制慢查询日志的还有一个系统变量：min_examined_row_limit。这个变量的意思是，查询扫描过的最少记录数。这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过 long_query_time 的值，那么，这个查询就被记录到慢查询日志中；反之，则不被记录到慢查询日志中。查看其配置：

show variables like 'min%';

这个值默认是 0。与 long_query_time=10 合在一起，表示只要查询的执行时间超过 10 秒钟，哪怕一个记录也没有扫描过，都要被记录到慢查询日志中。可修改，修改后需重启 MySQL 服务器。

3.错误日志

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

其配置在“my.ini"文件中：

# Error Logging.
log-error="GJTECH-PC.err"

4.二进制日志

二进制日志主要记录数据库的更新事件，比如创建数据库，创建表，插入表数据，更新表数据，删除表数据，操作数据所花费的时间等。由于日志的延续性和时效性，可以利用日志完成无损失的数据恢复和主从服务器之间的数据同步。

二进制日志是数据恢复和数据复制的利器。

-- 查看二进制日志，当前二进制的名称，和写入位置
mysql> SHOW MASTER STATUS;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| GJTECH-PC-bin.000011 | 2207 | | | |
+----------------------+----------+--------------+------------------+-------------------+   -- 当前正在写入的二进制日志是“GJTECH-PC-bin.000011”，当前的位置是2207。
1 row in set (0.00 sec)


-- 查看所有二进制日志的 SQL 语句
SHOW BINARY LOGS;

-- 查看二进制日志中所有更新事件的 SQL 语句
SHOW BINARY EVENT IN 二进制文件名;

-- 刷新二进制日志，即关闭当前正在写入的二进制日志文件，并重新打开一个新的，文件名后缀加1
-- 二进制日志大小超过系统变量 max_binlog_size 指定的值时，系统就会生成一个新的二进制日志文件
FLUSH BINARY LOGS;

-- 用二进制日志恢复数据，执行二进制日志中从xxx位置到yyy位置中所有的更新操作
mysqlbinlog –start-positon=xxx --stop-position=yyy 二进制文件名 | mysql -u 用户 -p

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

5.数据恢复案例

1. 数据库备份（整个数据库）

   mysqldump -u 用户 -p 密码 数据库 > 备份文件
   

2. 刷新二进制日志， 产生新的二进制日志文件，则该文件只包含备份后的日志

   FLUSH BINARY LOGS;
   

3. 给商品信息表插入一条新的数据记录“笔”

   mysql> INSERT INTO demo.goodsmaster
   -> (
   -> itemnumber,
   -> barcode,
   -> goodsname,
   -> salesprice
   -> )
   -> VALUES
   -> (
   -> 2,
   -> '0002',
   -> '笔',
   -> 3
   -> );
   Query OK, 1 row affected (0.03 sec)
   

假设这个时候，系统突然宕机，数据库无法启动，为了使系统恢复正常，我们重启机器，重新创建数据库，并且需要进行数据恢复

4. 准备从备份文件恢复数据：首先查看正在使用的二进制日志

   SHOW BINARY LOGS
   -- 如最大的文件名为：GJTECH-PC-bin.000012；
   -- 刷新日志
   FLUSH BINARY LOGS;
   -- 确保已经创建新日志
   SHOW MASTER STATUS;
   -- 删除数据库
   DROP DATABASE demo;
   -- 创建新的数据库
   CREATE DATABASE demo;
   -- 恢复备份时的数据库
   mysql -u 用户 -p 密码 数据库名称 < 备份文件
   

5. 从GJTECH-PC-bin.000012二进制日志中恢复数据

   -- 找到更新操作的位置和结束位置
   SHOW BINLOG EVENTS IN 'GJTECH-PC-bin.000012';
   -- 恢复
   mysqlbinlog --start-position=306 "GJTECH-PC-bin.000012" | mysql -u root -p
   

6.中继日志

中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，先从主服务器读取二进制日志内容，并把内容写入到本地日志文件中（即中继日志），然后再从中继日志中读取日志来更新数据，完成主从服务器的同步。回滚日志可以让其他事务读取到当前事务更改前的值，从而确保其他事务不受这个事务修改数据的影响。

7.回滚日志

回滚日志的作用是进行事务回滚。当事务执行的时候，回滚日志记录着事务中数据每次更新前的状态。当事务需要回滚时，可根据回滚日志恢复到指定的位置。

mysql> SHOW VARIABLES LIKE '%innodb_max_undo_log_size%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
+--------------------------+------------+
1 row in set, 1 warning (0.00 sec)


mysql> SHOW VARIABLES LIKE '%innodb_undo%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_undo_directory | .\ |  -- 表示回滚日志的存储目录是数据目录。 
| innodb_undo_log_encrypt | OFF | -- 表示回滚日志不加密。
| innodb_undo_log_truncate | ON | -- 表示回滚日志是否自动截断回收，前提是设置了独立表空间。
| innodb_undo_tablespaces | 2 |  -- 表示回滚日志有自己的独立表空间，而不是在共享表空间ibdata文件中。
+--------------------------+-------+
4 rows in set, 1 warning (0.00 sec)

8.重做日志

重做日志是存储在磁盘上的一种日志，作用：

1. 在系统遇到故障恢复的过程中，可以修复被未完成事务修改的数据。

2. MySQL为了提高数据存储效率，减少磁盘操作的频次。对数据的更新操作不会立即写入到磁盘中，而是把数据更新先放在内存中，积累到一定程度后再集中进行磁盘操作。这样就存在一个问题：当服务器宕机或停电等情况，内存中保存的更新操作没有写入磁盘，此时就可以通过重做日志执行完剩余的操作，保证数据的一致性。

   mysql> SHOW VARIABLES LIKE '%innodb_log_files_in_group%';
   +---------------------------+-------+
   | Variable_name | Value |
   +---------------------------+-------+
   | innodb_log_files_in_group | 2 |
   +---------------------------+-------+
   1 row in set, 1 warning (0.00 sec)
   

   变量 innodb_log_files_in_group 值的取值范围是 1~4。即4个文件，分别为

   1. 用户创建表的插入操作
   2. 用户创建表的更新和删除操作
   3. 临时表的插入操作
   4. 临时表的更新和删除操作