Linux之MySQL日志

前言

数据库就像一个庞大的图书馆，而日志则是记录这个图书馆内每一本书的目录。正如在图书馆中找到特定书籍一样，数据库日志帮助我们追溯数据的变更、定位问题和还原状态。

在MySQL中，日志是非常重要的一个组成部分，它记录了数据库运行状态的各种信息，包括错误信息、查询信息、事务信息等等，是进行异常排查、性能优化、数据恢复和备份的关键基础。

MySQL日志分类及介绍

MySQL中的日志主要分为两种类型：Server层和引擎层的日志（本次课程主要介绍Server层日志）。

Server层日志简介

用于记录MySQL Server层的各种操作和异常情况

• Error Log （错误日志） ：记录MySQL服务的启动、运行或停止过程中出现的问题和错误信息。用于了解服务器的状态和故障排除。
• Binary Log （二进制日志）：记录对MySQL数据库执行的更改操作，包括语句的发生时间、执行时长等信息，主要用于数据库恢复和主从复制。
• Relay Log （ 中继日志）：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的中间文件，从服务器通过读取中继日志的内容来同步主服务器上的操作。
• **DDL Log **：记录数据定义语句执行的元数据操作。
• General Log（查询日志） ：记录所有连接到数据库的客户端发送给数据库服务器的指令，包括连接和断开连接信息、执行的查询和SQL语句等。
• Slow Query Log （慢查询日志）：记录执行时间超过阈值的SQL查询语句，用于识别性能瓶颈、定位慢查询并进行优化。

InnoDB层日志简介

• 重做日志（redo log）：InnoDB使用重做日志来确保事务的持久性。当事务提交时，InnoDB会将该事务对数据库的修改记录到重做日志中。在数据库发生崩溃或意外关闭时，InnoDB可以通过重做日志来恢复未提交的事务，以保持数据的一致性。

• 回滚日志（undo log）：InnoDB使用回滚日志来实现事务的原子性和隔离性。当事务执行时，InnoDB会为该事务创建一个回滚日志，记录事务执行前的数据状态。如果事务需要回滚，InnoDB可以使用回滚日志将数据恢复到事务执行前的状态。此外，回滚日志还用于支持多版本并发控制（MVCC），使不同事务可以同时读取和修改相同的数据而不会相互干扰。

Server层日志

错误日志

错误日志是MySQL中最重要的日志之一，它记录了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

该日志是默认开启的，默认存放目录/var/log/，默认的日志文件名为mysqld.log。查看日志位置：

show variables like '%log_error%'

切换至root用户，然后编辑/var/lib/mysql/auto.cnf。随机修改server_uuid信息，然后再重启mysqld服务信息，最后通过查看错误日志信息。

sudo vim /var/lib/mysql/auto.cnf
# 修改成功之后，退出并保存
# 重启mysql服务
sudo systemctl restart mysql
设置拥有者为mysql
sudo chown mysql auto.cnf

注意：修改之前，先保存好原有的server_uuid，防止忘记。

重启mysql服务之后，通过查看错误信息后发现是修改了server_uuid导致服务无法正常启动。

# 查看mysql日志
sudo tail -f /var/log/mysql/error.sql

二进制日志

• 介绍

二进制日志（BINLOG）记录了所有的DDL（数据定义语言）和DML（数据操作语言）语句，但不包括数据查询（SELECT、SHOW）语句。

作用：1）灾难时的数据恢复；2）MySQL的主从复制。在MySQL8版本中，默认二进制是开启的；在MySQL5.7版本中需要手动开启。

查看命令如下：

show variables like '%log_bin%';

修改/etc/mysql/mysql.conf.d/mysqld.cnf，开启log_bin二进制日志功能。在[mysqld]配置中添加以下信息：

[mysqld]
...
# 开启log_bin
log_bin=mysql_bin
# 设置唯一id
server-id=1
# 设置log_bin的日志格式（默认ROW）
binlog_format=ROW

最后，保存并退出，重启mysql服务。再次登陆mysql查看修改情况：

# 重启mysql服务
sudo systemctl restart mysql
# 再次登陆mysql
mysql -u root -p
# 输入root密码

主要参数介绍

参数	说明
log_bin	是否开启log_bin日志，NO表示开启，OFF表示关闭
log_bin_basename	binlog日志的基本文件名，产生的bin-log文件名即为bin-log.*
log_bin_index	binlog文件的索引文件，管理所有binlog文件

切换到/var/lib/mysql目录下，查看二进制日志文件。（注意：请使用root账号切换目录）

# 切换root账号
su root
# 切换目录
cd /var/lib/mysql
# 查看目录下的文件信息
ll

• 日志格式

MySQL服务器中提供了多种格式来记录二进制日志，具体格式及特点如下：

日志格式	说明
STATEMENT	基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中。
ROW	基于行的日志记录，记录的是每一行的数据变更。（默认）
MIXED	混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录。

查看binlog的日志格式命令如下：

show variables like '%binlog_format%';

创建一个数据库db1，并在数据库下创建t_score成绩表。

