mysql delete表或drop表恢复

由drds闪回和回收站功能引发的测试

1. delete表恢复

（1）背景：误操作delete没有where限制，mysql binlog开启

mysql> select * from zhongwen2;

+----+--------------+

| id | name         |

+----+--------------+

|  1 | 沈阳         |

|  2 | 沈阳       |

|  3 | æ·»åŠ        |

|  4 | 添加         |

+----+--------------+

4 rows in set (0.00 sec)

mysql> delete from zhongwen2;

Query OK, 4 rows affected (0.00 sec)

mysql> select * from zhongwen2;

Empty set (0.00 sec)

 

（2）利用mysqlbinlog解析binlog，定位误操作

mysql> show master status;

+--------------------------+----------+--------------+------------------+------------------------------------------------------------------------------------------------------------------------------------+

| File                     | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                                  |

+--------------------------+----------+--------------+------------------+------------------------------------------------------------------------------------------------------------------------------------+

| pxc-node-2-binlog.000023 |     6051 |              |                  | 64959f3d-0c5e-ee16-56a9-6072e1bfd4bd:1-952052,

8f090ed9-f46f-11e9-9ebb-005056a0799e:1-2,

91d6e66b-f3a1-11e9-baca-005056a015ef:1-17 |

+--------------------------+----------+--------------+------------------+------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> show master logs;

+--------------------------+-----------+

| Log_name                 | File_size |

+--------------------------+-----------+

| pxc-node-2-binlog.000021 | 246892536 |

| pxc-node-2-binlog.000022 | 171207288 |

| pxc-node-2-binlog.000023 |      6051 |

+--------------------------+-----------+

3 rows in set (0.00 sec)

 

mysqlbinlog --base64-output=decode-rows -v --start-datetime="2019-11-26 00:00:00" --stop-datetime="2019-11-27 00:00:00" --database=test pxc-node-2-binlog.000023 > ddd.sql

cat ddd.sql可以看到我们的误操作

或者 show binlog events in 'pxc-node-2-binlog.000023' from 5870\G;也可以解析

（3）安装开源工具binlog2sql，生成回滚sql

1. 使用binlog2sql之前需要安装python，pip，pymysql，pymysqlreplication，安装步骤如下：

下载包binlog2sql.tar.gz（已附在文章中）

tar xvf binlog2sql.tar.gz

cd binlog2sql/binlog2sql_dependencies/

tar xvf setuptools-0.6c11.tar.gz

cd setuptools-0.6c11

python setup.py install

cd ..

tar xvf pip-9.0.1.tar.gz

cd pip-9.0.1

python setup.py install

cd ..

pip install *.whl mysql-replication-0.9.tar.gz

cd ../binlog2sql

cp binlog2sql_util.py binlog2sql.py /mysql/bin_log

1. 利用binlog2sql生成原始误操作的sql

cd /mysql/bin_log

python binlog2sql.py  -h127.0.0.1 -P3306  -uroot -p'123' -dtest -tzhongwen2 --start-file='pxc-node-2-binlog.000023'

1. 利用binlog2sql --flashback生成原始误操作sql的反向insert语句

python binlog2sql.py  --flashback -h127.0.0.1 -P3306  -uroot -p'123' -dtest -tzhongwen2 --start-file='pxc-node-2-binlog.000023'

1. 执行这些insert语句，恢复完成

 

2. drop表恢复

（1）背景：开启binlog，有全备；或者binlog从未被删除

mysql> drop table test5;

Query OK, 0 rows affected (0.16 sec)

（2）利用mysqldump的全备恢复表test5

mysqldump的语句是

mysqldump -uroot -p123 --all-databases --skip_add_locks --skip-lock-tables  >/mysql/all-databases.sql

1. 生成建表语句

sed  -e '/./{H;$!d;}' -e  'x;/CREATE TABLE `test5`/!d;q'    /mysql/all-databases.sql

1. 进数据库执行建表语句
2. 生成插入语句

grep --ignore-case 'insert into `test5`' /mysql/all-databases.sql >inser_test5.sql

1. 执行sql文件插入数据

mysql> source inser_test5.sql

Query OK, 3 rows affected (0.03 sec)

Records: 3  Duplicates: 0  Warnings: 0

（3）以用binlog恢复全备之后drop之前的操作，根据delete表恢复剩余数据

 

附录：

binlog2mysql使用可参考 https://github.com/danfengcao/binlog2sql

 

mysql连接配置

-h host; -P port; -u user; -p password

解析模式

--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。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。

 

📎binlog2sql.tar.gz