实际操作mysql数据的误删除与恢复

一、构建测试库样板

1.在服务器上创建用来恢复的备份库databak

MariaDB [(none)]> create database databak;
Query OK, 1 row affected (0.00 sec)

MariaDB [databak]> CREATE TABLE `bak_test` (
    ->   `id` int unsigned NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(30) NOT NULL,
    ->   `memo` varchar(30) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

MariaDB [databak]> select * from bak_test;
+----+-------+------+
| id | name  | memo |
+----+-------+------+
|  1 | one   | 1    |
|  2 | two   | 2    |
|  3 | three | 3    |
|  4 | five  | 5    |
+----+-------+------+
4 rows in set (0.00 sec)

二、防删除机制的建立

1.启用mysql的log-bin日志，进行增量备份

直接修改/etc/my.cnf文件，或在/etc/my.cnf.d目录 (该目录被my.cnf包含)下，进行服务端的配置更新，需要设置的信息如下

[mysqld]
log-bin=log-bin
binlog-ignore-db=mysql

binlog-ignore-db=mysql，设置binlog不需要备份的数据库，即mysql库的操作不需要被记录

更改完毕后重启mysql，然后执行如下命令查看

MariaDB [(none)]> show master status;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000001 |    53893 |              | mysql,test       |
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

2.数据库完整备份

使用mysqldump，将当前数据库中需要备份的数据进行完整导出，构建一个初始的数据库，并且刷新binlog日志文件

mysqldump --opt -F -uroot databak >databak_20190124_1142.sql

三、更新多次数据后进行删除操作

1.执行多次正常的新增，更新，删除等操作，最终bak_test表形成如下数据

MariaDB [databak]> select * from bak_test;
+----+-------+------+
| id | name  | memo |
+----+-------+------+
|  1 | one   | 1    |
|  2 | two   | 2    |
|  3 | third | 3    |
|  5 | four  | 4    |
+----+-------+------+
4 rows in set (0.00 sec)

2.执行全删除命令，bak_test表被清空

MariaDB [databak]> delete from bak_test;
Query OK, 4 rows affected (0.00 sec)

四、恢复数据至全删除命令前

1.通过导入之前备份的 databak_20190124_1142.sql文件，恢复databak数据库至完整备份时间节点

MariaDB [(none)]> use databak;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [databak]> source databak_20190124_1142.sql;
Query OK, 0 rows affected (0.00 sec)
...

MariaDB [databak]> select * from bak_test;
+----+-------+------+
| id | name  | memo |
+----+-------+------+
|  1 | one   | 1    |
|  2 | two   | 2    |
|  3 | three | 3    |
|  4 | five  | 5    |
+----+-------+------+
4 rows in set (0.00 sec)

2.恢复增量备份数据

# mysqlbinlog --database databak /var/lib/mysql/log-bin.000001 >~/binlog.sql

将增量日志备份文件log-bin.000001中databak库相关的操作命令导出至binlog.sql中，多个文件依此处理，可以加上--start-datetime和--stop-datetime或--start-position和--stop-position进行部分提取。

在binlog.sql文件中，找到错误的删除命令的位置，将其和之后的所有操作全部删除

# at 287173
#190124 14:06:09 server id 1  end_log_pos 287259        Query   thread_id=34    exec_time=0     error_code=0
SET TIMESTAMP=1548309969/*!*/;
delete from bak_test
/*!*/;

然后执行数据导入

MariaDB [databak]> source binlog.sql
Query OK, 0 rows affected (0.00 sec)
...

最后数据库bak_test中的数据结果查询如下

MariaDB [databak]> select * from bak_test;
+----+-------+------+
| id | name  | memo |
+----+-------+------+
|  1 | one   | 1    |
|  2 | two   | 2    |
|  3 | third | 3    |
|  5 | four  | 4    |
+----+-------+------+
4 rows in set (0.00 sec)

由此，数据已经恢复至误删除之前。

至于误删除之后的数据如何在数据恢复后更新，就需要大家在实际应用中，根据具体情况灵活操作了。