mysql> create database db1;
mysql> use db1;
mysql> create table t_score(id int primary key auto_increment,name varchar(20) not null,course varchar(20) not null,score float default 0);
mysql> insert into t_score(name,course,score) values('张三','语文',99),('张三','数学',98),('里斯','语文',90),('里斯','数学',87);
mysql> commit;

• 日志查看

由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具mysqlbinlog来查看，具体语法：

mysqldbinlog [参数选项] logfilename

参数选项说明：

参数	说明
-d	指定数据库名称，只列出指定的数据库相关操作
-o	忽略点日志中的前n行命令
-v	将行事件(数据变更)重构为SQL语句
-w	将行事件(数据变更)重构为SQL语句，并输出注释信息

示例1：查看上一步骤的操作日志

切换到/var/lib/mysql目录，执行以下命令，查看执行日志：

# 切换目录
cd /var/lib/mysql
# 通过mysqlbinlog命令查看日志信息（看不到有效信息）
mysqlbinlog mysql_bin.000001
# 通过配置指定参数再次查看（可以清晰的看到SQL语句）
mysqlbinlog -v mysql_bin.000001

示例2：更换日志格式为STATEMENT

修改/etc/mysql/mysql.conf.d/mysqld.cnf文件。

# 编辑mysql配置文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf

将[mysqld]配置中binlog_format=ROW修改成binlog_format=STATEMENT，其他配置不变。

[mysqld]
...
# 设置log_bin的日志格式（默认ROW）
binlog_format=STATEMENT

修改完成之后，重启mysql服务。（重要！！！）

这时可以在/var/lib/mysql目录下看到一个新的二进制日志文件。

注意：这里因为修改了日志格式，重启mysql服务之后，将重新产生一个全新的二进制日志文件，如图上所示mysql-bin.000002。

基于日志格式STATEMENT，修改t_score表中所有人的成绩后，再次查看日志情况。

mysql> update t_score set score=score+1;
mysql> commit;# 切换目录
cd /var/lib/mysql
# 查看日志
mysqlbinlog -v mysql-bin.000002

• 日志删除

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：

指令	说明
reset master	删除全部binlog日志，删除之后，日志编号将从binlog.000001重新开发
purge master logs to 'binlog.******************'	删除 ****************** 编号之前的所有日志
purge master logs before 'yyyy-mm-dd hh24:mi:ss'	删除日志为 “yyyy-mm-dd hh24:mi:ss”之前产生的日志

示例1：删除指定编号之前的所有日志

mysql> purge master logs to 'mysql-bin.000002'

示例2：删除全部日志

mysql> reset master

mysql8.0以下版本查看当前数据库日志binlog保存时效 以天为单位，默认0永不过期，最多只能设置99天。

show variables like '%expire_logs_days%';

mysql8.0以上版本通过设置全局参数binlog_expire_logs_seconds修改binlog保存时间，以秒为单位；默认30天。

show variables like '%binlog_expire_logs_seconds%'

SHOW VARIABLES LIKE 'log_bin';

示例2：数据备份和恢复

数据备份

mysqldump -u root -p db1 t_score >    backup_t_score.sqlroot:链接名
db1:数据库名
t_score:表名
backup_t_score.sql:备份文件名

查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况下，查询日志是未开启的。

show variables like '%general%';

如果需要开启查询日志，修改/etc/mysql/mysql.conf.d/mysqld.cnf文件。可以设置以下配置：

[mysqld]
# 该选项用来开启查询日志，可选值：0或1，0代表关闭，1代表开启
general_log=1
# 设置日志的文件名，如果没有指定，默认的文件名为host_name.log
general_log_file=mysql_query.log

修改完毕之后，需要重启mysql服务。

使用tail命令实时查看mysql_query.log日志文件信息。

tail -f mysql_query.log

然后再登录到mysql，执行以下操作后，在切换到mysql_query.log日志文件中查看信息。

mysql> show databases;
mysql> use db1;
mysql> show tables;
mysql> select * from t_score;
mysql> select * from t_score where id = 1;
mysql> update t_score set score = score - 1;
....

慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志，默认未开启。long_query_time默认为10秒，最小为0，精度可以到微秒。

show variables like '%slow_query%';

如果需要开启查询日志，修改/etc/mysql/mysql.conf.d/mysqld.cnf文件。可以设置以下配置：

[mysqld]
# 慢查询日志
slow_query_log=1
# 执行时间参数
long_query_time=2

修改完毕之后，需要重启mysql服务。

使用tail命令实时查看zking-slow.log日志文件信息。

tail -f zking-slow.log

然后再登录到mysql，切换至db1数据库并创建t_log表（参考资料db.txt）。将资料中t_log.sql上传至虚拟机并导入到数据库中。

# 上传t_log.sql
rz t_log.sql
# 导入数据
mysql -uroot -密码 数据库名 < t_log.sql

最后登录mysql，执行以下SQL语句并查看慢查询日志文件信息。

mysql> select * from t_log limit 120000,10;

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和更改此行为log_queries_not_using_indexes，如下所示：

[mysqld]
# 记录执行较慢的管理语句
log_slow_admin_statements=1
# 记录执行较慢的未使用索引的语句
log_queries_not_using_indexes=1