百度360必应搜狗淘宝本站头条
tomcat 乱码无法与服务器建立安全连接是什么意思sysv-rc-confdocker映射容器目录到宿主机internet explorer 增强的安全配置已启用
当前位置：网站首页 > 技术文章 > 正文

带你解析MySQL binlog

nanshan 2024-11-27 18:15 9 浏览 0 评论

前言：

我们都知道，binlog可以说是MySQL中比较重要的日志了，在日常学习及运维过程中，也经常会遇到。不清楚你对binlog了解多少呢？本篇文章将从binlog作用、binlog相关参数、解析binlog内容三个方面带你了解binlog。

1.binlog简介

binlog即binary log，二进制日志文件。它记录了数据库所有执行的DDL和DML语句（除了数据查询语句select、show等），以事件形式记录并保存在二进制文件中。

binlog主要有两个应用场景，一是用于复制，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。二是用于数据恢复，例如还原备份后，可以重新执行备份后新产生的binlog，使得数据库保持最新状态。除去这两个主要用途外，binlog可以用于异构系统之间数据的交互，binlog完整保存了一条记录的前项和后项记录，可以用DTS服务，将MySQL数据以准实时的方式抽取到底层数据平台，比如HBase、Hive、Spark等，打通OLTP和OLAP。

binlog日志可以选择三种模式，分别是 STATEMENT、 ROW、 MIXED，下面简单介绍下这三种模式：

  • STATEMENT：基于SQL语句的复制，每一条会修改数据的sql语句会记录到binlog中。该模式下产生的binlog日志量会比较少，但可能导致主从数据不一致。
  • ROW：基于行的复制，不记录每一条具体执行的SQL语句，仅需记录哪条数据被修改了，以及修改前后的样子。该模式下产生的binlog日志量会比较大，但优点是会非常清楚的记录下每一行数据修改的细节，主从复制不会出错。
  • Mixed：混合模式复制，以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

binlog模式在MySQL 5.7.7之前，默认为 STATEMENT，在之后的版本中，默认为ROW。这里建议采用ROW模式，因为ROW模式更安全，可以清楚记录每行数据修改的细节。

2.binlog相关参数

binlog默认情况下是不开启的，不过一般情况下，初始化的时候建议在配置文件中增加log-bin参数来开启binlog。

# 配置文件中增加log-bin配置
[mysqld]
log-bin = binlog

# 不指定路径默认在data目录下，也可以指定路径
[mysqld]
log-bin = /data/mysql/logs/binlog

# 查看数据库是否开启了binlog
show variables like 'log_bin%';

开启binlog后，还需注意一些与binlog相关的参数，下面简单介绍下相关参数：

binlog_format

设置binlog模式，建议设为ROW。


binlog_do_db

此参数表示只记录指定数据库的二进制日志，默认全部记录，一般情况下不建议更改。


binlog_ignore_db

此参数表示不记录指定的数据库的二进制日志，同上，一般不显式指定。


expire_logs_days

此参数控制二进制日志文件保留天数，默认值为0，表示不自动删除，可设置为0~99。可根据实际情况设置，比如保留15天或30天。MySQL8.0版本可用binlog_expire_logs_seconds参数代替。


max_binlog_size

控制单个二进制日志大小，当前日志文件大小超过此变量时，执行切换动作。此参数的最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。一般情况下可采取默认值。


log_bin_trust_function_creators

当二进制日志启用后，此参数就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。建议设置为1。


sync_binlog

控制MySQL服务端将二进制日志同步到磁盘的频率，默认值为1。设置为0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新；设置为1，表示每次事务提交，MySQL都会把binlog刷下去，这是最安全的设置，但由于磁盘写入次数增加，可能会对性能产生负面影响；设置为n，其中n为0或1以外的值，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，将Binlog文件缓存刷新到磁盘。推荐设置为1，出于性能考虑也可酌情调整。

关于binlog操作与管理相关的SQL也有很多，下面介绍下部分常用的语句：


3.解析binlog内容

前面说过，所有对数据库的修改都会记录在binglog中。但binlog是二进制文件，无法直接查看，想要更直观的观测它就要借助mysqlbinlog命令工具了，下面的内容主要介绍如何使用mysqlbinlog来解析binlog日志内容。

为了故事的顺利发展，我们首先切换下binlog，然后创建测试库、测试表，执行插入数据，更新数据。这些前置操作暂不展示，下面我们来看下如何解析并查看生成的binlog内容：

# 本次解析基于MySQL8.0版本，实例已开启gtid，模式为ROW

