专栏名称: Linux就该这么学

专注于Linux运维技术培训，让您学习的每节课都有所收获，订阅本号后可每天获得最新Linux运维行业资讯、最实用的Linux免费教程以及独家Linux考证资料，三十多万技术小伙伴的选择，Linux就该这么学！

MySQL 日志管理：数据库运维的 “ 侦探工具 ”

Linux就该这么学 · 公众号 · linux · 2025-02-03 08:02

主要观点总结

文章主要介绍了MySQL的日志管理，包括错误日志、常规日志、二进制日志、慢查询日志等。文章详细描述了各种日志的作用、默认状态、路径、是否可以修改，以及如何修改日志路径等。此外，还包括了如何查看和使用这些日志，以及如何处理日志数据故障恢复。同时，也介绍了如何优化和删除binlog，以及慢查询日志的使用和分析。最后，提供了相关工具的下载链接和可视化界面的介绍。

关键观点总结

关键观点1: MySQL日志简介

描述了MySQL的几种主要日志类型和作用。

关键观点2: 日志路径和状态

详细说明了各种日志的默认状态、路径，以及是否可以修改。

关键观点3: 日志查看和使用

介绍了如何查看和使用各种日志，包括二进制日志和慢查询日志。

关键观点4: 日志数据故障恢复

描述了如何利用二进制日志进行故障恢复。

关键观点5: 优化和删除binlog

介绍了如何优化和删除binlog，包括根据生存时间删除日志，以及如何重置binlog。

关键观点6: 慢查询日志的使用和分析

详细说明了慢查询日志的作用、如何开启、如何设置阀值，以及如何分析和可视化慢查询日志。

正文

链接：https://www.cnblogs.com/wangchengww/p/16595659.html

MySQL日志管理

MySQL日志简介

错误日志

# 默认是开启：开启

# 默认路径及文件名：
	- 源码和二进制安装：datadir/$hostname.err
	- yum安装：/var/log/mysql.log
	
# 是否可以修改：可以
作用：查看MySQL启动时的报错找[Error]

# 修改日志路径
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
log_error=/tmp/err.log

mysql> show variables like 'log_error';

常规日志

# 默认是开启：否

# 默认路径及文件名：datadir/$hostname.err

# 是否可以修改：可以

# 作用：记录MySQL的常规操作

# 修改日志路径（一般来说不会开启）
[root@db02 world]# vim /etc/my.cnf
[mysqld]
general_log=1
general_log_file=/tmp/zls.log

二进制日志(binlog)

# 默认是否开启：否
root@localhost:(none)>show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

# 默认日志路径及文件名：datadir/xxx.000001

# 是否可以修改：可以

# 作用：
	1.记录已结束的DML事务语句，并拆分为多个事件(event)来进行记录
	2.记录所有DDL、DCL等语句
	3.总之，二进制日志会记录所有对数据库、表发生修改的操作
	
# 如何修改日志路径
# 以下修改方式为 mysql5.6
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
log-bin=/application/mysql/data/mysql-bin

[root@db02 ~]# vim /etc/my.cnf
[mysqld]
log-bin=/tmp/zls-bin

# MySQL5.7修改方式
MySQL想要开启binlog必须配置server_id
server_id=1
log-bin=mysql-bin

mysql-bin.index：MySQL二进制日志binlog的索引文件，有几个binlog就会记录几个binlog

二进制日志的工作模式

# statment：语句模式（MySQL5.6 默认的工作模式）
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

将所有的语句，记录binlog中
	优点：通俗易懂，占用磁盘空间小
	缺点：不严谨

# row: 行级模式（MySQL5.7的默认工作模式）
root@localhost:(none)>show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
将所有的语句及变化过程，记录在binlog中
	优点：严谨
	缺点：不易懂，占用磁盘空间大
	
# mixed：混合模式(了解)
语句模式和行级模式的混合模式
自己判断，什么时候只记录语句，什么时候记录语句和变化过程

工作模式如何修改

[root@db02 data]# vim /etc/my.cnf
[mysqld]
log-bin=/application/mysql/data/mysql-bin
binlog_format=row或者（statment）

如何查看二进制日志

# 查看行级模式
[root@db01 mysql]#  mysqlbinlog -vvv --base64-output=decode-row mysql_bin.000001

# 库内查看当前有几个binlog日记及大小
root@localhost:(none)>show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql_bin.000001 |      2479 |
+------------------+-----------+

# 库内查看binlog日志事件
root@localhost:(none)>show binlog events in 'mysql_bin.000001';

事件介绍

在binlog中最小的记录单元为event
一个事务会被拆分为多个事件（event）

事件的特性

每个event都有一个开始位置（start position）和结束位置（stop position）
所谓的位置就是event对整个二进制文件的相对位置
对一个二进制日志中，前120个position是文件格式信息预留空间

# MySQL5.6
在MySQL5.6中，一个新的binlog起始位置点事120,120是系统信息预留空间
其实120，就是该文件的大小
143是空的binlog日志，里面没有任何SQL语句

# MySQL5.7
在MySQL5.7中，一个新的binlog起始位置点是154，154是系统信息预留空间
其实154，就是该文件大小
177是空binlog，里面没有任何SQL语句执行

二进制日志数据故障恢复

#查看binlog信息
mysql> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 154 |
+------------------+----------+

# 创建binlog数据库
mysql[(none)]> create database binlog;

# 查看位置点
mysql> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 319 |
+------------------+----------+

# 创建一张表
mysql[(none)]> use binlog
mysql[binlog]> create table tb1(id int);

