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
在中加入
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):
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=2backup |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/
#mysqlbinlogmysql-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)
恢复完成！
页:
[1]