MySQL restores data through binlog

prepare

1. Confirm that binlog is enabled

show variables like 'log_%';

After execution, log in the red box below_ Bin on means binlog is on

2. Prepare database tables and data for operation

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:

3. Perform misoperation

It is overwritten by the old backup file, which lacks the test column and the data with id 11

/*
 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:

recovery

1. View the list of all binlog logs

show master logs;

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

show master status;

3. In order to prevent interference, we flush refresh the log log and generate a binlog log file with a new number from this moment

flush logs;

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

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

5. (skip this step for non docker) since I am docker, the mysqlbinlog command needs to be executed in the docker container

docker ps -a
docker exec -it mysql /bin/bash

6. Find the location of mysqlbinlog

which mysqlbinlog

7. Switch to binlog storage location

8. Recover the file of 2 through mysqlbinlog -v, print binlog information and find the place of misoperation

Full scope of the document (if the document is too large to be implemented, the results can be converted into documents, or the following two methods can be used)
/usr/bin/mysqlbinlog -v mysql-bin.000013
 Specify location range
/usr/bin/mysqlbinlog -v mysql-bin.000013 --start-position=0 --end-position=986
 Specify time range
/usr/bin/mysqlbinlog -v mysql-bin.000013 --start-datetime="2021-06-01 03:18:00"

!!! You need to check the log file carefully here

After searching for a period of time, the position is finally found. The last row in the red box is the operation of deleting the table

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

10. Perform recovery

Through the operation in the previous step, we found the deleted position 5101 (i.e. the second red box)

Execute the following statement:

/usr/bin/mysqlbinlog -v mysql-bin.000013 --stop-position=5036 -v | mysql -uroot -p own MySQL Password for

The error message here is because the test I did here did not execute drop table if exists study before the first table creation tb_ Class. Of course, it is normal to execute this item before creating the table. At present, I have to delete and change the table first

So far, there is no problem. Next, let's verify whether the data is recovered

So far, the data has been completely recovered

summary

  • When executing the table creation statement, add DROP TABLE IF EXISTS in front to ensure that the database can be executed normally even if there are tables with the same name in the database
  • When you plan to restore, execute flush logs to prevent interference;
  • Before binlog recovery, ensure that the database table structure is consistent
  • Carefully find the position location that needs to be restored

Keywords: Database MySQL

Added by atzi on Thu, 03 Feb 2022 00:55:55 +0200

Popular Keywords

©2024 Programming VIP