CentOS+Mysql误操作恢复

xiaoxiao2021-02-28  92

安装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) 误操作,删除id4的数据 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 事件位置作为事件截至

转载请注明原文地址: https://www.6miu.com/read-52511.html

最新回复(0)