MySQL之常见的日志文件

前言

日志文件记录了影响MySQL数据库的各种类型活动，这些日志文件可以帮助DBA对MySQL数据库的运行情况进行诊断，从而更好地进行数据库层面的优化。MySQL数据库中常见的日志文件有：

• 错误日志（error log）
• 二进制日志（binlog）
• 慢查询日志（slow query log）
• 查询日志（general log）

---

1. 错误日志

错误日志（error log）对MySQL的启动、运行、关闭过程进行了记录，主要作用是帮助MySQL DBA在遇到问题时快速定位问题。该文件不仅记录了所有的错误信息（ERROR），也记录了一些警告信息（Warning）以及普通信息（Note）。错误日志默认是开启的，默认存放路径为datadir，默认的日志文件名为hostname.err（hostname为主机名），且错误日志是可以直接阅读的日志，建议开启。

1.1 错误日志的简单配置

# 设置错误日志log-err存储路径及文件名前缀
log-error = /usr/local/mysql/logs/mysql-error

1.2 查看错误日志文件存储位置

show variables like 'log_error%';

示例：

mysql> show variables like 'log_error%';
+---------------------+---------------------------------------+
| Variable_name       | Value                                 |
+---------------------+---------------------------------------+
| log_error           | /usr/local/mysql/logs/mysql-error.err |
| log_error_verbosity | 3                                     |
+---------------------+---------------------------------------+
2 rows in set (0.00 sec)

mysql> 

---

2. 二进制日志

二进制日志（binlog）记录了对MySQL数据库执行更改的所有操作，但是不包括select和show语句，其中包括了所有的DDL（数据定义语言）和DML（数据操作语言），如create、alter、drop、truncate以及insert、delete、update等，主要作用是进行数据恢复和主从复制。

二进制日志默认为关闭状态，需要在MySQL配置文件中进行开启，并设置二进制日志文件的日志记录形式。开启二进制日志对于MySQL整体性能会有一定的影响，大于为1%，但是考虑到二进制日志提供的功能，这种性能损失是完全可以接受的，建议开启。

2.1 二进制日志的简单配置

# 设置二进制日志log-bin存储路径及文件名前缀
# 若不指定具体路径,则是存放在datadir路径下
log-bin = /usr/local/mysql/logs/mysql-bin

# 设置binlog日志记录格式
binlog-format = STATEMENT

在存储二进制日志文件的文件夹内，一般包含二进制日志索引文件（.index）和二进制日志文件(.00000X)，其中二进制日志索引文件（.index）中记录的是所有二进制日志文件的文件名，是直接可阅读的，而二进制日志文件(.00000X)记录的则是二进制日志，不可以直接阅读，可以使用MySQL服务器命令行工具mysqlbinlog进行查看

2.2 二进制日志记录格式

• STATEMENT

在此记录格式下，二进制日志文件中记录的是对数据库造成修改的逻辑SQL语句，通过mysqlbinlog工具看到的是修改行为对应的SQL语句

• ROW

在此记录格式下，二进制日志文件中不再是记录简单的SQL语句，而是记录表的行更改情况，是目前（MySQL 5.7）默认的二进制日志文件的记录格式，由于记录的是表中行记录的更改，当进行批量修改时，相比于STATEMENT需要占用的存储空间要大得多

• MIXED

在此记录格式下，MySQL一般会采用STATEMENT格式进行二进制文件的记录，但是在一些特殊情况下会使用ROW格式进行记录

注意： 并不是所有的存储引擎都支持STATEMENT和ROW记录格式

2.3 二进制日志的查看

使用MySQL提供的服务器命令行工具mysqlbinlog可以通过多种方式查看和管理二进制日志文件binlog。在MySQL客户端命令行也可以使用对应命令查看二进制日志。

语法：

mysqlbinlog [options] log-files1 log-files2...

常用选项：

-d, --database=name				指定数据库名称
-o, --offset=n					设置查看的偏移量,即忽略前n行
-r, --result-file=name			将显示内容输出到指定文件
-s, --short-from				按照简单格式显示,省略部分信息
--start-datatime=detel --stop-datatime=date2
								指定日志显示条目的时间间隔
--start-position=pos1 --stop-position=pos2
								指定日志显示条目的开始和结束位置

示例1：

• 使用mysqlbinlog查看指定的binlog日志文件

mysqlbinlog mysql-bin.000001

• 如果binlog的记录形式为ROW，则可以通过-v参数或者-vv参数来显示对应的SQL语句，后者参数会显示更多的信息

mysqlbinlog -vv mysql-bin.000005

示例2：

• 在MySQL客户端命令行查看binlog日志文件列表

mysql> show binary logs\G;

• 查看指定binlog文件内容，并格式化输出

mysql> show binlog events in 'mysql-bin.000001'\G;

• 刷新二进制日志文件，即生成新的二进制日志文件，编号递增

