1 MySQL日志分类
MySQL 的日志分为两部分：
- Server层的日志，所有引擎共享
- Engine层日志，本文只说明 InnoDB 引擎日志
2 Server 层日志
2.1 错误日志
MySQL的error log用于记录错误信息的log，但error记录的不仅仅是错误信息，有关服务进程的错误信息也会被记录（critical级别）；如果mysqld进程发现某些表需要自动检查或者修复的话，也会抛出相关信息到该log。
配置方法：
- 找到配置文件/etc/mysql/my.cnf 或者 /etc/mysql/conf.d/*.conf
- 写入配置
[mysqld_safe] log-error=/var/lib/mysql/mysql.err - 默认
- 如果 --log-error 没有设置,默认输出则为console.
- 如果 --log-error 设置了没有文件名的一个文件.默认值为host_name.err 在data 目录下.
- 如果 --log-error 给了一个文件名.如果没有给后缀名则添加.err 后缀名.文件默认再DATA 目录下.除非使用了绝对路径进行指定.
查看当前配置：
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，然后启动。
影响慢查询的参数：
| 参数名称 | 说明 |
|---|---|
| slow_query_log | 慢查询开关，OFF / ON |
| long_query_time | 超过多少秒的查询写入日志，单位：秒 |
| log_output | 存储方式，file: 文件 / table: 表（mysql.slow_log) / none：不存储 |
| slow_query_log_file | 慢查询日志文件路径 |
| log_queries_not_using_indexes | 如果值设置为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)
配置方法：
- 慢查询参数均可动态配置， 直接使用类似
set global slow_query_log = "on";即可。此方法在数据库重启后失效。 - 永久配置可参考错误日志配置方案，在配置文件中增加/修改对应参数配置
2.3 查询日志
MySQL的查询日志记录了所有MySQL数据库请求的信息。无论这些请求是否得到了正确的执行。默认文件名为hostname.log。默认情况下MySQL查询日志是关闭的。生产环境，如果开启MySQL查询日志，对性能还是有蛮大的影响的。另外很多时候，MySQL慢查询日志基本可以定位那些出现性能问题的SQL，所以MySQL查询日志应用的场景其实不多，有点鸡肋的感觉。
查看当前配置：
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 的参数：
| 参数名称 | 说明 |
|---|---|
| log_bin | 二进制日志开关 OFF / ON |
| log_bin_basename | 主文件名称 |
| log_bin_index | 轮询文件名称 |
| bin_log_format | 日志模式：statement(基于语句) / row(基于行) / mixed(混合) |
| sync_binlog | 每写缓冲多少次就同步到磁盘。如果设为1，表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志，如果是事务性的引擎，本身就是为了保证事物安全的，没理由不把sync_binlog 设置为1 |
| max_bin_log_size | 单个日志文件最大值(字节)，超过该值，则产生新日志文件，后缀名+1，并记录到.index文件 |
| expire_logs_days | 二进制日志滚动之后会生成新的文件来存储日志，日志文件逾期之后会自动删除，否则会产生源源不断的日志文件 |
| binlog_cache_size | 二进制日志的缓存，当事物开始的时候，会按照binlog_cache_size系统变量指定的值分配内容空间，如果指定的binlog_cache_size缓存空间不够则会报错并回滚事物，单位：字节，默认：32K。基于会话，当一个线程开始一个事务时，MySQL会自动分配一个大小为binlog_cache_size的缓存 |
| max_binlog_cache_size | 实例级别的二进制日志缓存，如果并发量很大，就需要考虑将max_binlog_cache_size设置的稍微大一些 |
查看当前配置：
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 这两种日志有以下三点不同：
- redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
- redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的 是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
- redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件 写到一定大小后会切换到下一个，并不会覆盖以前的日志。
影响 redo log 的参数
| 参数名称 | 说明 |
|---|---|
| innodb_log_files_in_group | 一组 redo log 有几个文件 |
| innodb_log_file_size | 组内每个文件的大小（字节） |
| innodb_log_group_home_dir | 文件的路径，默认在 data 目录下 |
| innodb_log_buffer_size | 日志内存缓冲大小，事务提交前记录在这里 |
| innodb_flush_log_at_trx_commit | 将buffer刷入log文件的时机， 0：事务提交时不会写buffer到os buffer，而是每秒刷buffer到os buffer并调fsync刷到磁盘中，当系统崩溃，会丢失1秒钟的数据。 1：事务提交时写buffer到os buffer同时调fsync刷到磁盘，系统崩溃不会丢数据，但每次刷磁盘，IO性能较差。 2：事务提交时写buffer到os buffer，然后每秒调用fsync刷磁盘。 |
查看当前配置：
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）
- 设置 undo state=TRX_UNDO_PREPARED
- 刷事务更新产生的 redo log；【步骤1产生的 redo log 也会刷入】
- commit 阶段（写 binlog）
- 将事务产生的 binlog 写入文件，刷入磁盘；
- 设置 undo页 的状态，置为 TRX_UNDO_TO_FREE 或 TRX_UNDO_TO_PURGE；
- 记录事务对应的 binlog 偏移，写入系统表空间;
mysqld 可能在任何情况下crash，os也有可能出现问题，另外若机器掉电，mysqld也会同样挂掉。但是即使这样，mysql仍然能保证数据库的一致性。接下来，结合上述流程，分析二阶段提交如何保证这点的。下面给出几种常见的场景：
- prepare阶段，redo log落盘前，mysqld crash 由于redo没有落盘，毫无疑问，事务的更新肯定没有写入磁盘，数据库的一致性不受影响
- prepare阶段，redo log落盘后，binlog落盘前，mysqld crash 这时候redo log写入完成，但binlog还未写入，事务处于TRX_STATE_PREPARED状态。服务恢复后，检测到 binlog 未写入，执行回滚
- commit阶段，binlog落盘后，mysqld crash redo log和binlog都已经落盘，只是undo状态没有更新。服务恢复后，检测到log均写入，继续执行提交
