How to Do data recovery _mysql after Mysql error operation

First, open Binlog.

First check to see if Binlog is open

Mysql> Show variables like "Log_bin";
+---------------+-------+
| variable_name | Value 
+---------------+-------+
| Log_bin  off 
+---------------+-------+
1 row in Set (0.00 sec)

The value is off, it needs to be turned on and the Binlog mode is as follows:

#vim/etc/my.cnf

Add in [mysqld]

Log-bin     = Mysql-bin
log-bin     =/usr/local/mysql/log/mysql-bin.log

Restart the MySQL service

#service mysqld Stop
#service mysqld start

Second, analog data writing

Build a library

Create database backup;

Building a table

CREATE TABLE ' number ' (
 ' id ' int (one) not NULL auto_increment COMMENT ' numbered ',
 ' 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 ()
#数据插入 for
i In range (0,10):
#time =time.strftime ("%y-%m-%d%h:%m:%s")
sql = ' INSERT into number (UpdateTime) values (%s) ' C21/>values = [(Time.strftime ("%y-%m-%d%h:%m:%s")]]
 db1 = connect_mysql ()
print db1.executemany (SQL, Values

Querying 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 |
+-------+------------------------+
rows in Set (0.00 sec)

Three, full-volume backup

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

Note: Plus-F can refresh binlog to facilitate recovery operation.

Iv. Analog Write incremental data

Continue execution of program 2-1.

Querying 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 |
+-------+---------------------+
rows in Set (0.00 sec)

Five, incremental backups

Keep the mysql-bin.000002 and Binlog.

Vi. Simulation of false operation

Delete from number;

Seven, write incremental data again

Execution procedure 2-1

SELECT * from Bumber;

+------+------------------------+
| id | updatetime   |
+------+------------------------+ |
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 |
+------+------------------------+
rows in Set (0.00 sec)

Viii. Recovery

At this point, the previous delete operation was found to be a misoperation, urgent recovery, the following recovery process

Add a read lock to the table

Lock table number read;

Importing data from a full amount of backup

#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

Can determine mysql-bin.000002 as incremental data binlog

Import full-volume backups

#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

Before the DELETE statement is found in Bin.sql, delete

Mysql-uroot-p <bin.sql

Ix. confirmation of recovered data

Login 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 | +----+---------------------+ rows in Set (0.00 sec)

Restore Complete! The above is the entire content of this article, in the operation of the database should be more careful to avoid misoperation, if in case of encounter, I hope this article can help everyone.