通过mysql-bin.log恢复数据
# 1 导出sql
mysqlbinlog --base64-output=decode-rows --skip-gtids -v mysql-bin.002193 --result-file=002193.sql
1
# 2 过滤关键字
grep -r 'DELETE FROM mpm_saas_wms.storage_info' -A 19 002193.sql > 1.sql
1
# 3 解析sql
sed -i 's/^### //g' 1.sql
sed -i "s/^DELETE FROM/INSERT INTO/g" 1.sql
sed -i "s/^WHERE/VALUES(/g" 1.sql
sed -i '/INSERT INTO/i );' 1.sql
cat 1.sql | awk -F"=|" '{
if($0 ~ /^INSERT|^VALUES|^);/){
print $0;
}else{
printf $2",";
};
}' > 1.sql
sed -i "s/,);$/);/g" 1.sql
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
此时1.sql就是丢失数据的插入sql,直接执行sql就可恢复丢失的数据
编辑 (opens new window)
上次更新: 2025/04/09, 17:51:58
