Question

在 MySQL 中，除了之前提到的 Redo 日志和 Undo 日志外，还有一些其他不同功能的日志。

• 慢查询日志 :记录所有执行时间超过 long_query_time 的查询。
• 通用查询日志 :记录所有链接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令。
• 错误日志 :记录 MySQL 服务的启动、运行、停止时遇到的各种错误。
• 二进制日志 :记录所有更改数据的语句，用于主从服务器之间的数据同步，以及服务器遇到故障后的恢复。
• 中继日志 :用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件（MySQL8.0 以后新增）。
• 数据定义语言日志 :记录数据定义语句执行的元数据操作（MySQL8.0 以后新增）。

除了二进制日志，其他日志都是文本文件。默认情况下，所有日志都创建在 MySQL 数据目录中。

慢查询日志

前面性能分析工具写过了，不再赘述。

通用查询日志

通用查询日志用来记录用户的所有操作，包括启动和关闭 MySQL 服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等。

由于通用查询日志记录的数据非常多，因此只建议在测试环境下开启。

查看当前通用查询日志的状态

mysql> show variables like '%general_log%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /var/lib/mysql/DB-Server.log |
+------------------+------------------------------+

• 参数 general_log 用来控制开启、关闭 MySQL 查询日志
• 参数 general_log_file 用来控制查询日志的位置

设置通用查询日志的存储方式

可以通过 log_output 设置通用查询日志的存储方式:

• FILE :表示日志存储在文件中。
• TABLE :表示日志存储在 mysql 库中的 general_log 表 中。
• FILE, TABLE :表示将日志同时存储在文件和 general_log 表 中，会徒增很多 IO 压力，一般不会这样设置。
• NONE :表示不记录日志，即使 general_log 设置为 ON，如果 log_output 设置为 NONE，也不会记录查询日志。

mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

注意: log_output 参数不止用于设置通用查询日志的存储方式，也同样会影响慢查询日志。

开启/关闭通用查询日志

• 方法一:修改 my.cnf 配置文件，需要重启 MySQL 服务，修改后永久生效。

  [mysqld]
  general_log = 1/0
  

• 方法二:命令行设置，重启后会失效。

  set global general_log = 1/0
  

错误日志

错误日志默认开启，并且无法被关闭。默认情况下，错误日志存储在数据库的数据文件目录中，名称为 hostname.err ，其中，hostname 为服务器主机名。

查看错误日志配置

mysql> show variables like 'log_err%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| log_error                  | ./fengye.err                           |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+

删除错误日志

MySQL5.5.7 之前，可以用 mysqladmin –u root –pflush-logs 命令，直接将旧文件重命名为 filename.err._old ，并创建新文件；从 MySQL5.5.7 开始，只能手动重命名后，开启一个新的错误日志文件:

[root@fengye data]# mv fengye404.err  fengye404.err._old
[root@fengye data]# mysqladmin flush-logs

二进制日志

MySQL 的二进制日志（binary log）是一个二进制文件，主要记录所有数据库表结构变更（例如 CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息。

binlog 的应用场景

• 数据恢复:如果 MySQL 服务意外停止，可以通过 binlog 来恢复。
• 数据复制:主数据库向从数据库复制数据。

binlog 和 redolog 的区别

1. redolog 由 InnoDB 产生；binlog 由 MySQL 数据库产生。
2. redolog 是物理日志，记录了”某个页上做了什么修改“；biglog 是逻辑日志，存储对应的 SQL 语句。
3. redolog 的空间是固定的，循环写入；binlog 空间没有限制，追加写入。
4. redolog 一般对于用户不可见，由存储引擎维护并保证数据库崩溃时事务的持久性；binlog 用于人工恢复数据。

查看 binlog 配置

mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name                   | Value                            |
+---------------------------------+----------------------------------+
| log_bin                         | ON                               |
| log_bin_basename                | /www/server/data/mysql-bin       |
| log_bin_index                   | /www/server/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                              |
| log_bin_use_v1_row_events       | OFF                              |
| sql_log_bin                     | ON                               |
+---------------------------------+----------------------------------+