[root@centos logs]# mysqlbinlog  --no-defaults --base64-output=decode-rows -vv binlog.000013
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
...
...
#200708 16:52:09 server id 1003306  end_log_pos 1049 CRC32 0xbcf3de39   Query   thread_id=85    exec_time=0     error_code=0    Xid = 1514
use `bindb`/*!*/;
SET TIMESTAMP=1594198329/*!*/;
SET @@session.explicit_defaults_for_timestamp=1/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE  `bin_tb` (
  `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `stu_id` int(11) NOT NULL COMMENT '学号',
  `stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='测试binlog'
/*!*/;
# at 1049
#200708 16:52:45 server id 1003306  end_log_pos 1128 CRC32 0xf19ea0a9   GTID    last_committed=2        sequence_number=3       rbr_only=yes    original_committed_timestamp=1594198365741300   immediate_commit_timestamp=1594198365741300        transaction_length=468
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1594198365741300 (2020-07-08 16:52:45.741300 CST)
# immediate_commit_timestamp=1594198365741300 (2020-07-08 16:52:45.741300 CST)
/*!80001 SET @@session.original_commit_timestamp=1594198365741300*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= '0032d819-2d32-11ea-91b5-5254002ae61f:24883'/*!*/;
# at 1128
#200708 16:52:45 server id 1003306  end_log_pos 1204 CRC32 0x5b4b03db   Query   thread_id=85    exec_time=0     error_code=0
SET TIMESTAMP=1594198365/*!*/;
BEGIN
/*!*/;
# at 1204
#200708 16:52:45 server id 1003306  end_log_pos 1268 CRC32 0xd4755d50   Table_map: `bindb`.`bin_tb` mapped to number 139
# at 1268
#200708 16:52:45 server id 1003306  end_log_pos 1486 CRC32 0x274cf734   Write_rows: table id 139 flags: STMT_END_F
### INSERT INTO `bindb`.`bin_tb`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1001 /* INT meta=0 nullable=0 is_null=0 */
###   @3='from1' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###   @5=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### INSERT INTO `bindb`.`bin_tb`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1002 /* INT meta=0 nullable=0 is_null=0 */
###   @3='dfsfd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###   @5=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
...
# at 1486
#200708 16:52:45 server id 1003306  end_log_pos 1517 CRC32 0x0437e777   Xid = 1515
COMMIT/*!*/;
...
# at 1596
#200708 16:54:35 server id 1003306  end_log_pos 1681 CRC32 0x111539b6   Query   thread_id=85    exec_time=0     error_code=0
SET TIMESTAMP=1594198475/*!*/;
BEGIN
/*!*/;
# at 1681
#200708 16:54:35 server id 1003306  end_log_pos 1745 CRC32 0x6f0664ee   Table_map: `bindb`.`bin_tb` mapped to number 139
# at 1745
#200708 16:54:35 server id 1003306  end_log_pos 1939 CRC32 0xfafe7ae8   Update_rows: table id 139 flags: STMT_END_F
### UPDATE `bindb`.`bin_tb`
### WHERE
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1005 /* INT meta=0 nullable=0 is_null=0 */
###   @3='dsfsdg' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###   @5=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### SET
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1005 /* INT meta=0 nullable=0 is_null=0 */
###   @3=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
###   @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###   @5=1594198475 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### UPDATE `bindb`.`bin_tb`
### WHERE
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1006 /* INT meta=0 nullable=0 is_null=0 */
###   @3='fgd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
###   @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###   @5=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### SET
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1006 /* INT meta=0 nullable=0 is_null=0 */
###   @3=NULL /* VARSTRING(60) meta=60 nullable=1 is_null=1 */
###   @4=1594198365 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###   @5=1594198475 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
...
# at 1939
#200708 16:54:35 server id 1003306  end_log_pos 1970 CRC32 0x632a82b7   Xid = 1516
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

# 可以看出，binlog中详细记录了每条sql执行产生的变化，
并且包括执行时间、pos位点、server_id等系统值。

关于mysqlbinlog工具的使用技巧还有很多，例如只解析对某个库的操作或者某个时间段内的操作等。简单分享几个常用的语句，更多操作可以参考官方文档。

mysqlbinlog --no-defaults --base64-output=decode-rows -vv binlog.000013 > /tmp/bin13.sql

将解析到的SQL导入文件中


mysqlbinlog --no-defaults --base64-output=decode-rows -vv --database=testdb binlog.000013

只解析某个库的操作


mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-datetime="2020-01-11 01:00:00" --stop-datetime="2020-01-11 23:59:00" binlog.000008

解析指定时间段内的操作


mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-position=204136360 --stop-position=204136499 binlog.000008

解析指定pos位点内的操作


mysqlbinlog --no-defaults --start-position=204136360 --stop-position=204136499 binlog.000008 | mysql -uroot -pxxxx testdb

在指定库中恢复指定位点间的操作

总结：

不知不觉写的挺长了，本文讲述了各类binlog相关知识点，希望你读完会对binlog有更深的认识。其实最重要的还是实践，只有多学多用才能更好的掌握。这么硬核的知识，希望大家用到的时候可以拿来读读，欢迎各位转发分享，让更多人看到。

相关推荐

今晚拿下PHP反序列化的一系列操作

引言在CTF中反序列化类型的题目还是比较常见的，之前有学习过简单的反序列化，以及简单pop链的构造。这次学习内容为php内置的原生类的反序列化以及一点进阶知识。在题目给的的代码中找不到可利用的类时，这...

Win10安装Apache和PHP（apache安装php模块）

说明：虽然PHPStudy之类的软件给我们提供了方便的集成环境，但是其使用的是非线程安全的PHP版本（NotThreadSafe，NTS），某些功能不可以使用。所以，我们还需要自己再安装一个Apa...

腾讯云云函数部署laravel项目（腾讯云函数 selenium）

1、购买函数套餐包在Serverless控制台，选择函数套餐包进行购买2、新建函数服务2.1、模板创建选择函数URL：新建函数URL，启用公网访问：2.1.1、postman访问首页2.1.2、pos...

站长教你搭建属于自己的网站（站长教你搭建属于自己的网站是什么）

每个人都希望可以有自己的网站，因为那样高端大气上档次，低调奢华有内涵，尤其是公司用户，一般会有自己的网站。而个人呢，也可以搭建自己的网站博客，论坛等，但是一般个人都是搭建博客的。好了，那么下面站长教你...

微信公众号开发出现 cURL error 60: SSL certificate problem ssl证书

在phpstudy的环境下如果出现这样的报错cURLerror60:SSLcertificateproblem:unabletogetlocalissuercertificat...

【网络安全】关于PHP Study nginx解析高危漏洞的预警通报

网络安全近日，山石网科安全研究院监测发现PHPStudyWindows最新版本存在nginx解析漏洞，可以造成任意代码执行。一、漏洞情况phpStudy是一个PHP调试环境的程序集成包，该程序包集成...

PHP 环境 搭建教程（php环境搭建教程linux）

PHP是一种编程语言，很多网站都用PHP语言编写，我们有时候需要测试一个网站，就需要PHP环境才能运行，又要安装Apache、又要安装MySQL……真的非常麻烦。其实我们可以使用PHP集成...

黑客搭建钓鱼平台，手把手教你如何钓鱼?

跨站脚本攻击XSS：通过HTML注入篡改了网页，插入了恶意的脚本，从而用户浏览网页时，控制用户浏览器的一种攻击那么，我们搭建一个XSS钓鱼平台吧，注意：这个平台仅用于学习和测试，小伙伴们不要动有坏心思...

php源码网站搭建方法和过程（php网站源码完整）

web网站是我们上网的窗口，而网站是如何搭建的呢？今天我们来做一个介绍，以php代码为例来进行介绍（后续会介绍一下java代码搭建，如果想要我这里涉及的工具或源码请私信我）。1、首先你需要去网上下载你...

使用VS Code调试PhpStudy环境里的代码

最近几个月把所有项目都迁过来VSCode了（除了因为Unity调试问题反而用回了VisualStudio），PHP也就抛弃了最强的PhpStorm。这段时间抽空在帮朋友处理PHP项目，然...

phpstudy搭建PHP+Mysql服务（用phpstudy搭建服务器）

PHP是一种创建动态交互性站点的强有力的服务器端脚本语言。PHP是免费的，并且使用非常广泛。同时，对于像微软ASP这样的竞争者来说，PHP无疑是另一种高效率的选项。（1）PHP环境搭建使用V...

Windows安装phpstudy（Windows安装mysql）

说明：phpstudy是一个PHP+MySQL+Apache的集成环境，可以减少单独部署各个所需软件的麻烦，以及更加方便地切换版本。phpenv、wamp等软件的作用一样。由于环境的不同，安装过程中可...

phpstudy安装及简单使用教程（phpstudy安装教程详解）

phpstudy前不久爆出有后门，我的看法是，去看下是哪个版本有后门，为啥会有后门，怎么解决掉这个后门，而不是听到后门就弃用了。毕竟phpstudy绿色安装，配置简单，多版本融合，真香。前言：关于开发...

如何对dedeCMS的开源程序进行二次开发

二次开发，简单的说就是在现有的软件上进行定制修改，功能的扩展，然后达到自己想要的功能和效果，一般来说都不会改变原有系统的内核。为了让更多人了解二次开发，并更方便的了解DedeCMS的二次开发，下面将会...

mysql基础问题三问(底层逻辑;正在执行;日志观察)

背景：经常面试会遇到且实际工作中也会应用到的三个场景：目录：一.mysql查询时的底层原理是什么？二.如何查看正在执行的mysql语句？三.如何观察mysql运行过程中的日志信息？-----...

取消回复欢迎 你 发表评论:

一周热门
最近发表
标签列表