At work, if we delete data or databases by mistake, do we have to run away? I don’t think so, programmers must learn to save themselves, undetected data recovery.

In MySQL, we know that binlog logs record all our operations to the database, so binlog logs are a powerful tool to save ourselves.

Today MySQL database as an example, hand to hand teach you how to restore, daily work, there will always be due to hand shaking, write wrong conditions, write wrong table name, the wrong connection to the production database caused by the wrong deletion of database tables and data things happen.

1. Before data restoration, back up data and enable binlog in the row format. If there is no backup file, then delete the database table is really deleted, lsOF records, it is possible to restore part of the file. But if the database does not open the table file, you have to run away. If binlog is not enabled, all data from the backup point in time will be lost after data restoration. If the binlog format is not ROW, there is no way to flash back after misoperating data and you have to follow the backup and restore process.

2. Direct Restore Direct restore uses backup files to perform full restore, which is the most common scenario.

2.1 mysqldump Full backup restore

Restore data from mysqldump (mysqldump)

gzip -d backup.sql.gz | mysql -u<user> -h<host> -P<port> -p
Copy the code

2.2 Xtrabackup Backup full restoration

Recovery process:

Innobackupex --decompress < decompress > Innobackupex --datadir=<MySQL data directory > --copy-back <MySQL data directory >Copy the code

2.3 Point-in-time Recovery

Point-in-time recovery relies on binlog logs. You need to find all logs from the backup point to the recovery point from the binlog and then apply it. Let’s test that out.

Create test table:

chengqm-3306>>show create table mytest.mytest \G;
*************************** 1. row ***************************
       Table: mytest
