MySQL的日志文件

官方文档：https://dev.mysql.com/doc/refman/5.7/en/server-logs.html

目录

1，日志刷新

2，错误日志

 3，全查询日志（通用查询日志）

4，慢查询日志

5，二进制日志

5.1，启用

5.2,文件组成

5.3，二进制日志文件相关参数

5.4，删除日志文件

5.5，查看二进制记录格式

5.6 恢复日志

---

文档持续更新中...

在MySQL中，主要有5种日志文件

• 错误日志(error log)：记录mysql服务的启停时的信息，还记录启动、停止、运行过程中的错误信息，事件调试器运行一个事件的信息
• 全查询日志(general log)：记录建立的客户端连接和执行的语句，也就是记录了所有对数据库请求的信息，不管请求是否正确的执行
• 二进制日志(bin log)：记录所有更改数据的语句，可用于数据复制
• 慢查询日志(slow log)：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询
• 中继日志(relay log)：主从复制时使用的日志，是从主服务器的二进制日志文件中复制而来的事件而保存的二进制日志文件

另外还有DDL日志。

1，日志刷新

mysql> FLUSH LOGS;
shell> mysqladmin flush-logs
shell> mysqladmin refresh

说明，刷新日志文件时会关闭旧的日志文件并重新打开日志文件。对于有些日志类型，如二进制日志，刷新日志会滚动日志文件，而不仅仅是关闭并重新打开

2，错误日志

再遇到问题时候，第一时间应该看错误日志，这个error日志类似于oracle的alert告警文件，默认情况以err结尾

如果在my.cnf中配置了日志文件路径log-error=/space/mysql/mysql-8.0.11-linux-glibc2.12-x86_64/data/mysql.err

使用如下命令可以在mysql启动之后查看日志文件位置：

mysql> show variables like 'log_error';
+---------------+-----------------------------------------------------------------+
| Variable_name | Value                                                           |
+---------------+-----------------------------------------------------------------+
| log_error     | /space/mysql/mysql/data/mysql.err |
+---------------+-----------------------------------------------------------------+

如果没有配置，默认以“hostname.err”存在datadir目录中，hostname代表当前主机名
与错误日志相关的还有一个参数：log_warnings,代表是否将告警信息记录进错误日志。
log_warnings 为0， 表示不记录告警信息
log_warnings 为1， 表示告警信息写入错误日志 一般默认为1
log_warnings 大于1， 表示各类告警信息，例如有关网络故障的信息和重新连接信息写入错误日志
如果想禁用告警日志写入，可以配置log_warnings 为0
从MySQL 5.7.2开始，推荐log_error_verbosity系统变量，而不是使用--log-warnings选项或log_warnings系统变量，这个参数从MySQL 8.0.3开始被移除
log_error_verbosity三个可选值 1，错误信息；2，错误信息和告警信息（一般默认）  3，错误信息、告警信息和通知信息
可以使用系统命令perror查看错误日志：

[root@centos7 bin]# perror 1006 
MySQL error code MY-001006 (ER_CANT_CREATE_DB): Can't create database '%-.192s' (errno: %d - %s)

清理错误日志用命令：flush logs
在旧版本MySQL，刷新日志操作(如flush logs)会备份旧的错误日志(以_old结尾)，并创建一个新的错误日志文件并打开，在MySQL 5.5.7之后，执行刷新日志的操作时，错误日志会关闭并重新打开，如果错误日志不存在，则会先创建
在MySQL正在运行状态下删除错误日志后，不会自动创建错误日志，只有在刷新日志的时候才会创建一个新的错误日志文件

 3，全查询日志（通用查询日志）

查询日志分为一般查询日志和慢查询日志，它们是通过查询是否超出变量 long_query_time 指定时间的值来判定的。在超时时间内完成的查询是一般查询，可以将其记录到一般查询日志中，超出时间的查询是慢查询，可以将其记录到慢查询日志中

全查询日志默认放在datadir变量指定的目录下，默认文件名为"主机名".log 默认不开启，需要开启时设置“set  global general_log=1”或者”set @@global.general_log=1“;同样刷新日志也会对此日志产生影响

