MySQL Error operation recovery process

First, open the Binlog.

Show variables like ' Log_bin ';

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M00/83/A8/wKiom1d51KaAqKsqAAAZuhGGanc245.png-wh_500x0-wm_3 -wmp_4-s_824524314.png "title=" FVI${YCWRF (fi4anyg$ky_u.png "alt=" Wkiom1d51kaaqksqaaazuhgganc245.png-wh_50 "/>

#vim/etc/my.cnf

in the added in [mysqld]

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

Restart MySQL Service

#service mysqld Stop
#service mysqld Start

Second, data write

Build Library

Create database backup;

Build table

CREATE TABLE ' number ' (
' id ' int (one) 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

Procedure 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) '
values = [(Time.strftime ("%y-%m-%d%h:%m:%s")]
DB1 = Connect_mysql ()
Print Db1.executemany (sql,values)
#初始化数据游标, and returns the cursor as data

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

Three, full-scale backup

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

Note: Plus-F can refresh the Binlog for easy recovery operation.

Or

#innobackupex--user=root--password= ' Martin '--include=backup.number/data/mysql_backup/number/2>/data/mysql_ Backup/number.log

Iv. Write Incremental data

Continue to execute the 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 backup

Keep the mysql-bin.000002 and the binlog after it.

VI, analog error operation

Delete from number;

VII. Write incremental data again

Executing the program 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 |+------+------------------------+

Rows in Set (0.00 sec)

Viii. Recovery

At this point, the deletion of the previous delete operation is a mistake, urgent recovery, the recovery process is as follows

Add a read lock to the table

Lock table number read;

Import data from a full-scale 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

MYSQL-BIN.000002 can be determined as incremental data binlog

Import full-scale 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

Before Bin.sql find the DELETE statement, remove the

Mysql-uroot-p <bin.sql

Nine, confirm the recovered data

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 | |  31 | 2016-06-29 23:43:05 |+----+---------------------+31 rows in set   (0.00 SEC)

Back to Normal

This article is from "Martin" blog, please make sure to keep this source http://529876181.blog.51cto.com/9524887/1811680

MySQL Error operation recovery process