Shulou(Shulou.com)06/01 Report--

This article mainly explains the method of MySQL using mysqldump+binlog to completely restore the deleted database, the content is clear, interested friends can learn, I believe it will be helpful after reading.

(1) Overview

During the daily operation and maintenance of MySQL database, users may delete data by mistake. Common operations include:

The user executes delete, deletes the data that should not be deleted because the condition is wrong (DML operation), the user executes update, because the condition is incorrect, there is an error in updating the data (DML operation), the user deletes the table drop table by mistake (DDL operation), the user deletes the table truncate by mistake (DDL operation), the user deletes the database drop database, runs away (DDL operation). Etc.

Although these situations are not often encountered, we need to be able to restore them. Here is how to recover them.

(2) recovery principle

If you want to restore the database before the point of failure, you need to have all the binary logs generated after the database is complete and complete.

Full role: use full to restore the database to the location of the last full backup

Binary log function: after using the full backup set to restore the database to the location of the last full backup, you need to redo all the actions generated by the database after the last complete backup, and the redo process is to parse the binary log file into SQL statements, and then put it into the database to execute again.

For example: Xiao Ming used mysqldump to back up the database at 8:00 on April 1st. At 12:00 in the morning on April 2, Xiaohua accidentally deleted the database. Then, when performing database recovery, it is necessary to use the full backup on the evening of April 1st to restore the database to "8:00 in the evening of April 1st." How can the data be restored from 8:00 on April 1 to 12:00 in the morning on April 2? You have to redo the SQL that has been executed during this period by parsing the binary log.

(3) deletion and recovery test

(3.1) Experimental purpose

In this experiment, I directly test the deletion of the library, execute drop database lijiamandb, and confirm whether it can be restored.

(3.2) Test process

Create test tables test01 and test02 in the test database lijiamandb, then execute mysqldump to complete the database, and then execute drop database to confirm that database can be restored.

STEP1: create test data, in order to simulate the daily busy production environment, frequent operation of the database to generate a large number of binary logs, I specially use stored procedures and EVENT to generate a large amount of data.

Create a test table:

Use lijiamandb;create table test01 (id1 int not null auto_increment, name varchar (30), primary key (id1)); create table test02 (id2 int not null auto_increment, name varchar (30), primary key (id2))

Create a stored procedure, insert data into the test table, and insert 10000 pieces of data into test01 and test02 each time the stored procedure is executed:

CREATE DEFINER= `root` @ `% `PROCEDURE `p_ insert` () BEGIN#Routine body goes here...DECLARE str1 varchar (30); DECLARE str2 varchar (30); DECLARE I int;set I = 0position while I

< 10000 do set str1 = substring(md5(rand()),1,25); insert into test01(name) values(str1); set str2 = substring(md5(rand()),1,25); insert into test02(name) values(str1); set i = i + 1; end while; END 制定事件，每隔10秒钟，执行上面的存储过程： use lijiamandb; create event if not exists e_insert on schedule every 10 second on completion preserve do call p_insert(); 启动EVENT，每个10s自动向test01和test02各自插入10000条数据 mysql>

Show variables like'% event_scheduler%' +-- +-+ | Variable_name | Value | +- -+-+ | event_scheduler | OFF | +-- +-- + mysql > set global event_scheduler = on Query OK, 0 rows affected (0.08 sec)

In three minutes.

STEP2: after generating a large amount of test data in the first step, perform a full backup of the lijiamandb database using mysqldump

Mysqldump-h292.168.10.11-uroot-p123456-P3306-single-transaction-- master-data=2-- events-- routines-- databases lijiamandb > / mysql/backup/lijiamandb.sql

Note:-- master-data=2 must be added so that the destination of the mysqldump backup in the backup set will be found.

In three minutes.

STEP3: to facilitate the data consistency check before and after database deletion, stop the data insertion of the table first. At this time, both test01 and test02 have 930000 rows of data, and we need to ensure that there are 930000 rows of data in subsequent recovery.

Mysql > set global event_scheduler = off;Query OK, 0 rows affected (0.00 sec) mysql > select count (*) from test01; +-+ | count (*) | +-+ | 930000 | +-+ row in set (0.14 sec) mysql > select count (*) from test02 +-+ | count (*) | +-+ | 930000 | +-+ row in set (0.13sec)

STEP4: deleting a database

Mysql > drop database lijiamandb;Query OK, 2 rows affected (0.07 sec)

STEP5: full import using mysqldump

Mysql > create database lijiamandb;Query OK, 1 row affected (0.01sec) mysql > exit Bye [root@masterdb binlog] # mysql-uroot-p123456 lijiamandb

< /mysql/backup/lijiamandb.sql mysql: [Warning] Using a password on the command line interface can be insecure. 在执行全量备份恢复之后，发现只有753238笔数据： [root@masterdb binlog]# mysql -uroot -p123456 lijiamandb mysql>

Select count (*) from test01; +-+ | count (*) | +-+ | 753238 | +-+ row in set (0.12 sec) mysql > select count (*) from test02; +-+ | count (*) | +-+ | 753238 | +-+ row in set (0.11 sec)

Obviously, after the full import, the data is incomplete, and then use mysqlbinlog to perform an incremental recovery of the binary log.

The most important thing to use mysqlbinlog for incremental log recovery is to determine the start position (start-position) and the end position (stop-position) to be restored. The start position (start-position) is the position after we perform full coverage, while the end position is the position before the failure.

STEP6: confirm the final location where mysqldump is backed up

[root@masterdb backup] # cat lijiamandb.sql | grep "CHANGE MASTER"-CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000044', MASTER_LOG_POS=8526828