相关日志变量：
long_query_time = 10 # 指定慢查询超时时长，超出此时长的属于慢查询，会记录到慢查询日志中
log_output={TABLE|FILE|NONE}  # 定义一般查询日志和慢查询日志的输出格式，不指定时默认为file
TABLE表示记录日志到表中，FILE表示记录日志到文件中，NONE表示不记录日志。只要这里指定为NONE，即使开启了一般查询日志和慢查询日志，也都不会有任何记录
和一般查询日志相关的变量有：
general_log=off  # 是否启用一般查询日志，为全局变量，必须在global上修改。
sql_log_off=off # 控制是否禁用将一般查询日志类信息记录进查询日志文件，默认为off，即启用
general_log_file=/space/mysql/mysql/data/centos7.log  # 默认是库文件路径下主机名加上.log

依次执行select 1； use db1；show tables；查看慢查询日志内容：

[root@centos7 data]# tail -1f centos7.log 
2019-03-09T06:14:15.565408Z         8 Query     select 1
2019-03-09T06:14:41.763379Z         8 Query     SELECT DATABASE()
2019-03-09T06:14:41.921474Z         8 Init DB   db1
2019-03-09T06:14:41.938736Z         8 Query     show databases
2019-03-09T06:14:41.996368Z         8 Query     show tables
2019-03-09T06:14:42.006181Z         8 Field List        t1 
2019-03-09T06:14:42.027486Z         8 Field List        t2 
2019-03-09T06:14:42.122935Z         8 Field List        t3 
2019-03-09T06:14:42.125944Z         8 Field List        tbl2 
2019-03-09T06:14:53.335713Z         8 Query     show tables

4，慢查询日志

查询超出变量 long_query_time（默认为10s） 指定时间值的为慢查询。但是查询获取锁(包括锁等待)的时间不计入查询时间内

mysql记录慢查询日志是在查询执行完毕且已经完全释放锁之后才记录的，因此慢查询日志记录的顺序和执行的SQL查询语句顺序可能会不一致

慢查询日志默认不启用，如果不是调优需要，不建议开启

和慢查询有关的变量：

• long_query_time=10    # 指定慢查询超时时长，超出此时长的属于慢查询
• log_output={TABLE|FILE|NONE} # 定义一般查询日志和慢查询日志的输出格式，默认为file
• log_slow_queries={yes|no}    # 是否启用慢查询日志，默认不启用
• slow_query_log={1|ON|0|OFF}  # 也是是否启用慢查询日志，此变量和log_slow_queries修改一个另一个同时变化
• slow_query_log_file=/mydata/data/hostname-slow.log  #默认路径为库文件目录下主机名加上-slow.log
• log_queries_not_using_indexes=OFF # 查询没有使用索引的时候是否也记入慢查询日志
• log_slow_admin_statements #记录慢的OPTIMIZE TABLE,ANALYZE TABLE 和ALTER TABLE的语句
• log_slow_slave_statements #记录由slave所产生的慢查询

常见的慢查询分析工具有，mysqldumpslow。还有mysqlsla和percona-toolkit中的等工具
mysqlsla是hackmysql.com推出的一款MySQL的日志分析工具，功能非常强大. 数据报表,非常有利于分析慢查询的原因, 包括执行频率, 数据量, 查询消耗等可以分析的日志类型为slow, general, binary等，比如分析slow日志：mysqlsla -lt slow mysql-slow.log
pt-query-digest是用于分析mysql慢查询的一个工具，它可以分析binlog、General、slowlog

mysqldumpslow简单用法:

mysqldumpslow --help              
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose#显示详细信息
  -d           debug 
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries #显示前多少条
  -a           don't abstract all numbers to N and strings to 'S' 使用N替换数字，S替换字符串
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string#通过grep筛选select语句
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

比如：

/mysqldumpslow -a centos7-slow.log 

Reading mysql slow query log from centos7-slow.log

Count: 1  Time=10.04s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[192.168.0.199]
  select sleep(10)

5，二进制日志

二进制日志是记录数据库变化的一个功能，不包括select和show类操作（如果delete没有引起变化也会记录），类似于oracle的归档日志，可以进行恢复操作；另外二进制日志还记录了执行数据库更改操作的时间信息；二进制语句以"事件"的形式保存，所以包含了时间、事件开始和结束位置等信息

