CentOS+Mysql误操作恢复

安装mysql5.6参考 http://blog.csdn.net/qq1940879801/article/details/77623179

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

MySQL的binlog日志的开启方式以及binlog日志的一些原理和常用操作，binlog有两大作用，一个是使用binlog恢复数据，另一个就是用来做主从复制。本篇笔记就是来记录如何使用binlog日志来做数据恢复。当然了，使用binlog日志所恢复的数据只能是部分数据，并不能够使用binlog日志来做数据库的备份，如果想要做数据库备份，依然要使用我们传统的备份方法，而binlog可以作为增量备份。

下面，就mysql数据库误删除后的恢复方案进行说明。 一、工作场景 （1）MySQL数据库每晚12:00自动完全备份。 （2）某天早上上班，9点的时候，一同事犯晕drop了一个数据库！ （3）需要紧急恢复！可利用备份的数据文件以及增量的binlog文件进行数据恢复。 二、数据恢复思路 （1）利用全备的sql文件中记录的CHANGE MASTER语句，binlog文件及其位置点信息，找出binlog文件中增量的那部分。 （2）用mysqlbinlog命令将上述的binlog文件导出为sql文件，并剔除其中的drop语句。 （3）通过全备文件和增量binlog文件的导出sql文件，就可以恢复到完整的数据。

三、实例说明

1.开启MySQL的binlog日志

编辑/etc/my.cnf [root@localhost ~]# vim /etc/my.cnf 然后在[mysqld]区块添加： #添加日志，用于误操作还原 log-bin=/usr/local/mysql/log/mysql-bin 最后保存退出 先按esc，然后按:键盘，在这个输入wq，然后按回车即可保存退出 重启mysqld服务 [root@localhost ~]# service mysqld restart

2.创建数据库：

连接mysql数据库 [root@localhost ~]# mysql -uroot -p; 然后输入密码回车就行 Enter password: 首先我们来创建一个数据库mytest mysql> create database mytest; 接着我们来创建一张表 mysql> use mytest; mysql> create table t1(id int ,name varchar(20)); 然后我们插入两条数据 mysql> insert into t1 values (1,'xiaoming'); mysql> insert into t1 values (2,'xiaohong');

3.备份数据库：

创建/opt/backup [root@localhost ~]# mkdir /opt/backup [root@localhost ~]# chmod 777 /opt/backup 备份数据库到/opt/backup/ [root@localhost ~]# mysqldump -uroot -p -B -F -R -x --master-data=2 mytest | gzip > /opt/backup/mytest_$(date +%F).sql.gz

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

4.解压并恢复备份数据库

删除数据库 mysql> drop database if exists mytest; 解压缩备份数据库 [root@localhost ~]# gzip -d /opt/backup/mytest_xxx.gz 导入备份数据库，即恢复数据库 [root@localhost ~]# mysql -uroot -p < /opt/backup/mytest_xxx.sql 然后输入密码回车就行，这样我们就把数据导入到库里了。 Enter password:

5.生成并查看binlog日志文件 继续上面的操做，我们先来刷新binlog日志，生成一个新的日志，那么我们之后所要操做的内容都会被记录到新的日志文件中。（通过前面binlog日志的详细说明我们知道，每次刷新和服务重启的时候，都会生成一个binlog日志文件。）

刷新binlog日志 mysql> flush logs; Query OK, 0 rows affected (0.23 sec) 查看binlog的状态 mysql> show master status;

我们注意，binlog的文件是00001，位置是在120，这两个信息很重要 下面我们来做插入和删除操作 这个时候我们应该是来查看一下binlog日志的状态，以便与我们一会来进行恢复到此状态，但是，真正的环境中我们并不知道这个状态，因此这里也就不去查看这个状态了，这个状态的值可以通过后面查看binlog日志文件来进行分析。

6.操作数据库 新增xiaoli和xiaozhao这两条数据，并把xiaozhao这条记录删除掉。

插入2条数据 mysql> insert into t1 values(3,"xiaoli"); Query OK, 1 row affected (0.17 sec) mysql> insert into t1 values(4,"xiaozhang"); Query OK, 1 row affected (0.01 sec) 误操作，删除id为4的数据 mysql> delete from mytest.t1 where id=4; Query OK, 1 row affected (0.02 sec) 查看binlog的状态 mysql> show master status;

这个时候我们发现我删除操作是个错误的操作，要进行恢复，那么该如何恢复呢？这个时候我们就可以通过binlog的position来进行恢复。

在进行其他的处理之前，我们建议，马上再执行一次flush logs，也就是让出错的部分就集中在这么一个binlog日志文件中。

刷新binlog日志 mysql> flush logs; Query OK, 0 rows affected (0.23 sec)

我们来查看00001的binlog日志。 mysql> show binlog events in ‘mysql-bin.000001’;

我们看到delete_rows 结束点是797，起始点是在573，我们可以把操作的这些数据删除到我们上次备份的内容，然后通过执行binlog来进行恢复，当然恢复到755这个点之前。

7.使用binlog日志恢复数据 比如我上次备份的是整个数据库，我就可以把整个数据库删除，然后通过备份文件恢复，然后再通过binlog日志做增量恢复。这样数据就回来了。

删除数据库 mysql> drop database if exists mytest; 导入备份数据库，即恢复数据库 [root@localhost ~]# mysql -uroot -p < /opt/backup/mytest_xxx.sql 然后输入密码回车就行，这样我们就把数据导入到库里了。 Enter password: 进入mysql-bin.000001所在目录 [root@localhost ~]# cd /usr/local/mysql/log 通过binlog日志做增量恢复，我们要把所有的操作都恢复（不包括我们的删除，我们知道删除是在573点上） [root@localhost log]# mysqlbinlog mysql-bin.000001 --start-position 120--stop-position 573 | mysql -uroot -p mytest 然后输入密码回车就行 Enter password:

通过binlog日志做增量恢复语法如下 [root@localhost ~]# cd /usr/local/mysql/log [root@localhost log]# mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

再来查看表，数据就恢复了

下面是binlog日志恢复中一些常用的参数 –start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间 –stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样 –start-position：从二进制日志中读取指定position 事件位置作为开始。 –stop-position：从二进制日志中读取指定position 事件位置作为事件截至