mysql log不起作用_《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之后再对应目录下可以看到如下日志:

24a9ced63ac6a7d79487940e65450e3b.png

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

9352bd45365fb6de8fa323cfa2273ca8.png

重要: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日志文件

55b3ca8716aa3f32fa8ac11d3c4fd1e3.png

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

d67c2ccf06acf2dc1764cd840021f519.png

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

163ac54c72df6dd3096262b82edaf785.png

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

1e248f8fc14abe7ca6cc2f46ce28ed93.png

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

bf47902f8534784874c70c78d7a05f36.png

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

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

d626c6e7ae999857087b99aed76ae481.png

4. 通过binlog 恢复数据

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

(0) drop database if existstestdb ;

(1) create databasetestdb charset utf8;

(2) usetestdb;

(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;

595b5ee66f5150f3a952c8e43fa23917.png

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

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

af9f1f019095cc61f8f25958743f8dc3.png

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

511bad6f22ccc1e3d3de300bd644d1ef.png

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

14683968ffd4c9ee13fea3c557c767d7.png

binlog日志中也记录了GTID。

91c00aaf326a0786bfd037b7945aa81d.png

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

0668a3a6011951974b433076e6be4ce0.png

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

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

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

(1) create databasegtiddb charset utf8;

(2) usegtiddb ;

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

94efc33650b05aff2b253ed8f78dc95b.png

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

31cc4977f31976aded25228e78c35d78.png

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

d829675ae31b1afe1e6c68eb3aa20da2.png

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

090b6701a269d4c3e5d37c36272c1e79.png

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

d11d3c209ab7001243ed507272a3ec47.png

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值