二进制日志是以事件形式记录的，不是事务日志(但可能是基于事务来记录二进制日志)，不代表它只记录innodb日志，myisam表也一样有二进制日志；另外二进制日志只在事务提交的时候一次性写入

二进制日志可以通过“set  sql_log_bin=1”来开启，使用--log-bin[=[on|off|file_name]]来指定文件，如果文件为空，默认用hostname+"-bin.XXXX"，其中XXXX为一串数字。

mysqld还创建一个二进制日志索引文件，当二进制日志文件滚动的时候会向该文件中写入对应的信息。所以该文件包含所有使用的二进制日志文件的文件名。默认情况下该文件与二进制日志文件的文件名相同，扩展名为'.index'。要指定该文件的文件名使用 --log-bin-index[=file_name]选项

当重启mysql服务或刷新日志或者达到日志最大值时，将滚动二进制日志文件，滚动日志时只修改日志文件名的数字序列部分

二进制日志文件的最大值通过变量 max_binlog_size 设置(默认值为1G)。但由于二进制日志可能是基于事务来记录的(如innodb表类型)，而事务是绝对不可能也不应该跨文件记录的，如果正好二进制日志文件达到了最大值但事务还没有提交则不会滚动日志，而是继续增大日志，所以 max_binlog_size 指定的值和实际的二进制日志大小不一定相等

因为二进制日志文件增长迅速，但官方说明因此而损耗的性能小于1%，且二进制目的是为了恢复定点数据库和主从复制，所以出于安全和功能考虑，不建议将二进制日志和datadir放在同一磁盘上

二进制日志作用：

• 恢复（recovery），某些数据恢复需要二进制日志，在全库恢复之后，可以在此基础上使用二进制日志进行point-to-time的恢复
• 复制（replication），原理与恢复类似，通过复制和执行二进制日志，使得一台slave数据库与Master数据库进行同步

 

5.1，启用

查看是否启用

mysql> SHOW VARIABLES LIKE '%LOG_BIN%';
+---------------------------------+------------------------------------+
| Variable_name                   | Value                              |
+---------------------------------+------------------------------------+
| log_bin                         | ON  

否则，在配置文件/etc/my.cnf中配置：
log-bin=mysql-bin

另外可以配置log_bin_basename

5.2,查看文件组成

查看日志内容

• mysqlbinlog log_file
• 使用show

SHOW {BINARY | MASTER} LOGS      # 查看使用了哪些日志文件
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos]   # 查看日志中事件
SHOW MASTER STATUS         # 显式主服务器中的二进制日志信息

mysql> show master logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000024 |      4439 |
| binlog.000025 |       199 |
| binlog.000026 |       199 |
| binlog.000027 |       155 |
| binlog.000028 |       199 |
| binlog.000029 |       199 |
| binlog.000030 |       155 |
+---------------+-----------+
7 rows in set (0.00 sec)

