1. Binlog二进制日志详解binlog 是MySQLServer层的逻辑日志所有存储引擎共用只记录修改数据的事件DDL/DML 不记录SELECT、SHOW这类查询语句追加写、不会覆盖旧日志核心作用主从复制主库把 binlog 推送给从库从库重放实现数据同步主从、MGR 底层都依赖它MySQL时间点恢复 PIT全量备份 binlog 增量回放恢复到误操作前的任意时刻删表、误更新救命日志MySQLCDC 数据采集Canal、Debezium 监听 binlog做数据同步、数据中台、缓存更新审计追溯记录谁、什么时候改了什么数据三种记录格式binlog_formatMySQL8.0默认**ROW**生产推荐ROW5.7默认MIXEDSTATEMENTSBR语句模式记录原始SQL语句例如 update t set nameawhere id1;✅ 优点日志体积小❌ 缺点不安全主从容易不一致now()、rand()、uuid()、存储过程、limit 随机结果从库重放结果不一样适用极少使用ROWRBR行模式推荐不记录SQL记录每行变更前后镜像比如 update记录id1old_namexxnew_namexx✅ 优点绝对安全主从一致支持闪回binlog2sql❌ 缺点日志量大批量 update 会生成大量行事件IO 变大事件类型WRITE_ROWS_EVENT / UPDATE_ROWS_EVENT / DELETE_ROWS_EVENTMIXED默认用 STATEMENTMySQL 自动识别不安全 SQL自动切换为 ROW 模式记录开启配置my.cnf/my.ini# 开启binlog指定前缀路径不要放数据盘同一块盘 log_bin/var/lib/mysql/binlog/mysql-bin # 日志文件最大大小超过自动滚动新文件 max_binlog_size1G # binlog刷盘策略【核心参数】 sync_binlog1# 日志自动过期天数自动清理 expire_logs_days7#8.0推荐用这个替代expire_logs_days binlog_expire_logs_seconds604800# 记录库可选 binlog_do_dbtest # 忽略库 binlog_ignore_dbmysqlsync_binlog 解释sync_binlog1每次事务提交binlog 立刻 fsync 刷磁盘最安全性能最低金融核心库必选sync_binlog0交给操作系统 pagecache 异步刷盘性能高宕机可能丢日志sync_binlogN累计 N 次事务再刷盘折中方案文件组成mysql-bin.000001、mysql-bin.000002binlog 日志文件序号递增滚动生成mysql-bin.index索引文件记录当前所有 binlog 文件名列表-- 查看是否开启binlogshowvariableslikelog_bin;-- 查看所有binlog文件、大小showbinarylogs;-- 查看当前正在写入的binlog文件和position位点showmasterstatus;-- 刷新binlog生成新文件备份前常用flush logs;-- 清理binlog谨慎-- 删除到指定文件之前purgebinarylogstomysql-bin.000005;-- 删除指定时间之前purgebinarylogs before2026-10-20 10:00:00;-- 重置清空全部binlog主从环境严禁reset master;2 MySQL主备的基本原理如下图1所示就是基本的主备切换流程在状态1中客户端的读写都直接访问节点A而节点B是A的备库只是将A的更新都同步过来到本地执行。这样可以保持节点B和A的数据是相同的。当需要切换的时候就切成状态2。这时候客户端读写访问的都是节点B而节点A是B的备库。在状态1中虽然节点B没有被直接访问但是我依然建议你把节点B也就是备库设置成只读readonly模式。这样做有以下几个考虑1.有时候一些运营类的查询语句会被放到备库上去查设置为只读可以防止误操作2.防止切换逻辑有bug比如切换过程中出现双写造成主备不一致3.可以用readonly状态来判断节点的角色把备库设置成只读了还怎么跟主库保持同步更新呢readonly设置对超级(super)权限用户是无效的而用于同步更新的线程就拥有超级权限。再看看节点A到B这条线的内部流程是什么样的如下主备流程图所示记录了语句在节点A执行然后同步到节点B的完整流程图可以看到主库接收到客户端的更新请求后执行内部事务的更新逻辑同时写binlog。备库B跟主库A之间维持了一个长连接。主库A内部有一个线程专门用于服务备库B的这个长连接。一个事务日志同步的完整过程1.在备库B上通过change master命令设置主库A的IP、端口、用户名、密码以及要从哪个 位置开始请求binlog这个位置包含文件名和日志偏移量。2.在备库B上执行start slave命令这时候备库会启动两个线程就是图中的io_thread和 sql_thread。其中io_thread负责与主库建立连接3.主库A校验完用户名、密码后开始按照备库B传过来的位置从本地读取binlog发给B4.备库B拿到binlog后写到本地文件称为中转日志relay log5.sql_thread读取中转日志解析出日志里的命令并执行2.1 binlog里面到底是什么内容binlog有两种格式一种是statement一种是row。可能其他资料上还会看到有第三种格式叫作mixed其实它就是前两种格式的混合。举例说明CREATETABLEt(idint(11)NOTNULL,aint(11)DEFAULTNULL,t_modifiedtimestampNOTNULLDEFAULTCURRENT_TIMESTAMP,PRIMARYKEY(id),KEYa(a),KEYt_modified(t_modified))ENGINEInnoDB;insertintotvalues(1,1,2018-11-13);insertintotvalues(2,2,2018-11-12);insertintotvalues(3,3,2018-11-11);insertintotvalues(4,4,2018-11-10);insertintotvalues(5,5,2018-11-09);如果要在表中删除一行数据的话看看这个delete语句的binlog是怎么记录的?注意下面这个语句包含注释如果你用MySQL客户端来做这个实验的话要记得加-c参数否则客户端会自动去掉注释。mysqldeletefromt/*comment*/wherea4andt_modified2018-11-10limit1;当binlog_formatstatement时binlog里面记录的就是SQL语句的原文。你可以用命令查看mysqlshowbinlog eventsinmaster.000001;1.第一行SETSESSION.GTID_NEXTANONYMOUS’可以先忽略2.第二行是一个BEGIN跟第四行的commit对应表示中间是一个事务3.第三行就是真实执行的语句了。可以看到在真实执行的delete命令之前还有一个“use ‘test’”命令。这条命令不是我们主动执行的而是MySQL根据当前要操作的表所在的数据库 自行添加的。这样做可以保证日志传到备库去执行的时候不论当前的工作线程在哪个库 里都能够正确地更新到test库的表t。 use test’命令之后的delete 语句就是我们输入的SQL原文了。可以看到binlog“忠实”地记 录了SQL命令甚至连注释也一并记录了。4.最后一行是一个COMMIT。你可以看到里面写着xid61为了说明statement 和 row格式的区别看一下这条delete命令的执行效果图可以看到运行这条delete命令产生了一个warning原因是当前binlog设置的是statement格式并且语句中有limit所以这个命令可能是unsafe的。为什么这么说呢这是因为delete 带limit很可能会出现主备数据不一致的情况。比如上面这个例子1.如果delete语句使用的是索引a那么会根据索引a找到第一个满足条件的行也就是说删除的是a4这一行2.但如果使用的是索引t_modified那么删除的就是 t_modified2018-11-09’也就是a5这一行。由于statement格式下记录到binlog里的是语句原文因此可能会出现这样一种情况在主库执行这条SQL语句的时候用的是索引a而在备库执行这条SQL语句的时候却使用了索引t_modified。因此MySQL认为这样写是有风险的。那么如果把binlog的格式改为binlog_format‘row’ 是不是就没有这个问题了呢看看这时候binog中的内容:可以看到与statement格式的binlog相比前后的BEGIN和COMMIT是一样的。但是row格式的binlog里没有了SQL语句的原文而是替换成了两个eventTable_map和Delete_rows。1.Table_map event用于说明接下来要操作的表是test库的表t;2.Delete_rows event用于定义删除的行为其实通过上图是看不到详细信息的还需要借助mysqlbinlog工具。用下面这个命令解析和查看binlog中的内容图中的信息显示这个事务的binlog是从8900这个位置开始的所以可以用start-position参数来指定从这个位置的日志开始解析。mysqlbinlog-vv data/master.000001--start-position8900;从上图6中可以看到以下几个信息1.server id1表示这个事务是在server_id1的这个库上执行的2.每个event都有CRC32的值这是因为参数binlog_checksum设置成了CRC323.Table_map event跟在图5中看到的相同显示了接下来要打开的表map到数字226。现在我 们这条SQL语句只操作了一张表如果要操作多张表呢每个表都有一个对应的Table_mapevent、都会map到一个单独的数字用于区分对不同表的操作。4.在mysqlbinlog的命令中使用了-vv参数是为了把内容都解析出来所以从结果里面可以 看到各个字段的值比如14、24这些值5.binlog_row_image的默认配置是FULL因此Delete_event里面包含了删掉的行的所有字段 的值。如果把binlog_row_image设置为MINIMAL则只会记录必要的信息在这个例子里 就是只会记录id4这个信息。6.最后的Xidevent用于表示事务被正确地提交了可以看到当binlog_format使用row格式的时候binlog里面记录了真实删除行的主键id这样 binlog传到备库去的时候就肯定会删除id4的行不会有主备删除不同行的问题。2.2 为什么会有mixed格式的binlog1.因为有些statement格式的binlog可能会导致主备不一致所以要使用row格式2.但row格式的缺点是很占空间。比如你用一个delete语句删掉10万行数据用statement的 话就是一个SQL语句被记录到binlog中占用几十个字节的空间。但如果用row格式的binlog 就要把这10万条记录都写到binlog中。这样做不仅会占用更大的空间同时写binlog也要耗 费IO资源影响执行速度。3.所以MySQL就取了个折中方案也就是有了mixed格式的binlog。mixed格式的意思 是MySQL自己会判断这条SQL语句是否可能引起主备不一致如果有可能就用row格式 否则就用statement格式。也就是说mixed格式可以利用statment格式的优点同时又避免了数据不一致的风险。因此如果线上MySQL设置的binlog格式是statement的话那基本上就可以认为这是一个不合理的设置。至少应该把binlog的格式设置为mixed。比如这个例子设置为mixed后就会记录为row格式而如果执行的语句去掉limit 1就会记录为statement格式。现在越来越多的场景要求把MySQL的binlog格式设置成row。这么做的理由有很多给一个可以直接看出来的好处恢复数据接下来我们就分别从delete、insert和update这三种SQL语句的角度来看看数据恢复的问题。通过图6你可以看出来即使我执行的是delete语句row格式的binlog也会把被删掉的行的整行信息保存起来。所以如果你在执行完一条delete语句以后发现删错数据了可以直接把binlog中记录的delete语句转成insert把被错删的数据插入回去就可以恢复了。如果你是执行错了insert语句呢那就更直接了。row格式下insert语句的binlog里会记录所有的字段信息这些信息可以用来精确定位刚刚被插入的那一行。这时你直接把insert语句转成delete语句删除掉这被误插入的一行数据就可以了。如果执行的是update语句的话binlog里面会记录修改前整行的数据和修改后的整行数据。所以如果你误执行了update语句的话只需要把这个event前后的两行信息对调一下再去数据库里面执行就能恢复这个更新操作了。其实由delete、insert或者update语句导致的数据操作错误需要恢复到操作之前状态的情况也时有发生。MariaDB的Flashback工具就是基于上面介绍的原理来回滚数据的。虽然mixed格式的binlog现在已经用得不多了还是要再借用一下mixed格式来说明一个问题来看一下这条SQL语句mysqlinsert into tvalues(10,10,now());如果把binlog格式设置为mixed你觉得MySQL会把它记录为row格式还是statement格式呢来看一下这条语句执行的效果可以看到上图7中MySQL用的居然是statement格式。你一定会奇怪如果这个binlog过了1分钟才传给备库的话那主备的数据不就不一致了吗接下来我们再用mysqlbinlog工具来看看从图8中的结果可以看到原来binlog在记录event的时候多记了一条命令SETTIMESTAMP1546103491。它用 SET TIMESTAMP命令约定了接下来的now()函数的返回时间。因此不论这个binlog是1分钟之后被备库执行还是3天后用来恢复这个库的备份这个insert语句插入的行值都是固定的。也就是说通过这条SET TIMESTAMP命令MySQL就确保了主备数据的一致性。有人在重放binlog数据的时候是这么做的用mysqlbinlog解析出日志然后把里面的statement语句直接拷贝出来执行。现在知道了这个方法是有风险的。因为有些语句的执行结果是依赖于上下文命令的直接执行的结果很可能是错误的。所以用binlog来恢复数据的标准做法是用 mysqlbinlog工具解析出来然后把解析结果整个发给MySQL执行。2.3 循环复制问题通过上面对MySQL中binlog基本内容的理解可以知道binlog的特性确保了在备库执行相同的binlog可以得到与主库相同的状态。因此我们可以认为正常情况下主备的数据是一致的。也就是说图1中A、B两个节点的内容是一致的。其实图1中我画的是M-S结构但实际生产上使用比较多的是双M结构。下图9所示的主备切换流程。对比上图9和图1可以发现双M结构和M-S结构其实区别只是多了一条线即节点A和B之间总是互为主备关系。这样在切换的时候就不用再修改主备关系。但是双M结构还有一个问题需要解决业务逻辑在节点A上更新了一条语句然后再把生成的binlog 发给节点B节点B执行完这条更新语句后也会生成binlog。建议把参数log_slave_updates设置为on表示备库执行relay log后生成binlog。那么如果节点A同时是节点B的备库相当于又把节点B新生成的binlog拿过来执行了一次然后节点A和B间会不断地循环执行这个更新语句也就是循环复制了。这个要怎么解决呢从上面的图6中可以看到MySQL在binlog中记录了这个命令第一次执行时所在实例的server id。因此我们可以用下面的逻辑来解决两个节点间的循环复制的问题1.规定两个库的server id必须不同如果相同则它们之间不能设定为主备关系2.一个备库接到binlog并在重放的过程中生成与原binlog的server id相同的新的binlog3.每个库在收到从自己的主库发过来的日志后先判断server id如果跟自己的相同表示这个日志是自己生成的就直接丢弃这个日志。按照这个逻辑如果我们设置了双M结构日志的执行流就会变成这样1.从节点A更新的事务binlog里面记的都是A的server id2.传到节点B执行一次以后节点B生成的binlog 的server id也是A的server id3.再传回给节点AA判断到这个server id与自己的相同就不会再处理这个日志。所以死循 环在这里就断掉了。今天介绍的可以说是所有MySQL高可用方案的基础。在这之上演化出了诸如多节点、半同步、MySQL group replication等相对复杂的方案。