Shulou(Shulou.com)06/01 Report--

After drop misoperates and deletes the table, the approximate process of recovery is

1. Restore the table from the backup to the backup point in time

2. Find the drop operation point

3. Find all events between backup point and drop point in binlog, and filter out the events in the table.

4. Execute the event that found the table

1. Experimental data:

Mysql > select * from sale +-+ | month | user_id | amount | +-+ | 201601 | 1 | 201601 | 201601 | 2 | 201601 | 201601 | 3 | 201602 | 1 | 1000 | | 201602 | 201602 | | 201603 | 2 | 201602 | 201603 | 3 | 500 | | 201604 | 1 | | | 1000 | +-+ 8 rows in set (0.00 sec) |

Backup [root@wd-gtt-system-db data] # mysqldump-S / data/DB/mysql/mysql.sock-h272.30.249.143-P3306-uroot-p-- single-transaction-B test-- tables sale-- master-data=2 > / data/backup/mysql_dump.sql. The-- master-data parameter must be added here, because the binlog location at the time of backup must be recorded.

[root@wd-gtt-system-db data] # cat / data/backup/mysql_dump.sql-- MySQL dump 10.13 Distrib 5.6.27-76.0 For Linux (x86 / 64)-Host: 172.30.249.143 Database: test-Server version 5.6.27-76.0-log

/ *! 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * /; / *! 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /; / *! 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION * /; / *! 40101 SET NAMES utf8 * /; / *! 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /; / *! 40103 SET TIME_ZONE='+00:00' * / / *! 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 * /; / *! 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 * /; / *! 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' * /; / *! 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /

-Position to start replication or point-in-time recovery from

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=309610

. The red mark is the backup point. All the events from the backup point to the drop point can be found later in the binlog.

Three: add test data and drop table insert into sale values (201605) update sale set amount=0 where user_id=1 mysql > select * from sale +-+ | month | user_id | amount | +-+ | 201601 | 1 | 0 | 201601 | 2 | 201601 | 201601 | 3 | 201602 | 201602 | 1 | 0 | 201602 | 2 | 800 | 201603 | 2 | 201602 | 201603 | 3 | 500 | | 201604 | 1 | | 0 | | 201605 | 5 | 10000 | +-+ 9 rows in set (sec) mysql > drop table sale |

Four: restore data from backup to backup point

Because you are backing up the whole database, you need to find the table-building statements and data related to the sale table from the backup.

[root@wd-gtt-system-db ~] # sed-eplacemar./ {Htinct character_set_client *;}'- e'xrammant create TABLE `sale` /! dtertq' / data/backup/mysql_dump.sql DROP TABLE IF EXISTS `sale`; / *! 40101 SET character_set_client @ saved_cs_client = @ @ character_set_client * /; / *! 40101 SET character_set_client = utf8 * / CREATE TABLE `sale` (`month` int (10) DEFAULT NULL, `user_ id` varchar (64) DEFAULT NULL, `amount t` int (11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8; / *! 40101 SET character_set_client = @ saved_cs_client * / [root@wd-gtt-system-db ~] # grep 'INSERT INTO `sale` / data/backup/mysql_dump.sql > data.sql [root@wd-gtt-system-db ~] # cat data.sql INSERT INTO `sale` VALUES (201601), (201601), (201601) (201601), (201602), (201602), (201602), (201603), (201603), (201604) 1000)

Execute the above statement to view the data, and the status of the backup point has been restored mysql > select * from sale +-+ | month | user_id | amount | +-+ | 201601 | 1 | 201601 | 201601 | 2 | 201601 | 201601 | 3 | 201602 | 1 | 1000 | | 201602 | 201602 | | 201603 | 2 | 201602 | 201603 | 3 | 500 | | 201604 | 1 | | | 1000 | +-+ 8 rows in set (0.00 sec) |

5. Query the position of dorp operation

Because the drop operation occurs after the backup Add a-- start-position=309610 [root@wd-gtt-system-db mysql] # mysqlbinlog-v-- base64-output=DECODE-ROWS-- set-charset=UTF-8-- start-position=309610 / data/DB/mysql/mysql-bin.000002 | grep DROP-A10-B10 # @ 1 / VARSTRING 201604 / * INT meta=0 nullable=1 is_null=0 * / # # @ 2 / VARSTRING (192) meta=192 nullable=1 is_null=0 * / # @ 3 # 0 / * INT meta=0 nullable=1 is_null=0 * / # at 328220 # 161220 15:30:49 server id 2 end_log_pos 328251 CRC32 0xb42e62b5 Xid = 8713 commit # at 328251 # 161220 15:31:12 server id 2 end_log_pos 328368 CRC32 0xf8c5dde3 Query thread_id=127 exec_time=0 error_code=0 use `test` / *! * /; SET timestamp 1482219072, DROP TABLE `sale` / * generated by server * / The red mark indicates the drop operation performed by position=328368, and we need to revert to this, that is, the 328251 event.

Six: find all the events from the backup point to the drop point in binlog

-- start-position= backup point

-- event point before stop-position=drop operation

[root@wd-gtt-system-db mysql] # mysqlbinlog-v-- base64-output=DECODE-ROWS-- set-charset=UTF-8-- start-position=309610-- stop-position=328251 mysql-bin.000002 > recover.sql

Retrieve sale related events

[root@trcloud opt] # more recover.sql | grep-- ignore-case-E 'insert | update | delete'-A2-B2 | grep sale insert into sale values (201605) update sale set amount=0 where user_id=1

Note: only when binlog is in MIXED or Statement mode can the DML statement of the event be found by the above method

Seven: execute the sentences found above

View the data and restore it all

Mysql > select * from sale +-+ | month | user_id | amount | +-+ | 201601 | 1 | 0 | 201601 | 2 | 201601 | 201601 | 3 | 201602 | 201602 | 1 | 0 | 201602 | 2 | 800 | 201603 | 2 | 201602 | 201603 | 3 | 500 | | 201604 | 1 | | 0 | | 201605 | 5 | 10000 | +-+ 9 rows in set (0.00 sec) |

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.