MySQL误操作后的数据恢复
来源:互联网 发布:java object oriented 编辑:程序博客网 时间:2024/05/13 01:49
一、开启binlog。
首先查看binlog是否开启
mysql> show variables like "log_bin";+---------------+-------+|Variable_name | Value +---------------+-------+| log_bin OFF +---------------+-------+1 row in set ( sec)
值为OFF,需开启,开启binlog方式如下:
#vim /etc/
在[mysqld]中加入
log-bin = mysql-binlog-bin = /usr/local/mysql/log/mysql-
重启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#import MySQLdbimport timedef connect_mysql(db_host="",user="martin",passwd="martin",db="backup",charset="utf8"): conn = (host=db_host,user=user,passwd=passwd,db=db,charset=charset) (True)return ()#数据插入for i in range(0,10): sql = 'insert into number(updatetime) values(%s)' values = [(("%Y-%m-%d %H:%M:%S"))] db1 = connect_mysql() print (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 ( sec)
三、全量备份
mysqldump -uroot -p -F --master-data=2 backup |gzip> /martin/data/backup_$(date +%F)
注:加-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 ( sec)
五、增量备份
保留mysql-及之后的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 ( sec)
八、恢复
此时发现之前的delete操作为误操作,急需恢复,恢复过程如下
给该表加上读锁(执行这条语句的窗口在恢复全程不能关闭)
lock table number read;
将全量备份的数据导入
#cd /martin/data/#gzip -d number_2016-06-#grep -i "change" *.sql-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-', MASTER_LOG_POS=107;
刷新日志
#mysqladmin -uroot -p'martin' flush-logs#cd /usr/local/mysql/log#ls|grep mysql-bin|grep -v indexmysql-mysql-mysql-
可确定mysql-为增量数据binlog
导入全量备份
#cd /martin/data/#mysql -uroot -p backup < number_2016-06-#cp /usr/local/mysql/log/mysql- /martin/data/#mysqlbinlog mysql- >#vim
在找到之前的delete语句,删除
mysql -uroot -p <
九、确认已恢复数据
登录mysql
#mysql -uroot -p'martin' backupselect * 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 ( sec)
恢复完成!
0 0
- MySQL误操作后的数据恢复
- mysql数据库误删除后的数据恢复操作说明
- mysql数据库误删除后的数据恢复操作说明
- mysql数据库误删除后的数据恢复操作说明
- MySQL误操作后如何快速恢复数据?
- MySQL误操作后如何快速恢复数据
- MySQL误操作后如何快速恢复数据
- MySQL误操作后如何快速恢复数据
- MySQL误操作后如何快速恢复数据
- SQL Server 2008 数据库误操作后的数据恢复
- 系统重装后MySQL的数据恢复
- Update误操作后如何恢复数据
- mysql 误操作通过日志数据恢复
- MySQL利用binlog恢复误操作数据
- 重装系统后mysql数据恢复
- 关于Oracle误操作--数据被Commit后的数据回退恢复(闪回)
- 关于Oracle误操作--数据被Commit后的数据回退恢复(闪回)
- 关于Oracle误操作--数据被Commit后的数据回退恢复(闪回)
- Android studio单元测试(JUnit)
- 如果有人问你数据库的原理,叫他看这篇文章
- Spring Ajax(1)
- 利用jQuery对table表达增删改查
- github上传项目
- MySQL误操作后的数据恢复
- Leetcode 403. Frog Jump
- 集邮,我的兴趣爱好
- 类型转换--Java基础007
- Linux下sz/rz的安装与使用
- web前端开发浏览器兼容性处理大全
- HTML每日一结1.10
- 设置select默认值-Angular
- leetcode70: Climbing Stairs