《MySQL数据库》MySQL ERRORLOG,BINLOG,SLOWLOG日志详解

前言

MySQL 经常出现启动错误或者执行错误等等，这个时候我们需要查询error日志

在数据库使用中，经常会出现需要恢复数据的情况，MySQL如果需要恢复数据的话需要开启binlog（二进制日志）。

Error Log

错误日志默认设置如下：

1. error log 默认路径在数据文件下。

2. error log 默认文件名为主机名.err，例如：iZm5e5v2zi93osbr5z21fvZ.err。

自定义设置文件名和路径：

打开MySQL的配置文件 vim /etc/my.cnf  加入如下配置

log_error=/usr/local/mysql/log/mysql.log

Bin Log

1. 配置

mysql 8.0 版本之前，默认不开启，生成建议开启。

my.cnf 文件夹下面参数设置。

server_id = 5 ;                   # 服务编号，5.7必须要。
log_bin=/data/binlog/mysql-bin    # binlog 日志存放目录+前缀，日志文件名例如：mysql-bin.0001 ，注意目录需要提前存在。
sync_binlog=1                     # binlog 刷写的策略。1：表示提交只有立即写入磁盘。
binlog_format=row                 # binlog的记录格式为row模式

重启MySQL之后再对应目录下可以看到如下日志：

其中 mysql-bin.index 是日志索引文件。

重要：binlog 日志建议和数据盘分开存放，防止硬盘坏了的情况。

mysql> select @@log_bin_basename;    -- 查询bin_log 存放位置

mysql> select @@log_bin;             -- 查询bin_log 是否开启 1：开启

 

2. 说明

1. binlog 记录内容

binlog 会记录下以下操作：

DDL 操作表结构，字段的语句。
DCL 权限等一些控制语句。
DML 操作表的语句（insert,update,delete），并且是已经提交的，未提交的不会记录

DDL 和 DCL 记录的是你写了啥就记录啥。 DML语句记录方式可以通过参数控制

mysql> select @@binlog_format;    -- 5.7 默认ROW, statement:写啥记啥，ROW:记录数据页变化，mixed:混合前两种记录

statement : 可读性高（文本），日志量少，不够严谨（遇到获取当前日期的时候，因为语句是故障时执行的，但是后续通过这种方式无法获取当时的时间点）。

row :  可读性低（二进制），日志量大，严谨。

 

3. bin log 查看

mysql> show binary logs;     -- 查询mysql存在的binlog日志文件

mysql> show master status;   -- 查询现在正在使用binlog 日志文件

mysql> show binlog events in 'mysql-bin.000002';   -- 查看当前被使用的binlog 信息

 mysqlbinlog mysql-bin.000002    -- 使用mysqlbinlog 命令查看binlog 日志。

mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002    -- 该命令可以翻译binlog 下的DML 操作语句，只能用于查看。

mysql> flush logs    -- 创建新的日志。后续binlog 就从这个新日志开始

查询日志就可以看到新的日志：

 

4. 通过binlog 恢复数据

模拟误操作的情况：第9步删除和第13步的drop。

(0) drop database if exists testdb ;
(1) create database testdb charset utf8;     
(2) use testdb;
(3) create table t1 (id int);
(4) insert into t1 values(1),(2),(3);
(5) insert into t1 values(4),(5),(6);
(6) commit
(7) update t1 set id=30 where id=3;
(8) commit;
(9) delete from t1 where id=4;
(10)commit;
(11)insert into t1 values(7),(8),(9);
(12)commit;
(13)drop database testdb;

我们要恢复第9步删除和第13步的drop 操作之前的数据

截取binlog 日志，找到起点和终点。  我现在要恢复到drop 操作前： 找到开始位置点和结束位置点， 我需要的 383 到 2048 ，下面的语句就是将日志转化成语句。

mysqlbinlog --start-position=383 --stop-position=2048 /usr/local/mysql/binlog/mysql-bin.000003 > /usr/local/mysql/bin2.sql

还有一种通过实践截取的方式：

mysqlbinlog --start-datetime=200812 21:08:26 --stop-datetime=200912 21:09:09 mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 > /usr/local/mysql/bin2.sql

接下来就是导入SQL文件即可，重点因为这是恢复，所以我们不需要他再记录binlog日志，故导入SQL文件前，先临时关闭binlog. 操作如下：

mysql> set sql_log_bin = 0;    -- 关闭binlog日志
mysql> source /usr/local/mysql/bin2.sql     -- 导入sql脚本（通过binlog截取出来的）
mysql> set sql_log_bin = 1;    -- 开启binlog日志

指定数据库的binlog导出（-d 数据库名）：

mysqlbinlog -d testdb --start-position=383 --stop-position=2048 /usr/local/mysql/binlog/mysql-bin.000003 > /usr/local/mysql/bin2.sql

binlog实际场景中的运用方式：

