详解MySQL误操作后怎样进行数据恢复
一、开启binlog。
首先查看binlog是否开启
mysql>showvariableslike"log_bin"; +---------------+-------+ |Variable_name|Value +---------------+-------+ |log_binOFF +---------------+-------+ 1rowinset(0.00sec)
值为OFF，需开启，开启binlog方式如下：
#vim/etc/my.cnf
在[mysqld]中加入
log-bin=mysql-bin log-bin=/usr/local/mysql/log/mysql-bin.log
重启mysql服务
#servicemysqldstop #servicemysqldstart
二、模拟数据写入
建库
createdatabasebackup;
建表
CREATETABLE`number`( `id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'编号', `updatetime`timestampNOTNULLDEFAULT'0000-00-0000:00:00', PRIMARYKEY(`id`) )ENGINE=InnoDBDEFAULTCHARSET=utf8;
写入数据
程序2-1
#coding:utf8
#python2.7
importMySQLdb
importtime
defconnect_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)
returnconn.cursor()
#数据插入
foriinrange(0,10):
#time=time.strftime("%Y-%m-%d%H:%M:%S")
sql='insertintonumber(updatetime)values(%s)'
values=[(time.strftime("%Y-%m-%d%H:%M:%S"))]
db1=connect_mysql()
printdb1.executemany(sql,values)
查询数据
mysql>select*fromnumber; +-------+------------------------+ |id|updatetime +--------------------------------+ |1|2016-06-2923:27:15| |2|2016-06-2923:27:15| |3|2016-06-2923:27:15| |4|2016-06-2923:27:15| |5|2016-06-2923:27:15| |6|2016-06-2923:27:15| |7|2016-06-2923:27:15| |8|2016-06-2923:27:15| |9|2016-06-2923:27:15| |10|2016-06-2923:27:15| +-------+------------------------+ 10rowsinset(0.00sec)
三、全量备份
mysqldump-uroot-p-F--master-data=2backup|gzip>/martin/data/backup_$(date+%F).sql.gz
注：加-F能刷新binlog，方便恢复时操作。
四、模拟写入增量数据
继续执行程序2-1。
查询数据
mysql>select*fromnumber; +----+---------------------------+ |id|updatetime| +----+---------------------------+ |1|2016-06-2923:27:15| |2|2016-06-2923:27:15| |3|2016-06-2923:27:15| |4|2016-06-2923:27:15| |5|2016-06-2923:27:15| |6|2016-06-2923:27:15| |7|2016-06-2923:27:15| |8|2016-06-2923:27:15| |9|2016-06-2923:27:15| |10|2016-06-2923:27:15| |11|2016-06-2923:31:03| |12|2016-06-2923:31:03| |13|2016-06-2923:31:03| |14|2016-06-2923:31:03| |15|2016-06-2923:31:03| |16|2016-06-2923:31:03| |17|2016-06-2923:31:03| |18|2016-06-2923:31:03| |19|2016-06-2923:31:03| |20|2016-06-2923:31:03| +-------+---------------------+ 20rowsinset(0.00sec)
五、增量备份
保留mysql-bin.000002及之后的binlog即可。
六、模拟误操作
deletefromnumber;
七、再次写入增量数据
执行程序2-1
select*frombumber;
+------+------------------------+ |id|updatetime| +------+------------------------+ |21|2016-06-2923:41:06| |22|2016-06-2923:41:06| |23|2016-06-2923:41:06| |24|2016-06-2923:41:06| |25|2016-06-2923:41:06| |26|2016-06-2923:41:06| |27|2016-06-2923:41:06| |28|2016-06-2923:41:06| |29|2016-06-2923:41:06| |30|2016-06-2923:41:06| +------+------------------------+ 10rowsinset(0.00sec)
八、恢复
此时发现之前的delete操作为误操作，急需恢复，恢复过程如下
给该表加上读锁
locktablenumberread;
将全量备份的数据导入
#cd/martin/data/ #gzip-dnumber_2016-06-29.sql.gz #grep-i"change"*.sql --CHANGEMASTERTOMASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=107;
刷新日志
#mysqladmin-uroot-p'martin'flush-logs #cd/usr/local/mysql/log #ls|grepmysql-bin|grep-vindex mysql-bin.000001 mysql-bin.000002 mysql-bin.000003
可确定mysql-bin.000002为增量数据binlog
导入全量备份
#cd/martin/data/ #mysql-uroot-pbackup<number_2016-06-29.sql #cp/usr/local/mysql/log/mysql-bin.000002/martin/data/ #mysqlbinlogmysql-bin.000002>bin.sql #vimbin.sql
在bin.sql找到之前的delete语句，删除
mysql-uroot-p<bin.sql
九、确认已恢复数据
登录mysql
#mysql-uroot-p'martin'backup select*fromnumber;
+----+---------------------+ |id|updatetime| +----+---------------------+ |1|2016-06-2923:27:15| |2|2016-06-2923:27:15| |3|2016-06-2923:27:15| |4|2016-06-2923:27:15| |5|2016-06-2923:27:15| |6|2016-06-2923:27:15| |7|2016-06-2923:27:15| |8|2016-06-2923:27:15| |9|2016-06-2923:27:15| |10|2016-06-2923:27:15| |11|2016-06-2923:31:03| |12|2016-06-2923:31:03| |13|2016-06-2923:31:03| |14|2016-06-2923:31:03| |15|2016-06-2923:31:03| |16|2016-06-2923:31:03| |17|2016-06-2923:31:03| |18|2016-06-2923:31:03| |19|2016-06-2923:31:03| |20|2016-06-2923:31:03| |21|2016-06-2923:41:06| |22|2016-06-2923:41:06| |23|2016-06-2923:41:06| |24|2016-06-2923:41:06| |25|2016-06-2923:41:06| |26|2016-06-2923:41:06| |27|2016-06-2923:41:06| |28|2016-06-2923:41:06| |29|2016-06-2923:41:06| |30|2016-06-2923:41:06| +----+---------------------+ 30rowsinset(0.00sec)
恢复完成！以上就是本文的全部内容，在操作数据库时候要多加小心尽量避免误操作，如果万一遇到了，希望本文能够帮助大家。