mysql+修改数据恢复_详解MySQL误操作后怎样进行数据恢复

一、开启binlog。

首先查看binlog是否开启

?mysql> show variableslike "log_bin";+---------------+-------+|Variable_name | Value+---------------+-------+| log_binOFF+---------------+-------+1 rowin set (0.00 sec)

值为OFF，需开启，开启binlog方式如下：

?1#vim /etc/my.cnf

在[mysqld]中加入

?12log-bin     = mysql-binlog-bin     = /usr/local/mysql/log/mysql-bin.log

重启mysql服务

?12#service mysqld stop#service mysqld start

二、模拟数据写入

建库

?1create database backup;

建表

?12345CREATE 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=InnoDBDEFAULT CHARSET=utf8;

写入数据

程序2-1

?#coding:utf8#python2.7import MySQLdbimporttimedef 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 iin 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 |+-------+------------------------+10rows in set (0.00 sec)

三、全量备份

?1mysqldump -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 rowsin set (0.00 sec)

五、增量备份

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

六、模拟误操作

?1delete 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 |+------+------------------------+10rows in set (0.00 sec)

八、恢复

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

给该表加上读锁

?1locktable numberread;

将全量备份的数据导入

?#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 -vindexmysql-bin.000001mysql-bin.000002mysql-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语句，删除

?1mysql -uroot -p

九、确认已恢复数据

登录mysql

?12#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 |+----+---------------------+30rows in set (0.00 sec)

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