MySQL recovers data through binlog

MySQL deployed in dockers recovers data through binlog

ready

1. Confirm that binlog is turned on

show variables like 'log_%';

After execution, log_bin ON in the red box below means binlog is turned on

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-ZwWsZ9nM-1622520402739) (mysql binlog recovery data.assets/image-20210601084338042.png)]

2. Database tables and data to be operated

CREATE TABLE `study`.`tb_class` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `score` int(255) DEFAULT NULL,
  `grade` varchar(255) DEFAULT NULL,
  `test` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `study`.`tb_class`(`id`, `name`, `score`, `grade`, `test`) VALUES (1, 'a', 10, 'a', 'test');
INSERT INTO `study`.`tb_class`(`id`, `name`, `score`, `grade`, `test`) VALUES (2, 'b', 20, 'a', 'test');
INSERT INTO `study`.`tb_class`(`id`, `name`, `score`, `grade`, `test`) VALUES (3, 'c', 30, 'a', 'test');
INSERT INTO `study`.`tb_class`(`id`, `name`, `score`, `grade`, `test`) VALUES (4, 'd', 40, 'a', 'test');
INSERT INTO `study`.`tb_class`(`id`, `name`, `score`, `grade`, `test`) VALUES (6, 'f', 60, 'a', 'test');
INSERT INTO `study`.`tb_class`(`id`, `name`, `score`, `grade`, `test`) VALUES (11, 'aa', 10, 'aa', 'test');
INSERT INTO `study`.`tb_class`(`id`, `name`, `score`, `grade`, `test`) VALUES (21, 'ba', 20, 'aa', 'test');
INSERT INTO `study`.`tb_class`(`id`, `name`, `score`, `grade`, `test`) VALUES (31, 'ca', 30, 'aa', 'test');
INSERT INTO `study`.`tb_class`(`id`, `name`, `score`, `grade`, `test`) VALUES (41, 'da', 40, 'aa', 'test');
INSERT INTO `study`.`tb_class`(`id`, `name`, `score`, `grade`, `test`) VALUES (51, 'ea', 50, 'aa', 'test');
INSERT INTO `study`.`tb_class`(`id`, `name`, `score`, `grade`, `test`) VALUES (61, 'fa', 60, 'aa', 'test');
INSERT INTO `study`.`tb_class`(`id`, `name`, `score`, `grade`, `test`) VALUES (42, 'da', 40, 'aa', 'test');
INSERT INTO `study`.`tb_class`(`id`, `name`, `score`, `grade`, `test`) VALUES (52, 'ea', 50, 'aa', 'test');
INSERT INTO `study`.`tb_class`(`id`, `name`, `score`, `grade`, `test`) VALUES (62, 'fa', 60, 'aa', 'test');

After execution:

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Jh8dmeNE-1622520402744) (mysql binlog recovery data.assets/image-20210601084823036.png)]

3. Misoperation

Overwritten by the old backup file, the test column is missing and the data with id 11 is missing

