How to make data recovery after MYSQL misoperation
One, open binlog.
First check whether binlog is turned on
mysql> show variables like "log_bin"; +---------------+-------+ |Variable_name | Value +---------------+-------+ | log_bin OFF +---------------+-------+ 1 row in set (0.00 sec)
The value is OFF and needs to be turned on.The way to turn on binlog is as follows:
#vim/etc/my.cnf
Add in [mysqld]
log-bin = mysql-bin log-bin =/usr/local/mysql/log/mysql-bin.log
Restart mysql service
#service mysqld stop #service mysqld start
2.Simulation data writing
Build a library
create database backup;
Create table
CREATE TABLE `number` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT'number', `updatetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Write data
Program 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() #Data Insertion 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)
Query data
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)
3.Full backup
mysqldump -uroot -p -F --master-data=2 backup |gzip>/martin/data/backup_$(date +%F).sql.gz
Note: Add -F to refresh the binlog, which is convenient for operation during recovery.
Four.Simulate write incremental data
Continue to execute Procedure 2-1.
Query data
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)
Five.Incremental backup
Just keep the binlog of mysql-bin.000002 and later.
Six.Simulate misoperation
delete from number;
Seven, write incremental data again
Execute procedure 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)
eight.Recovery
At this time, it was found that the previous delete operation was a misoperation and urgently needed to be restored.The restoration process is as follows
Add a read lock to the table
lock table number read;
Import the full backup data
#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;
Refresh log
#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
It can be determined that mysql-bin.000002 is incremental data binlog
Import full backup
#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
Find the previous delete statement in bin.sql, delete it
mysql -uroot -p <bin.sql
9.Confirm that the data has been recovered
Log in to 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)
Restore complete!
0 Comments