MyFlash MySQL数据恢复实战案例：将MySQL Docker容器误删除DELETE的数据进行闪回恢复

创建MySQL容器

mkdir -p {data,conf.d}

cat > conf.d/log-bin.cnf <<-EOF
[mysqld]
log_bin=mysql-binlog
server_id=1
EOF

docker rm -f mysql

docker run -d --name mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 -v /etc/localtime:/etc/localtime -v ./data:/var/lib/mysql -v ./conf.d:/etc/mysql/conf.d mysql:5.7

创建测试表

• 01_myflash_insert_data.sh

create database if not exists myflash_test character set utf8mb4 collate utf8mb4_unicode_ci comment '用于测试myflsh闪回工具';

use myflash_test;

create table if not exists test01(id int,name varchar(20),birthday date);

insert into test01 values(1,'小明','1993-01-02'),(2,'小华','1994-08-15'),(3,'小丽','1995-07-12');

docker cp 01_myflash_insert_data.sh mysql:/

docker exec mysql mysql -uroot -p123456 -e 'source /01_myflash_insert_data.sh'

• 02_insert-some-data.sh

use myflash_test;

insert into test01 values(4,'小红','2000-01-01');

delete from test01 where id=1;

update test01 set birthday='1994-09-15';

select * from test01;

show master status;

docker cp 02_insert-some-data.sh mysql:/

docker exec mysql mysql -uroot -p123456 -e 'source /02_insert-some-data.sh'

安装MyFlash

docker cp MyFlash-master/binary/flashback mysql:/flashback

docker cp MyFlash-master/binary/mysqlbinlog20160408 mysql:/mysqlbinlog

执行闪回操作

• flashback 和 mysqlbinlog 工具均位于 / 路径下。

docker exec -it mysql bash

bash-4.2# cd /

bash-4.2# /flashback --databaseNames=myflash_test --tableNames=test01 --sqlTypes='DELETE' --binlogFileNames=/var/lib/mysql/mysql-binlog.000004

bash-4.2# ls -l binlog_output_base.flashback 
-rw-r--r-- 1 root root 240 Sep 20 13:52 binlog_output_base.flashback

bash-4.2# /mysqlbinlog binlog_output_base.flashback | mysql -uroot -p123456

• 查询数据，发现刚才被delete掉的数据已经恢复了

bash-4.2# mysql -uroot -p123456 -e 'select * from myflash_test.test01'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+--------+------------+
| id   | name   | birthday   |
+------+--------+------------+
|    2 | 小华 | 1994-09-15 |
|    3 | 小丽 | 1994-09-15 |
|    4 | 小红 | 1994-09-15 |
|    1 | 小明 | 1993-01-02 |
+------+--------+------------+

links：

MySQL闪回工具–MyFlash