翼讯自助网站,公司内部网站管理系统,公司做网站的多吗,招生网站模版前言#xff1a;我们都知道#xff0c;binlog可以说是MySQL中比较重要的日志了#xff0c;在日常学习及运维过程中#xff0c;也经常会遇到。不清楚你对binlog了解多少呢#xff1f;本篇文章将从binlog作用、binlog相关参数、解析binlog内容三个方面带你了解binlog。1.bin… 前言我们都知道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模式保存binlogMySQL会根据执行的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# 查看数据库是否开启了binlogshow 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也有很多下面介绍下部分常用的语句SQL语句语句含义show master status查看当前最新的一个binlog日志的编号名称及最后一个事件结束的位置show binary logs查看目前保留的所有binlog日志列表及大小flush logs刷新binlog此刻开始产生一个新编号的binlog日志文件purge binary logs before 2020-07-01 12:00:00手动清理指定时间之前的binlog日志purge binary logs to binlog.000012将指定日志文件之前的日志清除reset master清空所有的binlog日志慎用show binlog events in binlog.000030查看指定的binlog日志eventshow binlog events in binlog.000030 from 931从指定的位置开始查看指定的binlog日志show binlog events in binlog.000030 from 931 limit 2从指定的位置开始查看指定的binlog日志限制查询的enent数 3.解析binlog内容前面说过所有对数据库的修改都会记录在binglog中。但binlog是二进制文件无法直接查看想要更直观的观测它就要借助mysqlbinlog命令工具了下面的内容主要介绍如何使用mysqlbinlog来解析binlog日志内容。为了故事的顺利发展我们首先切换下binlog然后创建测试库、测试表执行插入数据更新数据。这些前置操作暂不展示下面我们来看下如何解析并查看生成的binlog内容# 本次解析基于MySQL8.0版本实例已开启gtid模式为ROW[rootcentos logs]# mysqlbinlog --no-defaults --base64-outputdecode-rows -vv binlog.000013/*!50530 SET SESSION.PSEUDO_SLAVE_MODE1*/;/*!50003 SET OLD_COMPLETION_TYPECOMPLETION_TYPE,COMPLETION_TYPE0*/;......#200708 16:52:09 server id 1003306 end_log_pos 1049 CRC32 0xbcf3de39 Query thread_id85 exec_time0 error_code0 Xid 1514use bindb/*!*/;SET TIMESTAMP1594198329/*!*/;SET session.explicit_defaults_for_timestamp1/*!*/;/*!80013 SET session.sql_require_primary_key0*//*!*/;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)) ENGINEInnoDB DEFAULT CHARSETutf8 COMMENT测试binlog/*!*/;# at 1049#200708 16:52:45 server id 1003306 end_log_pos 1128 CRC32 0xf19ea0a9 GTID last_committed2 sequence_number3 rbr_onlyyes original_committed_timestamp1594198365741300 immediate_commit_timestamp1594198365741300 transaction_length468/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;# original_commit_timestamp1594198365741300 (2020-07-08 16:52:45.741300 CST)# immediate_commit_timestamp1594198365741300 (2020-07-08 16:52:45.741300 CST)/*!80001 SET session.original_commit_timestamp1594198365741300*//*!*/;/*!80014 SET session.original_server_version80019*//*!*/;/*!80014 SET session.immediate_server_version80019*//*!*/;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_id85 exec_time0 error_code0SET TIMESTAMP1594198365/*!*/;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### 11 /* INT meta0 nullable0 is_null0 */### 21001 /* INT meta0 nullable0 is_null0 */### 3from1 /* VARSTRING(60) meta60 nullable1 is_null0 */### 41594198365 /* TIMESTAMP(0) meta0 nullable0 is_null0 */### 51594198365 /* TIMESTAMP(0) meta0 nullable0 is_null0 */### INSERT INTO bindb.bin_tb### SET### 12 /* INT meta0 nullable0 is_null0 */### 21002 /* INT meta0 nullable0 is_null0 */### 3dfsfd /* VARSTRING(60) meta60 nullable1 is_null0 */### 41594198365 /* TIMESTAMP(0) meta0 nullable0 is_null0 */### 51594198365 /* TIMESTAMP(0) meta0 nullable0 is_null0 */...# at 1486#200708 16:52:45 server id 1003306 end_log_pos 1517 CRC32 0x0437e777 Xid 1515COMMIT/*!*/;...# at 1596#200708 16:54:35 server id 1003306 end_log_pos 1681 CRC32 0x111539b6 Query thread_id85 exec_time0 error_code0SET TIMESTAMP1594198475/*!*/;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### 15 /* INT meta0 nullable0 is_null0 */### 21005 /* INT meta0 nullable0 is_null0 */### 3dsfsdg /* VARSTRING(60) meta60 nullable1 is_null0 */### 41594198365 /* TIMESTAMP(0) meta0 nullable0 is_null0 */### 51594198365 /* TIMESTAMP(0) meta0 nullable0 is_null0 */### SET### 15 /* INT meta0 nullable0 is_null0 */### 21005 /* INT meta0 nullable0 is_null0 */### 3NULL /* VARSTRING(60) meta60 nullable1 is_null1 */### 41594198365 /* TIMESTAMP(0) meta0 nullable0 is_null0 */### 51594198475 /* TIMESTAMP(0) meta0 nullable0 is_null0 */### UPDATE bindb.bin_tb### WHERE### 16 /* INT meta0 nullable0 is_null0 */### 21006 /* INT meta0 nullable0 is_null0 */### 3fgd /* VARSTRING(60) meta60 nullable1 is_null0 */### 41594198365 /* TIMESTAMP(0) meta0 nullable0 is_null0 */### 51594198365 /* TIMESTAMP(0) meta0 nullable0 is_null0 */### SET### 16 /* INT meta0 nullable0 is_null0 */### 21006 /* INT meta0 nullable0 is_null0 */### 3NULL /* VARSTRING(60) meta60 nullable1 is_null1 */### 41594198365 /* TIMESTAMP(0) meta0 nullable0 is_null0 */### 51594198475 /* TIMESTAMP(0) meta0 nullable0 is_null0 */...# at 1939#200708 16:54:35 server id 1003306 end_log_pos 1970 CRC32 0x632a82b7 Xid 1516COMMIT/*!*/;SET SESSION.GTID_NEXT AUTOMATIC /* added by mysqlbinlog */ /*!*/;DELIMITER ;# End of log file/*!50003 SET COMPLETION_TYPEOLD_COMPLETION_TYPE*/;/*!50530 SET SESSION.PSEUDO_SLAVE_MODE0*/;# 可以看出binlog中详细记录了每条sql执行产生的变化并且包括执行时间、pos位点、server_id等系统值。关于mysqlbinlog工具的使用技巧还有很多例如只解析对某个库的操作或者某个时间段内的操作等。简单分享几个常用的语句更多操作可以参考官方文档。mysqlbinlog --no-defaults --base64-outputdecode-rows -vv binlog.000013 /tmp/bin13.sql将解析到的SQL导入文件中mysqlbinlog --no-defaults --base64-outputdecode-rows -vv --databasetestdb binlog.000013只解析某个库的操作mysqlbinlog --no-defaults --base64-outputdecode-rows -vv --start-datetime2020-01-11 01:00:00 --stop-datetime2020-01-11 23:59:00 binlog.000008解析指定时间段内的操作mysqlbinlog --no-defaults --base64-outputdecode-rows -vv --start-position204136360 --stop-position204136499 binlog.000008解析指定pos位点内的操作mysqlbinlog --no-defaults --start-position204136360 --stop-position204136499 binlog.000008 | mysql -uroot -pxxxx testdb在指定库中恢复指定位点间的操作总结不知不觉写的挺长了本文讲述了各类binlog相关知识点希望你读完会对binlog有更深的认识。其实最重要的还是实践只有多学多用才能更好的掌握。这么硬核的知识希望大家用到的时候可以拿来读读欢迎各位转发分享让更多人看到。推荐阅读(点击标题可跳转阅读)大数据如何基于 Flink 进行实时计算一文解决MySQL时区相关问题手把手教你看MySQL官方文档— END —如果你喜欢我的文章请在文末右下角点一下在看❤
