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

This article mainly explains "what is the method of MySQL data recovery". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the method of MySQL data recovery".

1 preface

The premise of data recovery is to make a backup, and open binlog, the format is row. If there are no backup files, then delete the library table will really delete, if there are still records in lsof, it is possible to restore some files. But if it happens that the database does not open the table file, you will have to run away. If binlog is not enabled, the data from the backup point in time will be gone after the data is restored. If the binlog format is not row, then there is no way to do the flashback operation after mismanipulating the data, and you can only honestly follow the backup and recovery process.

2 Direct recovery

Direct recovery is the use of backup files for full recovery, which is the most common scenario.

2.1 full recovery of mysqldump backup

Using the mysqldump file to recover the data is very simple and directly decompresses the execution:

Gzip-d backup.sql.gz | mysql-u-h-P-p

2.2 full recovery of xtrabackup backup

Recovery process:

# step 1: decompress (this step can be ignored if there is no compression) innobackupex-- decompress # step 2: apply log innobackupex-- apply-log # step 3: copy backup files to the data directory innobackupex-- datadir=-- copy-back

2.3 Point-in-time recovery

Point-in-time recovery relies on binlog logs, and all logs from the backup point to the recovery point need to be found from the binlog, and then applied. Let's test it.

Create a new 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

Insert one piece of 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 done

Backup:

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

Find out the location of the log at the time of backup:

