Mysql----阿里数据库（mysql）误删除后的数据恢复

注意：本案例适用于人为SQL语句造成的误操作或者没有主从复制等的热备情况宕机时的修复

思路：

利用全备的sql文件中记录的CHANGE MASTER语句，binlog文件及其位置点信息，找出binlog文件中增量的那部分
用mysqlbinlog 命令将上述的binlog 文件导出为sql文件，并剔除其中的drop语句
通过全备文件和增量binlog文件的导出sql文件，就可以恢复到完整的数据

详细模拟操作如下：

一. 创建数据库，表，插入模拟数据

开启 binlog日志功能
/etc/my.cnf文件里的[mysqld]区块添加：log-bin=mysql-bin 重启服务

use test;

CREATE TABLE IF NOT EXISTS recover_table(
 id mediumint unsigned NOT NULL AUTO_INCREMENT,
 name varchar(32) NOT NULL, 
 age int unsigned NOT NULL,
 PRIMARY KEY(id)
)ENGINE=innoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 COMMENT 'recover_table';


insert into recover_table values(null, 'tom', 20);
insert into recover_table values(null, 'jack', 22);
insert into recover_table values(null, 'rose', 23);
insert into recover_table values(null, 'json', 23);


mysql> select * from recover_table;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | tom  |  20 |
|  2 | jack |  22 |
|  3 | rose |  23 |
| 4 | json | 22 |
+----+------+-----+
4 rows in set (0.00 sec)

二. 进行全文件备份

参数说明：
-B：指定数据库
-F：刷新日志
-R：备份存储过程等
-x：锁表
–master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息
test 为数据库名

[root@iZ95i72m2pc9 backup]# mysqldump -uroot -p -B -F -R -x --master-data=2 test|gzip >/test/backup/test_$(date +%F).sql.gz
Enter password: 

mysql> select * from recover_table;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | tom  |  20 |
|  2 | jack |  22 |
|  3 | rose |  23 |
| 4 | json | 22 |
+----+------+-----+
4 rows in set (0.00 sec)

三. 再插入模拟数据, 删除数据库

insert into recover_table values(null, 'sam', 25);
insert into recover_table values(null, 'jare', 26);

mysql> select * from recover_table;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | tom  |  20 |
|  2 | jack |  22 |
|  3 | rose |  23 |
|  4 | json |  22 |
|  5 | sam  |  25 |
| 6 | jare | 26 |
+----+------+-----+
6 rows in set (0.00 sec)

drop database test;

四. 查看全备之后新增的binlog文件

[root@iZ95i72m2pc9 backup]# ll
-rw-r--r-- 1 root root 936 Oct 17 10:40 test_2017-10-17.sql.gz

[root@iZ95i72m2pc9 backup]# gzip -d test_2017-10-17.sql.gz 
[root@iZ95i72m2pc9 backup]# ll
-rw-r--r-- 1 root root 2397 Oct 17 10:40 test_2017-10-17.sql

[root@iZ95i72m2pc9 backup]# grep CHANGE test_2017-10-17.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=120;

说明：mysql-bin.000002的120行，在该文件之前的binlog文件中的数据都已经包含在这个全备的sql文件中了（增量）

五. 查看并切换到mysql 数据存放目录, 转sql 格式

[root@iZ95i72m2pc9 backup]# ps -ef|grep mysql
root      4411  4376  0 10:13 pts/0    00:00:00 mysql -uroot -p
root      4458  4421  0 10:45 pts/1    00:00:00 grep mysql
root     13046     1  0 Apr12 ?        00:00:00 /bin/sh /alidata/server/mysql/bin/mysqld_safe --datadir=/alidata/server/mysql/data --pid-file=/alidata/server/mysql/data/iZ95i72m2pc9.pid
mysql    13347 13046  0 Apr12 ?        02:03:40 /alidata/server/mysql/bin/mysqld --basedir=/alidata/server/mysql --datadir=/alidata/server/mysql/data --plugin-dir=/alidata/server/mysql/lib/plugin --user=mysql --log-error=/alidata/log/mysql/error.log --pid-file=/alidata/server/mysql/data/iZ95i72m2pc9.pid --socket=/tmp/mysql.sock --port=3306
[root@iZ95i72m2pc9 backup]# cd /alidata/server/mysql/data

备份日志数据到指定目录,避免数据混乱的问题

[root@iZ95i72m2pc9 mysql]# cp ./data/mysql-bin.000004 /opt/backup/
[root@iZ95i72m2pc9 backup]# cd /opt/backup/
[root@iZ95i72m2pc9 backup]# ll
-rw-r----- 1 root root  743 Oct 17 10:52 mysql-bin.000004
-rw-r--r-- 1 root root 2397 Oct 17 10:40 test_2017-10-17.sql

转为sql 格式

[root@iZ95i72m2pc9 backup]# mysqlbinlog -d test mysql-bin.000004 >00004bin.sql

删除里面的drop 语句（或误操作的语句）

六. 开始恢复数据，先恢复备份文件，再恢复增加文件

[root@iZ95i72m2pc9 backup]# mysql -uroot -p < test_2017-10-17.sql 
Enter password: 

mysql> select * from recover_table;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | tom  |  20 |
|  2 | jack |  22 |
|  3 | rose |  23 |
| 4 | json | 22 |
+----+------+-----+
4 rows in set (0.00 sec)

[root@iZ95i72m2pc9 backup]# mysql -uroot -p < 00004bin.sql 
Enter password: 

mysql> select * from recover_table;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | tom  |  20 |
|  2 | jack |  22 |
|  3 | rose |  23 |
|  4 | json |  22 |
|  5 | sam  |  25 |
| 6 | jare | 26 |
+----+------+-----+
6 rows in set (0.00 sec)

总结：

恢复条件为mysql要开启binlog日志功能，并且要全备和增量的所有数据
恢复时建议对外停止更新，即禁止更新数据库
先恢复全量，然后把全备时刻点以后的增量日志，按顺序恢复成SQL文件，然后把文件中有问题的SQL语句删除（也可通过时间和位置点），再恢复到数据库

秒客网