Mysql----阿里数据库(mysql)误删除后的数据恢复

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

思路:

  1. 利用全备的sql文件中记录的CHANGE MASTER语句,binlog文件及其位置点信息,找出binlog文件中增量的那部分
  2. 用mysqlbinlog 命令将上述的binlog 文件导出为sql文件,并剔除其中的drop语句
  3. 通过全备文件和增量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)

总结:

  1. 恢复条件为mysql要开启binlog日志功能,并且要全备和增量的所有数据
  2. 恢复时建议对外停止更新,即禁止更新数据库
  3. 先恢复全量,然后把全备时刻点以后的增量日志,按顺序恢复成SQL文件,然后把文件中有问题的SQL语句删除(也可通过时间和位置点),再恢复到数据库
相关文章
相关标签/搜索