mysql> flush logs;

2.4 二进制日志的删除

方法一： 在MySQL客户端直接执行reset master命令，直接删除所有的binlog日志，并且日志编号从000001重新开始

RESET MASTER

方法二： 在MySQL客户端执行PURGE MASTER LOGS TO 'mysql-bin.xxxxxx'，此命令将删除指定编号之前的所有二进制日志，需要指定二进制日志文件名，如：

PURGE MASTER LOGS TO 'mysql-bin.000005';

方法三： 在MySQL客户端执行命令PURGE BINARY LOGS BEFORE 'YYYY-MM-DD hh:mm:ss';，此命令将删除指定日期之前的所有二进制日志，如

PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';

方法四： 设置binlog的自动过期天数，日志存放指定时间后将被自动删除，可以通过在MySQL配置文件中设置expire-logs-days参数，或者直接在MySQL客户端命令行中设置全局变量expire_logs_days来开启自动日志清除

---

2.5 利用二进制日志的数据恢复

MySQL官方提供的mysqlbinlog 命令行工具不仅可以查看binlog日志文件，还可以基于binlog二进制日志文件对数据库中的数据进行恢复，实际上就是将对数据库进行修改的逻辑SQL语句再顺序执行一次。

进行数据恢复时必须要有对应数据库某个时间点的备份。例如：如果误删了某张表的数据，则可以先将数据库恢复最近的备份状态，然后从二进制文件中获取上一次备份的到删表之前的SQL语句重新执行，最终将数据库恢复到删表之前的状态。

2.5.1 数据恢复简单示例：

1）创建测试表并插入数据

DROP TABLE IF EXISTS `authors`;
CREATE TABLE `authors` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `nation` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `authors` VALUES (1,'村上春树','Japan'),(2,'鲁迅','China'),(3,'金庸','China'),(4,'古龙','China');

2）备份对应数据库

mysqldump -uroot -proot  --single-transaction --set-gtid-purged=OFF --master-data=2 --databases books > books.sql

3）修改表数据

mysql> use books;
mysql> DELETE FROM `authors` WHERE `id`=4;

4）删除测试表（误删）

mysql> DROP TABLE IF EXISTS `authors`;

5）获取删表前到数据库备份时二进制日志位置，以及删除表过程在二进制日志中的起始位置

①找出备份过程在对应二进制日志中的结束位置pos1：

cat books.sql | grep -i "CHANGE MASTER TO MASTER_LOG_FILE"

# 示例
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=3361;

即pos1=3361

②找到删除表过程在对应二进制日志中的开始位置pos2，通过less命令检索指定SQL语句定位pos：

mysql -uroot -proot -e "show binlog events in 'mysql-bin.000002'\G" | less -N

# 示例
*************************** 40. row ***************************
   Log_name: mysql-bin.000002
        Pos: 3713
 Event_type: Query
  Server_id: 1
End_log_pos: 3845
       Info: use `books`; DROP TABLE IF EXISTS `authors` /* generated by server */

即pos2=3713

6）导入备份库数据重建库

mysql -uroot -proot < books.sql

7）获取对应二进制日志文件中pos1到pos2之间修改指定数据库的SQL语句并重新执行：

mysqlbinlog --start-position=3361 --stop-position=3713 -d books mysql-bin.000002 | mysql -uroot -proot

---

3. 慢查询日志

慢查询日志（slow query log）能够记录执行时间超过某阈值的SQL语句，且只记录成功执行的SQL语句，可以帮助DBA定位可能存在问题的SQL语句，从而进行SQL语言层面的查询优化。慢查询日志默认文件名为主机名-slow.log，默认存放路径为datadir，默认为关闭状态，建议开启。

具体使用可以参考（MySQL之慢查询日志使用入门教程）

3.1 慢查询日志的简单配置

# 同时设置查询日志general log和慢查询日志slow query log输出方式(可选FILE/TABLE/NONE/FILE,TABLE)
log-output = FILE
# 设置启动慢查询日志slow-query-log
slow-query-log=1
# 设置慢查询日志slow-query-log存储路径及文件名
slow-query-log-file = /usr/local/mysql/logs/slow_query_log.log
# 设置慢查询阈值
long-query-time = 10

---

4. 查询日志

查询日志（general log）也叫作通用日志，可以直接阅读，其中记录了所有对MySQL数据库请求的信息以及对应执行的SQL语句，不论这些请求是否得到了正确的执行。此日志功能默认为关闭状态，不建议开启。

4.1 查询日志的简单配置

# 同时设置查询日志general log和慢查询日志slow query log输出方式(可选FILE/TABLE/NONE/FILE,TABLE)
log-output = FILE
# 设置开启通用日志general-log
general-log = 1
# 设置通用日志存储路径及文件名
general-log-file = /usr/local/mysql/logs/general.log

---

End~