写点什么

Binlog2sql 恢复误删除的数据

发布于: 2020 年 05 月 16 日
Binlog2sql恢复误删除的数据

场景一 当你想要恢复的数据是insert或者create的时间是比较久之前的。

一,首先安装binlog2sql工具

  • 安装binlog2sql前先安装git和pip

yum -y install git python-pip
  • 安装binlog2sql

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt
  • 数据配置文件里面开启选项

[mysqld]
server_id = 值
log_bin = /home/mysql/mysql_data
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

二,查看binlog日志文件(格式是ROW,所以要加上一个解释ROW格式的选项)

cd /home/mysql/mysql_data && mysqlbinlog mysql-bin.000007 --base64-output=decode-rows -v > binlog.sql
more binlog.sql
  • 选择好要恢复的POSITION位置或者时间(754到14110)

#at 754
#190613 9:44:34 server id 92 end_log_pos 819 CRC32 0x23d85f30 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
..................
..................
### DELETE FROM `xxx`.`xxx`
4318 ### WHERE
4319 ### @1=4160026
4320 ### @2=1
4321 ### @3=1
4322 ### @4=4262999
4323 ### @5='deal'
4324 ### @6='2016-10-26 11:43:54'
4325 # at 14079
4326 #190613 9:44:34 server id 92 end_log_pos 14110 CRC32 0xaa751b04 Xid = 2876280
4327 COMMIT/*!*/;

三,利用工具恢复(反向标准)

  • 授权一个用户

授权用户REPLICATION SLAVE, REPLICATION CLIENT的权限(或者找有这两个权限的用户)
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'jmysql'@'localhost'
  • 从binlog日志里面导出sql语句文件

cd /home/mysql/mysql_data/binlog2sql/binlog2sql
python binlog2sql.py -ujmysql -hlocalhost -p123 -dxxx -ttest --start-position='754' --stop-position='14110' --start-file='mysql-bin.000007' -B > binlog2sql.sql
more binlog2sql.sql
  • binlog2sql的使用参数说明

  • mysql连接配置 -h连接的主机 -P端口号 -u用户 -p'密码‘'

--stop-never 持续同步binlog。可选。不加则同步至执行命令时最新的binlog位置。
-K, --no-primary-key 对INSERT语句去除主键。可选。
-B, --flashback 生成回滚语句,可解析大文件,不受内存限制,每打印一千行加一句SLEEP SELECT(1)。可选。与stop-never或no-primary-key不能同时添加。
解析范围控制
--start-file 起始解析文件。必须。
--start-position/--start-pos start-file的起始解析位置。可选。默认为start-file的起始位置。
--stop-file/--end-file 末尾解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never,此选项失效。
--stop-position/--end-pos stop-file的末尾解析位置。可选。默认为stop-file的最末位置;若解析模式为stop-never,此选项失效。
--start-datetime 从哪个时间点的binlog开始解析,格式必须为datetime,如'2016-11-11 11:11:11'。可选。默认不过滤。
--stop-datetime 到哪个时间点的binlog停止解析,格式必须为datetime,如'2016-11-11 11:11:11'。可选。默认不过滤。
对象过滤
-d, --databases 只输出目标db的sql。可选。默认为空。
-t, --tables 只输出目标tables的sql。可选。默认为空。
  • 导入数据恢复

mysql> source /home/mysql/mysql_data/binlog2sql/binlog2sql/binlog2sql.sql

四 恢复完数据之后利用审计插件进行查询

cat mysql-audit.json |grep 'delete from ware_close_log'
{"msg-type":"activity","date":"1560390274083","thread-id":"5167","query-id":"2876280","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"1662","_client_version":"5.7.26-29","_platform":"x86_64","program_name":"mysql"},"pid":"7844","os_user":"zabbix","appname":"mysql","rows":"531","status":"0","cmd":"delete","objects":[{"db":"t591","name":"ware_close_log","obj_type":"TABLE"}],"query":"delete from ware_close_log"}

场景二,当你create一个新库新表,set ,update或者insert values 之后不小心删除了操作可以用binlog直接恢复

  • 查看好binlog日志文件之后确定好要恢复的范围区间,直接执行命令即可恢复

mysqlbinlog /home/mysql/mysql_data/mysql-bin.000007 --start-position='值' --stop-position='值' | mysql -u用户 -p'密码'

--base64-output=decode-rows -v 解析sql语句



发布于: 2020 年 05 月 16 日阅读数: 60
用户头像

现在是你们的,未来是我们的 2020.01.11 加入

爱生活,爱DB

评论

发布
暂无评论
Binlog2sql恢复误删除的数据