系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

MySQL Binlog日志写入及文件操作

时间:2023-10-30来源:系统城装机大师作者:佚名

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会让日志暴涨。

  • 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语句选择写入模式。

Binlog 写入机制

常用的log event有:Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合。

  • 根据记录模式和操作触发event事件生成log event(事件触发执行机制)
  • 将事务执行过程中产生log event写入缓冲区,每个事务线程都有一个缓冲区

 Log Event保存在一个binlog_cache_mngr数据结构中,在该结构中有两个缓冲区,一个是stmt_cache,用于存放不支持事务的信息;另一个是trx_cache,用于存放支持事务的信息。

  • 事务在提交阶段会将产生的log event写入到外部binlog文件中。

 不同事务以串行方式将log event写入binlog文件中,所以一个事务包含的log event信息在binlog文件中是连续的,中间不会插入其他事务的log event。

Binlog 文件操作

Binlog状态查看

1 show variables like 'log_bin';

开启Binlog功能

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

1
2
3
4
#log-bin=ON
#log-bin-basename=mysqlbinlog
binlog-format=ROW
log-bin=mysqlbinlog

执行开启语句

1 set global log_bin=mysqllogbin;

使用show binlog events命令

1
2
3
4
5
6
7
8
9
10
11
show binary logs; //等价于show master logs;
show master status;
show binlog events;
show binlog events in 'mysqlbinlog.000001'\G;
结果:
     Log_name: mysql_bin.000001   //此条log存在那个文件中
        Pos: 174                 //log在bin-log中的开始位置
     Event_type: Intvar          //log的类型信息
        Server_id: 1  //可以查看配置中的server_id,表示log是那个服务器产生
     End_log_pos: 202          //log在bin-log中的结束位置
        Info: INSERT_ID=2    //log的一些备注信息,可以直观的看出进行了什么操作

可以用mysql自带的工具mysqlbinlog

1 mysqlbinlog "文件名" mysqlbinlog "文件名" > "文件名比如:test.sql"

使用 binlog 恢复数据

1
2
3
4
//按指定时间恢复
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文件

1
2
3
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文件会自动删除掉

Redo Log和 Binlog 区别

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

(crash-safe 即在 InnoDB 存储引擎中,事务提交过程中任何阶段,MySQL突然奔溃,重启后都能保证事务的完整性,已提交的数据不会丢失,未提交完整的数据会自动进行回滚。这个能力依赖的就是redo log和unod log两个日志。)

分享到:

相关信息

  • MySQL的核心查询语句详解

    一、单表查询 1、排序 2、聚合函数 3、分组 4、limit 二、SQL约束 1、主键约束 2、非空约束 3、唯一约束 4、外键约束 5、默认值 三、多表查询 1、内连接 1)隐式内连接: 2)显式内连接: 2、外连接 1)左外连接 2)右外连接 四...

    2023-10-30

  • Mysql中如何删除表重复数据

    Mysql删除表重复数据 表里存在唯一主键 没有主键时删除重复数据 Mysql删除表中重复数据并保留一条 准备一张表 用的是mysql8 大家自行更改 创建表并添加四条相同的数据...

    2023-10-30

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载