错误日志
默认情况下，错误日志是开启的，且无法被禁止。默认情况下，错误日志是存储在数据库的数据文件目录中，名称为 hostname.err，其中，hostname 为服务器主机名。
配置
为了方便管理，用户可以根据自己的需求来配置错误日志存储位置和日志级别，配置参数如下:
log_error = on | 文件路径是否启用错误日志，on 表示开启，文件路径表示指定自定义日志路径log_warnings = 1|0是否记录 warnings 信息到错误日志中
记录信息
- 服务器启动和关闭过程中的信息
未必是错误信息，比如 mysql 是如何去初始化存储引擎的过程记录在错误日志里等等
- 服务器运行过程中的错误信息
比如 sock 文件找不到，无法加载 mysql 数据库的数据文件，如果忘记初始化 mysql 或 data dir 路径找不到，或权限不正确等 都会记录在此
- 事件调度器运行一个事件时产生的信息
一旦 mysql 调度启动一个计划任务的时候，它也会将相关信息记录在错误日志中
- 在从服务器上启动从服务器进程时产生的信息
在复制环境下，从服务器进程的信息也会被记录进错误日志
删除错误日志
在 mysql5.5.7 之前：数据库管理员可以删除很长时间之前的错误日志，以保证 mysql 服务器上的硬盘空间。mysql 数据库中，可以使用 mysqladmin 命令开启新的错误日志。mysqladmin 命令的语法如下：
mysqladmin –u root –pflush-logs 也可以使用登录 mysql 数据库中使用 FLUSHLOGS 语句来开启新的错误日志。
在 mysql5.5.7 之后：服务器将关闭此项功能。只能使用重命名原来的错误日志文件，手动冲洗日志创建一个新的：方式如下：
[root@stu18 data]# mv stu18.magedu.com.err stu18.magedu.com.err.old
[root@stu18 data]# mysqladmin flush-logs
[root@stu18 data]# ls
hellodb myclass mysql-bin.000003 mysql-bin.index stu18.magedu.com.pid ibdata1 mysql mysql-bin.000004 performance_schema ib_logfile0 mysql-bin.000001 stu18.magedu.com.err test ib_logfile1 mysql-bin.000002 stu18.magedu.com.err.old
查询日志
查询日志在 MySQL 中被称为 general log (通用日志)，查询日志里的内容不要被 “查询日志” 误导，认为里面只存储 select 语句，其实不然，查询日志里面记录了数据库执行的所有命令，不管语句是否正确，都会被记录，具体原因如下:
- insert 查询为了避免数据冲突，如果此前插入过数据，当前插入的数据如果跟主键或唯一键的数据重复那肯定会报错
- update 时也会查询因为更新的时候很可能会更新某一块数据
- delete 查询，只删除符合条件的数据
因此都会产生日志，在并发操作非常多的场景下，查询信息会非常多，那么如果都记录下来会导致 IO 非常大，影响 MySQL 性能，因此如果不是在调试环境下，是不建议开启查询日志功能的。
查询日志的开启有助于帮助我们分析哪些语句执行密集，执行密集的 select 语句对应的数据是否能够被缓存，同时也可以帮助我们分析问题，所以，我们可以根据自己的实际情况来决定是否开启查询日志。
配置
- 参数 general_log 用来控制开启、关闭 MySQL 查询日志
- 参数 general_log_file 用来控制查询日志的位置
所以如果你要判断 MySQL 数据库是否开启了查询日志，可以使用下面命令。general_log 为 ON 表示开启查询日志，OFF 表示关闭查询日志。
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)
如果开启了查询日志，参数 log_output 控制着查询日志的存储方式， log_output 可以设置为以下 4 种值:
- FILE : 表示日志存储在文件中
- TABLE : 表示日志存储在 mysql 库中的 general_log 表中
- FILE, TABLE : 表示将日志同时存储在文件和 general_log 表中，改值会徒增很多 IO 压力，一般不会这样设置
- NONE : 表示不记录日志，即使 general_log 设置为 ON， 如果 log_output 设置为 NONE，也不会记录查询日志
log_output 不仅控制查询日志的输出，也控制着慢查询日志的输出，即: log_output 设置为 FILE，就表示查询日志和慢查询日志都存放在文件中，设置为 TABLE，查询日志和慢查询日志都存放在 mysql 库中的 general_log 表中
查看 log_output 设置:
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)
慢日志
慢查询会导致 CPU，IOPS，内存消耗过高。当数据库遇到性能瓶颈时，大部分时间都是由于慢查询导致的。 开启慢查询日志，可以让 MySQL 记录下查询超过指定时间的语句，之后运维人员通过定位分析，能够很好的优化数据库性能。
慢查询日志记录的慢查询不仅仅是执行比较慢的 SELECT 语句，还有 INSERT，DELETE，UPDATE，CALL 等 DML 操作，只要超过了指定时间，都可以称为 “慢查询”，被记录到慢查询日志中。
默认情况下，慢查询日志是不开启的，只有手动开启了，慢查询才会被记录到慢查询日志中。
配置
[mysqld]
# 开启慢日志
slow_query_log=1
# 慢查询指定时间设置，表示 "多长时间的查询" 被认定为 "慢查询"，单位是秒 (s)，默认是 10s，即超过 10s 的查询都被认定为慢查询。
long_query_time=20
# 表示如果运行的 SQL 语句没有使用到索引，是否也被当作慢查询语句记录到慢查询记录中，OFF 表示不记录，ON 表示记录。
log_queries_not_using_indexes=on
# 当使用文件存储慢查询日志时 (log_output 设置为 "FILE" 或者 "FILE,TABLE" 时)，制定慢查询日志存储在哪个文件中，默认的文件名是 "主机名 - slow.log"，存储目录为数据目录
slow_query_log_file=slow.log
# MySQL5.6.5 版本新引入的参数，用来限制没有使用索引的语句每分钟记录到慢查询日志中的次数。在生产环境中，有可能有很多没有使用索引的语句，可能会导致慢查询日志快速增长。
log_throttle_queries_not_using_indexes=100
慢查询日志分析工具
pt-query-digest 是分析 MySQL 查询日志最有力的工具，该工具功能强大，它可以分析 binlog，Generallog，slowlog，也可以通过 show processlist 或者通过 tcpdump 抓取的 MySQL 协议数据来进行分析，比 mysqldumpslow 更具体，更完善。以下是使用 pt-query-digest 的示例:
//直接分析慢查询文件
pt-query-digest slow.log > slow_report.log
该工具可以将查询的剖析报告打印出来，可以分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间，次数，占比等，可以借助分析结果找出问题进行优化。
事务日志
数据库数据存放的文件称为 data file；日志文件称为 log file；
数据库数据是有缓存的，如果没有缓存，每次都写或者读物理 disk，那性能就太低下了。
数据库数据的缓存称为 data buffer，日志（redo）缓存称为 log buffer；既然数据库数据有缓存，就很难保证缓存数据（脏数据）与磁盘数据的一致性。
但是万一数据库发生断电，因为缓存的数据没有写入磁盘，导致缓存在内存中的数据丢失而导致数据不一致怎么办？
为了保证事务的 ACID 特性，就不得不说 MySQL InnoDB 引擎的事务日志: 重做日志 redo 和回滚日志 undo
innodb 通过 force log at commit 机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的 redo log file 和 undo log file 中进行持久化。
注：在数据库的世界里，数据从来都不重要，日志才是最重要的，有了日志就有了一切
Redo 日志
Redo 日志简介
redo log 包括两部分：一是内存中的日志缓冲 (redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件 (redo log file)，该部分日志是持久的，并且是事务的记录是顺序追加的，性能非常高 (磁盘的顺序写性能逼内存的写性能差不了太多)
InnoDB 使用日志来减少提交事务时的开销。因为日志中已经记录了事务，就无须在每个事务提交时把缓冲池的脏块刷新 (flush) 到磁盘中。事务修改的数据和索引通常会映射到表空间的随机位置，所以刷新这些变更到磁盘需要很多随机 IO。InnoDB 假设使用常规磁盘，随机 IO 比顺序 IO 昂贵得多，因为一个 IO 请求需要时间把磁头移到正确的位置，然后等待磁盘上读出需要的部分，再转到开始位置。
InnoDB 用日志把随机 IO 变成顺序 IO。一旦日志安全写到磁盘，事务就持久化了，即使断电了，InnoDB 可以重放日志并且恢复已经提交的事务。
为了确保每次日志都能写入到事务日志文件中，在每次将 log buffer 中的日志写入日志文件的过程中都会调用一次操作系统的 fsync 操作 (即 fsync () 系统调用)。因为 MariaDB/MySQL 是工作在用户空间的，MariaDB/MySQL 的 log buffer 处于用户空间的内存中。要写入到磁盘上的 log file 中 (redo:ib_logfileN 文件，undo:share tablespace 或.ibd 文件)，中间还要经过操作系统内核空间的 os buffer，调用 fsync () 的作用就是将 OS buffer 中的日志刷到磁盘上的 log file 中。
也就是说，从 redo log buffer 写日志到磁盘的 redo log file 中，过程如下：
在此处需要注意一点，一般所说的 log file 并不是磁盘上的物理日志文件，而是操作系统缓存中的 log file，官方手册上的意思也是如此 (例如：With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second)。但说实话，这不太好理解，既然都称为 file 了，应该已经属于物理文件了。所以在本文后续内容中都以 os buffer 或者 file system buffer 来表示官方手册中所说的 Log file，然后 log file 则表示磁盘上的物理日志文件，即 log file on disk。另外，之所以要经过一层 os buffer，是因为 open 日志文件的时候，open 没有使用 O_DIRECT 标志位，该标志位意味着绕过操作系统层的 os buffer，IO 直写到底层存储设备。不使用该标志位意味着将日志进行缓冲，缓冲到了一定容量，或者显式 fsync () 才会将缓冲中的刷到存储设备。使用该标志位意味着每次都要发起系统调用。比如写 abcde，不使用 o_direct 将只发起一次系统调用，使用 o_object 将发起 5 次系统调用。
MySQL 支持用户自定义在 commit 时如何将 log buffer 中的日志刷 log file 中。这种控制通过变量 innodb_flush_log_at_trx_commit 的值来决定。该变量有 3 种值：0、1、2，默认为 1。但注意，这个变量只是控制 commit 动作是否刷新 log buffer 到磁盘。
- 当设置为 0 的时候，事务提交时不会将 log buffer 中日志写入到 os buffer，而是每秒写入 os buffer 并调用 fsync () 写入到 log file on disk 中。也就是说设置为 0 时是 (大约) 每秒刷新写入到磁盘中的，当系统崩溃，会丢失 1 秒钟的数据。
- 当设置为 1 的时候，事务每次提交都会将 log buffer 中的日志写入 os buffer 并调用 fsync () 刷到 log file on disk 中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO 的性能较差。
- 当设置为 2 的时候，每次提交都仅写入到 os buffer，然后是每秒调用 fsync () 将 os buffer 中的日志写入到 log file on disk。
在主从复制结构中，要保证事务的持久性和一致性，需要对日志相关变量设置为如下：
- 如果启用了二进制日志，则设置 sync_binlog=1，即每提交一次事务同步写到磁盘中。
- 总是设置 innodb_flush_log_at_trx_commit=1，即每提交一次事务都写到磁盘中。
上述两项变量的设置保证了：每次提交事务都写入二进制日志和事务日志，并在提交时将它们刷新到磁盘中。
Redo 日志参数
- innodb_log_files_in_group
redo log 文件的个数，命名方式如：ib_logfile0，iblogfile1… iblogfilen。默认 2 个，最大 100 个。
- innodb_log_file_size
文件设置大小，默认值为 48M，最大值为 512G，注意最大值指的是整个 redo log 系列文件之和，即（innodb_log_files_in_group * innodb_log_file_size ）不能大于最大值 512G。
- innodb_log_group_home_dir
文件存放路径
- innodb_log_buffer_size
Redo Log 缓存区，默认 8M，可设置 1-8M。延迟事务日志写入磁盘，把 redo log 放到该缓冲区，然后根据 innodb_flush_log_at_trx_commit 参数的设置，再把日志从 buffer 中 flush 到磁盘中。
- innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit=0，事务发生过程，日志一直激励在 redo log buffer 中，跟其他设置一样，但是在事务提交时，不产生 redo 写操作，而是 MySQL 内部每秒操作一次，从 redo log buffer，把数据写入到系统中去。如果发生 crash，即丢失 1s 内的事务修改操作
innodb_flush_log_at_trx_commit=1，每次 commit 都会把 redo log 从 redo log buffer 写入到 system，并 fsync 刷新到磁盘文件中。
innodb_flush_log_at_trx_commit=2，每次事务提交时 MySQL 会把日志从 redo log buffer 写入到 system，但只写入到 file system buffer，由系统内部来 fsync 到磁盘文件。如果数据库实例 crash，不会丢失 redo log，但是如果服务器 crash，由于 file system buffer 还来不及 fsync 到磁盘文件，所以会丢失这一部分的数据。
注意：由于进程调度策略问题，这个 “每秒执行一次 flush (刷到磁盘) 操作” 并不是保证 100% 的 “每秒”。
undo 日志
undo 日志简介
undo log 有两个作用：提供回滚和多个行版本控制 (MVCC)。
在数据修改的时候，不仅记录了 redo，还记录了相对应的 undo，如果因为某些原因导致事务失败或回滚了，可以借助该 undo 进行回滚。
undo log 和 redo log 记录物理日志不一样，它是逻辑日志。可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录。
当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过 undo log 来实现的：当读取的某一行被其他事务锁定时，它可以从 undo log 中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。
undo 日志存储方式
innodb 存储引擎对 undo 的管理采用段的方式。rollback segment 称为回滚段，每个回滚段中有 1024 个 undo log segment。
在以前老版本，只支持 1 个 rollback segment，这样就只能记录 1024 个 undo log segment。后来 MySQL5.5 可以支持 128 个 rollback segment，即支持 128*1024 个 undo 操作，还可以通过变量 innodb_undo_logs (5.6 版本以前该变量是 innodb_rollback_segments) 自定义多少个 rollback segment，默认值为 128。
undo log 默认存放在共享表空间中。
[root@xuexi data]# ll /mydata/data/ibda*
-rw-rw---- 1 mysql mysql 79691776 Mar 31 01:42 /mydata/data/ibdata1
如果开启了 innodb_file_per_table ，将放在每个表的.ibd 文件中。
在 MySQL5.6 中，undo 的存放位置还可以通过变量 innodb_undo_directory 来自定义存放目录，默认值为 “.” 表示 datadir。
默认 rollback segment 全部写在一个文件中，但可以通过设置变量 innodb_undo_tablespaces 平均分配到多少个文件中。该变量默认值为 0，即全部写入一个表空间文件。该变量为静态变量，只能在数据库示例停止状态下修改，如写入配置文件或启动时带上对应参数。但是 innodb 存储引擎在启动过程中提示，不建议修改为非 0 的值，如下：
2017-03-31 13:16:00 7f665bfab720 InnoDB: Expected to open 3 undo tablespaces but was able
2017-03-31 13:16:00 7f665bfab720 InnoDB: to find only 0 undo tablespaces.
2017-03-31 13:16:00 7f665bfab720 InnoDB: Set the innodb_undo_tablespaces parameter to the
2017-03-31 13:16:00 7f665bfab720 InnoDB: correct value and retry. Suggested value is 0
undo 日志参数
mysql> show global variables like '%undo%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory | ./ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 3 |
+--------------------------+------------+
mysql> show global variables like '%truncate%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_undo_log_truncate | OFF |
+--------------------------------------+-------+
- innodb_max_undo_log_size
控制最大 undo tablespace 文件的大小，当启动了 innodb_undo_log_truncate 时，undo tablespace 超过 innodb_max_undo_log_size 阀值时才会去尝试 truncate。该值默认大小为 1G，truncate 后的大小默认为 10M。
- innodb_undo_tablespaces
设置 undo 独立表空间个数，范围为 0-128， 默认为 0，0 表示表示不开启独立 undo 表空间 且 undo 日志存储在 ibdata 文件中。该参数只能在最开始初始化 MySQL 实例的时候指定，如果实例已创建，这个参数是不能变动的，如果在数据库配置文 件 .cnf 中指定 innodb_undo_tablespaces 的个数大于实例创建时的指定个数，则会启动失败，提示该参数设置有误。
- innodb_undo_log_truncate
InnoDB 的 purge 线程，根据 innodb_undo_log_truncate 设置开启或关闭、innodb_max_undo_log_size 的参数值，以及 truncate 的频率来进行空间回收和 undo file 的重新初始化。
该参数生效的前提是，已设置独立表空间且独立表空间个数大于等于 2 个。
purge 线程在 truncate undo log file 的过程中，需要检查该文件上是否还有活动事务，如果没有，需要把该 undo log file 标记为不可分配，这个时候，undo log 都会记录到其他文件上，所以至少需要 2 个独立表空间文件，才能进行 truncate 操作，标注不可分配后，会创建一个独立的文件 undo__trunc.log，记录现在正在 truncate 某个 undo log 文件，然后开始初始化 undo log file 到 10M，操作结束后，删除表示 truncate 动作的 undo__trunc.log 文件，这个文件保证了即使在 truncate 过程中发生了故障重启数据库服务，重启后，服务发现这个文件，也会继续完成 truncate 操作，删除文件结束后，标识该 undo log file 可分配。
- innodb_purge_rseg_truncate_frequency
用于控制 purge 回滚段的频度，默认为 128。假设设置为 n，则说明，当 Innodb Purge 操作的协调线程 purge 事务 128 次时，就会触发一次 History purge，检查当前的 undo log 表空间状态是否会触发 truncate。
二进制日志
二进制日志简介
MySQL 的二进制日志（binary log）是一个二进制文件，主要记录所有数据库表结构变更（例如 CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的所有操作。二进制日志（binary log）中记录了对 MySQL 数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录 SELECT、SHOW 等那些不修改数据的 SQL 语句。
二进制日志的作用
恢复（recovery）：某些数据的恢复需要二进制日志。例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point-in-time 的恢复。
复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的 MySQL 数据库（一般称为 slave 或者 standby）与一台 MySQL 数据库（一般称为 master 或者 primary）进行实时同步。
审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。
除了上面介绍的几个作用外，binlog 对于事务存储引擎的崩溃恢复也有非常重要的作用。在开启 binlog 的情况下，为了保证 binlog 与 redo 的一致性，MySQL 将采用事务的两阶段提交协议。当 MySQL 系统发生崩溃时，事务在存储引擎内部的状态可能为 prepared 和 commit 两种。对于 prepared 状态的事务，是进行提交操作还是进行回滚操作，这时需要参考 binlog：如果事务在 binlog 中存在，那么将其提交；如果不在 binlog 中存在，那么将其回滚，这样就保证了数据在主库和从库之间的一致性。
二进制日志文件
为了管理所有的 binlog 文件，MySQL 额外创建了一个 base-name.index 文件，它按顺序记录了 MySQL 使用的所有 binlog 文件。如果你想自定义 index 文件的名称，可以设置 log_bin_index=file 参数。千万不要在 mysqld 运行的时候手动修改 index 文件的内容，这样会使 mysqld 产生混乱。
二进制日志的开启
如果想开启 binlog，默认关闭，可以在 MySQL 配置文件中通过配置参数 log-bin = [base-name] 启动二进制日志。如果不指定 base-name，则默认二进制日志文件名为主机名，并以自增的数字作为后缀，例如 mysql-bin.000001，所在目录为数据库所在目录（datadir）。顺序说一下，对于二进制文件当满足下面三种情况时会创建新的文件，文件后缀会自增。
- 文件大小达到 max_binlog_size 参数设置值时。
- 执行 flush logs 命令。
- 重启 mysqld 进程。
你可能会有顾虑，当文件后缀从 000001 增长到 999999 时会怎样？有网友测试过，当文件达到 999999 时又会回到 000001，并不会有什么异常。
二进制日志格式
binlog 格式分为: STATEMENT、ROW 和 MIXED 三种，详情如下:
- STATEMENT
STATEMENT 格式的 binlog 记录的是数据库上执行的原生 SQL 语句。这种方式有好处也有坏处。
好处就是相当简单，简单地记录和执行这些语句，能够让主备保持同步，在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。另一个好处是二进制日志里的时间更加紧凑，所以相对而言，基于语句的复制模式不会使用太多带宽，同时也节约磁盘空间。并且通过 mysqlbinlog 工具容易读懂其中的内容。
坏处就是同一条 SQL 在主库和从库上执行的时间可能稍微或很大不相同，因此在传输的二进制日志中，除了查询语句，还包括了一些元数据信息，如当前的时间戳。即便如此，还存在着一些无法被正确复制的 SQL。例如，使用 INSERT INTO TB1 VALUE (CUURENT_DATE ()) 这一条使用函数的语句插入的数据复制到当前从服务器上来就会发生变化。存储过程和触发器在使用基于语句的复制模式时也可能存在问题。另外一个问题就是基于语句的复制必须是串行化的。这要求大量特殊的代码，配置，例如 InnoDB 的 next-key 锁等。并不是所有的存储引擎都支持基于语句的复制。
- ROW
从 MySQL5.1 开始支持基于行的复制，也就是基于数据的复制，基于行的更改。这种方式会将实际数据记录在二进制日志中，它有其自身的一些优点和缺点，最大的好处是可以正确地复制每一行数据。一些语句可以被更加有效地复制，另外就是几乎没有基于行的复制模式无法处理的场景，对于所有的 SQL 构造、触发器、存储过程等都能正确执行。主要的缺点就是二进制日志可能会很大，而且不直观，所以，你不能使用 mysqlbinlog 来查看二进制日志。也无法通过看二进制日志判断当前执行到那一条 SQL 语句了。
现在对于 ROW 格式的二进制日志基本是标配了，主要是因为它的优势远远大于缺点。并且由于 ROW 格式记录行数据，所以可以基于这种模式做一些 DBA 工具，比如数据恢复，不同数据库之间数据同步等。
- MIXED
MIXED 也是 MySQL 默认使用的二进制日志记录方式，但 MIXED 格式默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。比如用到 UUID ()、USER ()、CURRENT_USER ()、ROW_COUNT () 等无法确定的函数。
二进制日志参数
- max_binlog_size
可以通过 max_binlog_size 参数来限定单个 binlog 文件的大小（默认 1G），如果当前 binlog 文件的大小达到了参数指定的阈值，会创建一个新的 binlog 文件作为当前活跃的 binlog 文件，后续所有对数据库的修改都会记录到新的 binlog 文件中。
对于 binlog 文件的大小，有个需要注意的地方是，binlog 文件可能会大于 max_binlog_size 参数设定的阈值。由于一个事务所产生的所有事件必须记录在同一个 binlog 文件中，所以即使 binlog 文件的大小达到 max_binlog_size 参数指定的大小，也要等到当前事务的所有事件全部写入到 binlog 文件中才能切换，这样就会出现 binlog 文件的大小大于 max_binlog_size 参数指定的大小的情况。
- binlog_cache_size
当使用事务的表存储引擎（如 InnoDB 存储引擎）时，所有未提交（uncommitted）的二进制日志会被记录到一个缓存中去，等该事务提交（committed）时直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由 binlog_cache_size 决定，默认大小为 32K。此外，binlog_cache_size 是基于会话（session）的，也就是说，当一个线程开始一个事务时，MySQL 会自动分配一个大小为 binlog_cache_size 的缓存，因此该值的设置需要相当小心，不能设置过大。当一个事务的记录大于设定的 binlog_cache_size 时，MySQL 会把缓冲中的日志写入一个临时文件中，因此该值又不能设得太小。通过 SHOW GLOBAL STATUS 命令查看 binlog_cache_use、binlog_cache_disk_use 的状态，可以判断当前 binlog_cache_size 的设置是否合适。binlog_cache_use 记录了使用缓冲写二进制日志的次数，binlog_cache_disk_use 记录了使用临时文件写二进制日志的次数。
- sync_binlog
在 MySQL 5.7 之前版本默认情况下，二进制日志并不是在每次写的时候同步的磁盘（用户可以理解为缓冲写）。因此，当数据库所在的操作系统发生宕机时，可能会有最后一部分数据没有写入二进制文件中，这会给恢复和复制带来问题。参数 sync_binlog=[N] 中的 N 表示每提交多少个事务就进行 binlog 刷新到磁盘。如果将 N 设为 1，即 sync_binlog=1 表示采用同步写磁盘的方式来写二进制日志，每次事务提交时就会刷新 binlog 到磁盘；sync_binlog 为 0 表示刷新 binlog 时间点由操作系统自身来决定，操作系统自身会每隔一段时间就会刷新缓存数据到磁盘；sync_binlog 为 N 表示每 N 个事务提交会进行一次 binlog 刷新。如果使用 Innodb 存储引擎进行复制，并且想得到最大的高可用性，需要将此值设置为 1。不过该值为 1 时，确时会对数据库 IO 系统带来一定的开销。
但是，即使将 sync_binlog 设为 1，还是会有一种情况导致问题的发生。当使用 InnoDB 存储引擎时，在一个事务发出 COMMIT 动作之前，由于 sync_binlog 为 1，因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志，但是提交还没有发生，并且此时发生了宕机，那么在 MySQL 数据库下次启动时，由于 COMMIT 操作并没有发生，这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。对于这个问题，MySQL 使用了两阶段提交来解决的，简单说就是对于已经写入到 binlog 文件的事务一定会提交成功， 而没有写入到 binlog 文件的事务就会进行回滚，从而保证二进制日志和 InnoDB 存储引擎数据文件的一致性，保证主从复制的安全。
- binlog-do-db&binlog-ignore-db
参数 binlog-do-db 和 binlog-ignore-db 表示需要写入或者忽略写入哪些库的二进制日志。默认为空，表示需要同步所有库的日志到二进制日志。
- log-slave-update
如果当前数据库是复制中的 slave 角色，则它不会将 master 取得并执行的二进制日志写入自己的二进制日志文件中去。如果需要写入，要设置 log-slave-update。如果需要搭建 master–>slave–>slave 架构的复制，则必须设置该参数。
- binlog-format
binlog_format 参数十分重要，用来设置二进制日志的记录格式，详情参考 (6.5 binlog 格式)
- log_bin_trust_function_creators
默认为 OFF，这个参数开启会限制存储过程、Function、触发器的创建。
中继日志
什么是 relay log？
The relay log, like the binary log, consists of a set of numbered files containing events that describe database changes, and an index file that contains the names of all used relay log files.
The term “relay log file” generally denotes an individual numbered file containing database events. The term”relay log” collectively denotes the set of numbered relay log files plus the index file relay log 是复制过程中产生的日志，很多方面都跟 binary log 差不多，区别是: relay log 是从库服务器 I/O 线程将主库服务器的二进制日志读取过来记录到从库服务器本地文件，然后从库的 SQL 线程会读取 relay-log 日志的内容并应用到从库服务器上。
relay log 相关参数
- max_relay_log_size
标记 relay log 允许的最大值，如果该值为 0，则默认值为 max_binlog_size (1G)；如果不为 0，则 max_relay_log_size 则为最大的 relay_log 文件大小；
- relay_log
定义 relay_log 的位置和名称，如果值为空，则默认位置在数据文件的目录，文件名为 host_name-relay-bin.nnnnnn（By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory）；
- relay_log_index
同 relay_log，定义 relay_log 的位置和名称；
- relay_log_info_file
设置 relay-log.info 的位置和名称（relay-log.info 记录 MASTER 的 binary_log 的恢复位置和 relay_log 的位置）
- relay_log_purge
是否自动清空不再需要中继日志时。默认值为 1 (启用)。
- relay_log_recovery
当 slave 从库宕机后，假如 relay-log 损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的 relay-log，并且重新从 master 上获取日志，这样就保证了 relay-log 的完整性。默认情况下该功能是关闭的，将 relay_log_recovery 的值设置为 1 时，可在 slave 从库上开启该功能，建议开启。
- relay_log_space_limit
防止中继日志写满磁盘，这里设置中继日志最大限额。但此设置存在主库崩溃，从库中继日志不全的情况，不到万不得已，不推荐使用；
- sync_relay_log
这个参数和 sync_binlog 是一样的，当设置为 1 时，slave 的 I/O 线程每次接收到 master 发送过来的 binlog 日志都要写入系统缓冲区，然后刷入 relay log 中继日志里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量 I/O。当设置为 0 时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘 I/O 操作。这个值默认是 0，可动态修改。
- sync_relay_log_info
这个参数和 sync_relay_log 参数一样，当设置为 1 时，slave 的 I/O 线程每次接收到 master 发送过来的 binlog 日志都要写入系统缓冲区，然后刷入 relay-log.info 里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量 I/O。当设置为 0 时，并不是马上就刷入 relay-log.info 里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘 I/O 操作。这个值默认是 0，可动态修改。
完整日志配置文件示例
[mysqld]
# 全局配置
port = 3306 # 端口
datadir = /var/lib/mysql # 数据存放目录
socket = /var/lib/mysql/mysql.sock # 同 host 连接文件
pid-file = /var/lib/mysql/mysqld.pid # pid 存储文件
user = mysql # 账号配置
default_storage_engine = InnoDB # 默认引擎
symbolic-links = 0 # 禁用文件软连接
character-set-client-handshake = FALSE # 在客户端字符集和服务端字符集不同的时候将拒绝连接到服务端执行任何操作
character-set-server = utf8mb4 # 默认字符集
collation-server = utf8mb4_general_ci # 默认字符集编码
init_connect = 'SET NAMES utf8mb4' # 执行第一次查询之前执行此语句，统一字符集
wait_timeout = 31536000 # 连接空闲超时时间，秒
interactive_timeout = 31536000 # 连接空闲超时时间，秒
skip_name_resolve # 关闭域名解析，避免dns导致的请求失败(关闭后要使用IP访问mysql)，mysql 会做正向和反向dns查询。一旦失败就会拒绝连接。
# innodb 引擎配置
innodb_buffer_pool_size = 256M # 引擎缓冲池大小，根据实际调整
innodb_log_file_size = 50M # 引擎日志文件大小，根据实际调整
innodb_file_per_table = 1 # 开启独立表空间，开启后每个表都有自已独立的表空间，每个表的数据和索引都会存在自已的表空间中
innodb_flush_method = O_DIRECT # innodb 使用 O_DIRECT 打开数据文件，使用 fsync () 刷写数据文件跟 redo log
# LOGGING
log-error = /var/log/mysql-error.log # 错误日志存放位置
slow_query_log = ON # 开启慢日志
slow_query_log_file = /var/log/mysql-slow.log # 慢日志存放位置
long_query_time = 3 # 慢日志阈值，秒
# OTHER
tmp_table_size = 32M # 临时表内存缓存大小
max_heap_table_size = 32M # MEMORY 内存引擎的表大小
max_connections = 100 # 最大连接数
thread_cache_size = 50 # 线程池缓存大小
table_open_cache = 10 # 表文件描述符的缓存大小
open_files_limit = 65535 # 文件描述符限制数量
[client]
default-character-set = utf8mb4 # 默认字符集
[mysql]
default-character-set = utf8mb4 # 默认字符集
文档信息
- 本文作者：Lewin
- 本文链接：https://lewinz.com/2022/01/25/mysql-log/
- 版权声明：自由转载-非商用-非衍生-保持署名