MySQL日志-Binlog

最新推荐文章于 2024-08-01 15:13:04 发布

四月丶丶

最新推荐文章于 2024-08-01 15:13:04 发布

阅读量382

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/rzpy_qifengxiaoyue/article/details/108559318

版权

MySQL 专栏收录该内容

22 篇文章 3 订阅

订阅专栏

1. Binlog记录模式

Redo Log 是属于InnoDB引擎所特有的日志，而MySQL Server也有自己的日志，即 Binary log(二进制日志)，简称Binlog。Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录SELECT和SHOW这类操作。Binlog日志是以事件形式记录，还包含语句所执行的消耗时间。开启Binlog日志有以下两个最重要的使用场景。

主从复制:在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到 Binlog后实现数据恢复达到主从数据一致性。
数据恢复:通过mysqlbinlog工具来恢复数据。

Binlog文件名默认为“主机名_binlog-序列号”格式，例如oak_binlog-000001，也可以在配置文件中指定名称。文件记录模式有STATEMENT、ROW和MIXED三种，具体含义如下。

ROW(row-based replication, RBR):日志中会记录每一行数据被修改的情况，然后在 slave端对相同的数据进行修改。

优点:能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。
缺点:批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。
show variables like ‘%innodb_log%’;

STATMENT(statement-based replication, SBR):每一条被修改数据的SQL都会记录到 master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的 SQL再次执行。简称SQL语句复制。

优点:日志量小，减少磁盘IO，提升存储和恢复速度
缺点:在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。

MIXED(mixed-based replication, MBR):以上两种模式的混合使用，一般会使用 STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存 binlog，MySQL会根据执行的SQL语句选择写入模式。

2. Binlog文件结构

MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。比较常用的log event有:Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合。
Binlog文件中Log event结构如下图所示:
在这里插入图片描述

3. Binlog写入机制

根据记录模式和操作触发event事件生成log event(事件触发执行机制)
将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区
Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是 stmt_cache，用于存放不支持事务的信息;另一个是trx_cache，用于存放支持事务的信息。事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。
事务在提交阶段会将产生的log event写入到外部binlog文件中。
不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在binlog文件中是连续的，中间不会插入其他事务的log event。也就是说一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了 binlog cache 的保存问题。
系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。状态如下图所示。

可以看到，每个线程有自己 binlog cache，但是共用同一份 binlog 文件。
图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。
图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。
write 和 fsync 的时机，是由参数 sync_binlog 控制的：

sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。
但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

4. Binlog文件操作

Binlog状态查看

show variables like 'log_bin';

开启Binlog功能

mysql> set global log_bin=mysqllogbin;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

需要修改my.cnf或my.ini配置文件，在[mysqld]下面增加log_bin=mysql_bin_log，重启 MySQL服务。

#log-bin=ON 
#log-bin-basename=mysqlbinlog 
binlog-format=ROW 
log-bin=mysqlbinlog

使用show binlog events命令

show binary logs; //等价于show master logs; show master status;
show binlog events;
show binlog events in 'mysqlbinlog.000001';

使用mysqlbinlog 命令

mysqlbinlog "文件名"
mysqlbinlog "文件名" > "test.sql"

使用 binlog 恢复数据

//按指定时间恢复
mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stop- datetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234 
//按事件位置号恢复
mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002 | mysql -uroot -p1234

mysqldump:定期全部备份数据库数据。mysqlbinlog可以做增量备份和恢复操作。

删除Binlog文件

purge binary logs to 'mysqlbinlog.000001'; //删除指定文件
purge binary logs before '2020-04-28 00:00:00'; //删除指定时间之前的文件 
reset master; //清除所有文件

可以通过设置expire_logs_days参数来启动自动清理功能。默认值为0表示没启用。设置为1表示超出1天binlog文件会自动删除掉。

5. Redo Log和Binlog区别

Redo Log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制文件记录。
Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。 Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不会覆盖使用。
Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用。Binlog没有自动crash-safe能力。