# 查看位置点
mysql[binlog]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 486 |
+------------------+----------+

# 插入数据
mysql[binlog]> insert into tb1 values(1),(2),(3);
mysql[binlog]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 486 |
+------------------+----------+
mysql[binlog]> commit;

# 查看表信息
mysql[binlog]> select * from tb1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+

# 修改数据
mysql[binlog]> update tb1 set id=10 where id=1;
mysql[binlog]> commit;
mysql[binlog]> select * from tb1;
+------+
| id |
+------+
| 10 |
| 2 |
| 3 |
+------+

# 查看位置信息
mysql[binlog]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 1018 |
+------------------+----------+
mysql[binlog]> delete from tb1 where id=2;
mysql[binlog]> commit;

# 查看位置点
mysql[binlog]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 1276 |
+------------------+----------+
mysql[binlog]> select * from tb1;
+------+
| id |
+------+
| 10 |
| 3 |
+------+

## 删除表
mysql[binlog]> drop table tb1;
mysql[binlog]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 1461 |
+------------------+----------+

## 删除库
mysql[binlog]> drop database binlog;
mysql[(none)]> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000004 | 1615 |
+------------------+----------+

如何恢复

# 1.查看binlog，找到起始位置和结束位置点
[root@db01 mysql]#  mysqlbinlog -vvv --base64-output=decode-row mysql_bin.000001
起始位置点：219
+------+
| id |
+------+
| 10 |
| 2 |
| 3 |
+------+
结束位置点：1018

# 2.截取binlog
mysqlbinlog --start-position=219 --stop-position=1018 mysql_bin.000001 > /tmp/binlog.sql

# 3.导入截取数据
[root@db04 data]# mysql 

# 4.查询数据
mysql[binlog]> show tables;
+------------------+
| Tables_in_binlog |
+------------------+
| tb1 |
+------------------+
1 row in set (0.00 sec)
mysql[binlog]> select * from tb1;
+------+
| id |
+------+
| 10 |
| 2 |
| 3 |
+------+

存在问题

如果误删除的库是10年前创建的

解决方案：
全量备份，配合binlog的增量部分

用户使用数据是穿插使用的，binlog也不是一个库一个库记录的

解决方案
只需要过滤出，被删除的数据库相关binlog中的SQL语句
使用-d指定数据库截取binlog
[root@db04 data]# mysqlbinlog -d zls2 -vvv --base64-output=decode-row mysql_bin.000001

刷新binlog

# 刷新binlog
1.重启数据库会自动刷新binlog
2.当binlog大小达到1G时，会自动刷新出下一个binlog
3.手动执行 flush logs;
4.使用mysqladmin flush-log
[root@db01 mysql]# mysqladmin -uroot -p'456' flush-log
5.使用MySQLdump做备份时，可以刷新binlog
[root@db02 data]# mysqldump -A -F > /tmp/full.sql

删除binlog

原则：
在存储能力范围内，能保留多少binlog就保留多少binlog

# 根据生存时间删除日志
# 临时生效
set global expire_logs_days = 7;

# 永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7

# 删除指定时间段binlog
purge binary logs before now() - interval 3 day;

# 指定binlog名字删除，之前binlog都删除
root@localhost:(none)>purge binary logs to 'mysql_bin.000002';

# 重置binlog，删除所有binlog
mysql> reset master;

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 120 |
+------------------+-----------+

慢日志(慢查询日志)

# 默认是否开启：否
root@localhost:(none)>show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+

# 默认路径文件名：datadir/￥hostname-slow.log

# 是否可以修改：可以

# 作用
	- 记录执行的比较慢的SQL语句
	
# 修改日志路径
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
# 指定是否开启慢查询日志
slow_query_log = 1
# 指定慢日志文件存放位置（默认在data）
slow_query_log_file=/application/mysql/data/slow.log
# 设定慢查询的阀值(默认10s)
long_query_time=0.05
# 不使用索引的SQL语句是否记录到慢查询日志
log_queries_not_using_indexes
------------------------------------------------------------
# 查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
min_examined_row_limit=1000（鸡肋）

查看慢日志查询

[root@db02 data]# mysqldumpslow
-s：指定如何排序
    c：按照记录次数
    t：按照时间排序
    r：按照返回记录排序
    l：按照查询时间排序
    
    ac：按照记录次数 倒序排序
    at：按照时间排序 倒序排序
    ar：按照返回记录 倒序排序
    al：按照查询时间 倒序排序
-t：top N
-g：指定正则表达式

[root@db02 data]# mysqldumpslow -s t -t 10 db02-slow.log

percona 慢查询工具

# percona下载地址
wget http://test.driverzeng.com/MySQL_Package/percona-toolkit-3.0.11-1.el6.x86_64.rpm

[root@db02 ~]# yum localinstall -y percona-toolkit-3.0.11-1.el6.x86_64.rpm
[root@db02 ~]# pt-query-digest /application/mysql/data/db02-slow.log

慢日志可视化界面

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

httpss://www.percona.com/downloads/percona-toolkit/LATEST/ 慢日志分析工具下载

httpss://github.com/box/Anemometer 可视化代码下载

END

官方站点：www.linuxprobe.com

Linux命令大全：www.linuxcool.com

刘遄老师QQ：5604215

Linux技术交流群：2636170

（新群，火热加群中……）

想要学习Linux系统的读者可以点击"阅读原文"按钮来了解书籍《Linux就该这么学》，同时也非常适合专业的运维人员阅读，成为辅助您工作的高价值工具书！