MySQL误删数据之后回滚数据

MySQL误删数据之后回滚数据

场景

1. 用Navicat数据迁移功能

1. 数据传输产生数据丢失

主要执行的信息
Data Transfer start	数据传送速率
Getting tables	准备开始
Get table data for TableName	获取TableName的表数据
Drop table: TableName	把整张表干掉
Create table: TableName	创建表
Begin transaction on target server	在目标服务器上开始事务
Start transfer data for table: TableName	开始为表传输数据: TableName
End transaction on target server	在目标服务器上结束事务
Finished successfully	成功完成了

由Navicat的执行过程可以看到, Navicat的数据传输操作是先执行Drop操作, 将整张表干掉

产生的后果

1. 会导致整张表被删除, 没有记录删除数据的SQL, binlog只会记录Drop这一句SQL, 此时binlog是没有办法恢复数据的
2. 你们公司只有定时开启备份数据库功能才可以回滚

注意: 在公司开发中一定要禁用Navicat的数据传输, 这样会导致数据不可恢复, binlog也救不了你

注意: 在公司开发中一定要禁用Navicat的数据传输, 这样会导致数据不可恢复, binlog也救不了你

注意: 在公司开发中一定要禁用Navicat的数据传输, 这样会导致数据不可恢复, binlog也救不了你

2.不小心手动删除了数据怎么办

1. 如果你们公司电脑开启了binlog, 那么不要怕, 可以直接利用binlog进行回滚

相关操作

show binary logs	获取binlog文件列表
show binlog events	获取第一个binlog的内容
show binlog events in 'mysql-binName'	查看指定binlog文件的内容
show master status\G	查看当前正在写入的binlog文件

要查询某个时间段内某个数据库内的操作日志, 将SQL输入到某个文件中

mysqlbinlog --no-defaults --database=#数据库名# --start-datetime="2017-09-17 07:21:09" --stop-datetime="2017-09-19 07:59:50" #binlog文件名# > #输出的文件名称及地址#

如果是本地查询命令

mysqlbinlog --no-defaults --database=#数据库名# --start-datetime="2017-09-17 07:21:09" --stop-datetime="2017-09-19 07:59:50" #binlog文件名#

如果需要过滤

mysqlbinlog --no-defaults --database=#数据库名#  #binlog文件名# |grep insert/update/delete |more

参数	含义
start-datetime	开始时间
stop-datetime	结束时间
database	数据库名称

恢复操作

https://github.com/danfengcao/binlog2sql

访问这个地址, 将代码拉下来之后, 有详细的文档, 可以查看

环境

Python	2.7, 3.4+
MySQL	5.6, 5.7
PIP	自己安装

python -m pip install --upgrade pip   #安装pip
pip install pymysql					  #安装pymysql

MYSQL Server必须设置以下参数开启binlog

server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

账户需要的最小权限

权限
select	需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
super/replication client	两个权限都可以，需要执行’SHOW MASTER STATUS’, 获取server端的binlog列表
replication slave	通过BINLOG_DUMP协议获取binlog内容的权限

解析出标准SQL

python binlog2sql.py -h127.0.0.1 -P3306 -uroot -proot -dtest -t test3 test4 --start-file=#mysql-bin文件名称# > 输出文件地址

解析出回滚SQL

python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uroot -proot -dtest -ttest3 --start-file=#mysql-bin文件名称# --start-position=763 --stop-position=1147 > 输出文件地址

参数

mysql连接配置
-u	连接ip
-P	端口号
-u	用户名
-p	密码
start-position	起始节点
stop-position	结束节点

解析模式
--stop-never	持续解析binlog。可选。默认False，同步至执行命令时最新的binlog位置。
-K, --no-primary-key	对INSERT语句去除主键。可选。默认False
-B, --flashback	生成回滚SQL，可解析大文件，不受内存限制。可选。默认False。与stop-never或no-primary-key不能同时添加。
--back-interval	-B模式下，每打印一千行回滚SQL，加一句SLEEP多少秒，如不想加SLEEP，请设为0。可选。默认1.0。

解析范围控制
--start-file	起始解析文件，只需文件名，无需全路径 。必须。
--start-position/--start-pos	起始解析位置。可选。默认为start-file的起始位置。
--stop-file/--end-file	终止解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。
--stop-position/--end-pos	终止解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。
--start-datetime	起始解析时间，格式’%Y-%m-%d %H:%M:%S’。可选。默认不过滤。
--stop-datetime	终止解析时间，格式’%Y-%m-%d %H:%M:%S’。可选。默认不过滤。

对象过滤
-d, --databases	只解析目标db的sql，多个库用空格隔开，如-d db1 db2。可选。默认为空。
-t, --tables	只解析目标table的sql，多张表用空格隔开，如-t tbl1 tbl2。可选。默认为空。
--only-dml	只解析dml，忽略ddl。可选。默认False。
--sql-type	只解析指定类型，支持INSERT, UPDATE, DELETE。多个类型用空格隔开，如–sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。

限制(对比mysqlbinlog)

• mysql server必须开启，离线模式下不能解析
• 参数 binlog_row_image 必须为FULL，暂不支持MINIMAL
• 解析速度不如mysqlbinlog

优点(对比mysqlbinlog)

• 纯Python开发，安装与使用都很简单
• 自带flashback、no-primary-key解析模式，无需再装补丁
• 解析为标准SQL，方便理解、筛选
• 代码容易改造，可以支持更多个性化解析

借鉴

https://github.com/danfengcao/binlog2sql

https://blog.csdn.net/a1010256340/article/details/80306952