我要评论1 事情起因
在最近的一次开发过程中，由于错将eq写成了set，导致全表数据被修改(还好是测试环境)
2 解决思路
利用mysql的binlog进行数据回滚
- 利用binlog文件查询到修改的那一条记录
- 对记录进行反向解析，获取被修改前数据的update语句
- 执行解析后的update语句，恢复数据
3 利用binlog进行数据回滚
3.1 确认是否启用binlog日志
show variables like 'log_bin';
3.2 确认是否有binlog文件
show binary logs;
3.3 找到误操作的时间范围
这一步仅仅是为了缩小排查区间
可以通过对应服务的日志查询出大概的误操作时间范围
3.4 登录mysql服务器查找binlog文件
3.4.1 查询binlog文件路径
打开mysql配置文件（通常是/etc/my.cnf或/etc/mysql/my.cnf）
找到与这个相似的配置(binlog存储路径)：log-bin=/var/lib/mysql/mysql-bin
如果找不到上述配置，采用另外一种思路获取binlog文件路径，查询日志文件名或索引文件名，能带出binlog的存储路径
-- 用于查看 mysql 服务器的二进制日志文件的基本文件名。
show variables like 'log_bin_basename';
-- 用于查看 mysql 服务器的二进制日志索引文件的名称。
show variables like 'log_bin_index';
从获取到的结果来看，可以得出binlog是存在于/usr/local/src/mysql/data目录下的
3.4.2 找到binlog文件
3.4.3 确认误操作被存储在哪一份binlog文件中
我在执行误操作时大概是7月16日的14:30左右，所以应该查看的二进制日志文件是binlog.000034
3.5 查看二进制日志文件内容
3.5.1 利用被更新的表名筛选出大概的时间点
mysqlbinlog --no-defaults --start-datetime="2024-07-16 14:00:00" --stop-datetime="2024-07-16 15:00:00" binlog.000034 | grep -i 'item_code_distributor_rel'
3.5.2 对每个时间点进行查询，找出误操作的具体时间和记录
mysqlbinlog --no-defaults --start-datetime="2024-07-16 14:50:27" --stop-datetime="2024-07-16 14:50:28" --base64-output=decode-rows --verbose binlog.000034 | less
这块需要能够对业务了解，大概知道哪些数据被更新为了什么，被更新了多少条
这样就能够定位到binlog中具体的那条记录了
--base64-output=decode-rows --verbose 字段命令的作用是base64解码：是因为binlog是base64 编码的二进制数据，需要解码
3.5.3 找到误操作的记录
更新了多少行数据，这里就会有多少个update语句
这个操作记录中set是被更新的数据，where是原本的数据
3.6 保存误操作的记录日志
mysqlbinlog --no-defaults --start-datetime="2024-07-16 14:50:27" --stop-datetime="2024-07-16 14:50:28" --base64-output=decode-rows --verbose binlog.000034 > cjh_get_parsed_binlog_2024-07-16-17-05.sql
3.7 分析记录，得出需要逆向解析sql的思路
需要结合业务来看，哪些字段的数据被误更新了，以及3.5.3的图片为例
我将全表数据的 @4 和 @16都进行了错误更新
所以仅需要以主键id(@1)作为条件，将旧数据(where中)的@4 和 @16重新set回去即可
结合日志记录，获取期望的更新语句样例为：
update 数据库.表名 set @4的字段名 = @4,@16的字段名 = @16 where @1的字段名 = @1;
3.8 编写脚本解析记录，得到sql
package pers.chenjiahao.util;
import java.io.bufferedreader;
import java.io.file;
import java.io.filereader;
import java.io.ioexception;
import java.util.arraylist;
import java.util.list;
/**
* @author chenjiahao(五条)
* @date 2024/7/16 17:36
*/
public class mysqlbinarylogparser {
public static void main(string[] args) {
string filepath = "d:工作文件技术文档cjh_get_parsed_binlog_2024-07-16-17-05.sql";
string document = readfilecontent(filepath);
list<string> updatestatements = parsedocument(document);
for (string statement : updatestatements) {
system.out.println(statement);
}
}
/**
* 读取文本内容
*/
private static string readfilecontent(string filepath) {
stringbuilder content = new stringbuilder();
try (bufferedreader reader = new bufferedreader(new filereader(new file(filepath)))) {
string line;
while ((line = reader.readline()) != null) {
content.append(line).append("
");
}
} catch (ioexception e) {
e.printstacktrace();
}
return content.tostring();
}
/**
* 解析文本内容
*/
private static list<string> parsedocument(string document) {
list<string> updatestatements = new arraylist<>();
// 每个"### update "是一条更新语句
string[] sections = document.split("### update ");
for (int i = 1; i < sections.length; i++) {
string section = sections[i];
string[] lines = section.split("
");
// 待拼接的where条件
string whereclause = "";
// 待拼接的set
stringbuilder sb = new stringbuilder();
for (string line : lines) {
if (line.startswith("### @1=")) {
whereclause = "id = " + line.split("=")[1];
}else if (line.startswith("### @4=")) {
sb.append("item_code = " + line.split("=")[1]);
}else if (line.startswith("### @16=")) {
sb.append(",order_channel_id = " + line.split("=")[1]);
}
// 不需要读取日志文件中set的内容，跳过即可
if (line.startswith("### set")){
break;
}
}
// 拼接sql
string updatestatement = "update hm_product.item_code_distributor_rel " + "set " + sb + " where " + whereclause + ";";
updatestatements.add(updatestatement);
}
return updatestatements;
}
}
3.9 执行sql语句，实现回滚
update hm_product.item_code_distributor_rel set item_code = 'ot2djszc8e',order_channel_id = null where id = 1; ..........省略n多条.......
4 最后
这次事情的起因也是因为一次编写代码的粗心造成的，虽然造成的影响不太好，但是解决问题的过程也挺有趣的。
以上就是使用mysql的binlog进行数据回滚的完整流程的详细内容，更多关于mysql binlog数据回滚的资料请关注代码网其它相关文章！
发表评论