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也救不了你
注意: 在公司开发中一定要禁用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，方便理解、筛选
- 代码容易改造，可以支持更多个性化解析
