MySQL误操作后的数据恢复

67543

一、开启binlog。
首先查看binlog是否开启

1 2 3 4 5 6 7

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]中加入

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

重启mysql服务

1	<span style="font-family:'宋体', SimSun;font-size:18px;">#service mysqld stop<br>#service mysqld start</span>

二、模拟数据写入

建库

1	<span style="font-size:18px;">create database backup;<br></span>

建表

1	<span style="font-size:18px;">CREATE TABLE `number` (<br>  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',<br>  `updatetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',<br>  PRIMARY KEY (`id`)<br>) ENGINE=InnoDB DEFAULT CHARSET=utf8;<br></span>

写入数据

程序2-1

1

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

查询数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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)

三、全量备份

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

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

四、模拟写入增量数据

继续执行程序2-1。

查询数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

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;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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

将全量备份的数据导入

1	<span style="font-size:18px;">#cd /martin/data/<br>#gzip -d number_2016-06-29.sql.gz<br>#grep -i "change" *.sql<br>-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=107;</span><br>

刷新日志

1	<span style="font-size:18px;">#mysqladmin -uroot -p'martin' flush-logs<br>#cd /usr/local/mysql/log<br>#ls|grep mysql-bin|grep -v index<br></span>

mysql-bin.000001

mysql-bin.000002

mysql-bin.000003

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

导入全量备份

1	<span style="font-size:18px;">#cd /martin/data/<br>#mysql -uroot -p backup < number_2016-06-29.sql<br>#cp /usr/local/mysql/log/mysql-bin.000002 /martin/data/<br>#mysqlbinlog  mysql-bin.000002 >bin.sql<br>#vim bin.sql<br></span>

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

1	mysql -uroot -p <bin.sql

九、确认已恢复数据

登录mysql

1	<span style="font-size:18px;">#mysql -uroot -p'martin' backup<br>select * from number;</span><br>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

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

恢复完成！