在软件开发与数据库管理的日常中，程序bug或误执行SQL语句导致的数据错误更新是一个不容忽视的问题。这类问题不仅可能导致业务数据的混乱，还可能对企业造成严重的经济损失。幸运的是，数据库系统为我们提供了一种强大的数据恢复机制——binlog（Binary Log），它能够记录所有修改数据的SQL语句，为我们在数据遭遇不测时提供了一线生机。本文将详细介绍如何通过解析binlog来恢复因程序bug或误执行SQL导致的数据错误更新或删除。binlog基础什么是binlog？binlog是MySQL数据库中的一种二进制日志文件，它记录了数据库中所有修改数据的SQL语句，包括增删改操作。binlog的主要作用是用于数据库的主从复制和数据恢复。binlog的类型MySQL的binlog有三种格式：STATEMENT、ROW和MIXED。STATEMENT：记录每一条会修改数据的SQL语句本身。ROW：记录每一行数据的变化，不记录SQL语句本身。MIXED：默认情况下使用STATEMENT格式，但在某些特殊情况下会自动切换为ROW格式。通过binlog恢复数据理论基础准备工作确认binlog已开启：确保MySQL的binlog功能已开启，且binlog类型为ROW。定位问题时间点：确定误执行的时间范围，以便在binlog中找到相关的记录。误执行的SQL语句：确定误执行的SQL语句，以便在binlog中找到相关的记录。备份当前数据：在进行数据恢复前，务必备份当前的数据状态，以防恢复过程中出现意外。恢复步骤查找binlog文件：根据问题时间点，定位到包含误操作的binlog文件。解析binlog：使用MySQL提供的mysqlbinlog工具解析binlog文件，找到异常的SQL语句。mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000001 > binlog.out在生成的SQL文件中搜索关键词，定位到误执行的SQL语句。编写恢复脚本：根据解析出的binlog内容，编写一个反向操作的SQL脚本，用于撤销误执行的SQL影响的数据修改。例如，如果错误地更新了某个字段的值，可以在恢复脚本中将其改回原来的值。执行恢复脚本：在确保备份了当前数据后，执行恢复脚本。验证恢复结果：检查恢复后的数据是否与预期一致，确保业务数据恢复正常。实战场景一：update语句不加where条件误操作数据恢复事件描述程序BUG导致一张表的所有数据被逻辑删除（is_deleted字段被更新为1），该程序BUG是由于执行了一条UPDATE语句，WHERE条件设置有误引起，如下：UPDATE `test`.`t1` SET is_deleted = 1;根据开发同事的反馈，该SQL是在2025-09-05 14:48左右执行的。诉求恢复程序BUG导致的错误更新is_deleted字段的值。解析binlog根据此前的理论知识，binlog记录每一行数据的变化。那么我们可以通过解析binlog，拿到这条SQL所影响的数据，它记录修改前、后的数据。获取binlog文件：如果是云数据，看不到最新的日志，需要执行：flush logs执行binlog解析命令：因为明确知道SQL的执行时间，我这里加了--start-datetime参数，减少数据量。2025-09-05 14:48左右执行的，我这里往前推了几分钟。因为时区问题，减去8小时。mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS --start-datetime="2025-09-05 06:40:00" mysqlbin.000391 > binlog.out查找关键字UPDATE `test`.`t1`# at 273068250 #250905 6:48:12 server id 414163 end_log_pos 273068548 CRC32 0x06b6ee0e Update_rows: table id 5546 flags: STMT_END_F ### UPDATE `test`.`t1` ### WHERE ### @1=5816 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @2=4956 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @5='风管机' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */ ### @6='FGR3.5Pd/C3(S)Nha-N3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */ ### @7=2.0000 /* DECIMAL(12,4) meta=3076 nullable=1 is_null=0 */ ### @8='套' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */ ### @9=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */ ### @10=2 /* INT meta=0 nullable=0 is_null=0 */ ### @11=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */ ### @12=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */ ### @13=0 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @14='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @15='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @16=19 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @17=19 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @18='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */ ### SET ### @1=5816 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @2=4956 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @5='风管机' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */ ### @6='FGR3.5Pd/C3(S)Nha-N3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */ ### @7=2.0000 /* DECIMAL(12,4) meta=3076 nullable=1 is_null=0 */ ### @8='套' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */ ### @9=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */ ### @10=2 /* INT meta=0 nullable=0 is_null=0 */ ### @11=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */ ### @12=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */ ### @13=1 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @14='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @15='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @16=19 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @17=19 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @18='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */ ### UPDATE `test`.`t1` ### WHERE ### @1=5818 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @2=11309 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @5='风管机' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */ ### @6='FGR3.5Pd/C3(S)Nha-N3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */ ### @7=1.0000 /* DECIMAL(12,4) meta=3076 nullable=1 is_null=0 */ ### @8='套' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */ ### @9=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */ ### @10=2 /* INT meta=0 nullable=0 is_null=0 */ ### @11=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */ ### @12=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */ ### @13=0 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @14='2023-04-14 16:31:43' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @15='2025-05-29 22:12:25' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @16=493 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @17=16 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @18='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */ ### SET ### @1=5818 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @2=11309 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @5='风管机' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */ ### @6='FGR3.5Pd/C3(S)Nha-N3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */ ### @7=1.0000 /* DECIMAL(12,4) meta=3076 nullable=1 is_null=0 */ ### @8='套' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */ ### @9=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */ ### @10=2 /* INT meta=0 nullable=0 is_null=0 */ ### @11=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */ ### @12=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */ ### @13=1 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @14='2023-04-14 16:31:43' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @15='2025-05-29 22:12:25' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @16=493 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @17=16 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @18='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */ …… # at 273068548 #250905 6:48:12 server id 414163 end_log_pos 273068579 CRC32 0xce483344 Xid = 494745070 COMMIT/*!*/;先看一下binlog解析后输出的内容格式：# at 273068250 #250905 6:48:12 server id 414163 end_log_pos 273068548 CRC32 0x06b6ee0e Update_rows: table id 5546 flags: STMT_END_F ### UPDATE `test`.`t1` ### WHERE （旧值，用来定位被更新的行） ### SET （新值，表示更新后的结果） ……（这个事件修改的其他行的内容） # at 273068548 #250905 6:48:12 server id 414163 end_log_pos 273068579 CRC32 0xce483344 Xid = 494745070 COMMIT/*!*/;稍做分析# at 273068250表示这条 binlog 事件的起始位置（offset = 273068250）。这个表有18个字段，字段的位置以@序号的表示方式。我们还需要结合这个表的表结构的字段名和binlog的@序号对应起来。CREATE TABLE `test` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', -- @1 `project_id` bigint(20) DEFAULT NULL COMMENT '项目id', -- @2 `oppty_info_id` bigint(20) DEFAULT NULL COMMENT '商机id', -- @3 `oppty_info_palmtop_id` bigint(20) DEFAULT NULL COMMENT '掌上通id', `device_name` varchar(255) NOT NULL DEFAULT '' COMMENT '分类名称', `device_type` varchar(255) NOT NULL DEFAULT '' COMMENT '设备型号', `price` decimal(12,4) DEFAULT NULL COMMENT '单价(万)', `unit` varchar(100) DEFAULT '' COMMENT '单位', `unit_barcode` text COMMENT '机组条码List,[xxx,xxx]', `qty` int(11) NOT NULL DEFAULT '0' COMMENT '数量', `drawing_no` varchar(32) DEFAULT NULL COMMENT '图号', `tool_no` varchar(100) DEFAULT NULL COMMENT '工装编号', `is_deleted` tinyint(1) DEFAULT '0' COMMENT '删除状态：1-删除 0-未删除', -- @13 `created_time` datetime DEFAULT NULL COMMENT '创建时间', `updated_time` datetime DEFAULT NULL COMMENT '更新时间', `created_by` bigint(20) DEFAULT NULL COMMENT '创建人', `updated_by` bigint(20) DEFAULT NULL COMMENT '更新人', `remark` varchar(255) DEFAULT '' COMMENT '备注', -- @18 PRIMARY KEY (`id`), KEY `idx_oppty_info_id_deleted` (`oppty_info_id`,`is_deleted`) ) ENGINE=InnoDB AUTO_INCREMENT=146778 DEFAULT CHARSET=utf8mb4 COMMENT='设备清单'提取这条SQL的完整binlog内容：sed -n '/# at 273068250/,/COMMIT/p' binlog.out > 273068250.out组装恢复脚本cat 273068250.out | \ sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' | \ sed -r '/SET/{:a;N;/@18/!ba;s/@14.*//g}' | \ sed -r '/SET/{:a;N;/@12/!ba;s/@1.*//g}' | \ sed -r '/WHERE/{:a;N;/@18/!ba;s/@2.*//g}' | \ sed 's/### //g;s/\/\*.*\*\//,/g' | \ sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT.*//g' | \ sed '/^$/d' | \ sed 's/@13/is_deleted/g;s/@1/id/g' | \ sed -r 's/(is_deleted=.*),/\1/g' > 273068250.recovery.sql逐行解释一下：sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}'/WHERE/{ ... }只有匹配到包含 WHERE 的行时，才会执行 {} 里的动作。:a; N; /SET/!ba;这几条是一个 循环结构：:a → 定义一个标签 a。N → 把下一行读进来（追加到 pattern space）。/SET/!ba → 如果当前缓存区里还 没有匹配到 SET，就跳回到 a 标签继续读下一行。作用：一直读，直到读到包含 SET 的那一行，才停止。这样就能保证 pattern space 中存放的是：WHERE ... SETs/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/这是一个替换操作，里面有三个分组：([^\n]*) → 第一行（匹配到换行符前的内容）。(.*) → 第二行到倒数第二行。(.*) → 最后一行。综合起来看，整个命令做的事情就是：找到 WHERE 那一行开始。一直往下读，直到 SET 那一行。然后把这WHERE和SET调换位置。举个例子假设输入：### WHERE ### @1=123 ### @2=456 ### SET ### ...经过这个 sed 命令处理后，会变成：### SET ### @1=123 ### @2=456 ### WHERE ### ...sed -r '/SET/{:a;N;/@18/!ba;s/@14.*//g}'sed -r '/SET/{:a;N;/@12/!ba;s/@1.*//g}'这两行一起看，先看第一行。-r打开 扩展正则（ERE），写法上可以少一些反斜杠。/SET/{ ... }只有匹配到包含 SET 的那一行时，才会执行 {} 里的动作。:a; N; /@18/!ba;这是一个典型的 sed 循环：:a → 定义标签 a。N → 把下一行读入到 pattern space（追加）。/@18/!ba → 如果没有读到包含 @18 的行，就跳回 a 继续读。作用：把从 SET 开始到 @18 行为止的 整个多行内容 都读进来。s/@14.*//g在当前累积的 pattern space 中，匹配从@14开始，后面跟任意字符 .*，并替换为空。作用：删除@14 那行到@18那行的内容。第二行同样的道理，作用是删除@1那行到@12那行的内容。假设输入：### SET ### @1=5816 ### @2=4956 ### @3=0 ### @4=0 ### @5='风管机' ### @6='FGR3.5Pd/C3(S)Nha-N3' ### @7=2.0000 ### @8='套' ### @9=NULL ### @10=2 ### @11=NULL ### @12=NULL ### @13=0 ### @14='2023-04-14 16:18:08' ### @15='2023-04-14 16:18:08' ### @16=19 ### @17=19 ### @18='' ### WHERE ### ...经过这个 sed 命令处理后，会变成：### SET ### @13=0 ### WHERE ### ...sed -r '/WHERE/{:a;N;/@18/!ba;s/@2.*//g}'作用：删除WHERE后面的@2到@18的行，因为WHERE条件只需要id字段，即@1。sed 's/### //g;s/\/\*.*\*\//,/g'作用：### 替换为空，/* ... */替换成逗号。sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT.*//g'作用：@1那一行的,替换成;删除有#号的行，删除COMMIT那一行sed '/^$/d'作用：删除空白行sed 's/@13/is_deleted/g;s/@1/id/g'作用：把@13替换成is_deleted，把@1替换成idsed -r 's/(is_deleted=.*),/\1/g'作用：删除is_deleted那一行后面的,命令组合后的整体效果：假设输入：# at 273068250 #250905 6:48:12 server id 414163 end_log_pos 273068548 CRC32 0x06b6ee0e Update_rows: table id 5546 flags: STMT_END_F ### UPDATE `test`.`t1` ### WHERE ### @1=5816 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @2=4956 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @5='风管机' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */ ### @6='FGR3.5Pd/C3(S)Nha-N3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */ ### @7=2.0000 /* DECIMAL(12,4) meta=3076 nullable=1 is_null=0 */ ### @8='套' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */ ### @9=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */ ### @10=2 /* INT meta=0 nullable=0 is_null=0 */ ### @11=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */ ### @12=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */ ### @13=0 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @14='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @15='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @16=19 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @17=19 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @18='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */ ### SET ### @1=5816 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @2=4956 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @5='风管机' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */ ### @6='FGR3.5Pd/C3(S)Nha-N3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */ ### @7=2.0000 /* DECIMAL(12,4) meta=3076 nullable=1 is_null=0 */ ### @8='套' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */ ### @9=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */ ### @10=2 /* INT meta=0 nullable=0 is_null=0 */ ### @11=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */ ### @12=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */ ### @13=1 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @14='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @15='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @16=19 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @17=19 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @18='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */ # at 273068548 #250905 6:48:12 server id 414163 end_log_pos 273068579 CRC32 0xce483344 Xid = 494745070 COMMIT/*!*/;处理后输出：UPDATE `test`.`t1` SET is_deleted=0 WHERE id=5816 ;我这个案例中，我只需要恢复一个字段（is_deleted），如果你要恢复多个字段，需要改造一下命令。实战场景二：delete语句不加where条件误操作数据恢复事件描述误操作导致一张表的多条数据被物理删除，该操作是由于执行了一条DELETE语句不加WHERE条件，或WHERE条件有误引起。DELETE FROM `test`.`t1`;诉求恢复误操作删除的数据。解析binlogmysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS binlog.000029 > binlog.out查找关键字DELETE FROM `test`.`t1`# at 39574418 #250909 5:32:07 server id 1 end_log_pos 39581451 CRC32 0x239d3070 Delete_rows: table id 102 flags: STMT_END_F ### DELETE FROM `test`.`t1` ### WHERE ### @1=5816 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @2=4956 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @5='风管机' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */ ### @6='FGR3.5Pd/C3(S)Nha-N3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */ ### @7=2.0000 /* DECIMAL(12,4) meta=3076 nullable=1 is_null=0 */ ### @8='套' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */ ### @9=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */ ### @10=2 /* INT meta=0 nullable=0 is_null=0 */ ### @11=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */ ### @12=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */ ### @13=0 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @14='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @15='2023-04-14 16:18:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @16=19 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @17=19 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @18='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */ ### DELETE FROM `test`.`t1` ### WHERE ### @1=5818 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @2=11309 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @4=0 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @5='风管机' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */ ### @6='FGR3.5Pd/C3(S)Nha-N3' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */ ### @7=1.0000 /* DECIMAL(12,4) meta=3076 nullable=1 is_null=0 */ ### @8='套' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */ ### @9=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */ ### @10=2 /* INT meta=0 nullable=0 is_null=0 */ ### @11=NULL /* VARSTRING(128) meta=128 nullable=1 is_null=1 */ ### @12=NULL /* VARSTRING(400) meta=400 nullable=1 is_null=1 */ ### @13=0 /* TINYINT meta=0 nullable=1 is_null=0 */ ### @14='2023-04-14 16:31:43' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @15='2025-05-29 22:12:25' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ### @16=493 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @17=16 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @18='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */ …… # at 39581451 #250909 5:32:07 server id 1 end_log_pos 39581482 CRC32 0x7d7f2b3a Xid = 96546 COMMIT/*!*/;稍做分析# at 39574418表示这条 binlog 事件的起始位置（offset = 39574418）。这个表有18个字段，字段的位置以@序号的表示方式。CREATE TABLE `t1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', -- @1 `project_id` bigint(20) DEFAULT NULL COMMENT '项目id', -- @2 `oppty_info_id` bigint(20) DEFAULT NULL COMMENT '商机id', -- @3 `oppty_info_palmtop_id` bigint(20) DEFAULT NULL COMMENT '掌上通id', `device_name` varchar(255) NOT NULL DEFAULT '' COMMENT '分类名称', `device_type` varchar(255) NOT NULL DEFAULT '' COMMENT '设备型号', `price` decimal(12,4) DEFAULT NULL COMMENT '单价(万)', `unit` varchar(100) DEFAULT '' COMMENT '单位', `unit_barcode` text COMMENT '机组条码List,[xxx,xxx]', `qty` int(11) NOT NULL DEFAULT '0' COMMENT '数量', `drawing_no` varchar(32) DEFAULT NULL COMMENT '图号', `tool_no` varchar(100) DEFAULT NULL COMMENT '工装编号', `is_deleted` tinyint(1) DEFAULT '0' COMMENT '删除状态：1-删除 0-未删除', `created_time` datetime DEFAULT NULL COMMENT '创建时间', `updated_time` datetime DEFAULT NULL COMMENT '更新时间', `created_by` bigint(20) DEFAULT NULL COMMENT '创建人', `updated_by` bigint(20) DEFAULT NULL COMMENT '更新人', `remark` varchar(255) DEFAULT '' COMMENT '备注', -- @18 PRIMARY KEY (`id`), KEY `idx_oppty_info_id_deleted` (`oppty_info_id`,`is_deleted`) ) ENGINE=InnoDB AUTO_INCREMENT=146778 DEFAULT CHARSET=utf8mb4 COMMENT='设备清单'提取这条SQL的完整binlog内容：sed -n '/# at 39574418/,/COMMIT/p' binlog.out > 39574418.out组装恢复脚本cat 39574418.out | \ sed -n '/###/p' | \ sed 's/### //g;s/\/\*.*\*\//,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g' | \ sed -r 's/(@18.*),/\1;/g' | \ sed -r 's/@[0-9]+=//g' > 39574418.recovery.sql这个sed还算容易理解，经过处理后的内容如下：INSERT INTO `test`.`t1` SELECT 5816 , 4956 , 0 , 0 , '风管机' , 'FGR3.5Pd/C3(S)Nha-N3' , 2.0000 , '套' , NULL , 2 , NULL , NULL , 0 , '2023-04-14 16:18:08' , '2023-04-14 16:18:08' , 19 , 19 , '' ; INSERT INTO `test`.`t1` SELECT 5818 , 11309 , 0 , 0 , '风管机' , 'FGR3.5Pd/C3(S)Nha-N3' , 1.0000 , '套' , NULL , 2 , NULL , NULL , 0 , '2023-04-14 16:31:43' , '2025-05-29 22:12:25' , 493 , 16 , '' ; ……觉得太复杂了？来点简单的上面的操作太复杂了，有没有易用的工具呢？在github上找到这两个项目，可以大大简化操作。binlog2sqlbinlog2sql使用Python写的项目，多年没有更新了，可能对MySQL 8.0支持有限。binlog2sql_gobinlog2sql_go使用Go写的项目，此项目使用的人不多，可能有BUG，需要你自行测试功能。使用限制必须连接MySQL Server，因为字段名是从Server上获取的。解析本地binlog，也是需要连接数据库的。因为字段名是从Server上获取的，所以如果表结构有变动的话（特别是新增列和删除列），会导致生成的SQL不对或报错。
查询sql_mode的方法#查询全局sql_mode SELECT @@GLOBAL.sql_mode; #查询当前会话sql_mode SELECT @@SESSION.sql_mode;MySQL 5.6 的默认 sql_mode：STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONMySQL 5.7 的默认 sql_mode：ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTIONMySQL 8.0 的默认 sql_mode：ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTIONsql_mode 说明ONLY_FULL_GROUP_BY 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中。 NO_AUTO_VALUE_ON_ZERO 该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，该列又是自增长的，那么这个选项就有用了。 STRICT_TRANS_TABLES 在该模式下，如果一个值不能插入到一个事物表中，则中断当前的操作，对非事物表不做限制 NO_ZERO_IN_DATE 在严格模式下，不允许日期和月份为零 NO_ZERO_DATE 设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。 ERROR_FOR_DIVISION_BY_ZERO 在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL NO_AUTO_CREATE_USER 禁止GRANT创建密码为空的用户 NO_ENGINE_SUBSTITUTION 如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常 PIPES_AS_CONCAT 将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似 ANSI_QUOTES 启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符 HIGH_NOT_PRECEDENCE NOT运算符的优先级使得诸如 NOT a BETWEEN b AND c 之类的表达式被解析为NOT（a BETWEEN b AND c） mysql> SET sql_mode = ''; mysql> SELECT NOT 1 BETWEEN -5 AND 5; -> 0 mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE'; mysql> SELECT NOT 1 BETWEEN -5 AND 5; -> 1 IGNORE_SPACE 允许函数名和括号【（】之间的空格。这会导致内置函数名被视为保留字。 IGNORE_SPACE SQL模式适用于内置函数，而不适用于用户定义的函数或存储函数。 无论是否启用IGNORE_SPACE，始终允许在UDF或存储的函数名后面包含空格。 mysql> CREATE TABLE count (i INT); ERROR 1064 (42000): You have an error in your SQL syntax mysql> CREATE TABLE `count` (i INT); Query OK, 0 rows affected (0.00 sec)
概述关于XtraBackupXtraBackup是由Percona公司开发的一款基于InnoDB的MySQL数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对InnoDB和XtraDB数据库进行热备的工具。其有如下特点：备份过程快速、可靠；备份过程不会打断正在执行的事务；能够基于压缩等功能节约磁盘空间和流量；自动实现备份检验；还原速度快；XtraBackup功能热备份增量备份备份到另一台MySQL服务器在MySQL服务器之间在线迁移表轻松创建新的从服务器备份时，不占用服务器资源XtraBackup工作原理XtraBackup是基于InnoDB的crash恢复功能。复制InnoDB数据文件，但是数据是不一致的，然后使用crash恢复让数据文件一致。当InnoDB启动时会去检查数据文件和日志文件，然后重做已提交事务，执行未提交事务。XtraBackup记下LSN（Log Sequence Number），然后启动，复制数据文件。同时XtraBackup启动一个后台进程用来监控日志文件，然后复制修改，这个进程在备份期间一直是运行的，因为日志文件时回绕的，避免数据被覆盖无法恢复。直到备份完成。第二阶段就是预备阶段，XtraBackup通过执行crash恢复，应用日志文件到数据文件上。等待复制InnoDB结束，然后执行FLUSH TABLES WITH READ LOCK，停止对mysql数据的修改。复制非innodb引擎表，直到复制完成，然后释放锁。这样在prepare阶段后，InnoDB和非InnoDB相互保持了一致性。InnoDB会一直redo，直到备份完成。这个时间刚刚好和FLUSH TABLES WITH READ LOCK时间一直，所以InnoDB和非InnoDB是保持同步的。工作原理图示：备份过程：恢复过程：增量备份过程：增量备份恢复过程1：增量备份恢复过程2：安装XtraBackup介绍yum的安装方法，更多安装方法参考官方文档Installing Percona XtraBackup 2.4安装Percona官方提供yum源yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm # 应该会看到以下输出： Retrieving http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm Preparing... ######################### [100%] 1:percona-release ######################### [100%]安装percona-xtrabackupyum install percona-xtrabackup-24备份前的准备权限xtrabackup需要连接到数据库和datadir操作权限。xtrabackup或者innobackupex在使用过程中设计到2类用户权限：系统用户，用来调用innobackupex或者xtrabackup数据库用户，数据库内使用的用户连接innobackupex或者xtrabackup通过--user和--password连接到数据库服务# 备份到指定目录 xtrabackup --user=DVADER --password=14MY0URF4TH3R --backup --target-dir=/data/bkps/ # 同上 innobackupex --user=DBUSER --password=SECRET /path/to/backup/dir/ # 压缩保存 innobackupex --user=LUKE --password=US3TH3F0RC3 --stream=tar ./ | bzip2 -其他连接选项选项描述--port指定连接的端口--socket指定连接socket--host指定连接的主机3.4 MySQL账号权限RELOAD和LOCK TABLES权限为了执行FLUSH TABLES WITH READ LOCKREPLICATION CLIENT为了获取binary log 位置CREATE TABLESPACE权限为了导入表，用户表级别的恢复PROCESS 权限为了执行 SHOW ENGINE INNODB STATUSSUPER 权限在slave环境下备份用来启动和关闭slave线程CREATE权限为了创建PERCONA_SCHEMA.xtrabackup_history 数据库和表INSERT权限为了写入历史记录到xtrabackup_history表SELECT权限为了查询xtrabackup_history表创建一个最小权限账号mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret'; mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON . TO 'bkpuser'@'localhost'; mysql> FLUSH PRIVILEGES;配置XtraBackup（可选）编辑my.cnf，加入[xtrabackup] target_dir = /data/backups/mysql/添加后，执行xtrabackup命令时，--target-dir参数就可以不写了xtrabackup与innobackupex在 XtraBackup 2.2 及之前的版本，innobackupex 是 perl 脚本对 xtrabackup 命令的封装和功能扩展。在 XtraBackup 2.3 及以后的版本中，innobackupex 仅仅是 xtrabackup 的一个软链接，但是为了兼容之前的版本，仍然保留 innobackupex 命令的用法。全备的实现创建全备使用xtrabackup命令想要创建一个全备，执行xtrabackup 命令带上--backup选项，还必须指定--target-dir选项指定备份存放目录。当--target-dir指定的路径不存在或为空目录时，程序会自动创建，当指定是一个为非空目录时，会报一个错误file exists。$ xtrabackup --backup --target-dir=/data/backups/base此外，如果访问 MySQL 需要账号密码，你应该还要加上--user=USER --password=PASSWORD，如果提示 socket 找不到，应该还要加上--socket=/PATH/TO/SOCKET选项当成功执行，会得到以下结果：160906 10:19:17 Finished backing up non-InnoDB tables and files 160906 10:19:17 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '62988944' xtrabackup: Stopping log copying thread. .160906 10:19:18 >> log scanned up to (137343534) 160906 10:19:18 Executing UNLOCK TABLES 160906 10:19:18 All tables unlocked 160906 10:19:18 Backup created in directory '/data/backups/base' 160906 10:19:18 [00] Writing backup-my.cnf 160906 10:19:18 [00] ...done 160906 10:19:18 [00] Writing xtrabackup_info 160906 10:19:18 [00] ...done xtrabackup: Transaction log of lsn (26970807) to (137343534) was copied. 160906 10:19:18 completed OK!或者使用innobackupex命令$ innobackupex /data/backups/base默认情况下，使用 innobackupex 命令，将会在目标目录下新建一个以当前日期命名的目录，并将备份文件存放到这个新建的目录下。命令执行成功后，将会得到以下结果innobackupex: Backup created in directory '/data/backups/2017-08-09_00-00-19' innobackupex: MySQL binlog position: filename 'mysql-bin.000003', position 1946 111225 00:00:53 innobackupex: completed OK!可以看到备份目录在/data/backups/2017-08-09_00-00-19全备创建完成后，备份文件夹下的xtrabackup-checkpoints文件会记录以下信息：backup_type = full-backuped from_lsn =0 to_lsn =1291135其中backup_type = full-backuped表示这个备份为全备其他选项：--no-timestamp，指定了这个选项备份会直接备份在 BACKUP-DIR，不再创建时间戳文件夹，建议使用此选项。--default-file，指定配置文件，用来配置 innobackupex 的默认参数。使用全备还原数据一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。需要执行“准备”动作。准备一个完全备份“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。使用xtrabackup命令$ xtrabackup --prepare --target-dir=/data/backups/base执行成功，得到以下输出：InnoDB: Shutdown completed; log sequence number 137345046 160906 11:21:01 completed OK!或者使用innobackupex命令$ innobackupex --apply-log /data/backups/base执行成功，得到以下输出：InnoDB: Shutdown completed; log sequence number 137345046 160906 11:21:01 completed OK!从一个准备好的完全备份恢复数据准备好备份之后，就可以使用备份来还原了第一步：停止数据库$ mysqladmin shutdown第二步：删除数据库所有文件、binlog、undolog、ibdata、innodb_log$ rm -fr /home/mysql/data/{binlog,innodb_data,my_data,innodb_undo,my_data}/*第三步：恢复数据使用xtrabackup命令$ xtrabackup --copy-back --target-dir=/data/backups/base或者使用innobackupex命令$ innobackupex --copy-back /data/backups/base第四步：修改权限$ chown -R mysql.mysql /home/mysql/data/第五步：启动MySQL服务$ service mysql start完成后，得到以下输出：innobackupex: Finished copying back files. 111225 01:08:13 innobackupex: completed OK!增备的实现创建全备要想创建一个增备，必须先创建一个全备。参考前面章节$ xtrabackup --backup --target-dir=/data/backups/base或者$ innobackupex --no-timestamp /data/backups/base创建第一个增备$ xtrabackup --backup --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/base或者$ innobackupex --no-timestamp --incremental /data/backups/inc1 --incremental-basedir=/data/backups/base增备创建完成后，备份文件夹下的xtrabackup-checkpoints文件会记录以下信息：backup_type = incremental from_lsn =0 to_lsn =1291135其中backup_type = incremental表示这个备份为增备创建第二个增备$ xtrabackup --backup --target-dir=/data/backups/inc2 --incremental-basedir=/data/backups/inc1或者$ innobackupex –no-timestamp --incremental /data/backups/inc2 --incremental-basedir=/data/backups/inc1使用增备还原数据经过上面的全备和增备过程，得到的目录结构如下/data/backups/base /data/backups/inc1 /data/backups/inc2准备$ xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base或者$ innobackupex --apply-log --redo-lonly /data/backups/base执行成功，得到以下输出：InnoDB: Shutdown completed; log sequence number 137345046 160906 11:21:01 completed OK!将第一个增备应用到全备$ xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base --incremental-dir=/data/backups/inc1或者$ innobackupex --apply-log --redo-only /data/backups/base --incremental-dir=/data/backups/inc1将中间的增备都要应用...将最后一个增备应用到全备$ xtrabackup --prepare --target-dir=/data/backups/base --incremental-dir=/data/backups/inc2或者$ innobackupex --apply-log /data/backups/base --incremental-dir=/data/backups/inc2这时，将会得到一个完整的备份。恢复数据恢复步骤和第四章节的一样，必须得先停止数据库，存放数据的目录为空。详情参考第4章节。$ xtrabackup --copy-back /data/backups/base或者$ innobackupex --copy-back /data/backups/base恢复完成后，还要修改目录权限才能启动MySQL服务，第四章节有详细的描述。压缩备份创建压缩备份使用 xtrabackup --compress选项创建一个压缩备份$ xtrabackup --backup --compress --target-dir=/data/compressed/还可以限制压缩所使用的线程数$ xtrabackup --backup --compress --compress-threads=4 --target-dir=/data/compressed/准备一个压缩备份$ xtrabackup --decompress --target-dir=/data/compressed/ $ xtrabackup --prepare --target-dir=/data/compressed/经过这个步骤，压缩备份被解压并准备好了，可以直接用于数据还原。还原数据$ xtrabackup --copy-back --target-dir=/data/compressed/
PS：今天一同事问我有木有比较靠谱的mysql压力测试工具可用。其实mysql自带就有一个叫mysqlslap的压力测试工具，还是模拟的不错的。下面举例说说。mysqlslap是从5.1.4版开始的一个MySQL官方提供的压力测试工具。通过模拟多个并发客户端访问MySQL来执行压力测试，同时详细的提供了“高负荷攻击MySQL”的数据性能报告。并且能很好的对比多个存储引擎在相同环境下的并发压力性能差别。通过mysqlslap –help可以获得可用的选项，这里列一些主要的参数，更详细的说明参考官方手册。如果是系统自带或者使用rpm包安装的mysql，安装了MySQL-client端的包就有mysqlslap这个工具。下图是运行mysqlslap -a -c 500 -i 10 -uroot -p123456测试时mysql的连接进程数：使用语法如下：mysqlslap [options]常用参数 [options] 详细说明：--auto-generate-sql, -a 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力。--auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)。--auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持。--number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1--number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1--number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)--query=name,-q 使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。--create-schema 代表自定义的测试库名称，测试的schema，MySQL中schema也就是database。--commint=N 多少条DML后提交一次。--compress, -C 如果服务器和客户端支持都压缩，则压缩信息传递。--concurrency=N, -c N 表示并发量，也就是模拟多少个客户端同时执行select。可指定多个值，以逗号或者--delimiter参数指定的值做为分隔符。例如：--concurrency=100,200,500。--engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开。例如：--engines=myisam,innodb。--iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次。--only-print 只打印测试语句而不实际执行。--detach=N 执行N条语句后断开重连。--debug-info, -T 打印内存和CPU的相关信息。说明：测试的过程需要生成测试表，插入测试数据，这个mysqlslap可以自动生成，默认生成一个mysqlslap的schema，如果已经存在则先删除。可以用--only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹。各种测试参数实例（-p后面跟的是mysql的root密码）：单线程测试。测试做了什么。mysqlslap -a -uroot -p123456多线程测试。使用–concurrency来模拟并发连接。mysqlslap -a -c 100 -uroot -p123456迭代测试。用于需要多次执行测试得到平均值。mysqlslap -a -i 10 -uroot -p123456 mysqlslap ---auto-generate-sql-add-autoincrement -a -uroot -p123456 mysqlslap -a --auto-generate-sql-load-type=read -uroot -p123456 mysqlslap -a --auto-generate-secondary-indexes=3 -uroot -p123456 mysqlslap -a --auto-generate-sql-write-number=1000 -uroot -p123456 mysqlslap --create-schema world -q "select count(*) from City" -uroot -p123456 mysqlslap -a -e innodb -uroot -p123456 mysqlslap -a --number-of-queries=10 -uroot -p123456测试同时不同的存储引擎的性能进行对比：mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -p123456执行一次测试，分别50和100个并发，执行1000次总查询：mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -p12345650和100个并发分别得到一次测试结果(Benchmark)，并发数越多，执行完所有查询的时间越长。为了准确起见，可以多迭代测试几次:mysqlslap -a --c oncurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -p123456
1. 设置localhost的访问密码 $ mysqladmim -uroot -hlocalhost -p password '123456' 2. 设置已存在的用户的密码 mysql> set password for 'USERNAME'@'HOST'=password('password'); 3. 设置已存在的用户的密码 mysql> update user set Password=password(password') where User='USERNAME' and Host='HOST'; 4. 给远程用户设置密码 mysql> grant all privileges on *.* to 'root'@'192.168.31.%' identified by '123456';mysql> flush privileges;
默认情况下，mysql5.1/5.5的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库，这一动作就结束了，并不会验证是否接受完毕，但这同时也带来了很高的风险，这就意味着当主服务器发送故障时，有可能从机没有接受到主机发过来的binlog日志，会造成主服务器/从服务器的数据不一致，甚至在恢复时会造成数据丢失。为了解决这个问题，mysql5.5引入了一种半同步复制模式，该模式可以确保从服务器接受完主服务器发送的binlog日志并写入自己的中继日志relay log里，然后会给主服务器一个反馈，告诉对方已经接收完毕，这时主库线程才返回当前session告知操作完成。当出现超市情况时，源主服务器会暂时切换到异步复制模式，知道至少有一台设置为半同步模式的从服务器及时收到信息为止。半同步复制安装配置插件路径：/usr/local/mysql/lib/plugin/semisync_master.so、semisync_slave.somaster：1. 安装插件：mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 2. 启动模块：mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1; 3. 设置超时时间：mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000； my.cnf rpl_semi_sync_master_enabled = 1 slave：1. 安装插件：msyql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; 2. 启动模块：mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1; 3. 重启进程使其模块生效：mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD; my.cnf rpl_semi_sync_slave_enabled = 1
MySQL常见错误 -- update忘加where条件误操作恢复（模拟Oracle闪回功能）
开启binlog，且binlog-format=row新建一个表[sql]CREATE TABLE student (id int(10) unsigned NOT NULL AUTO_INCREMENT,name varchar(10) NOT NULL DEFAULT '', class int(10), score varchar(10) NOT NULL DEFAULT '', PRIMARY KEY (id));[/sql]插入测试数据：insert into student(name,class,score) values('a',1,56),('b',1,61),('c',2,78),('d',2,45),('e',3,76),('f',3,89),('g',4,43),('h',4,90);模拟update忘加where条件update student set score='failure';mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS bin-log.000039 | grep -B 15 'failure'/*!*/; # at 8513042 #160323 9:50:53 server id 1 end_log_pos 8513098 CRC32 0xb96214ed Table_map: `abc`.`student` mapped to number 151 # at 8513098 #160323 9:50:53 server id 1 end_log_pos 8513398 CRC32 0xf3c6e6c0 Update_rows: table id 151 flags: STMT_END_F ### UPDATE `abc`.`student` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='a' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ ### @4='56' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='a' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ ### @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### UPDATE `abc`.`student` ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='b' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ ### @4='61' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### SET ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='b' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ ### @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### UPDATE `abc`.`student` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='c' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=2 /* INT meta=0 nullable=1 is_null=0 */ ### @4='78' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### SET ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='c' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=2 /* INT meta=0 nullable=1 is_null=0 */ ### @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### UPDATE `abc`.`student` ### WHERE ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2='d' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=2 /* INT meta=0 nullable=1 is_null=0 */ ### @4='45' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### SET ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2='d' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=2 /* INT meta=0 nullable=1 is_null=0 */ ### @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### UPDATE `abc`.`student` ### WHERE ### @1=5 /* INT meta=0 nullable=0 is_null=0 */ ### @2='e' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=3 /* INT meta=0 nullable=1 is_null=0 */ ### @4='76' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### SET ### @1=5 /* INT meta=0 nullable=0 is_null=0 */ ### @2='e' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=3 /* INT meta=0 nullable=1 is_null=0 */ ### @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### UPDATE `abc`.`student` ### WHERE ### @1=6 /* INT meta=0 nullable=0 is_null=0 */ ### @2='f' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=3 /* INT meta=0 nullable=1 is_null=0 */ ### @4='89' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### SET ### @1=6 /* INT meta=0 nullable=0 is_null=0 */ ### @2='f' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=3 /* INT meta=0 nullable=1 is_null=0 */ ### @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### UPDATE `abc`.`student` ### WHERE ### @1=7 /* INT meta=0 nullable=0 is_null=0 */ ### @2='g' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=4 /* INT meta=0 nullable=1 is_null=0 */ ### @4='43' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### SET ### @1=7 /* INT meta=0 nullable=0 is_null=0 */ ### @2='g' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=4 /* INT meta=0 nullable=1 is_null=0 */ ### @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### UPDATE `abc`.`student` ### WHERE ### @1=8 /* INT meta=0 nullable=0 is_null=0 */ ### @2='h' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=4 /* INT meta=0 nullable=1 is_null=0 */ ### @4='90' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### SET ### @1=8 /* INT meta=0 nullable=0 is_null=0 */ ### @2='h' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=4 /* INT meta=0 nullable=1 is_null=0 */ ### @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS bin-log.000039 | sed -n '/# at 8513098/,/COMMIT/p' > ~/1.txt sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' ~/1.txt | sed -r '/WHERE/{:a;N;/@4/!ba;s/@2.*//g}' | sed 's/### //g;s/\/\*.*\*\//,/g' | sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT.*//g' | sed '/^$/d' | sed 's/@1/id/g;s/@2/name/g;s/@3/class/g;s/@4/score/g' | sed -r 's/(score=.*),/\1/g' > ~/recovery.sqlsed解释sed -n '/# at 8513098/,/COMMIT/p' #打印# at 8513098到COMMIT之间的行 sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' #匹配WHERE，如果匹配成功执行{}的命令 #\([^\n]*\)\n\(.*\)\n\(.*\)/ #|————————| #第一个回车之前的，就是WHERE #@@@@@@@@@@ #\n #@@@@@@@@@@@@|—————| #.*最大匹配，一直到最后一个\n之前，也就是\nSET之前的 #@@@@@@@@@@@@@@@@@@@ #\n #@@@@@@@@@@@@@@@@@@@@@—————— #最后一行是SET #\3\n\2\n\1 1和3调换 sed -r '/WHERE/{:a;N;/@4/!ba;s/@2.*//g}' #将@2到@4的内容删除 sed 's/### //g;s/\/\*.*\*\//,/g' #删除### #删除/*...*/ sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT.*//g' #在WHERE\n @1=1 ,后面加; #删除COMMIT所在行 sed '/^$/d' #删除空行 sed 's/@1/id/g;s/@2/name/g;s/@3/class/g;s/@4/score/g' #替换以下字段 #@1 => id #@2 => name #@3 => class #@4 => score sed -r 's/(score=.*),/\1/g' #删除score那行后面的,cat ~/recovery.sqlUPDATE `abc`.`student` SET id=1 , name='a' , class=1 , score='56' WHERE id=1 ; UPDATE `abc`.`student` SET id=2 , name='b' , class=1 , score='61' WHERE id=2 ; UPDATE `abc`.`student` SET id=3 , name='c' , class=2 , score='78' WHERE id=3 ; UPDATE `abc`.`student` SET id=4 , name='d' , class=2 , score='45' WHERE id=4 ; UPDATE `abc`.`student` SET id=5 , name='e' , class=3 , score='76' WHERE id=5 ; UPDATE `abc`.`student` SET id=6 , name='f' , class=3 , score='89' WHERE id=6 ; UPDATE `abc`.`student` SET id=7 , name='g' , class=4 , score='43' WHERE id=7 ; UPDATE `abc`.`student` SET id=8 , name='h' , class=4 , score='90' WHERE id=8 ;mysql> select * from student; +----+------+-------+---------+ | id | name | class | score | +----+------+-------+---------+ | 1 | a | 1 | failure | | 2 | b | 1 | failure | | 3 | c | 2 | failure | | 4 | d | 2 | failure | | 5 | e | 3 | failure | | 6 | f | 3 | failure | | 7 | g | 4 | failure | | 8 | h | 4 | failure | +----+------+-------+---------+ 8 rows in set (0.00 sec) mysql> source ~/recovery.sql Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> select * from student; +----+------+-------+-------+ | id | name | class | score | +----+------+-------+-------+ | 1 | a | 1 | 56 | | 2 | b | 1 | 61 | | 3 | c | 2 | 78 | | 4 | d | 2 | 45 | | 5 | e | 3 | 76 | | 6 | f | 3 | 89 | | 7 | g | 4 | 43 | | 8 | h | 4 | 90 | +----+------+-------+-------+ 8 rows in set (0.00 sec)
开启binlog，且binlog-format=row新建一个表CREATE TABLE `student` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(10) NOT NULL DEFAULT '', `class` int(10), `score` varchar(10) NOT NULL DEFAULT '', PRIMARY KEY (`id`));插入测试数据insert into student(name,class,score) values('a',1,56),('b',1,61),('c',2,78),('d',2,45),('e',3,76),('f',3,89),('g',4,43),('h',4,90);模拟删除mysql> delete from student; Query OK, 8 rows affected (0.00 sec) mysql> select * from student; Empty set (0.00 sec)看一下删除命令的binlog POSITIONmysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS bin-log.000039 | grep -B 10 "DELETE"# at 8515372 #160323 17:16:31 server id 1 end_log_pos 8515519 CRC32 0xb503c687 Delete_rows: table id 151 flags: STMT_END_F ### DELETE FROM `abc`.`student` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='a' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ ### @4='56' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */导出binlogmysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS bin-log.000039 | sed -n '/# at 8515372/,/COMMIT/p' > ~/delete.txtcat ~/delete.txt# at 8515372 #160323 17:16:31 server id 1 end_log_pos 8515519 CRC32 0xb503c687 Delete_rows: table id 151 flags: STMT_END_F ### DELETE FROM `abc`.`student` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='a' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ ### @4='56' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### DELETE FROM `abc`.`student` ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='b' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ ### @4='61' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### DELETE FROM `abc`.`student` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='c' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=2 /* INT meta=0 nullable=1 is_null=0 */ ### @4='78' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### DELETE FROM `abc`.`student` ### WHERE ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2='d' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=2 /* INT meta=0 nullable=1 is_null=0 */ ### @4='45' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### DELETE FROM `abc`.`student` ### WHERE ### @1=5 /* INT meta=0 nullable=0 is_null=0 */ ### @2='e' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=3 /* INT meta=0 nullable=1 is_null=0 */ ### @4='76' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### DELETE FROM `abc`.`student` ### WHERE ### @1=6 /* INT meta=0 nullable=0 is_null=0 */ ### @2='f' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=3 /* INT meta=0 nullable=1 is_null=0 */ ### @4='89' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### DELETE FROM `abc`.`student` ### WHERE ### @1=7 /* INT meta=0 nullable=0 is_null=0 */ ### @2='g' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=4 /* INT meta=0 nullable=1 is_null=0 */ ### @4='43' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### DELETE FROM `abc`.`student` ### WHERE ### @1=8 /* INT meta=0 nullable=0 is_null=0 */ ### @2='h' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3=4 /* INT meta=0 nullable=1 is_null=0 */ ### @4='90' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ # at 8515519 #160323 17:16:31 server id 1 end_log_pos 8515550 CRC32 0xbe922c5c Xid = 416140 COMMIT/*!*/;输出sql语句sed -n '/###/p' ~/delete.txt | sed 's/### //g;s/\/\*.*\*\//,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g' | sed -r 's/(@4.*),/\1;/g' | sed 's/@[1-9]=//g' > ~/insert.sql cat ~/insert.sqlINSERT INTO `abc`.`student` SELECT 1 , 'a' , 1 , '56' ; INSERT INTO `abc`.`student` SELECT 2 , 'b' , 1 , '61' ; INSERT INTO `abc`.`student` SELECT 3 , 'c' , 2 , '78' ; INSERT INTO `abc`.`student` SELECT 4 , 'd' , 2 , '45' ; INSERT INTO `abc`.`student` SELECT 5 , 'e' , 3 , '76' ; INSERT INTO `abc`.`student` SELECT 6 , 'f' , 3 , '89' ; INSERT INTO `abc`.`student` SELECT 7 , 'g' , 4 , '43' ; INSERT INTO `abc`.`student` SELECT 8 , 'h' , 4 , '90' ;导入SQLmysql> source ~/insert.sql Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from student; +----+------+-------+-------+ | id | name | class | score | +----+------+-------+-------+ | 1 | a | 1 | 56 | | 2 | b | 1 | 61 | | 3 | c | 2 | 78 | | 4 | d | 2 | 45 | | 5 | e | 3 | 76 | | 6 | f | 3 | 89 | | 7 | g | 4 | 43 | | 8 | h | 4 | 90 | +----+------+-------+-------+ 8 rows in set (0.00 sec)
#!/bin/bash ######################################################################## ## ##此脚本是用来自动处理同步报错的，默认跳过10次。 ## ##只有Last_SQL_Error:Could not execute Delete_rows event on table ...;Can't find record ## in ...;Error_code:1032;handler error HA_ERR_KEY_NOT_FOUND;the event's master log ## bin-log.000001.end_log_pos ... ##这种情况才跳过，其他的情况自行处理，以免丢失数据。 ## ######################################################################### export LANG=zh_CN . /root/.bash_profile v_dir=/usr/local/mysql/bin/ v_user=root v_password= v_log=/home/logs/ v_time=10 if [ -d "${v_log}" ];then echo "${v_log} has existed before." else mkdir ${v_log} fi echo "" > ${v_log}/slave_status.log echo "" > ${v_log}/slave_status_error.log count=1 while true do Seconds_Behind_Master=$(${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" | awk -F':' '/Seconds_Behind_Master/{print $2}') if [ "$Seconds_Behind_Master" != "NULL" ];then echo "slave is ok!" ${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" >> ${v_log}/slave_status.log break else echo "" >> ${v_log}/slave_status_error.log date >> ${v_log}/slave_status_error.log echo "" >> ${v_log}/slave_status_error.log ${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" >> ${v_log}/slave_status_error.log ${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" | egrep 'Delete_rows' > /dev/null 2>&1 if [ $? = 0 ];then ${v_dir}/mysql -u${v_user} -p${v_password} -e "stop slave;set global sql_slave_skip_counter=1;start slave;" else ${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" | grep 'Last_SQL_Error' break fi let count++ if [ $count -gt "${v_times}" ];then break else ${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" >> ${v_log}/slave_status_error.log sleep 2 continue fi fi done
主从数据不一致时，slave上已经有该条记录，但我们又在master上插入了同一条记录，此时就会报错，报错信息如下：Last_SQL_Error:Could not execute Write_rows event on table ...;Duplicater entry '2' for key 'PRIMARY',Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;the event's master log bin-log.000006, end_log_pos 924解决方法：在slave上使用命令“desc abc.student;”先查看一下表结构，如下所示：mysql> desc abc.student; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | | | | class | int(10) | YES | | NULL | | | score | varchar(10) | NO | | | | +-------+------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec)得到主键的字段名id，接着删除重复的主键，命令如下：delete from student where id=2;开启主从复制：start slave; show slave status\G;