• log_bin:是否开启 binlog，MySQL8 默认开启
• log_bin_basename:binlog 日志不止一个文件，由多个文件组成（每次 MySQL 重启都会创建一个新的 binlog）。这个参数表示 binlog 的基本文件名，每个文件后面都会追加标识来表示每个文件。
• log_bin_index:binlog 文件的索引文件。由于 binlog 可能包含很多文件，因此需要一个索引来管理。
• log_bin_trust_function_creators:是否可以创建存储过程。（具体参考: MySQL 参数 log_bin_trust_function_creators 介绍 - 潇湘隐者 - 博客园 (cnblogs.com) ）

修改 binlog 配置

• 方法一:修改 my.cnf 配置文件，需要重启 MySQL 服务，修改后永久生效。

  [mysqld]
  log-bin=fengye404-bin
  # binlog 的基础文件名，当带上路径时，也会同时指定文件存放的路径
  binlog_expire_logs_seconds=3600
  # binlog 文件的过期时间，单位是秒，超过时间后会删除，不设置默认 30 天
  max_binlog_size=100M
  # 单个 binlog 文件的大小，超过大小时会自动创建新的文件，不设置默认 1GB
  

• 方法二:命令行设置，重启后会失效（不支持 global，只支持 session）。

  mysql> SET sql_log_bin=0;
  

查看 binlog 列表

mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000014 |  21784592 | No        |
| mysql-bin.000015 |  11311449 | No        |
+------------------+-----------+-----------+

查看 binlog 内容

由于 binlog 是二进制文件，无法直接查看，需要借助 mysqlbinlog 命令工具

mysqlbinlog "/www/server/data/mysql/mysql-bin.000015"

除了使用 mysqlbinlog 工具，还可以使用下面这种更加方便的查询命令:

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

• IN 'log_name' :指定要查询的 binlog 文件名（不指定就是第一个 binlog 文件）　
• FROM pos :指定从哪个 pos 起始点开始查起（不指定就是从整个文件首个 pos 点开始算）
• LIMIT [offset] :偏移量（不指定就是 0)
• row_count :查询总条数（不指定就是所有行）

例子: show binlog events in "/www/server/data/mysql/mysql-bin.000015";

查看 binlog 格式

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+

• STATEMENT:记录每一条修改数据的 SQL 语句。

  优点:不需要记录每一行的变化，减少了 binlog 的日志量，节约 IO，提高性能。

• ROW:不记录 SQL 语句，而是记录那些行被修改。

  优点:清楚地记录每一行数据修改地细节，不会出现某些特定情况下存储过程、函数、trigger 的调用导致的无法正确复制的问题。

• MIXED:Statement 和 Row 的结合

使用 binlog 恢复数据

mysqlbinlog [option] <filename> | mysql –u [username] -p [password] -v [database];

• filename:文件完整路径
• option:可选参数。比较重要的两对 option 参数是--start-date、--stop-date 和 --start-position、-- stop-position。
  • --start-date 和 --stop-date :可以指定恢复数据库的起始时间点和结束时间点。
  • --start-position 和 --stop-position :可以指定恢复数据的开始位置和结束位置。
• username、password、database:用户名、密码、指定的数据库

注意:使用 binlog 恢复数据的同时，也相当于对数据库中的数据进行改动。因此恢复数据的操作也会被同时写入 binlog。因此在恢复之前最好使用 flush logs 重新开启一个新的 binlog 文件。

删除 binlog 文件

mysql>PURGE {MASTER | BINARY} LOGS TO '指定日志文件名';
# 删除指定文件之前的（不包括指定文件）文件
mysql>PURGE {MASTER | BINARY} LOGS BEFORE '指定日期';

中继日志

中继日志只在主从服务器架构的从服务器上存在 。从服务器为了与主服务器保持一致，要从主服务器读取 binlog 的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫 中继日志 。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。

文件名格式: 从服务器名 -relay-bin.序号 。中继日志也有一个索引文件 从服务器名 -relay-bin.index

中继日志的格式与二进制日志相同，也需要用 mysqlbinlog 查看