binlog 主要是配和备份数据一起恢复数据库， 先通过备份数据库恢复到比如一个星期前的数据，然后剩下的数据由binlog恢复。

 

5. binlog 维护命令

mysql> select @@max_binlog_size;

binlog 不要直接删除物理文件：

mysql> select @@expire_logs_days;    -- binlog保存天数，0特殊表示永久保存。

mysql> purge binary logs to 'mysql-bin.000002';     -- 删除binlog日志到mysql-bin.000002，也就是把mysql-bin.000001 删除。

purge binary logs before now() - interval 3 day;     根据时间删除，删除3天前的日志。

mysql> reset master;    -- 全部删除，操作不建议使用，主从必然会down。

 

6. binlog(GTID)

GTID(Global Transaction ID) :

mysql> select @@session.gtid_next = 'anonymous';     -- GTID是否开启，1开启

GTID 定义 

GTID = source_id ：transaction_id  例如：7E11FA47-31CA-19E1-9E56-C43AA21293967:29

gtid-mode=on                            -- 和参数enforce-gtid-consistency一起开启gtid功能。
enforce-gtid-consistency=true     -- 见gtid-mode说明

开启GTID之后对数据库的操作会记录GTID

binlog日志中也记录了GTID。

mysql> select @@server_uuid;      -- 查看gtid的uuid

数据文件目录下会存在一个文件：

/usr/local/mysql/mysql-5.7.22-linux-glibc2.12-x86_64/data/auto.cnf   里面也记录了server_uuid。

通过案例说明如何使用gtid恢复数据

(1) create database gtiddb charset utf8;     
(2) use gtiddb ;
(3) create table t1 (id int);
(4) insert into t1 values(1),(2),(3);
(5) insert into t1 values(4),(5),(6);
(6) commit
(7) update t1 set id=30 where id=3;
(8) commit;
(9) delete from t1 where id=4;
(10)commit;
(11)insert into t1 values(7),(8),(9);
(12)commit;
(13)glush logs;
(14)insert into t1 values(10);
(15)drop database gtiddb ;

第13步模拟切换日志， 第15步误操作。  需要恢复到第14步。

根据（show binlog events in 'mysql-bin.000002';） 找到恢复数据的gtid起点：8993a5f5-2921-11ea-a866-00163e046b75:2 终点：8993a5f5-2921-11ea-a866-00163e046b75:9

下面是截取命令，我们取2到9 的gtid数据，并且排除7，delete 我们也不要删除。

mysqlbinlog --include-gtids='8993a5f5-2921-11ea-a866-00163e046b75:2-9' --exclude-gtids='8993a5f5-2921-11ea-a866-00163e046b75:7' mysql-bin.000002 mysql-bin.000003 > /usr/local/mysql/gtid.sql

恢复操作和之前是一样的

mysql> set sql_log_bin = 0;    -- 关闭binlog日志
mysql> source /usr/local/mysql/gtid.sql;     -- 导入sql脚本（通过binlog截取出来的）
mysql> set sql_log_bin = 1;    -- 开启binlog日志

上述操作你会发现恢复不了数据，那是为什么呢？

重点：GTID的幂等性：开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行。 那要如何处理呢？

截取日志的时候过滤掉gtid  需要在截取命令中加入参数 --skip-gtids。

mysqlbinlog --skip-gtids --include-gtids='8993a5f5-2921-11ea-a866-00163e046b75:2-9' --exclude-gtids='8993a5f5-2921-11ea-a866-00163e046b75:7' mysql-bin.000002 mysql-bin.000003 > /usr/local/mysql/gtid.sql

小结：binlog 重要性不言而喻，后续真正备份恢复中也需要binlog日志，才能达到真正的恢复如初。

 

Slow Log

作用：记录慢SQL语句的日志,定位低效SQL语句的工具日志，默认不开启。

配置：

mysql> select @@slow_query_log;   -- 控制慢日志是否开启的开关0：不开启，1：开启

mysql> select @@slow_query_log_file;   -- 慢日志存放的位置

mysql> select @@long_query_time;       --设置时间规则执行多少时间算慢。

mysql> select @@log_queries_not_using_indexes;   -- 开启是否记录不走索引的语句，0:关，1：开

案例：准备100万数据量的表。然后执行查询语句，然后查看日志

当你日志庞大的时候，你会发现vi 看不方便，mysql给我们提供了方便看这个信息的命令：

mysqldumpslow -s c -t 5 /usr/local/mysql/mysql-5.7.22-linux-glibc2.12-x86_64/data/iZm5e5v2zi93osbr5z21fvZ-slow.log  -- c 表示数量，-t 5 显示前5条。

扩展可以实现：更加方便的展示slowlog日志信息。

Anemometer基于pt-query-digest将MySQL慢查询可视化。

 

总结

会看MySQL日志是非常重要的，并且MySQL提供的日志非常重要，我们需要能够理解日志原理，更好为MySQL的数据安全，性能提供帮助。