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操作, 将整张表干掉

产生的后果

会导致整张表被删除, 没有记录删除数据的SQL, binlog只会记录Drop这一句SQL, 此时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。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。