深入解析 MySQL binlog

1.概述

binlog是Mysql sever层维护的一种二进制日志,与innodb引擎中的redo/undo log是完全不同的日志;其主要是用来记录所有数据库表结构变更、以及数据修改的二进制文件,不会记录SELECT SHOW等操作,Binlog以"事务"的形式保存在磁盘中,还包含语句执行的消耗时间;主要应用于两种场景:
主从复制
数据恢复
Binlog的文件名默认为“主机名_binlog-序列号”格式,也可以在配置文件中指定名称。文件记录模式一种有三种:

ROW:日志中会记录每一行数据被修改的情况,然后再slave端对相同的数据进行修改。完全实现数据同步、数据恢复。但是会产生大量的日志(可靠但是消耗资源)。如:大批量的数据更新、尤其是alter table 会让日志暴涨。

STATMENT:每一条被修改数据的sql都会被记录到master的binlog中,slave在复制的时候sql进程会解析成和原来master端执行过的相同sql再次执行。简称sql复制。日志量少、减少了IO、提升了存储和恢复速度,在某些情况下,会导致主从数据的不一致。如:now() last_insert_id()。

MIXED:以上两种模式的混用,一般使用STATMENT模式保存binlog,对于STATMENT无法复制的操作使用ROW模式保存binlog,MYSQL会根据执行的sql语句写入模式。

2.Binlog文件结构

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

3.binlog的写入机制

根据记录模式和操作触发event事件生成log event (事件触发执行机制),将事务执行过程中产生log event 写入缓冲区,每个事务线程都有一个缓冲区。log event 保存在一个bin_cache_mngr的数据结构中,在该结构中有两个缓冲区,一个是stmt_cache, 用于存放不支持事务的信息;另一个是trx_cache,用于存放支持事务的信息。事务提交阶段将会产生的log event 写入到外部binlog文件中,不同事务以串行的方式将log event 写入到binlog文件中,所以一个事务包含的log event 信息保存在binlog文件中是连续的,中间不会插入其他事务的log event。

4 命令操作-恢复数据

在/etc 目录下新建my.cnf 并添加如下内容:

➜  /Users/zhaoshuai11 cd /etc
➜  /etc cat my.cnf
[mysqld]
# log_bin
log-bin = mysql-bin #开启binlog
binlog-format = ROW #选择row模式
server_id = 1 #配置mysql replication需要定义,不能和canal的slaveId重复
show variables like '%log_bin%';

在这里插入图片描述
获取binlog文件列表:

show binary logs;

在这里插入图片描述
生成新的日志文件的条件:每当我们停止或重启服务器时,服务器会把日志文件记入下一个日志文件,MySQL会在重启时生成一个新的日志文件,文件序号递增。如果日志文件超过max_binlog_size(默认值1G)系统变量配置的上限时,也会生成新的日志文件(在这里需要注意的是,如果你正使用大的事务,二进制日志还会超过max_binlog_size,不会生成新的日志文件,事务全写入一个二进制日志中,这种情况主要是为了保证事务的完整性)日志被刷新时,新生成一个日志文件

flush logs;

在这里插入图片描述
执行下列的语句:

create database if not exists test_binlog;

use test_binlog;


DROP TABLE IF EXISTS `Websites`;
CREATE TABLE `Websites` (
  `id` int(11) NOT NULL,
  `name` char(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `access_log`;
CREATE TABLE `access_log` (
  `aid` int(11) NOT NULL,
  `site_id` int(11) NOT NULL,
  `count` int(11) NOT NULL
);

INSERT INTO `Websites` VALUES ('1', 'Google');
INSERT INTO `Websites` VALUES('2', 'TaoBao');
INSERT INTO `Websites` VALUES('3', 'CaiNiao');

INSERT INTO `access_log` VALUES ('1', '1', '45');
INSERT INTO `access_log` VALUES('2', '3', '100');
INSERT INTO `access_log` VALUES('3', '1', '230');
INSERT INTO `access_log` VALUES('4', '2', '10');

show binary logs 可以看到输出如下,说明我们刚才执行的操作已经写入了mysql-bin.000007中
在这里插入图片描述
我们可以使用如下的命令来查看当前正在写入的binlog文件:

show master status\G

在这里插入图片描述
使用mysqlbinlog进行查看还是以mysql-bin.000007日志为例,基本使用用法如下:

mysqlbinlog "/usr/local/mysql/data/mysql-bin.000007"

在这里插入图片描述

mysqlbinlog mysql-bin.000007

mysqlbinlog mysql-bin.000007 > 'test.sql'

在这里插入图片描述
使用mysqlbinlog恢复数据:

mysqlbinlog --start-datetime="2022-06-22 11:38:59" --stop-datetime="2022-06-22 11:40:01" 'mysql-bin.000007' | mysql -uroot -proot

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值