If you back up to 8526828 of log 44, the starting point for recovery can be set to 8526828 of log 44.

-- next, confirm the destination location to be restored, that is, the location before the execution of "DROP DATABASE LIJIAMAN". You need to confirm it in binlog.

[root@masterdb binlog] # ls master-bin.000001 master-bin.000010 master-bin.000019 master-bin.000028 master-bin.000037 master-bin.000046 master-bin.000055 master-bin.000002 master-bin.000011 master-bin.000020 master-bin.000029 master-bin.000038 master-bin.000047 master-bin.000056 master-bin.000003 master-bin.000012 master-bin.000021 master-bin.000030 master-bin.000039 master-bin.000048 master-bin.000057 master-bin.000004 master- Bin.000013 master-bin.000022 master-bin.000031 master-bin.000040 master-bin.000049 master-bin.000058 master-bin.000005 master-bin.000014 master-bin.000023 master-bin.000032 master-bin.000041 master-bin.000050 master-bin.000059 master-bin.000006 master-bin.000015 master-bin.000024 master-bin.000033 master-bin.000042 master-bin.000051 master-bin.index master-bin.000007 master-bin.000016 master-bin.000025 master-bin.000034 master- Bin.000043 master-bin.000052 master-bin.000008 master-bin.000017 master-bin.000026 master-bin.000035 master-bin.000044 master-bin.000053 master-bin.000009 master-bin.000018 master-bin.000027 master-bin.000036 master-bin.000045 master-bin.000054# multiple lookups Drop database was found in log file 54 [root@masterdb binlog] # mysqlbinlog-v master-bin.000056 | grep-I "drop database lijiamandb" [root@masterdb binlog] # mysqlbinlog-v master-bin.000055 | grep-I "drop database lijiamandb" [root@masterdb binlog] # mysqlbinlog-v master-bin.000055 | grep-I "drop database lijiamandb" [root@masterdb binlog] # mysqlbinlog-v master-bin.000054 | grep-I "drop database lijiamandb" drop database lijiamandb# saved to text It is convenient to search [root@masterdb binlog] # mysqlbinlog-v master-bin.000054 > master-bin.txt# to confirm that the previous location of drop database is: document 54 9019487 # at 9019422 # 200423 16:07:46 server id 11 end_log_pos 9019487 CRC32 0x86f13148 Anonymous_GTID last_committed=30266 sequence_number=30267 rbr_only=no SET @ @ SESSION.GTID_NEXT= 'ANONYMOUS retail retail account # at 9019487 # 200423 16:07:46 server id 11 end_log_pos 9019597 CRC32 0xbd6ea5dd Query thread_id=100 exec_time=0 error_code=0 SET timestamping 1587629266 pedigree; SET @ @ session.sqlautomotive autochangisymplicate nullable raceme; / *!\ C utf8 * / / *! /; SET @ session.session setclientconnection3 mindsession.collationconnectionconnection3title session. Collationalization serveration33cuspplains; session / *! / / # at 9019597 # 200423 16:09:25 server id 11 end_log_pos 9019662 CRC32 0x8f7b11dc Anonymous_GTID last_committed=30267 sequence_number=30268 rbr_only=noSET @ @ SESSION.GTID_NEXT= 'ANONYMOS license plaza; # at 9019662 # 200423 16:09:25 server id 11 end_log_pos 9019774 CRC32 0x9b42423d Query thread_id=100 exec_time=0 error_code=0 SET timestamp 1587629365 Accord; create database lijiamandb

STEP7: determine the start and end point and perform incremental recovery

Start: 8526828 of log 44

End: 9019487 of document 54

Here, it is divided into three commands: the starting log file involves the parameter start-position parameter, which is executed separately; the aborting file involves the stop-position parameter, which is executed separately; and the middle log file does not involve special parameters, all of which are executed together.

# start log file

# initial log file mysqlbinlog-- start-position=8526828 / mysql/binlog/master-bin.000044 | mysql-uroot-p123456 # Intermediate log file mysqlbinlog / mysql/binlog/master-bin.000045 / mysql/binlog/master-bin.000046 / mysql/binlog/master-bin.000047 / mysql/binlog/master-bin.000048 / mysql/binlog/master-bin.000049 / mysql/binlog/master-bin.000050 / mysql/binlog/master-bin.000051 / mysql/binlog/master-bin.000052 / Mysql/binlog/master-bin.000053 | mysql-uroot-p123456 # terminate the log file mysqlbinlog-- stop-position=9019487 / mysql/binlog/master-bin.000054 | mysql-uroot-p123456

STEP8: after the recovery, confirm that all data has been restored.

[root@masterdb binlog] # mysql-uroot-p123456 lijiamandbmysql > select count (*) from test01;+-+ | count (*) | +-+ | 930000 | +-+ row in set (0.15 sec) mysql > select count (*) from test02;+-+ | count (*) | +-+ | 930000 | +-+ row in set (0.13 sec)

(4) Summary

1. For DML operations, binlog records all DML data changes:

For insert,binlog, the row data of insert is recorded.

-- for update,binlog, the row data before and after changes are recorded.

-- for delete,binlog to record the data before deletion

If the user accidentally performs a DML operation, you can use mysqlbinlog to restore the database before the point of failure.

two。 For DDL operations, binlog only records user behavior, not row changes, but it does not affect us to restore the database to the point of failure.

In short, using mysqldump full plus binlog logs, the data can be restored to any time before the failure.

After reading the above content, do you have a better understanding of MySQL's method of using mysqldump+binlog to fully recover the deleted database? if you want to learn more, you are welcome to follow the industry information channel.

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.