详解MySQL误操作后怎样进行数据恢复

一、开启binlog。

首先查看binlog是否开启

mysql> show variables like "log_bin";
+---------------+-------+
|Variable_name | Value
+---------------+-------+
| log_bin  OFF
+---------------+-------+
1 row in set (0.00 sec)

值为OFF,需开启,开启binlog方式如下:

#vim /etc/my.cnf

在[mysqld]中加入

log-bin     = mysql-bin
log-bin     = /usr/local/mysql/log/mysql-bin.log

重启mysql服务

#service mysqld stop
#service mysqld start

二、模拟数据写入

建库

create database backup;

建表

CREATE TABLE `number` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
 `updatetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

写入数据

程序2-1

#coding:utf8
#python2.7
import MySQLdb
import time
def connect_mysql(db_host="192.168.11.169",user="martin",passwd="martin",db="backup",charset="utf8"):
 conn = MySQLdb.connect(host=db_host,user=user,passwd=passwd,db=db,charset=charset)
 conn.autocommit(True)
return conn.cursor()
#数据插入
for i in range(0,10):
#time=time.strftime("%Y-%m-%d %H:%M:%S")
sql = 'insert into number(updatetime) values(%s)'
values = [(time.strftime("%Y-%m-%d %H:%M:%S"))]
 db1 = connect_mysql()
print db1.executemany(sql,values)

查询数据

mysql> select * from number;
+-------+------------------------+
| id | updatetime
+--------------------------------+
| 1 | 2016-06-29 23:27:15 |
| 2 | 2016-06-29 23:27:15 |
| 3 | 2016-06-29 23:27:15 |
| 4 | 2016-06-29 23:27:15 |
| 5 | 2016-06-29 23:27:15 |
| 6 | 2016-06-29 23:27:15 |
| 7 | 2016-06-29 23:27:15 |
| 8 | 2016-06-29 23:27:15 |
| 9 | 2016-06-29 23:27:15 |
| 10 | 2016-06-29 23:27:15 |
+-------+------------------------+
10 rows in set (0.00 sec)

三、全量备份

mysqldump -uroot -p -F --master-data=2 backup |gzip> /martin/data/backup_$(date +%F).sql.gz

注:加-F能刷新binlog,方便恢复时操作。

四、模拟写入增量数据

继续执行程序2-1。

查询数据

mysql> select * from number;
+----+---------------------------+
| id | updatetime   |
+----+---------------------------+
| 1 | 2016-06-29 23:27:15 |
| 2 | 2016-06-29 23:27:15 |
| 3 | 2016-06-29 23:27:15 |
| 4 | 2016-06-29 23:27:15 |
| 5 | 2016-06-29 23:27:15 |
| 6 | 2016-06-29 23:27:15 |
| 7 | 2016-06-29 23:27:15 |
| 8 | 2016-06-29 23:27:15 |
| 9 | 2016-06-29 23:27:15 |
| 10 | 2016-06-29 23:27:15 |
| 11 | 2016-06-29 23:31:03 |
| 12 | 2016-06-29 23:31:03 |
| 13 | 2016-06-29 23:31:03 |
| 14 | 2016-06-29 23:31:03 |
| 15 | 2016-06-29 23:31:03 |
| 16 | 2016-06-29 23:31:03 |
| 17 | 2016-06-29 23:31:03 |
| 18 | 2016-06-29 23:31:03 |
| 19 | 2016-06-29 23:31:03 |
| 20 | 2016-06-29 23:31:03 |
+-------+---------------------+
20 rows in set (0.00 sec)

五、增量备份

保留mysql-bin.000002及之后的binlog即可。

六、模拟误操作

delete from number;

七、再次写入增量数据

执行程序2-1

select * from bumber;

+------+------------------------+
| id | updatetime   |
+------+------------------------+
| 21 | 2016-06-29 23:41:06 |
| 22 | 2016-06-29 23:41:06 |
| 23 | 2016-06-29 23:41:06 |
| 24 | 2016-06-29 23:41:06 |
| 25 | 2016-06-29 23:41:06 |
| 26 | 2016-06-29 23:41:06 |
| 27 | 2016-06-29 23:41:06 |
| 28 | 2016-06-29 23:41:06 |
| 29 | 2016-06-29 23:41:06 |
| 30 | 2016-06-29 23:41:06 |
+------+------------------------+
10 rows in set (0.00 sec)

八、恢复

此时发现之前的delete操作为误操作,急需恢复,恢复过程如下

给该表加上读锁

lock table number read;

将全量备份的数据导入

#cd /martin/data/
#gzip -d number_2016-06-29.sql.gz
#grep -i "change" *.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=107;

刷新日志

#mysqladmin -uroot -p'martin' flush-logs
#cd /usr/local/mysql/log
#ls|grep mysql-bin|grep -v index
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003

可确定mysql-bin.000002为增量数据binlog

导入全量备份

#cd /martin/data/
#mysql -uroot -p backup < number_2016-06-29.sql
#cp /usr/local/mysql/log/mysql-bin.000002 /martin/data/
#mysqlbinlog mysql-bin.000002 >bin.sql
#vim bin.sql

在bin.sql找到之前的delete语句,删除

mysql -uroot -p <bin.sql

九、确认已恢复数据

登录mysql

#mysql -uroot -p'martin' backup
select * from number;
+----+---------------------+
| id | updatetime   |
+----+---------------------+
| 1 | 2016-06-29 23:27:15 |
| 2 | 2016-06-29 23:27:15 |
| 3 | 2016-06-29 23:27:15 |
| 4 | 2016-06-29 23:27:15 |
| 5 | 2016-06-29 23:27:15 |
| 6 | 2016-06-29 23:27:15 |
| 7 | 2016-06-29 23:27:15 |
| 8 | 2016-06-29 23:27:15 |
| 9 | 2016-06-29 23:27:15 |
| 10 | 2016-06-29 23:27:15 |
| 11 | 2016-06-29 23:31:03 |
| 12 | 2016-06-29 23:31:03 |
| 13 | 2016-06-29 23:31:03 |
| 14 | 2016-06-29 23:31:03 |
| 15 | 2016-06-29 23:31:03 |
| 16 | 2016-06-29 23:31:03 |
| 17 | 2016-06-29 23:31:03 |
| 18 | 2016-06-29 23:31:03 |
| 19 | 2016-06-29 23:31:03 |
| 20 | 2016-06-29 23:31:03 |
| 21 | 2016-06-29 23:41:06 |
| 22 | 2016-06-29 23:41:06 |
| 23 | 2016-06-29 23:41:06 |
| 24 | 2016-06-29 23:41:06 |
| 25 | 2016-06-29 23:41:06 |
| 26 | 2016-06-29 23:41:06 |
| 27 | 2016-06-29 23:41:06 |
| 28 | 2016-06-29 23:41:06 |
| 29 | 2016-06-29 23:41:06 |
| 30 | 2016-06-29 23:41:06 |
+----+---------------------+
30 rows in set (0.00 sec)

恢复完成!以上就是本文的全部内容,在操作数据库时候要多加小心尽量避免误操作,如果万一遇到了,希望本文能够帮助大家。

(0)

相关推荐

  • MySQL数据库误操作后快速回滚的方法

    基本上每个跟数据库打交道的程序员(当然也可能是你同事)都会碰一个问题,MySQL误操作后如何快速回滚?比如,delete一张表,忘加限制条件,整张表没了.假如这还是线上环境核心业务数据,那这事就闹大了.误操作后,能快速回滚数据是非常重要的. binlog2sql快速回滚 首先,确认你的MySQL server开启了binlog,设置了以下参数: [mysqld] server-id = 1 log_bin = /var/log/mysql/mysql-bin.log max_binlog_siz

  • 关于mysql数据库误删除后的数据恢复操作说明

    在日常运维工作中,对于mysql数据库的备份是至关重要的!数据库对于网站的重要性使得我们对mysql数据的管理不容有失! 然后,是人总难免会犯错误,说不定哪天大脑短路了来个误操作把数据库给删除了,怎么办??? 下面,就mysql数据库误删除后的恢复方案进行说明. 一.工作场景 (1)MySQL数据库每晚12:00自动完全备份. (2)某天早上上班,9点的时候,一同事犯晕drop了一个数据库! (3)需要紧急恢复!可利用备份的数据文件以及增量的binlog文件进行数据恢复. 二.数据恢复思路 (1

  • 浅析Mysql 数据回滚错误的解决方法

    MYSQL的事务处理主要有两种方法.1.用begin,rollback,commit来实现begin 开始一个事务rollback 事务回滚commit 事务确认 2.直接用set来改变mysql的自动提交模式MYSQL默认是自动提交的,也就是你提交一个QUERY,它就直接执行!我们可以通过set autocommit=0 禁止自动提交set autocommit=1 开启自动提交来实现事务的处理. 当你用 set autocommit=0 的时候,你以后所有的SQL都将做为事务处理,直到你用c

  • Mysql误操作后利用binlog2sql快速回滚的方法详解

    前言 在日常工作或者学习中,操作数据库时候难免会因为"大意"而误操作,需要快速恢复的话通过备份来恢复是不太可能的,下面这篇文章主要给大家介绍关于Mysql误操作后利用binlog2sql快速回滚的方法,话不多说,来一起看看详细的介绍: 一.总体解释: DML(data manipulation language): 它们是SELECT.UPDATE.INSERT.DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言 DDL(data definition la

  • MySQL数据库防止人为误操作的实例讲解

    有不少开发人员在操作MySQL数据库的时候都遇到过误操作的情况,例如更新数据库的时候update语句忘记加上where条件,就会造成极为悲剧的结果.本文就针对防止MySQL数据库误操作的方法做出如下详解: 1.mysql帮助说明 # mysql --help|grep dummy -U, --i-am-a-dummy Synonym for option --safe-updates, -U. i-am-a-dummy FALSE 在mysql命令加上选项-U后,当发出没有WHERE或LIMIT

  • MySQL误操作后快速恢复数据的方法

    摘要: 利用binlog闪回误操作数据. 基本上每个跟数据库打交道的程序员(当然也可能是你同事)都会碰一个问题,MySQL误操作后如何快速回滚?比如,delete一张表,忘加限制条件,整张表没了.假如这还是线上环境核心业务数据,那这事就闹大了.误操作后,能快速回滚数据是非常重要的. 传统解法 用全量备份重搭实例,再利用增量binlog备份,恢复到误操作之前的状态.然后跳过误操作的SQL,再继续应用binlog.此法费时费力,不值得再推荐. 利用binlog2sql快速闪回 首先,确认你的MySQ

  • MySQL中truncate误操作后的数据恢复案例

    实际线上的场景比较复杂,当时涉及了truncate, delete 两个操作,经确认丢数据差不多7万多行,等停下来时,差不多又有共计1万多行数据写入. 这里为了简单说明,只拿弄一个简单的业务场景举例. 测试环境: Percona-Server-5.6.16 日志格式: mixed 没起用gtid 表结构如下: CREATE TABLE `tb_wubx` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) DEFAULT NULL

  • 详解MySQL误操作后怎样进行数据恢复

    一.开启binlog. 首先查看binlog是否开启 mysql> show variables like "log_bin"; +---------------+-------+ |Variable_name | Value +---------------+-------+ | log_bin OFF +---------------+-------+ 1 row in set (0.00 sec) 值为OFF,需开启,开启binlog方式如下: #vim /etc/my.c

  • 详解mysql插入数据后返回自增ID的七种方法

    引言 mysql 和 oracle 插入的时候有一个很大的区别是: oracle 支持序列做 id: mysql 本身有一个列可以做自增长字段. mysql 在插入一条数据后,如何能获得到这个自增 id 的值呢? 一:使用 last_insert_id() SELECT LAST_INSERT_ID(); 1. 每次 mysql 的 query 操作在 mysql 服务器上可以理解为一次"原子"操作, 写操作常常需要锁表, 这里的锁表是 mysql 应用服务器锁表不是我们的应用程序锁表

  • 详解 Mysql 事务和Mysql 日志

    事务特性 1.原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节. 2.一致性(Consistency):事务开始前和结束后,数据库的完整性约束没有被破坏 .比如A向B转账,不可能A扣了钱,B却没收到. 3.隔离性(Isolation):同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰.比如A正在从一张银行卡中取钱,在A取钱的过程结束前,B不能向这张卡转账. 4.持久性(Durability):事务完成后,事务对数据库的所有更新

  • 详解mysql的备份与恢复

    前言: 前面几篇文章为大家介绍了 MySQL 各种语句语法的用法及用户权限相关知识.本篇文章将主要讲解 MySQL 数据库数据备份与恢复相关知识,主要聚焦于逻辑备份,介绍mysqldump工具的使用以及恢复方法. 这里简单讲下物理备份和逻辑备份的概念: 物理备份:备份数据文件,转储数据库物理文件到某一目录.物理备份恢复速度比较快,但占用空间比较大,MySQL中可以用 xtrabackup 工具来进行物理备份. 逻辑备份:对数据库对象利用工具进行导出工作,汇总入备份文件内.逻辑备份恢复速度慢,但占

  • 详解mysql权限和索引

    mysql权限和索引 mysql的最高用户是root, 我们可以在数据库中创建用户,语句为CREATE USER 用户名 IDENTIFIED BY '密码',也可以执行CREATE USER 用户名 语句来创建用户,不过此用户没有密码,可以将用户登录后进行密码设置:删除用户语句为DROP USER 用户:更改用户名的语句为RENAME USER 老用户名 to 新用户名: 修改密码语句为set password=password('密码'): 高级用户修改别的用户密码的语句为SET PASSW

  • 详解MySQL 表中非主键列溢出情况监控

    今天,又掉坑了. 之前踩到过MySQL主键溢出的情况,通过prometheus监控起来了,具体见这篇MySQL主键溢出复盘 这次遇到的坑,更加的隐蔽. 是一个log表里面的一个int signed类型的列写满了.快速的解决方法当然还是只能切新表来救急了,然后搬迁老表的部分历史数据到热表. 亡羊补牢,处理完故障后,赶紧写脚本把生产的其他表都捋一遍. 下面是我暂时用的一个检测脚本,还不太完善,凑合用 分2个文件(1个sql文件,1个shell脚本) check.sql 内容如下: SELECT ca

  • 详解MySQL 慢查询

    查询mysql的操作信息 show status -- 显示全部mysql操作信息 show status like "com_insert%"; -- 获得mysql的插入次数; show status like "com_delete%"; -- 获得mysql的删除次数; show status like "com_select%"; -- 获得mysql的查询次数; show status like "uptime";

随机推荐

其他