/*
 Navicat Premium Data Transfer

 Source Server         : MyData
 Source Server Type    : MySQL
 Source Server Version : 50731
 Source Host           : 152.136.228.132:30030
 Source Schema         : study

 Target Server Type    : MySQL
 Target Server Version : 50731
 File Encoding         : 65001

 Date: 01/06/2021 08:51:51
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_class
-- ----------------------------
DROP TABLE IF EXISTS `study`.`tb_class`;
CREATE TABLE `study`.`tb_class`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `score` int(255) NULL DEFAULT NULL,
  `grade` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_class
-- ----------------------------
INSERT INTO `study`.`tb_class` VALUES (1, 'a', 10, 'a');
INSERT INTO `study`.`tb_class` VALUES (2, 'b', 20, 'a');
INSERT INTO `study`.`tb_class` VALUES (3, 'c', 30, 'a');
INSERT INTO `study`.`tb_class` VALUES (4, 'd', 40, 'a');
INSERT INTO `study`.`tb_class` VALUES (6, 'f', 60, 'a');
INSERT INTO `study`.`tb_class` VALUES (21, 'ba', 20, 'aa');
INSERT INTO `study`.`tb_class` VALUES (31, 'ca', 30, 'aa');
INSERT INTO `study`.`tb_class` VALUES (41, 'da', 40, 'aa');
INSERT INTO `study`.`tb_class` VALUES (42, 'da', 40, 'aa');
INSERT INTO `study`.`tb_class` VALUES (51, 'ea', 50, 'aa');
INSERT INTO `study`.`tb_class` VALUES (52, 'ea', 50, 'aa');
INSERT INTO `study`.`tb_class` VALUES (61, 'fa', 60, 'aa');
INSERT INTO `study`.`tb_class` VALUES (62, 'fa', 60, 'aa');

SET FOREIGN_KEY_CHECKS = 1;

After execution:

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-zzaCHTFL-1622520402746) (mysql binlog recovery data.assets/image-20210601090023418.png)]

restore

1. View the list of all binlog logs

show master logs;
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-Y7bbtmXk-1622520402749) (mysql binlog recovery data.assets/image-20210601111621691.png)]

2. View the master status, that is, the number name of the last (latest) binlog log, and the last operation event pos end point (Position) value

show master status;
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-mN6uvDoN-1622520402751) (mysql binlog recovery data.assets/image-20210601111646055.png)]

3. In order to prevent interference, we flush the log log and start generating a new numbered binlog log file from now on

flush logs;
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-f1TKZoqv-1622520402752) (mysql binlog recovery data.assets/image-20210601090435311.png)]

4. Confirm that binlog has been replaced with a new one

Perform the operation of recovery 2, the result of the query is not in the result of the query of recovery 1, but a new binlog file

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-NaOfBXFN-1622520402753) (mysql binlog recovery data.assets/image-20210601111741438.png)]

5. (Non-docker skip this step) Because my side is docker, and the mysqlbinlog command needs to be executed in the docker container

docker ps -a
docker exec -it mysql /bin/bash
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-K6z3ZrNL-1622520402754) (mysql binlog recovery data.assets/image-20210601092730606.png)]

6. Find the location of mysqlbinlog

which mysqlbinlog
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-q6hK1B29-1622520402755) (mysql binlog recovery data.assets/image-20210601092858941.png)]

7. Switch to the binlog storage location

[External link image transfer failed. The source site may have an anti-hotlink mechanism. It is recommended to save the image and upload it directly (img-DfuNX8zn-1622520402756) (mysql binlog recovery data.assets/image-20210601111822865.png)]

8. mysqlbinlog -v 恢复2的文件Find the wrong operation by printing the binlog information

文件的全部范围(文件过大不宜执行,可将结果转成文件,或者用下面的两种办法)
/usr/bin/mysqlbinlog -v mysql-bin.000013
指定位置范围
/usr/bin/mysqlbinlog -v mysql-bin.000013 --start-position=0 --stop-position=986
指定时间范围
/usr/bin/mysqlbinlog -v mysql-bin.000013 --start-datetime="2021-06-01 03:18:00" 

! ! ! Here you need to carefully check the log file

After searching for a period of time, I finally found the location. The last line in the red box is the delete table operation.

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-CDdFag88-1622520402757) (mysql binlog recovery data.assets/image-20210601112137609.png)]

9. If the current table structure is different from the original table structure, it is best to make the table structure the same

10. Perform recovery

Through the operation of the previous step, we found the deleted location5101(即第二个红框)

Then execute the statement:

/usr/bin/mysqlbinlog -v mysql-bin.000013 --stop-position=5036 -v | mysql -uroot -p自己MySQL的密码
[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-VWphxExZ-1622520402758) (mysql binlog recovery data.assets/image-20210601112711228.png)]

Error message here is here because I do not DROP TABLE IF EXISTS test is performed before the first construction of the table study. tb_classLeads, of course, the normal situation is the implementation of this before the construction of the table, had to present my side of the table to delete change Executed

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-ezImXnxs-1622520402759) (mysql binlog recovery data.assets/image-20210601113007345.png)]

At this point, there is no problem at present. Next, we verify whether the data is restored

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-2CcHOfye-1622520402760) (mysql binlog recovery data.assets/image-20210601113058031.png)]

At this point, the data has been completely restored

to sum up

  • When executing the table creation statement, add DROP TABLE IF EXISTSthis to the front to ensure that the database can be executed normally even if there is a table with the same name in the database
  • When planning to do recovery, execute flush logsit to prevent interference;
  • Before binlog recovery, ensure that the database table structure is consistent
  • Carefully find the position that needs to be restored