Mysql__运用binlog日志与mysqldump备份工具恢复删除数据
目前该二进制日志中DML语句被隐藏，我们需要在做一步，该步骤是对二进制信息进行映射。3、权限：root（慎用，建议使用普通管理员权限 使用sudo进行操作）stu表里演示添加1000条数据，这里使用AI进行语句创建，为方便测试。观察日志信息得知，删除操作被记录，定位到该位置，删除如下段落。3、在ubuntu上创建库和表（省略语句）这里我们删除id为400-500的数据。>>>>>.........
·
目录
八、将db1.sql和binlog2.sql发送到远程ubuntu2204服务器上
一、实验环境
1、两台测试机:
rocky linux_8（主）
ubuntu_2204（测）
2、mysql版本
3、权限：root（慎用，建议使用普通管理员权限 使用sudo进行操作）
二、创建库、表
1、在rock linux 8上创建库：
create database db1;
2、在rock linux 8上创建表：
CREATE TABLE stu (
id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
name varchar(20) NOT NULL,
age tinyint unsigned DEFAULT NULL,
phone varchar(11) DEFAULT NULL,
gender enum('M','W') DEFAULT 'M',
tea_id int unsigned default null,
beizhu varchar(50) default null
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3、在ubuntu上创建库和表（省略语句）
4、在rocky linux中增加db1库里数据
stu表里演示添加1000条数据，这里使用AI进行语句创建，为方便测试
DELIMITER $$
CREATE PROCEDURE generate_stu_data()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE gender_val ENUM('M','W');
DECLARE name_val VARCHAR(20);
DECLARE age_val TINYINT;
DECLARE phone_val VARCHAR(11);
WHILE i < 1000 DO
-- 随机生成性别
SET gender_val = ELT(FLOOR(1 + RAND() * 2), 'M', 'W');
-- 随机生成姓名（根据性别）
IF gender_val = 'M' THEN
SET name_val = CONCAT(
ELT(FLOOR(1 + RAND() * 100), '王', '李', '张', '刘', '陈', '杨', '赵', '黄', '周', '吴',
'徐', '孙', '胡', '朱', '高', '林', '何', '郭', '马', '罗', '梁', '宋', '郑', '谢', '韩',
'唐', '冯', '于', '董', '萧', '程', '曹', '袁', '邓', '许', '傅', '沈', '曾', '彭', '吕',
'苏', '卢', '蒋', '蔡', '贾', '丁', '魏', '薛', '叶', '阎', '余', '潘', '杜', '戴', '夏',
'钟', '汪', '田', '任', '姜', '范', '方', '石', '姚', '谭', '廖', '邹', '熊', '金', '陆',
'郝', '孔', '白', '崔', '康', '毛', '邱', '秦', '江', '史', '顾', '侯', '邵', '孟', '龙',
'万', '段', '雷', '钱', '汤', '尹', '黎', '易', '常', '武', '乔', '贺', '赖', '龚', '文'),
ELT(FLOOR(1 + RAND() * 80), '伟', '强', '勇', '军', '磊', '洋', '超', '健', '明', '涛',
'鹏', '华', '平', '鑫', '刚', '杰', '峰', '建', '波', '浩', '宇', '宁', '栋', '彬', '翔',
'旭', '辰', '朔', '扬', '帅', '然', '博', '坤', '亮', '政', '良', '飞', '虎', '昕', '睿',
'达', '威', '耀', '翔', '龙', '寅', '钊', '铮', '昊', '悦', '宏', '舜', '延', '谊', '贤',
'逸', '卫', '臻', '炜', '祥', '旭', '煊', '昂', '策', '超', '畅', '誉', '绛', '竣', '骏',
'珺', '揆', '廓', '励', '迈', '劼', '溥', '濮', '峤', '晟', '偲', '邃', '韬', '叡')
);
ELSE
SET name_val = CONCAT(
ELT(FLOOR(1 + RAND() * 100), '王', '李', '张', '刘', '陈', '杨', '赵', '黄', '周', '吴',
'徐', '孙', '胡', '朱', '高', '林', '何', '郭', '马', '罗', '梁', '宋', '郑', '谢', '韩',
'唐', '冯', '于', '董', '萧', '程', '曹', '袁', '邓', '许', '傅', '沈', '曾', '彭', '吕',
'苏', '卢', '蒋', '蔡', '贾', '丁', '魏', '薛', '叶', '阎', '余', '潘', '杜', '戴', '夏',
'钟', '汪', '田', '任', '姜', '范', '方', '石', '姚', '谭', '廖', '邹', '熊', '金', '陆',
'郝', '孔', '白', '崔', '康', '毛', '邱', '秦', '江', '史', '顾', '侯', '邵', '孟', '龙',
'万', '段', '雷', '钱', '汤', '尹', '黎', '易', '常', '武', '乔', '贺', '赖', '龚', '文'),
ELT(FLOOR(1 + RAND() * 80), '芳', '娜', '敏', '静', '丽', '艳', '娟', '琳', '婷', '莹',
'雪', '慧', '玉', '颖', '悦', '洁', '萍', '梅', '鑫', '蕾', '蓓', '薇', '雯', '婕', '妍',
'怡', '雨', '琪', '蕊', '洁', '雅', '菁', '梦', '璐', '菲', '月', '明', '爽', '淑', '惠',
'珠', '翠', '芝', '芬', '芳', '燕', '彩', '春', '菊', '兰', '凤', '琴', '玲', '军', '红',
'娜', '丹', '蓉', '眉', '君', '琴', '蕊', '薇', '菁', '梦', '楠', '宁', '宜', '云', '聪',
'欣', '可', '飘', '滢', '怡', '贞', '莉', '兰', '娇', '倩', '姣', '婉', '娴', '瑾', '颖')
);
END IF;
-- 随机生成年龄(15-25岁)
SET age_val = FLOOR(15 + RAND() * 11);
-- 随机生成手机号
SET phone_val = CONCAT(
'1',
ELT(FLOOR(1 + RAND() * 4), '38', '39', '50', '51'),
LPAD(FLOOR(RAND() * 100000000), 8, '0')
);
-- 插入数据
INSERT INTO stu (name, age, phone, gender, tea_id, beizhu)
VALUES (
name_val,
age_val,
phone_val,
gender_val,
FLOOR(1 + RAND() * 10), -- tea_id 1-10
ELT(FLOOR(1 + RAND() * 6), '优秀学生', '表现良好', '需要改进', '勤奋好学', '积极参与', NULL)
);
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
-- 执行存储过程
CALL generate_stu_data();
-- 删除存储过程
DROP PROCEDURE generate_stu_data;6、查询stu表是否添加成功
三、进行全量备份当前数据库db1
mysqldump --source-data=1 -B db1 > /bak/db1.sql这里要开启日志位置信息，不然影响后续恢复。
四、进入stu表删除数据
这里我们删除id为400-500的数据
delete from stu where id between 400 and 500;显示已经没有400-500的数据
五、查看全量备份的db.sql文件相关日志位置信息
显示位于2074 及binlog.000006二进制日志信息中
六、通过mysqlbinlog工具导出相关日志信息
mysqlbinlog --start-position=2074 /mysql/log/binlog.000006 > binlog2.sql七、查询并更改可观测二进制日志
目前该二进制日志中DML语句被隐藏，我们需要在做一步，该步骤是对二进制信息进行映射
在ROW格式下，删除操作会表现为：
-
一个
Table_map事件（标识操作的是哪张表） -
一个或多个
Delete_rows事件（记录被删除的行数据） -
一个
Xid事件（事务提交）
mysqlbinlog --base64-output=DECODE-ROWS -v /mysql/log/binlog.000006 | grep -n -A 20 -B 5 "DELETE FROM.*stu\|Table_map:.*stu"...........
.........
........
.....
.....
--
31662-SET TIMESTAMP=1757831574/*!*/;
31663-BEGIN
31664-/*!*/;
31665-# at 336941
31666:#250914 14:32:54 server id 1 end_log_pos 337008 CRC32 0x06ecb1f0 Table_map: `db1`.`stu` mapped to number 173
31667-# has_generated_invisible_primary_key=0
31668-# at 337008
31669-#250914 14:32:54 server id 1 end_log_pos 341061 CRC32 0x9279940b Delete_rows: table id 173 flags: STMT_END_F
31670:### DELETE FROM `db1`.`stu`
31671-### WHERE
31672-### @1=400
31673-### @2='曾琴'
31674-### @3=16
31675-### @4='15098060589'
31676-### @5=2
31677-### @6=9
31678-### @7='勤奋好学'
31679:### DELETE FROM `db1`.`stu`
31680-### WHERE
31681-### @1=401
31682-### @2='沈云'
31683-### @3=16
31684-### @4='13995872156'
31685-### @5=2
31686-### @6=10
31687-### @7='勤奋好学'
31688:### DELETE FROM `db1`.`stu`
31689-### WHERE
31690-### @1=402
31691-### @2='文悦'
31692-### @3=25
31693-### @4='13856876200'
31694-### @5=2
31695-### @6=7
31696-### @7='勤奋好学'
.........
......
......
......
....
....
.....
.......
32578-### @7=NULL
32579-# at 341061
32580-#250914 14:32:54 server id 1 end_log_pos 341092 CRC32 0x52434846 Xid = 8355
32581-COMMIT/*!*/;
32582-SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
32583-DELIMITER ;
32584-# End of log file
32585-/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
32586-/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;观察日志信息得知，删除操作被记录，定位到该位置，删除如下段落
>>>>> SET TIMESTAMP=1757831574/*!*/;
>>>>> .........省略
>>>>> COMMIT/*!*/
保存日志
八、将db1.sql和binlog2.sql发送到远程ubuntu2204服务器上
scp binlog2.sql bak/db1.sql 192.168.153.142:九、还原被删除前的数据库数据
临时禁用二进制日志
还原全量备份的sql文件
set sql_log_bin=0
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> source /root/db1.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 3 warnings (0.02 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)还原二进制文件
source /root/binlog2.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Charset changed
.........
.........
......十、查看丢失的数据
select * from stu where id between 400 and 500;
+-----+--------+------+-------------+--------+--------+--------------+
| id | name | age | phone | gender | tea_id | beizhu |
+-----+--------+------+-------------+--------+--------+--------------+
| 400 | 曾琴 | 16 | 15098060589 | W | 9 | 勤奋好学 |
| 401 | 沈云 | 16 | 13995872156 | W | 10 | 勤奋好学 |
| 402 | 文悦 | 25 | 13856876200 | W | 7 | 勤奋好学 |
| 403 | 余丹 | 15 | 13897266621 | W | 9 | 表现良好 |
| 404 | 邱蓉 | 18 | 13999207077 | W | 9 | 优秀学生 |
| 405 | 陆翠 | 20 | 15175986723 | W | 3 | 优秀学生 |
| 406 | 何静 | 23 | 15054863646 | W | 1 | 积极参与 |
| 407 | 陆红 | 18 | 15016706548 | W | 10 | 优秀学生 |
| 408 | 孔玉 | 21 | 15042246864 | W | 2 | 需要改进 |
| 409 | 任雯 | 22 | 13945988966 | W | 9 | 积极参与 |
| 410 | 孙飞 | 25 | 13824359233 | M | 7 | 需要改进 |
| 411 | 郑杰 | 18 | 15160044090 | M | 4 | 积极参与 |
| 412 | 金琳 | 19 | 15179446747 | W | 6 | 表现良好 |
| 413 | 邹春 | 25 | 15111121552 | W | 6 | 勤奋好学 |
| 414 | 乔娟 | 20 | 13988072072 | W | 6 | 表现良好 |
| 415 | 卢誉 | 25 | 13855396635 | M | 6 | 表现良好 |
| 416 | 刘琴 | 22 | 13943946682 | W | 3 | NULL |
| 417 | 程菁 | 15 | 15106961297 | W | 9 | NULL |
| 418 | 吕波 | 25 | 13928072973 | M | 7 | 需要改进 |
| 419 | 罗臻 | 15 | 15136714104 | M | 3 | NULL |
| 420 | 曹耀 | 22 | 15143579182 | M | 5 | NULL |
| 421 | 陆可 | 19 | 13806529473 | W | 6 | 积极参与 |
| 422 | 徐宁 | 15 | 13905546243 | M | 10 | 勤奋好学 |
| 423 | 贾明 | 17 | 15071954475 | M | 7 | NULL |
| 424 | 吴玉 | 20 | 15126902035 | W | 6 | 积极参与 |
| 425 | 任旭 | 15 | 15146522236 | M | 6 | 需要改进 |
| 426 | 陈莹 | 20 | 15154079630 | W | 8 | 优秀学生 |
| 427 | 汤昕 | 22 | 13821469684 | M | 9 | 积极参与 |
| 428 | 尹煊 | 16 | 13892679512 | M | 9 | 勤奋好学 |
| 429 | 龙燕 | 18 | 15075605076 | W | 7 | 积极参与 |
| 430 | 钱铮 | 18 | 15143719617 | M | 6 | 表现良好 |
| 431 | 叶梦 | 21 | 13942100884 | W | 9 | NULL |
| 432 | 叶栋 | 25 | 15162585337 | M | 5 | 需要改进 |
| 433 | 邱艳 | 15 | 13839984725 | W | 7 | NULL |
| 434 | 李莹 | 20 | 13863424512 | W | 5 | 需要改进 |
| 435 | 胡菊 | 22 | 13916279518 | W | 6 | 表现良好 |
| 436 | 史菁 | 21 | 15003895181 | W | 6 | 需要改进 |
| 437 | 黄雨 | 19 | 13823654801 | W | 9 | 积极参与 |
| 438 | 朱虎 | 24 | 13875772700 | M | 6 | 需要改进 |
| 439 | 任寅 | 15 | 15008463158 | M | 5 | NULL |
| 440 | 金欣 | 18 | 13866718039 | W | 9 | 需要改进 |
| 441 | 龚铮 | 25 | 13875914959 | M | 4 | 勤奋好学 |
| 442 | 曾梦 | 24 | 13984181837 | W | 3 | 积极参与 |
| 443 | 贺峤 | 25 | 15193824334 | M | 8 | 优秀学生 |
| 444 | 石怡 | 23 | 13822885430 | W | 9 | 表现良好 |
| 445 | 高军 | 23 | 15159448691 | M | 7 | 表现良好 |
| 446 | 程翔 | 21 | 13897396865 | M | 4 | NULL |
| 447 | 韩洁 | 16 | 15022959440 | W | 6 | 表现良好 |
| 448 | 钱威 | 25 | 13815350526 | M | 2 | 表现良好 |
| 449 | 易磊 | 20 | 13950710971 | M | 4 | NULL |
| 450 | 袁飞 | 18 | 13865493122 | M | 10 | 积极参与 |
| 451 | 李骏 | 18 | 15176176132 | M | 10 | 勤奋好学 |
| 452 | 熊然 | 24 | 13905243289 | M | 3 | NULL |
| 453 | 唐贞 | 25 | 15127086070 | W | 10 | 积极参与 |
| 454 | 谢平 | 16 | 15160303567 | M | 1 | 勤奋好学 |
| 455 | 任鑫 | 19 | 13973833195 | W | 5 | 表现良好 |
| 456 | 雷蕾 | 21 | 13889250479 | W | 2 | NULL |
| 457 | 钱薇 | 17 | 15151781138 | W | 1 | 积极参与 |
| 458 | 徐鹏 | 19 | 15038517446 | M | 5 | NULL |
| 459 | 王贤 | 18 | 15063572246 | M | 10 | NULL |
| 460 | 彭艳 | 16 | 15099869405 | W | 9 | 需要改进 |
| 461 | 阎刚 | 19 | 15059707381 | M | 3 | 勤奋好学 |
| 462 | 萧洁 | 15 | 15198326467 | W | 4 | NULL |
| 463 | 吴博 | 22 | 13941900679 | M | 2 | 积极参与 |
| 464 | 彭蕾 | 15 | 15065542672 | W | 6 | NULL |
| 465 | 崔兰 | 21 | 13801939488 | W | 6 | NULL |
| 466 | 孙兰 | 20 | 15039561380 | W | 3 | 优秀学生 |
| 467 | 唐竣 | 20 | 15123232318 | M | 4 | NULL |
| 468 | 龙娜 | 25 | 15033570728 | W | 8 | NULL |
| 469 | 吴强 | 23 | 15197263160 | M | 3 | 需要改进 |
| 470 | 夏昊 | 19 | 13899700508 | M | 3 | 表现良好 |
| 471 | 段良 | 21 | 15070415902 | M | 5 | NULL |
| 472 | 潘刚 | 18 | 13918449052 | M | 2 | 表现良好 |
| 473 | 蒋菊 | 23 | 15125439122 | W | 6 | 优秀学生 |
| 474 | 阎翠 | 15 | 15122263000 | W | 5 | 需要改进 |
| 475 | 唐芬 | 25 | 13807384003 | W | 8 | 积极参与 |
| 476 | 杜浩 | 22 | 15057184164 | M | 4 | 优秀学生 |
| 477 | 赖杰 | 15 | 15049869989 | M | 10 | 需要改进 |
| 478 | 龚芝 | 22 | 15190498501 | W | 2 | 优秀学生 |
| 479 | 潘勇 | 21 | 15100809299 | M | 2 | 勤奋好学 |
| 480 | 邹梅 | 16 | 15121850280 | W | 4 | 优秀学生 |
| 481 | 汤鑫 | 16 | 13914429636 | M | 8 | 表现良好 |
| 482 | 史怡 | 17 | 15135364993 | W | 9 | 优秀学生 |
| 483 | 邵蓉 | 16 | 15054520366 | W | 10 | NULL |
| 484 | 沈贤 | 18 | 13904935753 | M | 2 | 需要改进 |
| 485 | 沈琴 | 16 | 15179058083 | W | 4 | 勤奋好学 |
| 486 | 常爽 | 21 | 13913974648 | W | 7 | 勤奋好学 |
| 487 | 周峰 | 23 | 13939774188 | M | 10 | 需要改进 |
| 488 | 苏炜 | 19 | 15162738328 | M | 2 | NULL |
| 489 | 何龙 | 18 | 15079857144 | M | 9 | 勤奋好学 |
| 490 | 程莉 | 24 | 13950470956 | W | 3 | NULL |
| 491 | 潘凤 | 21 | 13938934326 | W | 2 | 积极参与 |
| 492 | 林钊 | 20 | 15037630435 | M | 3 | NULL |
| 493 | 田劼 | 25 | 13839175773 | M | 8 | 勤奋好学 |
| 494 | 谭淑 | 20 | 15151128162 | W | 6 | 表现良好 |
| 495 | 胡洁 | 21 | 13962541033 | W | 7 | 表现良好 |
| 496 | 任菁 | 17 | 15106844044 | W | 1 | 表现良好 |
| 497 | 孟明 | 23 | 15054864726 | W | 10 | 表现良好 |
| 498 | 彭昊 | 24 | 13988660565 | M | 10 | 优秀学生 |
| 499 | 石雅 | 15 | 13896760517 | W | 9 | 优秀学生 |
| 500 | 姚铮 | 15 | 13901304408 | M | 8 | NULL |
+-----+--------+------+-------------+--------+--------+--------------+
101 rows in set (0.00 sec)
有“AI”的1024 = 2048，欢迎大家加入2048 AI社区
更多推荐
26
0- 0
已为社区贡献2条内容
所有评论(0)