Shulou(Shulou.com)05/31 Report--

This article is about how MySQL can recover from misoperation. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Common recovery methods

2.1 using backup to restore

To use this method, you must have the most recent backup set or know the binlog site or GTID at the beginning of the misoperation, restore it to the middle machine using the backup set, and then take advantage of the slave feature of MySQL

START SLAVE [SQL_THREAD] UNTIL

MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos

Until_option:

UNTIL {{SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set

| | MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos |

| | RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos |

| | SQL_AFTER_MTS_GAPS} |

Restore to a temporary instance, mistakenly deleted, updated data dump out and restored to the old instance. The affected tables during data recovery had better not be written, otherwise it will be difficult to achieve the most desired results. For example

Aweed 2, which was mistakenly updated to aweed 4, was updated to aweed 7 during the recovery period, and then returned to aquifer 2 after recovery.

This recovery method is not suitable for restoring a large number of databases and requires temporary instances.

2.2 restore using the open source tool binlog2sql.

Binlog2sql is developed by DBA of Dianping Company, which is based on the principle of restoring delete to insert,update value set field and where condition by parsing binlog.

Binlog format that uses restricted MySQL must be row

Installation

Git clone https://github.com/danfengcao/binlog2sql.git & & cd binlog2sql

Pip install-r requirments.txt

Usage

Usage: binlog2sql.py [- h HOST] [- u USER] [- p PASSWORD] [- P PORT]

[--start-file STARTFILE] [--start-position STARTPOS]

[--stop-file ENDFILE] [--stop-position ENDPOS]

[--start-datetime STARTTIME] [--stop-datetime STOPTIME]

[--stop-never] [--help] [- d [DATABASES [DATABASES...]

[- t [TABLES [TABLES...] [- K] [- B]

Examples

Create table flashback (

Id int (11) not null auto_increment primary key

Stat int (11) not null default 1

) engine=innodb default charset=utf8

Insert into flashback (stat) values (2), (3), (4), (7), (9), (22), (42), (33), (66), (88)

Misoperation

Update flashback set stat=15

Steps to recover data

1 get the binlog of the misoperated dml, but the general developers do not know the specific binlog. They only know when the misoperation occurred. Binlog2sql supports recovery according to the time range.

Mysql > show master logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000009 | 177 | |

| | mysql-bin.000010 | 464 |

| | mysql-bin.000011 | 8209 | |

+-+ +

3 rows in set (0.00 sec)

In this example, binlog is mysql-bin.000011

2 recover the data using binlog2sql, first parse the binlog to get the starting point of the update statement, in this case, start 5087 end 5428

Python binlog2sql.py-h227.0.0.1-P3307-udba-paired dbadmin'- dyang-tflashback-- start-file='mysql-bin.000011'

Use the binlog2sql-B parameter to get the recovered sql

Execute the acquired sql to the database, and if there is a problem in the production environment, be sure to communicate with the developer and confirm the exact records that need to be restored.

Mysql > select * from flashback

+-+ +

| | id | stat |

+-+ +

| | 1 | 2 |

| | 2 | 3 |

| | 3 | 4 |

| | 4 | 7 |

| | 5 | 9 |

| | 6 | 22 | |

| | 7 | 42 |

| | 8 | 33 | |

| | 9 | 66 | |

| | 10 | 88 |

+-+ +

10 rows in set (0.00 sec)

Limitations of binlog2sql

Mysql server must be enabled and cannot be parsed in offline mode

Advantages (compared to mysqlbinlog)

Pure Python development, easy to install and use

Comes with flashback and no-primary-key parsing mode, no need to install patches

Flashback mode is more suitable for flashback combat.

Parse to standard SQL for easy understanding and debugging

The code is easy to modify and can support more personalized parsing

In fact, MySQL also provides a parameter, sql_safe_updates, which disables delete and update statements without where conditions.

Thank you for reading! This is the end of this article on "how to restore misoperation of MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.