前言
日志文件记录了影响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