MySQL——MySQL日志查询_mysql 日志查看

1、MySQL数据库中常见的日志文件

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

2、错误日志

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

2.1、错误日志的简单配置

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

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

show variables like 'log_error%';
1

3、二进制日志

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

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

3.1、二进制日志的简单配置

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

# 设置binlog日志记录格式
binlog-format = STATEMENT
1
2
3
4
5
6

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

3.2、二进制日志记录格式

• STATEMENT：在此记录格式下，二进制日志文件中记录的是对数据库造成修改的逻辑SQL语句，通过mysqlbinlog工具看到的是修改行为对应的SQL语句；
• ROW ： 在此记录格式下，二进制日志文件中不再是记录简单的SQL语句，而是记录表的行更改情况，是目前（MySQL 5.7）默认的二进制日志文件的记录格式，由于记录的是表中行记录的更改，当进行批量修改时，相比于STATEMENT需要占用的存储空间要大得多；
• MIXED：在此记录格式下，MySQL一般会采用STATEMENT格式进行二进制文件的记录，但是在一些特殊情况下会使用ROW格式进行记录。

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

3.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
2
3
4
5
6
7
8
9
10
11

3.4、二进制日志的删除

• 方法一： 在MySQL客户端直接执行reset master命令，直接删除所有的 binlog 日志，并且日志编号从000001重新开始；
• 方法二： 在MySQL客户端执行PURGE MASTER LOGS TO 'mysql-bin.xxxxxx'，此命令将删除指定编号之前的所有二进制日志，需要指定二进制日志文件名；
• 方法三： 在MySQL客户端执行命令PURGE BINARY LOGS BEFORE 'YYYY-MM-DD hh:mm:ss';，此命令将删除指定日期之前的所有二进制日志；
• 方法四： 设置binlog的自动过期天数，日志存放指定时间后将被自动删除，可以通过在MySQL配置文件中设置expire-logs-days参数，或者直接在MySQL客户端命令行中设置全局变量expire_logs_days来开启自动日志清除。

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

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

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

恢复步骤

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

  • 找出备份过程在对应二进制日志中的结束位置**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;
    1
    2
    3
    4

  • 找到删除表过程在对应二进制日志中的开始位置 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 */
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

• 导入备份库数据重建库

  mysql -uroot -proot < books.sql
  1

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

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

4、慢查询日志

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

慢查询日志的简单配置

# 同时设置查询日志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
1
2
3
4
5
6
7
8

5、查询日志的简单配置

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

查询日志的简单配置

# 同时设置查询日志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
1
2
3
4
5
6