CentOS+Mysql误操作恢复

最新推荐文章于 2024-08-06 00:15:00 发布

Jesse_xjq

最新推荐文章于 2024-08-06 00:15:00 发布

阅读量4.1k

点赞数

安装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 事件位置作为事件截至