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
```
2.2、查看错误日志文件存储位置
```
show variables like 'log_error%';
```
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
```
在存储二进制日志文件的文件夹内，一般包含二进制日志索引文件（.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
                指定日志显示条目的开始和结束位置
```
  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;
      - 找到删除表过程在对应二进制日志中的开始位置 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 */
      - 导入备份库数据重建库
        
        mysql -uroot -proot < books.sql
      - 获取对应二进制日志文件中pos1到pos2之间修改指定数据库的SQL语句并重新执行：
        
        mysqlbinlog --start-position=3361 --stop-position=3713 -d books mysql-bin.000002 | mysql -uroot -proot
        
        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
        
        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

日志 文件

相关推荐

