• notice
  • Congratulations on the launch of the Sought Tech site

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!

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+