mysql> show binlog events in 'binlog.000026';
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                              |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| binlog.000026 |   4 | Format_desc    |         1 |         124 | Server ver: 8.0.11, Binlog ver: 4 |
| binlog.000026 | 124 | Previous_gtids |         1 |         155 |                                   |
| binlog.000026 | 155 | Rotate         |         1 |         199 | binlog.000027;pos=4               |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
3 rows in set (0.09 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000030 |      155 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

说明：

• show master status 命令查看当前正在写入的是哪个二进制文件
• 当某条语句即将执行结束时候，将在二进制末位写入一条记录，同时通知语句解析器语句已经执行完毕
• show binlog events 默认显示第一个二进制日志，如果显示指定日志文件请使用show binlog events in ’bin-log.xxxx‘
• 二进制事件有很多，上面列出了三种格式描述事件，previous_gtids，日志轮换（rotate）事件
• 每个二进制日志文件末尾事件会记录下个日志文件的位置，比如上面的pos=4

5.3，二进制日志文件相关参数

mysql重新启动时候日志文件号递增生成，或者达到日志文件上限

• log-bin [=on | off | file_name]  # 启用binlog日志功能，并可定制路径名称，默认mysql-bin
• sql_log_bin ={ on | off } # 指定是否启用记录二进制日志，只有在log_bin开启的时候才有效，默认ON
• binlog_format= { mixed | row | statement }  # 二进制日志基于什么模式记录，默认为mixed
• expire_logs_days  binlog={0..99}日志过期天数，默认单位是天，如果打开二进制日志，推荐打开这个参数，否则磁盘空间满了都不清楚
• log_bin_index =  # 指定二进制索引文件的路径
• binlog_do_db = # 明确指定要记录日志的数据库
• binlog_ignore_db = # 指定不记录二进制日志的数据库
• max_binlog_size # 默认值是1GB，指定二进制日志文件最大值，超出指定值将自动滚动。但由于事务不会跨文件，所以并不一定总是精确
• binlog_cache_size = 32768 #基于事务类型的日志会先记录在缓冲区，当达到该缓冲大小时这些日志会写入磁盘
• max_binlog_cache_size=  # 指定二进制日志缓存最大大小，硬限制。默认4G，也即使用最大内存的数。
• binlog_cache_use：使用缓存写二进制日志的次数(这是一个实时变化的统计值)
• binlog_cache_disk_use:使用临时文件写二进制日志的次数，当日志超过了binlog_cache_size的时候会使用临时文件写日志，如果该变量值不为0，则考虑增大binlog_cache_size的值
• binlog_stmt_cache_size = 32768 #一般等同于且决定binlog_cache_size大小，所以修改缓存大小时只需修改这个而不用修改binlog_cache_size
• binlog_stmt_cache_use：使用缓存写二进制日志的次数
• binlog_stmt_cache_disk_use: 使用临时文件写二进制日志的次数，当日志超过了binlog_cache_size的时候会使用临时文件写日志，如果该变量值不为0，则考虑增大binlog_cache_size的值
• sync_binlog = { 0 | n } #这个参数直接影响mysql的性能和完整性

    sync_binlog=0:不同步，日志何时刷到磁盘由FileSystem决定，这个性能最好。
    sync_binlog=n:每写n次二进制日志事件(不是事务)，MySQL将执行一次磁盘同步指令fdatasync()将缓存日志刷新到磁盘日志文件中。Mysql中默认的设置是sync_binlog=0，即不同步，这时性能最好，但风险最大。一旦系统奔溃，缓存中的日志都会丢失

5.4，删除日志文件

•  reset master; #清空所有二进制日志，并让日志文件重新从000001开始，谨慎操作
• --expire_logs_days=N  #指定过了多少天日志自动过期清空
• PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr } #master和binary同义词

purge master|binary logs to 'mysql-bin.000014'; #删除截止到日志xxx之前的日志
purge master|binary logs before 'date';  # 删除指定时间前日志

5.5，二进制文件结构和记录格式

5.5.1 文件结构

二进制日志不是一个单独的文件，而是一系列易于管理文件组成，包括一组存储实际内容的二进制文件和一个用来跟踪二进制日志文件存储位置的二进制日志索引文件。其中一个二进制日志文件是活动的二进制日志文件

 

二进制文件都是以格式描述事件（format description event）开始，以日志轮换事件（rotate event）结束。格式描述事件包括产生该文件的服务器版本号，服务器以及二进制日志的信息等。轮换事件包含下个二进制日志文件的名称，已告知二进制日志继续写入哪个文件。每个二进制日志文件包含多个二进制日志事件，各个事件互相独立，也是构成二进制日志文件的基本单位。另外格式描述日志事件还有一个标记，标记二进制日志文件是否正常关闭。

 5.5.2 记录格式

MySQL支持statement、row、mixed三种形式的记录方式

• statement：表示二进制日志文件记录的是日志的逻辑SQL语句
• row ：记录形式是基于行来记录，也就是将相关行的每一列的值都在日志中保存下来，这样的结果会导致日志文件变得非常大，但是保证了动态值的确定性。此时可以将InnoDB的事务隔离级别设置为READ COMMITED，以获得更好的并发性
• mixed形式，表示如何记录日志由MySQL自己来决定，默认用statement格式进行二进制日志文件记录，但是在一些情况下会使用ROW格式，比如：

1）表的存储引擎为NDB，这是对于表的dml操作都会记录ROW格式

2）使用了now(),sysdate(),UUID(),USER(),current_user(),row_count(),found_rows()等不确定函数

3）使用了insert delay语句

4）使用了用户自定义函数

5）使用了临时表

语句：

