当前位置：首页 > 资讯 > 技术文档

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

时间：2021-02-08 17:54 编辑：来源：阅读：
扫一扫，手机访问

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

[b]一、开启binlog。[/b] [b]首先查看binlog是否开启[/b]

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

[b]重启mysql服务[/b]

#service mysqld stop
#service mysqld start

[b]二、模拟数据写入[/b] [b]建库[/b]

create database backup;

[b]建表[/b]

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;

[b]写入数据[/b] [b]程序2-1[/b]

#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)

[b]查询数据[/b]

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)

[b]三、全量备份[/b]

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

[b]注：[/b]加-F能刷新binlog，方便恢复时操作。 [b]四、模拟写入增量数据[/b] [b]继续执行程序2-1。[/b] 查询数据

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)

[b]五、增量备份[/b] 保留mysql-bin.000002及之后的binlog即可。 [b]六、模拟误操作[/b]

delete from number;

[b]七、再次写入增量数据[/b] [b]执行程序2-1[/b] 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)

[b]八、恢复[/b] 此时发现之前的delete操作为误操作，急需恢复，恢复过程如下 [b]给该表加上读锁[/b]

lock table number read;

[b]将全量备份的数据导入[/b]

#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;

[b]刷新日志[/b]

#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 [b]导入全量备份[/b]

#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

[b]在bin.sql找到之前的delete语句，删除[/b]

mysql -uroot -p <bin.sql

[b]九、确认已恢复数据[/b] [b]登录mysql[/b]

#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)

上一篇：使用c#构造date数据类型
下一篇：c# 数据类型占用的字节数介绍

资讯排行榜
更多>>