data误删除 mysql mysql数据库误删

在日常运维工作中，对于mysql数据库的备份是至关重要的！数据库对于网站的重要性使得我们对mysql数据的管理不容有失！
然后，是人总难免会犯错误，说不定哪天大脑短路了来个误操作把数据库给删除了，怎么办？？？

下面，就mysql数据库误删除后的恢复方案进行说明。

一、工作场景

（1）MySQL数据库每晚12:00自动完全备份。
（2）某天早上上班，9点的时候，一同事犯晕drop了一个数据库！
（3）需要紧急恢复！可利用备份的数据文件以及增量的binlog文件进行数据恢复。

二、数据恢复思路

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

三、实例说明

----------------------------------------
首先，要确保mysql开启了binlog日志功能, 在/etc/my.cnf文件里的[mysqld]区块添加：
log-bin=mysql-bin
然后重启mysql服务后查看：

show variables like ‘%log_bin%’;

----------------------------------------

（1）在ops库下创建一张表customers

drop database ops;
create database ops;

use ops;
create table customers(
 id int not null auto_increment,
 name char(20) not null,
 age int not null,
 primary key(id)
 )engine=InnoDB;
 
insert into customers values(1,"wangbo","24");
insert into customers values(2,"guohui","22");
insert into customers values(3,"zhangheng","27");

（2）现在进行全备份

注明： 测试验证了，mysqldump执行完成会切换一个log-bin文件。那么我记录文件的开始位置时，知道了文件名称就基本可以了，因为文件开始地方之前基本上是空的，没有更改数据。

上面说明了起点,
还要两个问题，一个时终点，一个时范围。
终点根据时间段选择；
范围根据备份和恢复内容选择，log-bin里面有库名和表名。

log-bin的记录详细程度： 
	另起再续，或者见下面的连接

[root@vm-002 ~]# mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz
Enter password:
如果是windows系统： mysqldump -uroot -p -B -F -R -x --master-data=2 ops > D:\w_mysql\ops_.sql

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

D:\w_mysql>mysqldump --
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

D:\w_mysql>

（3）再次插入数据

insert into customers values(4,"liupeng","21");
insert into customers values(5,"xiaoda","31");
insert into customers values(6,"fuaiai","26");
select * from customers;

（4）此时误操作，删除了test数据库

drop database ops;

此时，全备之后到误操作时刻之间，用户写入的数据在binlog中，需要恢复出来!

下面开始恢复：

1、 停止数据库

2、 备份datadir目录（里面包含表数据和逻辑日志）

3、 修改监听端口

4、 启动数据库

5、 查看库全备文件，找到全备后的binlog起始点

6、 分析逻辑日志文件，记录起始点之后的sql语句形成脚本

7、 导入库全备数据

8、 导入sql增量数据

9、 关闭数据库

10、 恢复监听端口

11、 启动数据库

12、 恢复生产

这里面有1个问题：

1、全备是针对库基本做的，逻辑日志分析是针对全实例所有库做的，那么其他库的sql语句会重复执行。

这1个问题的解决方法：

1、全备针对库做， 或者 ，分析sql针对库做。

(5) 查看全备之后新增的binlog起始位置

#linux:
[root@vm-002 backup]# grep CHANGE ops_2016-09-25.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106;
这是全备时刻的binlog文件位置
即mysql-bin.000002的106行，因此在该文件之前的binlog文件中的数据都已经包含在这个全备的sql文件中了

#windows:
打开文件，找到这一行： -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=156;

（6）移动binlog文件，并导出为sql文件，剔除其中的drop语句

#查看mysql的数据存放目录，有下面可知是在/var/lib/mysql下
[root@vm-002 backup]# ps -ef|grep mysql
root 9272 1 0 01:43 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql 9377 9272 0 01:43 pts/1 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
[root@vm-002 backup]# cd /var/lib/mysql/
[root@vm-002 mysql]# ls
ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock test
[root@vm-002 mysql]# cp mysql-bin.000002 /opt/backup/

将binlog文件导出sql文件，并vim编辑它删除其中的drop语句
[root@vm-002 backup]# mysqlbinlog mysql-bin.000002 >002bin.sql    #即将binlog日志转化为可正常导入的sql文件
[root@vm-002 backup]# ls
002bin.sql mysql-bin.000002 ops_2016-09-25.sql
[root@vm-002 backup]# vim 002bin.sql #删除里面的drop语句

#windows:
复制D:\w_mysql\mydata\mysql-bin.000005 到 D:\w_mysql\目录下
#分析日志，形成增量sql脚本
mysqlbinlog --no-defaults mysql-bin.000005 >005bin.sql    #即将binlog日志转化为可正常导入的sql文件
删除下面的内容：

编辑sql脚本文件，删除drop database的事务：

#210428 16:39:35 server id 1  end_log_pos 1223 CRC32 0x7db59208 	Query	thread_id=14	exec_time=0	error_code=0	Xid = 224
SET TIMESTAMP=1619599175/*!*/;
drop database ops
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

（7）库全备的 恢复数据

mysql -uroot -p < ops_.sql

此时恢复了全备时刻的数据

（8）增量的 恢复数据

接着，使用002bin.sql文件恢复全备时刻到删除数据库之间，新增的数据
mysql -uroot -p < 005bin.sql

D:\w_mysql>mysql -uroot -p < ops_.sql
Enter password: ******

# 下面这个报错没有影响：
D:\w_mysql>mysql -uroot -p < 011bin.sql 
Enter password: ******
ERROR 3138 (HY000) at line 118: Variable pseudo_slave_mode cannot be changed by a client that owns a GTID. The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.

D:\w_mysql>

以上就是mysql数据库增量数据恢复的实例过程！

====================================================================
最后，总结几点：
1）本案例适用于人为SQL语句造成的误操作或者没有主从复制等的热备情况宕机时的修复
2）恢复条件为mysql要开启binlog日志功能，并且要全备和增量的所有数据
3）恢复时建议对外停止更新，即禁止更新数据库
4）先恢复全量，然后把全备时刻点以后的增量日志，按顺序恢复成SQL文件，然后把文件中有问题的SQL语句删除（也可通过时间和位置点），再恢复到数据库。