Two methods of restoring MySQL data

1. Introduction

Some time ago, there were a series of cases where developers accidentally deleted/updated the database due to erroneous operations in the test environment and production. For DBAs, rolling back data is really a headache. Whenever it comes to restoring online data, it will inevitably have a certain impact on the application. In most cases, developers delete data and update most rows due to mistaken operations. Based on previous operating experience, this article introduces common recovery methods.

2. Common recovery methods

2.1 Restoring from backup

The premise of using this method is that you must have the most recent backup set or know the binlog position or GTID where the error operation started, use the backup set to restore to the intermediate machine, and then use 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, dump the accidentally deleted and updated data, and restore it to the old instance. It is best not to make the affected tables writable during data recovery, otherwise it will be difficult to achieve the desired results. For example, a=2 is mistakenly updated to a=4, and during the recovery period it is updated to a=7, and then restored to a=2 after the recovery. This recovery method is not suitable for recovering a large number of databases and requires a temporary instance.

2.2 Restore using the open source tool binlog2sql

binlog2sql is a tool developed by Dianping's DBA. It is based on parsing binlog to restore delete to insert, and the update value set field and where condition are swapped to restore data. Usage restrictions MySQL binlog format must be row installation

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.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] 
           [-d [DATABASES [DATABASES ...]]]
           [-t [TABLES [TABLES ...]]] 
           [-K] [-B]
           [--help]

example

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 where the erroneous DML is located. However, developers generally do not know the specific binlog. They only know when the erroneous operation occurred. Binlog2sql supports recovery by 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. Use binlog2sql to restore data. First, parse the binlog to get the starting position of the update statement. In this example, start 5087 and end 5428. Execute the command

python binlog2sql.py -h127.0.0.1 -P3307 -udba -p'dbadmin' -dyang -tflashback --start-file='mysql-bin.000011'

Use binlog2sql -B parameter to get the restored sql

Execute the obtained SQL to the database. If a problem really occurs 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)

Features of binlog2sql:

MySQL server must be started, and the advantages cannot be parsed in offline mode (compared to mysqlbinlog).

Pure Python development, easy to install and use.

It comes with flashback and no-primary-key parsing modes, so no patches are required.

The flashback mode is more suitable for flashback combat.

Parse into standard SQL to facilitate understanding and debugging.

The code is easy to modify and can support more personalized analysis.

In fact, MySQL also provides a parameter sql_safe_updates, which will prohibit delete and update statements without where conditions. For specific usage and introduction, please refer to the official introduction of MySQL.

Conclusion

This article briefly introduces two methods of recovering data from erroneous operations. In fact, there are other ways, such as using mysqlbinlog to write scripts to recover data, using flashback patches or Qunar's inception, etc. You can continue to study them. Protecting data security is the basic responsibility of a DBA. Every year, there are various tragedies caused by accidental deletion of data. I hope every DBA can protect his or her lifeline.

The above are the details of two methods of recovering MySQL data. For more information about MySQL data recovery, please pay attention to other related articles on 123WORDPRESS.COM!