公司网站设计 上海,网站开发的技术,请人做网站需要注意什么,请问大连谁家做网站【BUG 记录】史诗级 BUG - MYSQL 删库删表却没有备份如何恢复数据 1. 问题描述2. 解决方案#xff08;binlog#xff09;2.1 构造测试环境2.2 查看 MySQL 环境是否开启 binlog2.3 查看所有的 binlog 日志记录2.4 查看当前正在使用的是哪一个 binlog 文件2.5 查看此时的 binlo… 【BUG 记录】史诗级 BUG - MYSQL 删库删表却没有备份如何恢复数据 1. 问题描述2. 解决方案binlog2.1 构造测试环境2.2 查看 MySQL 环境是否开启 binlog2.3 查看所有的 binlog 日志记录2.4 查看当前正在使用的是哪一个 binlog 文件2.5 查看此时的 binlog 日志记录2.6 删除记录这里模拟删除表操作2.7 mysqlbinlog 恢复数据2.8 binlog2sql 数据回滚 3. binlog2sql 详细介绍3.1 概念3.2 安装 binlog2sql3.3 更改mysql配置文件3.4 运行脚本获取数据库里的内容3.5 删除数据3.6 回滚数据 1. 问题描述 在实际开发过程中不小心把测试环境的三张业务表user、comany、user_dept_rel数据删除了并且当时没有备份这三张表的数据导致测试环境瘫痪其中最关键的user表给删了导致很多测试账号登录不上系统搞了一下午最终通过binlog日志 恢复了数据。在此记录一下当时的解决过程。
环境Python 3.11 mysql8.0
2. 解决方案binlog
2.1 构造测试环境 库test 建表user、company、user_dept_rel面下测试中主要使用user表来演示 create table user(id int AUTO_INCREMENT primary key,name varchar(50),phone varchar(50)
);insert into user(id,name,phone) value(1,zhangsan,17756566565);
insert into user(id,name,phone) value(2,lisi,17712345687);
insert into user(id,name,phone) value(3,wangwu,17756562333);
insert into user(id,name,phone) value(4,zhaoliu,17756564444);create table company(id int AUTO_INCREMENT primary key,name varchar(50),code varchar(50)
);insert into company(id,name,code) value(1,测试公司1,ceshigongsi1);
insert into company(id,name,code) value(2,测试公司2,ceshigongsi2);
insert into company(id,name,code) value(3,测试公司3,ceshigongsi3);
insert into company(id,name,code) value(4,测试公司4,ceshigongsi4);create table user_dept_rel(id int AUTO_INCREMENT primary key,user_id int,dept_id int
);insert into user_dept_rel(id,user_id,dept_id) value(1,111,1001);
insert into user_dept_rel(id,user_id,dept_id) value(2,222,1002);
insert into user_dept_rel(id,user_id,dept_id) value(3,333,1003);
insert into user_dept_rel(id,user_id,dept_id) value(4,444,1004);查看数据
2.2 查看 MySQL 环境是否开启 binlog 通过命令查看是否开启 binlog 记录功能该功能默认是开启的
show variables like %log_bin%;2.3 查看所有的 binlog 日志记录 命令show binary logs;结果
2.4 查看当前正在使用的是哪一个 binlog 文件 命令-- 查看当前的 binlog 文件
show master status;结果 当使用的是MYSQL-bin.000042这个日志文件在前面 binlog 日志文件路径 我的是D:\Settings\MySQL\DATA\Data 下查看
2.5 查看此时的 binlog 日志记录 命令 show binlog events in MYSQL-bin.000042;结果
2.6 删除记录这里模拟删除表操作 删除delete from test.user;
delete from test.company;
delete from test.user_dept_rel;查看日志 可以看到我们三个删除事件注意binlog 中每个事件都有一个 begin 和 commit我们后面进行恢复或回滚的时候开始和结束的 pos 都是取的事件整体的开始点和结束点。比如上面第一个删除事件的开始点(Pos)其实是7868结束点End_log_pos则是8167。
2.7 mysqlbinlog 恢复数据 注意 这里是恢复数据不是回滚数据
恢复的本质将原有的插入语句再执行一遍回滚的本质回退到删除之前的状态 mysqlbinlog是mysql自带的命令一般是在mysql安装目录下的bin目录里。因为我们是恢复数据所以要找到已经删除语句的对应写入事件将该事件再重新执行一遍即可。 通过命令确认插入语句的事件位置 show binlog events in MYSQL-bin.000042;确定了 user 表的插入事件起始位置是3677 结果位置是4819 使用 mysqlbinlog 命令恢复 D:\Settings\MySQL\DATA\Datamysqlbinlog.exe --start-position3677 --stop-position4819 mysql-bin.000042 | mysql -uroot -p123456常用参数解释 D:\Settings\MySQL\DATA\Data mysqlbinlog 的路径就上面通过语句查出来的日志路径 --start-position起始位置 --stop-position结束位置 --start-datetime起始日期 --stop-datetime结束日期 mysql-bin.000042日志文件 查看结果 查看表 可以看到数据已经回来了 查看 binlog 日志 可以看出日志中也多了四条写入事件。这里只恢复了user表company 和 user_dept 同理
注意如果说找不到 insert 语句了或者insert 语句 在很久之前执行的找不到我们可以通过在日志中找出它的 delete 语句然后手动将 delete from 语句转换成 insert 语句可以写一个python 脚本重新执行一下就可以了。我当时就是这么干的
小结 mysqlbinlog命令只是用于恢复不能用于回滚。如果数据进行update操作则很难通过该命令恢复。所以该命令比较适用一些数据迁移数据同步的场景。 mysqlbinlog 运行过程中如果出现unknown variable default-character-setutf8mb4异常可以再该命令后加--no-defaults参数解决mysqlbinlog --no-defaults
2.8 binlog2sql 数据回滚 binlog2sql是一个第三方的工具binlog2sql回滚的原理是生成要回滚事件对应的sql语句我们最后只需要拷贝该语句实现即可。
3. binlog2sql 详细介绍
3.1 概念 binlog2sql是一个用于解析二进制日志的开源工具。它具有从二进制日志中提取原始 SQL 语句的功能。它具有从二进制日志生成回滚 SQL 以进行时间点恢复的功能。
3.2 安装 binlog2sql 前提条件安装这个工具需要电脑具备python环境如果没有还请自行安装python 3.11 (这块我的python版本是3.11)
cd D:\Workspace\PyCharm\
git clone https://github.com/danfengcao/binlog2sql.git cd binlog2sql
pip install -r requirements.txt3.3 更改mysql配置文件 在 my.ini 中添加找到log-binMYSQL-bin然后在下一行添加
log-bin
server_id 1
binlog_format row
binlog_row_image full~修改完之后重启 mysql 服务
3.4 运行脚本获取数据库里的内容 D:\Workspace\PyCharm\binlog2sqlcd binlog2sql
D:\Workspace\PyCharm\binlog2sql\binlog2sqlpython binlog2sql.py -uroot -p123456 -P3306 -d test -t user --start-fileD:\Settings\MySQL\DATA\Data\ --stop-fileMYSQL-bin.000042
Traceback (most recent call last):File D:\Workspace\PyCharm\binlog2sql\binlog2sql\binlog2sql.py, line 145, in modulebinlog2sql Binlog2sql(connection_settingsconn_setting, start_fileargs.start_file, start_posargs.start_pos,^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^File D:\Workspace\PyCharm\binlog2sql\binlog2sql\binlog2sql.py, line 46, in __init__self.connection pymysql.connect(**self.conn_setting)^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^File D:\Settings\Python\Python311\Lib\site-packages\pymysql\__init__.py, line 90, in Connectreturn Connection(*args, **kwargs)^^^^^^^^^^^^^^^^^^^^^^^^^^^File D:\Settings\Python\Python311\Lib\site-packages\pymysql\connections.py, line 706, in __init__self.connect()File D:\Settings\Python\Python311\Lib\site-packages\pymysql\connections.py, line 931, in connectself._get_server_information()File D:\Settings\Python\Python311\Lib\site-packages\pymysql\connections.py, line 1269, in _get_server_informationself.server_charset charset_by_id(lang).name^^^^^^^^^^^^^^^^^^^File D:\Settings\Python\Python311\Lib\site-packages\pymysql\charset.py, line 38, in by_idreturn self._by_id[id]~~~~~~~~~~~^^^^
KeyError: 255如果出现以上报错更新PyMySQL 即可解决mysql8的版本问题安装对应的binlog2sql工具版本
pip uninstall PyMySQL
pip install PyMySQL0.9.3再次运行
D:\Workspace\PyCharm\binlog2sql\binlog2sqlpython binlog2sql.py -uroot -p123456 -P3306 -d test -t user --start-fileMYSQL-bin.000042 --stop-fileMYSQL-bin.000042
D:\Settings\Python\Python311\Lib\site-packages\pymysql\cursors.py:170: Warning: (1366, Incorrect string value: \\xD6\\xD0\\xB9\\xFA\\xB1\\xEA... for column VARIABLE_VALUE at row 1)result self._query(query)
USE btest;
create table t1 (title varchar(100) default null,price int not null) engineinnodb;
USE btest;
create table user(id int AUTO_INCREMENT primary key,name varchar(50),phone varchar(50)
);
INSERT INTO test.user(id, name, phone) VALUES (1, zhangsan, 17756566565); #start 3598 end 3874 time 2024-02-26 10:55:04
INSERT INTO test.user(id, name, phone) VALUES (2, lisi, 17712345687); #start 3905 end 4177 time 2024-02-26 10:55:04
INSERT INTO test.user(id, name, phone) VALUES (3, wangwu, 17756562333); #start 4208 end 4482 time 2024-02-26 10:55:04
INSERT INTO test.user(id, name, phone) VALUES (4, zhaoliu, 17756564444); #start 4513 end 4788 time 2024-02-26 10:55:04
USE btest;
create table company(id int AUTO_INCREMENT primary key,name varchar(50),code varchar(50)
);
USE btest;
create table user_dept_rel(id int AUTO_INCREMENT primary key,user_id int,dept_id int
);
DELETE FROM test.user WHERE id1 AND namezhangsan AND phone17756566565 LIMIT 1; #start 7789 end 8136 time 2024-02-26 11:39:35
DELETE FROM test.user WHERE id2 AND namelisi AND phone17712345687 LIMIT 1; #start 7789 end 8136 time 2024-02-26 11:39:35
DELETE FROM test.user WHERE id3 AND namewangwu AND phone17756562333 LIMIT 1; #start 7789 end 8136 time 2024-02-26 11:39:35
DELETE FROM test.user WHERE id4 AND namezhaoliu AND phone17756564444 LIMIT 1; #start 7789 end 8136 time 2024-02-26 11:39:35
INSERT INTO test.user(id, name, phone) VALUES (1, zhangsan, 17756566565); #start 9210 end 9497 time 2024-02-26 10:55:04
INSERT INTO test.user(id, name, phone) VALUES (2, lisi, 17712345687); #start 9528 end 9807 time 2024-02-26 10:55:04
INSERT INTO test.user(id, name, phone) VALUES (3, wangwu, 17756562333); #start 9838 end 10119 time 2024-02-26 10:55:04
INSERT INTO test.user(id, name, phone) VALUES (4, zhaoliu, 17756564444); #start 10150 end 10432 time 2024-02-26 10:55:04从以上内容能看出来我们刚才所执行的语句该命令只是将二进制文件解析成我们可读的sql文件。
常用参数解释
-h 主机
-P 端口
-u 用户名
-p 密码
-d 指定库名
-t 指定表--start-file 起始解析文件只需文件名无需全路径
--start-datetime 起始解析时间格式%Y-%m-%d %H:%M:%S。可选。默认不过滤。
--stop-datetime 终止解析时间格式%Y-%m-%d %H:%M:%S。可选。默认不过滤。--only-dml 只解析dml忽略ddl。可选。默认False。
--sql-type 只解析指定类型支持INSERT, UPDATE, DELETE。多个类型用空格隔开如--sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型则三者都不解析。其他参数大家可以自己网上搜索
3.5 删除数据 由于我们刚才重启了mysql服务所以当前的binlog日志文件变了 现在日志文件是MYSQL-bin.000044 我们删除数据(这块只删除user表)
delete from user;查看日志文件会多一条delete事件 3.6 回滚数据 添加参数 --flashback生成rollback语句
# 这是没有--flashback 的执行结果将delete from 语句打印出来
D:\Settings\Python\Python311\Lib\site-packages\pymysql\cursors.py:170: Warning: (1366, Incorrect string value: \\xD6\\xD0\\xB9\\xFA\\xB1\\xEA... for column VARIABLE_VALUE at row 1)result self._query(query)
DELETE FROM test.user WHERE id1 AND namezhangsan AND phone17756566565 LIMIT 1; #start 4 end 504 time 2024-02-26 14:22:35
DELETE FROM test.user WHERE id2 AND namelisi AND phone17712345687 LIMIT 1; #start 4 end 504 time 2024-02-26 14:22:35
DELETE FROM test.user WHERE id3 AND namewangwu AND phone17756562333 LIMIT 1; #start 4 end 504 time 2024-02-26 14:22:35
DELETE FROM test.user WHERE id4 AND namezhaoliu AND phone17756564444 LIMIT 1; #start 4 end 504 time 2024-02-26 14:22:35# 这是添加--flashback 的执行结果将delete from 语句转换成了insert into 语句 打印出来
D:\Workspace\PyCharm\binlog2sql\binlog2sqlpython binlog2sql.py -uroot -p123456 -P3306 -d test -t user --start-fileMYSQL-bin.000044 --flashback
D:\Settings\Python\Python311\Lib\site-packages\pymysql\cursors.py:170: Warning: (1366, Incorrect string value: \\xD6\\xD0\\xB9\\xFA\\xB1\\xEA... for column VARIABLE_VALUE at row 1)result self._query(query)
INSERT INTO test.user(id, name, phone) VALUES (4, zhaoliu, 17756564444); #start 4 end 504 time 2024-02-26 14:22:35
INSERT INTO test.user(id, name, phone) VALUES (3, wangwu, 17756562333); #start 4 end 504 time 2024-02-26 14:22:35
INSERT INTO test.user(id, name, phone) VALUES (2, lisi, 17712345687); #start 4 end 504 time 2024-02-26 14:22:35
INSERT INTO test.user(id, name, phone) VALUES (1, zhangsan, 17756566565); #start 4 end 504 time 2024-02-26 14:22:35我们复制insert into 语句执行就可以了
