Describes how to recover data after MySQL misoperations and mysql Data Recovery

Describes how to recover data after MySQL misoperations and mysql Data Recovery

1. Enable binlog.

First, check whether binlog is enabled.

mysql> show variables like "log_bin";+---------------+-------+|Variable_name | Value +---------------+-------+| log_bin   OFF  +---------------+-------+1 row in set (0.00 sec)

If the value is OFF, enable binlog as follows:

#vim /etc/my.cnf

Add in [mysqld]

log-bin         = mysql-binlog-bin         = /usr/local/mysql/log/mysql-bin.log

Restart mysql Service

#service mysqld stop#service mysqld start

Ii. Simulate Data Writing

Database creation

create database backup;

Create a table

Create table 'number' ('id' int (11) not null AUTO_INCREMENT COMMENT 'number', 'updatetime' timestamp not null default '2017-00-00 00:00:00 ', primary key ('id') ENGINE = InnoDB default charset = utf8;

Write Data

Program 2-1

# Coding: utf8 # python2.7import MySQLdbimport timedef 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 () # insert data 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.execute.pdf (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)

Iii. 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 to facilitate operations during recovery.

4. simulate writing incremental data

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

V. Incremental Backup

Keep the mysql-bin.000002 and the binlog.

6. Simulate misoperation

delete from number;

VII. re-write incremental data

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

VIII. Recovery

In this case, it is found that the previous delete operation is incorrect and recovery is urgently needed. The recovery process is as follows:

Add a read lock to the table

lock table number read;

Import 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 logs

#mysqladmin -uroot -p'martin' flush-logs#cd /usr/local/mysql/log#ls|grep mysql-bin|grep -v indexmysql-bin.000001mysql-bin.000002mysql-bin.000003

Determines that the 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 and delete it.

mysql -uroot -p <bin.sql

IX. Confirm that data has been recovered

Log on to mysql

#mysql -uroot -p'martin' backupselect * 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)

Recovery completed! The above is all the content of this article. When operating the database, you should be careful to avoid misoperations. If you encounter this problem, I hope this article will help you.