[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

Suppose we want to recover to the time point of 11:01:54 on 2019-08-09, we look up the log from 39654 to 39654-08-09 11:01:54 in binlog.

[mysql@mysql-test] $mysqlbinlog-- start-position=39654-- stop-datetime='2019-08-09 11 start-position=39654 01 stop-datetime='2019 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 # # @ 1mm 161 / * INT meta=0 nullable=0 is_null=0 * / # @ 2cm / * DATETIME (0) meta=0 nullable=1 is_null=0 * /.

Current number of data entries:

-- 2019-08-09 number of data entries before 11:01:54 chengqm-3306 > > select count (*) from mytest.mytest where ctime

< '2019-08-09 11:01:54'; +----------+ | count(*) | +----------+ | 161 | +----------+ 1 row in set (0.00 sec) -- 所有数据条数 chengqm-3306>

> select count (*) from mytest.mytest; +-+ | count (*) | +-+ | 180 | +-+ 1 row in set (0.00 sec)

Then perform the restore:

# full recovery [mysql@mysql-test ~] $mysql- S / tmp/mysql.sock

< backup.sql # 应用增量日志 [mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc.sql 检查数据： chengqm-3306>

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

It has been restored to 11:01:54 in 2019-08-09.

3 restore a table

3.1 restore a table from a mysqldump backup

Suppose the table to be restored is mytest.mytest:

# extract all the data from a library sed-n'/ ^-- Current Database: `mytest` /, / ^-- Current Database:/p' backup.sql > backup_mytest.sql # extract the table-building expression sed-estrangement from the backup file of the library. / {Hentinghead; create create TABLE `mytest` /! d Q 'backup_mytest.sql > mytest_table_create.sql # extract the insert data statement grep-I' INSERT INTO `mytest` 'backup_mytest.sql > mytest_table_insert.sql # from the library backup file to restore the table structure to the mytest library mysql-u-p mytest

< mytest_table_create.sql # 恢复表数据到 mytest.mytest 表 mysql -u -p mytest < mytest_table_insert.sql 3.2 从 xtrabackup 备份恢复一个表 假设 ./backup_xtra_full 目录为解压后应用过日志的备份文件。 3.2.1 MyISAM 表 假设从备份文件中恢复表 mytest.t_myisam。从备份文件中找到 t_myisam.frm, t_myisam.MYD, t_myisam.MYI 这 3 个文件，复制到对应的数据目录中，并授权进入 MySQL。检查表情况： 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 set (0.00 sec)

3.2.2 Innodb table

Suppose you restore the table mytest.t_innodb from the backup file, if innodb_file_per_table = on is set:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Give a new example

Create a table exactly like the original one on the instance

Execute alter table t_innodb discard tablespace; to delete the tablespace, which deletes the t_innodb.ibd

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

Perform alter table t_innodb IMPORT tablespace; load tablespace

Execute flush table tweak innobance check table tweak innobance; check list

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

Note:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

To restore and re-dump on the new instance is to avoid risk. For testing, you can perform steps 2-6 directly on the original library.

Valid only in versions prior to 8.0.

4 skipping misoperation of SQL

Skipping misoperation SQL is generally used to perform operations that cannot be flashback, such as drop table\ database.

4.1 Skip using backup file recovery

4.1.1 do not enable GTID

The steps to restore using backup files are similar to those based on a point-in-time restore, except that there is one more find binlog operation. For example, I set up two tables an and b here, insert one piece of data every minute, then make a full backup, and then delete table b. Now I'm going to skip this SQL.

Database status after deleting table b:

Chgnqm-3306 > > show tables; +-+ | Tables_in_mytest | +-+ | a | +-+ 1 row in set (0.00 sec)

1. Find out the location of the log at the time of 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

two。 Find out the pos location where the drop table statement was 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 1566128910Candle; DROP TABLE `b` / * generated by server * /

From the results, we can see that the statement where the drop is located starts at 120629 and ends at 120747.

3. Extract other records from binglog that skip this statement

# the start-position in Article 1 is the pos location of the backup file, and the stop-position is the start position of the drop statement mysqlbinlog-vv-- start-position=38414-- stop-position=120629 / data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_1.sql # the start-position in Article 2 is the end position of the drop statement mysqlbinlog-vv-- start-position=120747 / data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_2.sql

4. Restore backup files

[mysql@mysql-test ~] $mysql- S / tmp/mysql.sock

< backup.sql 全量恢复后状态： chgnqm-3306>

> show tables; +-+ | Tables_in_mytest | +-+ | a | | b | +-+ 2 rows in set (0.00 sec) chgnqm-3306 > > select count (*) from a +-+ | count (*) | +-+ | 71 | +-+ 1 row in set (0.00 sec)

5. Recover 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 恢复后状态，可以看到已经跳过了 drop 语句： chgnqm-3306>

> show tables; +-+ | Tables_in_mytest | +-+ | a | | b | +-+ 2 rows in set (0.00 sec) chgnqm-3306 > > select count (*) from a +-+ | count (*) | +-+ | 274 | +-+ 1 row in set (0.00 sec)

4.1.2 enable GTID

Using GTID, you can skip the wrong SQL directly:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Find out the location of the log at the time of backup

Find out the GTID value where the drop table statement was executed

Export the backup log location to the latest binglog log

Restore backup files

Skip this GTID

The GTID value'; BEGIN; COMMIT; SET SESSION GTID_NEXT= AUTOMATIC corresponding to SET SESSION GTID_NEXT='

6. Apply the incremental binlog log obtained in step 3.

4.2 Skip using the delay library

4.2.1 do not enable GTID

The key operation of using deferred library recovery is start slave until. I set up two MySQL nodes in the test environment, the second delay is 600s, create two new tables, and insert one data per second to simulate business data insertion.

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

Current Node 2 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...

Two tables of current nodes:

Chengqm-3307 > > show tables; +-+ | Tables_in_mytest | +-+ | a | | b | +-+

Delete table b at 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 set (0.00 sec)

The next step is to skip the SQL.

1. Delay the library to stop synchronization

Stop slave

two。 Find out the pos position of the previous sentence that executed the drop table statement

[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= 'ANONYMOUSpromotions # 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 * /

We can see from the result that the previous sentence of the drop statement starts at 35134, so let's synchronize to 35134 (don't make a mistake about this).

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

Check the status and see that it 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: 35134

4. Start synchronization after skipping a SQL

Set global sql_slave_skip_counter=1; start slave

To check the synchronization status, the statement to delete table b has been skipped:

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 set (0.00 sec)

4.2.2 enable GTID

The steps to skip with GTID are much easier, as long as you execute the same transaction as the GTID of the SQL you want to skip.

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Stop synchronization

Find the GTID that executed the drop table statement

Execute the transaction of this GTID

The GTID value'; BEGIN; COMMIT; SET SESSION GTID_NEXT= AUTOMATIC corresponding to SET SESSION GTID_NEXT='

4. Continue synchronization

5. Flashback.

Flashback operation is a reverse operation. For example, if delete from a where id=1 is executed, flashback will perform the corresponding insert operation insert into a (id,...). Values, used for mismanipulating data, is only valid for DML statements, and requires that the binlog format be set to ROW. This chapter introduces two open source tools that are easy to use.

5.1 binlog2sql

Binlog2sql is an open source Dianping 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 binlog2sql-master/ # installation depends on pip install-r requirements.txt

5.1.2 generate rollback SQL

Python binlog2sql/binlog2sql.py-- flashback\-h-P-u-packs'- d-t\-- start-file=''\-- start-datetime=''\-- stop-datetime='' >. / flashback.sql python binlog2sql/binlog2sql.py-- flashback\-h-P-u-packs'- d-t\-- start-file=''\-- start-position=\-- stop-position= >. / flashback.sql

5.2 MyFlash

MyFlash is a tool for rolling back DML operations developed and maintained by the Technical Engineering Department of Meituan Dianping Company, project link MyFlash.

Restrictions:

Binlog format must be row and binlog_row_image=full

Only 5.6 and 5.7 are supported

Only DML can be rolled back (add, delete, change).

5.2.1 installation

# dependent (centos) yum install gcc* pkg-config glib2 libgnomeui-devel-y # download file wget https://github.com/Meituan-Dianping/MyFlash/archive/master.zip-O MyFlash.zip unzip MyFlash.zip cd MyFlash-master # compile and install 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/flashback

5.2.2 use

Generate a rollback statement:

Flashback-databaseNames=-binlogFileNames=-start-position=-stop-position=

After execution, a binlog_output_base.flashback file is generated, which needs to be parsed with mysqlbinlog before using:

Mysqlbinlog-vv binlog_output_base.flashback | mysql-u-p Thank you for your reading. The above is the content of "what is the method of MySQL data recovery?" after the study of this article, I believe you have a deeper understanding of what the method of MySQL data recovery is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.