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

时间:2024-10-25 23:06:20

前言

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数据库》MySQL ERRORLOG,BINLOG,SLOWLOG日志详解

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

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

重要: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数据库》MySQL ERRORLOG,BINLOG,SLOWLOG日志详解

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

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

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

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

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

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

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

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

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

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

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

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;

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

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

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

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

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开启

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

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

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

binlog日志中也记录了GTID。

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

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

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

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

/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数据库》MySQL ERRORLOG,BINLOG,SLOWLOG日志详解

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

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

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

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

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

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

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

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

当你日志庞大的时候,你会发现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的数据安全,性能提供帮助。