MySQL日志之bin log

最新推荐文章于 2024-03-26 09:18:07 发布

DevXTalk

最新推荐文章于 2024-03-26 09:18:07 发布

阅读量41

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_45839894/article/details/134506387

版权

bin log(二进制日志)是什么

bin log 即 binary log ，二进制日志文件，也叫作变更日志（ update log ），是 MySQL 中比较重要的日志，和运维息息相关。它记录了所有更新数据库的语句（如 DDL 和 DML 语句）并以二进制的形式保存在磁盘中，但是不包含没有修改任何数据的语句（如数据查询语句select、show等）。

bin log 是逻辑日志，记录的是执行语句的逻辑，和 redis 的 AOP 日志类似，会按顺序记录所有涉及更新数据的逻辑操作。

主要作用：

数据恢复： MySQL 可以通过 bin log 恢复某一时刻的误操作的数据，是 DBA 常打交道的日志。
数据复制： MySQL 的数据备份、集群高可用、读写分离都是基于 bin log 的重放实现的。

bin log的作用

记录格式

binlog 日志有三种格式： statement 、 row 和 mixed ，对比如下：

格式	含义	优点	缺点
statement	基于SQL语句的复制，记录的是更新数据操作的SQL语句，这些语句同步时会被其他节点执行，如update T set time=NOW() where id = 1;	不需要记录数据的变化，减少了bin log文件大小，减少IO负担。	SQL中包含了每次执行结果不一致的函数、触发器时，同步数据时会造成不一致。
row	基于行的复制，5.1.5版本支持的格式，记录了数据被更改的具体值，如update T set time=1687843346000 where id = 1;	日志会清晰记录每条数据被修改的详细情况，保证了数据的一致性。	每条数据的更改被详细记录，如整表删除，alter表等操作涉及的数据行都会记录，ROW格式会产生大量日志。
mixed	混合模式，5.1.8版本开始，以上两种格式的混合版，对于DDL只对SQL语句进行记录，对DML操作则会进行判断，如果判断会造成主从不一致，就会采用row格式记录，反之则用statement格式记录。	既节省空间，又提高数据库性能，保证数据同步时的一致性。	无法对误操作数据进行单独恢复。

写入时机

什么时候写bin log?

事务执行过程中，会先把日志写到 binlog cache 中去，事务提交时，才把 binglog cache 写到 binlog 文件中去（刷盘）。 binlog cache 是为了保证一个事务的所有操作能够一次性写入 bin log 不被拆开而设置的缓存， binlog cache 大小受 binlog_cache_size 参数控制。

binlog写入时机

上图 binlog cache 写到 bin log 日志文件的过程包含了 write 和 fsync 两步操作：

write 是把日志写到文件系统缓存中，这一步是系统为了提高文件IO效率；
fsync 是把数据持久化到日志文件中去。

什么时候执行write和fsync？

写入策略受 sync_binlog 参数控制，默认0。

sync_binlog=0 ：表示每次提交事务都只 write ，由操作系统自行判断什么时候执行 fsync 。

优点：性能提升；

缺点：但是如果机器宕机， page cache 里的 bin log 会丢失。

sync_binlog=0

sync_binlog=N ：表示每次提交事务都只 write ，积攒 N 个事务后才执行 fsync 。

优点：机器宕机只丢失 N 个事务的 bin log ；

缺点：如果 N 设置的很小可能会出现IO瓶颈，需要适当调整 N 的大小。

sync_binlog=N

两阶段提交

MySQL 在执行更新操作的过程中，会记录两种日志： redo log 和 binlog ，都是以事务为单位：

redo log 是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎层，在事务过程中是不断写入的。
bin log 是逻辑日志，记录内容是语句的原始逻辑，属于Server层，只在事务提交时才写入。

redo log和bin log写入时机不同

redo log与bin log会出现什么问题？

对于如下 SQL ：

update T set c = 1 where id = 2;

在执行过程中一直在写 redo log ，但是当事务提交时，才开始写 bin log ，如果此时 MySQL 系统崩溃， bin log 还未来得及写入。

redo log与binlog会出现的问题

此时 MySQL 系统重启发现 redo log 有记录，主库通过 redo log 恢复了c值为1，而从库通过 bin log 同步数据之后c值为2，这就造成了主从数据同步的不一致，这是不允许的。

出现不一致

如何解决

为了解决两份日志之间的逻辑一致问题， InnoDB 存储引擎使用 两阶段提交 方案。

两阶段提交：将 redo log 的写入操作拆成了两个步骤 prepare 和 commit 进行，在事务执行期间，写入的 redo log 标记为 prepare 阶段，待事务提交且 bin log 写入成功时，才将 redo log 标记为 commit 阶段。

两阶段提交

情况1：写入bin log时发生异常

使用两阶段提交后，写入 bin log 时发生异常也不会有影响，因为 MySQL 在使用 redo log 恢复时，发下 redo log 还处于 prepare 阶段，而且此时没有 bin log ，认为该事务操作尚未完成提交，会回滚此操作。

mysql重启恢复

情况2：redo log在commit阶段发生异常

虽然 MySQL 重启后发现 redo log 是处于 prepare 阶段，但是能通过事务 id 找到了对应的 bin log 记录，所以 MySQL 认为此事务执行是完整的，就会提交事务恢复数据。

redo log在commit阶段发生异常

bin log相关操作

查看日志开启情况

mysql> show variables like'%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

mysql> show variables like'%log_bin%';
+---------------------------------+----------------------------------------------------------------------+
| Variable_name                   | Value                                                                |
+---------------------------------+----------------------------------------------------------------------+
| log_bin                         | ON                                                                   |
| log_bin_basename                | C:\ProgramData\MySQL\MySQL Server 8.0\Data\SC-202010081028-bin       |
| log_bin_index                   | C:\ProgramData\MySQL\MySQL Server 8.0\Data\SC-202010081028-bin.index |
| log_bin_trust_function_creators | OFF                                                                  |
| log_bin_use_v1_row_events       | OFF                                                                  |
| sql_log_bin                     | ON                                                                   |
+---------------------------------+----------------------------------------------------------------------+

bin log日志设置

修改 MySQL 的 my.cnf ( linux ) 或 my.ini ( windows )文件可以设置二进制日志的相关参数：

[mysqld]
#启用二进制日志
log-bin=SC-bin
binlog_expire_logs_seconds=600
max_binlog_size=100M

查看日志文件列表和大小

MySQL 服务每重新启动一次，文件后缀的数字就会+1，如果日志长度超过了 max_binlog_size 的上限（默认是1GB），也会创建一个新的日志文件。

mysql> SHOW BINARY LOGS;
+----------------------------+-----------+-----------+
| Log_name                   | File_size | Encrypted |
+----------------------------+-----------+-----------+
| SC-202010081028-bin.000634 |       179 | No        |
| SC-202010081028-bin.000635 |       156 | No        |
+----------------------------+-----------+-----------+

查看bin log记录的默认格式

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.05 sec)