MySQL 知识点分享二：重做日志及与二进制日志的区别

MySQL 默认情况下会有两个文件: ib_logfile0 和 ib_logfile1, 这两个文件就是重做日志文件, 或者事务日志

重做日志的目的: 实例或者介质失败, 重做日志文件就能派上用场 每个 InnoDB 存储引擎至少有一个重做日志文件组, 每个文件组下至少有 2 个重做日志文件, 如默认的 ib_logfile0, ib_logfile1. InnoDB存储引擎先写重做日志文件1, 当达到文件的最后时, 会切换至重做日志文件2, 当重做日志文件2也被写满时, 会再被切换到重做日志文件1中.

重做日志与二进制日志的区别:

二进制日志会记录所有与 mysql 有关的日志记录, 包括 InnoDB 等其他存储引擎的日志, 而 InnoDB 存储引擎的重做日志只记录有关其本身的事务日志

记录的内容不同, 不管你将二进制日志文件记录的格式设为哪一种, 其记录的都是关于一个事务的具体操作内容, 而 InnoDB 存储引擎的重做日志文件记录的关于每个页的更改的物理情况

写入的时间也不同, 二进制日志文件是在事务提交前进行记录的, 而在事务进行的过程中, 不断有重做日志条目被写入重做日志文件中

影响重做日志的参数:

innodb_log_files_in_group: 指定重做日志文件组中文件的数量, 默认 2

innodb_log_file_size: 重做日志文件的大小

innodb_log_group_home_dir: 指定日志文件组所在的路径,默认./, 表示在数据库的数据目录下

innodb_redo_log_archive_dirs: 定义可以在其中创建重做日志存档文件的标记目录. 可以在分号分隔的列表中定义多个标记的目录

innodb_redo_log_capacity: 定义重做日志文件占用的磁盘空间量(MySQL 8.0.30 or Higher)

innodb_flush_log_trx_commit 参数控制事务提交时写重做日志的行为方式,它有三个值: 0 1 2

1. 默认值为 1, 每次事务提交的时候都会将日志缓存中的数据写入到日志文件, 同时还会触发文件系统到磁盘的同步, 如果发生系统崩溃, 数据是零丢失, 这种方式对数据是最安全的, 但性能是最慢的, 因为把数据从缓存同步到磁盘的成本很高. 这种方式适用于对数据安全性要求高的行业, 如银行业. 但很多互联网的应用, 对数据的安全性要求不太高, 而对性能的要求很高, 设置成 0 或 2 会更合适

2. 设置成 0 时, 事务提交的时候不会触发写日志文件的操作, 日志缓存中的数据以每秒一次的频率写入到日志文件中, 同时还会进行文件系统到磁盘的同步操作

3. 设置成 2 时, 事务提交的时候会写日志文件, 但文件系统到磁盘的同步是每秒进行一次. 0 和 2 都是每秒进行一次文件系统到磁盘的同步, 因此这两种方式的性能都差不多, 当系统崩溃时, 最多丢失1秒的数据. 但 0 和 2 还有细微的不同, 当设置成 2 时, 每次事务提交都写日志文件, 因此数据已经从MySQL的日志缓存刷新到了操作系统的文件缓存, 如果只是 MySQL 崩溃, 而操作系统没有崩溃, 将不会丢失数据. 因此 0 和 2 比较起来, 通常设置为 2 比较好

影响二进制日志的参数

log_bin: 是否记录二进制日志

log_bin_basename: 保存二进制日志文件的基本名称和路径

log_bin_index: 二进制日志索引文件的名称, 其中包含二进制日志文件的名称

log_bin_trust_function_creators: 当启用二进制日志记录时, 此变量适用. 它控制是否可以信任存储函数创建者, 不创建可能导致不安全事件写入二进制日志的存储函数

sql_log_bin: 此变量控制是否为当前会话启用二进制日志的日志记录(假设二进制日志已启用)

sync_binlog 参数控制事务提交时写二进制日志的行为方式, 它有三个值: 0 1 N

1. 默认值为 1, 每次事务提交的时候都会把二进制日志刷新到磁盘, 这种方式对数据是最安全的, 但性能是最慢的

2. 设置成 0 时, 事务提交的时候不会把二进制日志刷新到磁盘, 刷磁盘的动作由操作系统控制

3. 设置成 N (N不等于 0 或 1 )时, 每进行 N 事务提交后会进行一次把二进制日志刷新到磁盘的动作. 没有备库和使用二进制日志进行时间点恢复的需求时, 可以把 sync_binlog 参数设置为 0 或 N, 设置为 0 是把刷新二进制日志文件的操作交给操作系统决定, 但操作系统可能会在二进制日志文件写满进行切换时才刷新磁盘文件, 这样会造成数秒的延迟, 在这期间事务无法提交, 因此把这个参数设置成 100 或 1000 之类的一个合理数值比设置成 0 好