1 MySQL日志分类

MySQL 的日志分为两部分：

• Server层的日志，所有引擎共享
• Engine层日志，本文只说明 InnoDB 引擎日志

2 Server 层日志

2.1 错误日志

MySQL的error log用于记录错误信息的log，但error记录的不仅仅是错误信息，有关服务进程的错误信息也会被记录（critical级别）；如果mysqld进程发现某些表需要自动检查或者修复的话，也会抛出相关信息到该log。

配置方法：

1. 找到配置文件/etc/mysql/my.cnf 或者 /etc/mysql/conf.d/*.conf
2. 写入配置

   [mysqld_safe]
   log-error=/var/lib/mysql/mysql.err
   

   代码100分

3. 默认
   • 如果 –log-error 没有设置,默认输出则为console.
   • 如果 –log-error 设置了没有文件名的一个文件.默认值为host_name.err 在data 目录下.
   • 如果 –log-error 给了一个文件名.如果没有给后缀名则添加.err 后缀名.文件默认再DATA 目录下.除非使用了绝对路径进行指定.

查看当前配置：

代码100分
mysql> show variables like "log_error";
+---------------+-----------------------------------+
| Variable_name | Value                             |
+---------------+-----------------------------------+
| log_error     | /home/mysql-server/log/mysqld.log |
+---------------+-----------------------------------+
1 row in set (0.04 sec)

2.2 慢查询日志

当语句执行时间较长时，通过日志的方式进行记录，这种方式就是慢查询的日志。默认情况下，MySQL数据库并不启动慢查询日志，你需要手工将slow_query_log参数设为ON，然后启动。

影响慢查询的参数：

查看当前配置：

mysql> show variables where variable_name in ("slow_query_log", "long_query_time", "log_output", "slow_query_log_file", "log_queries_not_using_indexes");
+-------------------------------+--------------------------------------+
| Variable_name                 | Value                                |
+-------------------------------+--------------------------------------+
| log_output                    | FILE                                 |
| log_queries_not_using_indexes | OFF                                  |
| long_query_time               | 10.000000                            |
| slow_query_log                | OFF                                  |
| slow_query_log_file           | /var/lib/mysql/b9cdf147d7ea-slow.log |
+-------------------------------+--------------------------------------+
5 rows in set (0.01 sec)

配置方法：

1. 慢查询参数均可动态配置， 直接使用类似 set global slow_query_log = "on"; 即可。此方法在数据库重启后失效。
2. 永久配置可参考错误日志配置方案，在配置文件中增加/修改对应参数配置

2.3 查询日志

MySQL的查询日志记录了所有MySQL数据库请求的信息。无论这些请求是否得到了正确的执行。默认文件名为hostname.log。默认情况下MySQL查询日志是关闭的。生产环境，如果开启MySQL查询日志，对性能还是有蛮大的影响的。另外很多时候，MySQL慢查询日志基本可以定位那些出现性能问题的SQL，所以MySQL查询日志应用的场景其实不多，有点鸡肋的感觉。

查看当前配置：

代码100分
mysql> show variables like "%general_log%";
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /var/lib/mysql/DB-Server.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)

2.4 二进制日志 binlog

二进制日志记录了对数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，如果你还想记录SELECT和SHOW操作，那只能使用查询日志，而不是二进制日志了。此外，二进制还包括了执行数据库更改操作的时间和执行时间等信息。

二进制日志主要有以下两种作用：

• 恢复（recovery）。某些数据的恢复需要二进制日志，如当一个数据库全备文件恢复后，我们可以通过二进制日志进行point-in-time的恢复。
• 复制（replication）。其原理与恢复类似，通过复制和执行二进制日志使得一台远程的MySQL数据库（一般称为slave或者standby）与一台MySQL数据库（一般称为master或者primary）进行实时同步。

影响 binlog 的参数：

查看当前配置：

mysql> show variables where variable_name in 
("log_bin", "log_bin_basename", "log_bin_index", "bin_log_format", "sync_binlog", "max_bin_log_size", "expire_logs_days", "binlog_cache_size", "max_binlog_cache_size");
+-----------------------+-------------------------------------------+
| Variable_name         | Value                                     |
+-----------------------+-------------------------------------------+
| binlog_cache_size     | 32768                                     |
| expire_logs_days      | 97                                        |
| log_bin               | ON                                        |
| log_bin_basename      | /home/mysql-server/binlog/mysql-bin       |
| log_bin_index         | /home/mysql-server/binlog/mysql-bin.index |
| max_binlog_cache_size | 18446744073709547520                      |
| sync_binlog           | 1                                         |
+-----------------------+-------------------------------------------+
7 rows in set (0.04 sec)

3 InnoDB 引擎日志

3.1 重做日志 redo log

redo log 与 binlog 这两种日志有以下三点不同：

1. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的 是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件 写到一定大小后会切换到下一个，并不会覆盖以前的日志。

影响 redo log 的参数

查看当前配置：

mysql> show variables like "innodb_%log%";
+----------------------------------+------------+
| Variable_name                    | Value      |
+----------------------------------+------------+
| innodb_api_enable_binlog         | OFF        |
| innodb_flush_log_at_timeout      | 1          |
| innodb_flush_log_at_trx_commit   | 1          |
| innodb_log_buffer_size           | 16777216   |
| innodb_log_checksums             | ON         |
| innodb_log_compressed_pages      | ON         |
| innodb_log_file_size             | 50331648   |
| innodb_log_files_in_group        | 2          |
| innodb_log_group_home_dir        | ./         |
| innodb_log_write_ahead_size      | 8192       |
| innodb_max_undo_log_size         | 1073741824 |
| innodb_online_alter_log_max_size | 134217728  |
| innodb_redo_log_encrypt          | OFF        |
| innodb_undo_log_encrypt          | OFF        |
| innodb_undo_log_truncate         | ON         |
+----------------------------------+------------+
15 rows in set (0.01 sec)

redo log 写入过程：

3.1.1 binlog 与 redo log 的两阶段提交

打开binlog选项后，执行事务提交命令时，就会进入两阶段提交模式。日志写入时机与前文介绍过的innodb_flush_log_at_trx_commit和sync_binlog 参数配置有关，“双1模式”的过程如下：

• prepare 阶段（写 redo log）
  1. 设置 undo state=TRX_UNDO_PREPARED
  2. 刷事务更新产生的 redo log；【步骤1产生的 redo log 也会刷入】
• commit 阶段（写 binlog）
  1. 将事务产生的 binlog 写入文件，刷入磁盘；
  2. 设置 undo页 的状态，置为 TRX_UNDO_TO_FREE 或 TRX_UNDO_TO_PURGE；
  3. 记录事务对应的 binlog 偏移，写入系统表空间;

mysqld 可能在任何情况下crash，os也有可能出现问题，另外若机器掉电，mysqld也会同样挂掉。但是即使这样，mysql仍然能保证数据库的一致性。接下来，结合上述流程，分析二阶段提交如何保证这点的。下面给出几种常见的场景：

1. prepare阶段，redo log落盘前，mysqld crash 由于redo没有落盘，毫无疑问，事务的更新肯定没有写入磁盘，数据库的一致性不受影响
2. prepare阶段，redo log落盘后，binlog落盘前，mysqld crash 这时候redo log写入完成，但binlog还未写入，事务处于TRX_STATE_PREPARED状态。服务恢复后，检测到 binlog 未写入，执行回滚
3. commit阶段，binlog落盘后，mysqld crash redo log和binlog都已经落盘，只是undo状态没有更新。服务恢复后，检测到log均写入，继续执行提交