mysql> alter table t2 add dt datetime default now();
Query OK, 1 row affected (0.27 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> reset master;
Query OK, 0 rows affected (0.03 sec)

mysql> show master logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       155 |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> insert into t2(id,name,dt) values(123,'CHINA',now());
Query OK, 1 row affected (0.01 sec)

mysql> show binlog events;
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| binlog.000001 |   4 | Format_desc    |         1 |         124 | Server ver: 8.0.11, Binlog ver: 4    |
| binlog.000001 | 124 | Previous_gtids |         1 |         155 |                                      |
| binlog.000001 | 155 | Anonymous_Gtid |         1 |         230 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 230 | Query          |         1 |         312 | BEGIN                                |
| binlog.000001 | 312 | Table_map      |         1 |         369 | table_id: 122 (db1.t2)               |
| binlog.000001 | 369 | Write_rows     |         1 |         420 | table_id: 122 flags: STMT_END_F      |
| binlog.000001 | 420 | Query          |         1 |         503 | COMMIT                               |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+

查看（其中-vv是格式化选项）：

[root@centos7 data]# ../bin/mysqlbinlog binlog.000001 -vv
...省略内容
# at 369
#190309 15:48:09 server id 1  end_log_pos 420 CRC32 0xcc8bd038  Write_rows: table id 122 flags: STMT_END_F

BINLOG '
uW+DXBMBAAAAOQAAAHEBAAAAAHoAAAAAAAEAA2RiMQACdDIAAwP+EgP+KAAHAQEAAgP8/wDlYboX
uW+DXB4BAAAAMwAAAKQBAAAAAHoAAAAAAAEAAgAD/wB7AAAABUNISU5BmaKS/Ak40IvM
'/*!*/;
### INSERT INTO `db1`.`t2`
### SET
###   @1=123 /* INT meta=0 nullable=1 is_null=0 */
###   @2='CHINA' /* STRING(40) meta=65064 nullable=1 is_null=0 */
###   @3='2019-03-09 15:48:09' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
# at 420
...省略内容

 

5.6 恢复日志

只需指定二进制日志的起始位置（可指定终止位置）并将其保存到sql文件中，由mysql命令来载入恢复即可。当然直接通过管道送给mysql命令也可。

至于是基于位置来恢复还是基于时间点来恢复，这两种行为都可以。选择时间点来恢复比较直观些，并且跨日志文件恢复时更方便

mysqlbinlog log_file | mysql -h -uXXX  -pXXX server_name 当然先恢复最开始的日志

比如：mysqlbinlog --stop-datetime="2019-3-09 15:27:48" mysql-bin.000001 | mysql -u user -p

或者多个二进制日志：mysqlbinlog mysql-bin.[*] | mysql -uroot -p password

或者单独导入文件恢复：

mysqlbinlog mysql-bin.000001 > /tmp/1.sql
mysqlbinlog mysql-bin.000002 >>/tmp/1.sql
mysql -uroot -ppassword -e "source /tmp/1.sql"

说明，跟ib_logfile区别：

ib_logfile0 记录系统的回滚，重做日志。
mysql-bin.000011 系统的所有更新记录。
如果需要更详细的则建议看一下数据库原理方面的教材，应该有一个章节讲这个redo,undo 日志的。 
,ib_logfile0是重做日志，记录的是文件的物理更改         
mysql-bin.000011是数据库更新日志  记录的是逻辑更改
ib_logfile0是重做日志，也就是 在你修改数据之前，会先把 修改的操作 作为日志先记录下来。
mysql-bin.000011是二进制日志，格式是二进制的，但是这个日志更加有用，比如 在我们做 数据库的主从复制时，这个二进制日志就是关键，mysql会把日志发送到slave，salve会接收日志，然后解析日志，把里面的sql语句重新应用到数据库里，于是就能同步数据了。
,ib_logfile0：记录的是redo log和undo log的信息，这里记录的基本是commit之前的数据。
mysql-bin.000011：记录的是已经执行完毕的对数据库的dml和ddl信息，这里记录的基本是commit之后的数据信息

另外修改ib_logfile0大小时候，确保innodb_fast_shutdown为1，这样在重启MySQL时，不会使用ib_logfile文件
参考：http://dev.mysql.com/doc/refman/5.7/en/binary-log.html