In daily work, there are always things like hand shaking, writing wrong conditions, writing wrong table names, and mistakenly deleting database tables and data. So, if you can’t even recover the data, you don’t need a DBA.

1. Introduction

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 recovery

Direct restore is a full restore using backup files, 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.

3 Restore a table

3.1 Restore a table from mysqldump backup

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. Find t_myisam. FRM, t_myisam.MYD, and t_myisam.MYI files from the backup files, copy them to the corresponding data directory, and authorize them

Enter the MySQL. Check list status:

chengqm-3306>>show tables; +------------------+ | Tables_in_mytest | +------------------+ | mytest | | t_myisam | +------------------+ 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:

  1. Start a new instance;
  2. Create an identical table on the instance;
  3. Run alter table t_innoDB discard tablespace; Mysql > delete tablespace t_innodb.ibd;
  4. Select t_innodb.ibd from the backup file, copy it to the corresponding data directory, and authorize it.
  5. Run alter table t_innodb IMPORT TABLESPACE; Load tablespace;
  6. Run flush table t_innodb; check table t_innodb; Check the table;
  7. Use mysqldump to export the data and then import it to the database you want to restore.

Note:

  1. 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.
  2. This parameter is valid only for versions earlier than 8.0.

4 Skip SQL misoperations

Skip misoperations SQL is typically used to perform operations 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/*! * /.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 | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the 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 | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the 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:

1. Locate the log location during backup.

2. Locate the GTID value where the DROP table statement is executed.

3. Export the backup log location to the latest bingLog.

4. Restore backup files.

5. Skip the GTID;

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

6. 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)
Copy the code

Current node status:

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;
Copy the code

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.

1. Stop the synchronization.

2. Locate the GTID where the DROP table statement is executed.

3. Execute the GTID transaction;

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

4. Continue the synchronization.

5. 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, which can be used to generate flashback statements, project address 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