Create Table: CREATE TABLE `mytest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ctime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Copy the code

Insert one data per second:

[mysql@mysql-test ~]$ while true; do mysql -S /tmp/mysql.sock -e 'insert into mytest.mytest(ctime)values(now())'; date; sleep 1; doneCopy the code

Backup:

[mysql@mysql-test ~]$ mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8 -S /tmp/mysql.sock -A > backup.sql
Copy the code

Find the log location when backing up:

[mysql@mysql-test ~]$ head -n 25 backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000032', MASTER_LOG_POS=39654;
Copy the code

Let’s go back to 2019-08-09 11:01:54 and find the binlog from 39654 to 019-08-09 11:01:54.

[mysql@mysql-test ~]$ mysqlbinlog --start-position=39654 --stop-datetime='2019-08-09 11:01:54' /data/mysql_log/mysql_test/mysql-bin.000032 > backup_inc.sql
[mysql@mysql-test-83 ~]$ tail -n 20 backup_inc.sql
......
### INSERT INTO `mytest`.`mytest`
### SET
###   @1=161 /* INT meta=0 nullable=0 is_null=0 */
###   @2='2019-08-09 11:01:53' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
......
Copy the code

Current number of data items:

Chengqm-3306 >> SELECT count(*) from mytest. Mytest WHERE ctime < '2019-08-09 11:01:54'; + -- -- -- -- -- -- -- -- -- -- + | count (*) | + -- -- -- -- -- -- -- -- -- -- + | 161 | + -- -- -- -- -- -- -- -- -- -- + 1 row set in article (0.00 SEC) - all data chengqm - 3306 > > select count(*) from mytest.mytest; + -- -- -- -- -- -- -- -- -- -- + | count (*) | + -- -- -- -- -- -- -- -- -- -- + | 180 | + -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Then perform the restore:

[mysql@mysql-test ~]$mysql-s/TMP /mysql.sock < backup. SQL [mysql@mysql-test ~]$mysql-s /tmp/mysql.sock < backup_inc.sqlCopy the code

Check data:

chengqm-3306>>select count(*) from mytest.mytest; + -- -- -- -- -- -- -- -- -- -- + | count (*) | + -- -- -- -- -- -- -- -- -- -- + | 161 | + -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) chengqm - 3306 > > select * from mytest.mytest order by id desc limit 5; +-----+---------------------+ | id | ctime | +-----+---------------------+ | 161 | 2019-08-09 11:01:53 | | 160 | The 11:01:52 2019-08-09 | | 159 | 2019-08-09 11:01:51 | | 158 | 2019-08-09 11:01:50 | | 157 | 2019-08-09 11:01:49 | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 5 rows in the set (0.00 SEC)Copy the code

2019-08-09 11:01:54 Has been restored to this point.

Mysql > restore a table from mysqldump

Mytest = mytest.mytest;

Sed -n '/^-- Current Database: 'mytest '/,/^-- Current Database:/p' backup. SQL > backup_mytest. SQL $! d; }' -e 'x; /CREATE TABLE `mytest`/! d; SQL > mytest_table_create. SQL # INSERT INTO 'mytest' backup_mytest.sql Mysql -u<user> -p mytest < mytest_table_create. SQL mysql -u<user> -p mytest < mytest_table_insert.sqlCopy the code

3.2 Restore a table from the Xtrabackup backup

In this example, the./backup_xtra_full directory is a backup file whose logs have been decompressed.

3.2.1 MyISAM table

Suppose the table mytest.t_myISam is restored from the backup file. T_myisam. FRM, t_myisam.MYD, and t_myisam.MYI files from the backup file, copy them to the corresponding data directory, and authorize access to MySQL. Check list status:

chengqm-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| mytest           |
| t_myisam         |
+------------------+
Copy the code

2 rows in set (0.00 sec)

chengqm-3306>>check table t_myisam; +-----------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------+-------+----------+----------+ | mytest.t_myisam | check | status | OK | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

3.2.2 Innodb tables

If innodb_file_per_table = on, restore mytest.t_innodb from the backup file:

Start a new instance;

Create an identical table on the instance;

Run alter table t_innoDB discard tablespace; Mysql > delete tablespace t_innodb.ibd;

Select t_innodb.ibd from the backup file, copy it to the corresponding data directory, and authorize it.

Run alter table t_innodb IMPORT TABLESPACE; Load tablespace;

Run flush table t_innodb; check table t_innodb; Check the table;

Use mysqldump to export the data and then import it to the database you want to restore.

Note:

  • To avoid risks, restore on a new instance and dump it out. If it is a test, you can directly perform steps 2-6 on the original library.
  • This parameter is valid only for versions earlier than 8.0.

Skip error operation SQL skip error operation SQL is used to perform an operation that cannot be flashback, such as drop table\database.

4.1 Restoring data Using backup Files Skip

4.1.1 Disabling GTID

The procedure for recovery using backup files is similar to that for point-in-time recovery, except that there is a binlog lookup operation. For example, I create two tables A and B, insert one data per minute, then do a full backup, then drop table B, now skip this SQL.

Alter TABLE B alter table B alter table B

chgnqm-3306>>show tables; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Tables_in_mytest | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | a | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

1. Locate the log location during backup

[mysql@mysql-test ~]$ head -n 25 backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000034', MASTER_LOG_POS=38414;
Copy the code

2. Locate the POS position where the DROP table statement is executed

[mysql@mysql-test mysql_test]$ mysqlbinlog -vv /data/mysql_log/mysql_test/mysql-bin.000034 | grep -i -B 3 'drop table `b`'; # at 120629 #190818 19:48:30 server id 83 end_log_pos 120747 CRC32 0x6dd6ab2a Query thread_id=29488 exec_time=0 error_code=0 SET TIMESTAMP=1566128910/*! * /. DROP TABLE `b` /* generated by server */Copy the code

From the result, we can see that the drop statement starts at 120629 and ends at 120747.

3. Extract other records from binglog that skip this statement

The first start-position is the pos position of the backup file. Mysqlbinlog-vv --start-position=38414 --stop-position=120629 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_1. SQL --start-position=120747 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_2.sqlCopy the code

4. Restore backup files

[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup.sql
Copy the code

Status after full recovery:

chgnqm-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a                |
| b                |
+------------------+
Copy the code

2 rows in set (0.00 sec)

chgnqm-3306>>select count(*) from a; + -- -- -- -- -- -- -- -- -- -- + | count (*) | + -- -- -- -- -- -- -- -- -- -- + | 71 | + -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

5. Restore incremental data

[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc_1.sql
[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc_2.sql
Copy the code

After resuming, you can see that the drop statement is skipped:

chgnqm-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a                |
| b                |
+------------------+
Copy the code

2 rows in set (0.00 sec)

chgnqm-3306>>select count(*) from a; + -- -- -- -- -- -- -- -- -- -- + | count (*) | + -- -- -- -- -- -- -- -- -- -- + | 274 | + -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

4.1.2 open GTID

Use GTID to skip SQL errors directly:

Find the log location when backing up;

Find the GTID value where the drop TABLE statement was executed.

Export the backup log location to the latest binglog;

Restore backup files;

Skip the GTID;

SET SESSION GTID_NEXT=' GTID value '; BEGIN; COMMIT; SET SESSION GTID_NEXT = AUTOMATIC;Copy the code

Apply the incremental binlog obtained in Step 3.

4.2 Using delay Library skip

4.2.1 Disabling GTID

The key operation for using the delay library recovery is to start slave until. I set up two MySQL nodes in the test environment, and the delay of node 2 was 600 seconds. I created two tables A and B, and inserted one data every second to simulate the insertion of business data.

Localhost :3306 -> localhost:3307(delay 600)

chengqm-3307>>show slave status \G; . Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000039 Read_Master_Log_Pos: 15524 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 22845 Relay_Master_Log_File: mysql-bin.000038 Slave_IO_Running: Yes Slave_SQL_Running: Yes ... Seconds_Behind_Master: 600 ...Copy the code

Table 2 of current nodes:

chengqm-3307>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a                |
| b                |
+------------------+
Copy the code

Drop table B on node 1;

chengqm-3306>>drop table b; Query OK, 0 rows affected (0.00 SEC) chengqm-3306>>show tables; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Tables_in_mytest | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | a | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

The next step is to skip this SQL.

1. Delay library synchronization

stop slave; 2. Locate the POS location where the preceding statement of the DROP table statement is executed

[mysql@mysql-test ~]$ mysqlbinlog -vv /data/mysql_log/mysql_test/mysql-bin.000039 | grep -i -B 10 'drop table `b`'; . # at 35134 #190819 11:40:25 server id 83 end_log_pos 35199 CRC32 0x02771167 Anonymous_GTID last_committed=132 sequence_number=133 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*! * /. # at 35199 #190819 11:40:25 server id 83 end_log_pos 35317 CRC32 0x50a018aa Query thread_id=37155 exec_time=0 error_code=0 use `mytest`/*! * /. SET TIMESTAMP=1566186025/*! * /. DROP TABLE `b` /* generated by server */Copy the code

From the result, we can see that the drop statement starts at 35134, so we synchronize to 35134 (don’t be wrong).

3. Delay library synchronization to the previous SQL to be skipped

change master to master_delay=0;
start slave until master_log_file='mysql-bin.000039',master_log_pos=35134;
Copy the code

The node has been synchronized to the corresponding node:

chengqm-3307>>show slave status \G; . Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000039 Read_Master_Log_Pos: 65792 ... Slave_IO_Running: Yes Slave_SQL_Running: No Exec_Master_Log_Pos: 35134 ... Until_Log_File: mysql-bin.000039 Until_Log_Pos: 35134Copy the code

4. Skip an SQL and start synchronization

set global sql_slave_skip_counter=1;
start slave;
Copy the code

Alter table B drop table B;

chengqm-3307>>show slave status \G; . Slave_IO_Running: Yes Slave_SQL_Running: Yes ... 1 row in set (0.00 SEC) chengqm-3307>>show tables; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Tables_in_mytest | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | a | b | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

4.2.2 open GTID

The step of skipping with GTID is much easier, just execute a transaction that is the same as the GTID of the SQL to skip.

Stop synchronization;

Find the GTID where the drop table statement was executed.

Execute the GTID transaction;

SET SESSION GTID_NEXT=' GTID value '; BEGIN; COMMIT; SET SESSION GTID_NEXT = AUTOMATIC;Copy the code

Continue synchronization;

Flashbacks.

Delete from a where id=1 insert into a (id,…) Values (1,…). , used to manipulate data incorrectly, valid only for DML statements, and requires the binlog format to be ROW. This chapter introduces two useful open source tools.

5.1 Binlog2SQL Binlog2SQL is an open source tool for parsing binlog. It can be used to generate flashback statements. The project address is binlog2SQL.

5.1.1 installation

wget https://github.com/danfengcao/binlog2sql/archive/master.zip -O binlog2sql.zip unzip binlog2sql.zip cd PIP install -r requirements.txtCopy the code

5.1.2 Generating Rollback SQL

python binlog2sql/binlog2sql.py --flashback \
-h<host> -P<port> -u<user> -p'<password>' -d<dbname> -t<table_name>\
--start-file='<binlog_file>' \
--start-datetime='<start_time>' \
--stop-datetime='<stop_time>' > ./flashback.sql

python binlog2sql/binlog2sql.py --flashback \
-h<host> -P<port> -u<user> -p'<password>' -d<dbname> -t<table_name> \
--start-file='<binlog_file>' \
--start-position=<start_pos> \
--stop-position=<stop_pos> > ./flashback.sql
Copy the code

5.2 MyFlash MyFlash is a tool for rolling back DML operations developed and maintained by the Technical Engineering Department of Meituan-Dianping. The project is linked to MyFlash.

Limitations:

  • The binlog format must be row and binlog_ROW_image =full.
  • Only 5.6 and 5.7 are supported.
  • Only DML (add, Delete, modify) can be rolled back.

5.2.1 installation

# yum install GCC * pkg-config glib2 libgnomeui-devel-y # yum install GCC * pkg-config glib2 libgnomeui-devel-y # yum install GCC * pkg-config glib2 libgnomeui-devel-y https://github.com/Meituan-Dianping/MyFlash/archive/master.zip - O MyFlash.zip unzip MyFlash.zip CD MyFlash - master # compiler installed GCC -w 'pkg-config --cflags --libs glib-2.0' source/ binlogparseglib. c -o binary/flashback mv binary/ usr/local/MyFlash ln -s /usr/local/MyFlash/flashback /usr/bin/flashbackCopy the code

5.2.2 use

Generate rollback statements:

flashback --databaseNames=<dbname> --binlogFileNames=<binlog_file> --start-position=<start_pos> --stop-position=<stop_pos>
Copy the code

The binlog_output_base. Flashback file will be generated after execution. Use mysqlbinlog to parse the file.

mysqlbinlog -vv binlog_output_base.flashback | mysql -u<user> -p
Copy the code