Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

MySQL- realizes the best flashback actual combat based on binlog

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

1. Background

* Master and slave are built for data security. Real-time master-slave backups can only prevent hardware problems, such as damage to the hard drive of the master library. But for misoperation, there is nothing we can do. For example, a table is mistakenly deleted in the main database, or a update statement does not specify a where condition, resulting in the whole table being updated. When the operation is synchronized to the slave library, the master and slave are "powerless".

* flashback is used to quickly recover data lost due to misoperation. In the event of misoperation of DBA, you can restore the database to a previous point in time (or a pos of a binlog). For example, if you forget the update and delete operations with where conditions, the traditional recovery method is to use full backup + binary log roll forward for recovery. Compared with the traditional full backup + additional operations, flashback is obviously faster and simpler.

two。 Test environment

Mysql > show variables like 'version' +-+-+ | Variable_name | Value | +-+-+ | version | 5.6.36-log | +-+-+ 1 row in set (0.03 sec) mysql > show variables like 'datadir' +-+-+ | Variable_name | Value | +-+-+ | datadir | / data/mysql_data6/ | + -+ 1 row in set (0.00 sec) mysql > show variables like 'log_bin' +-+-+ | Variable_name | Value | +-+-+ | log_bin | ON | +-+-+ 1 row in set (0.00 sec) mysql > show variables like 'binlog_format' +-+-+ | Variable_name | Value | +-+-+ | binlog_format | ROW | +-+-+ 1 row in set (0.00 sec) mysql > show variables like 'binlog_row_p_w_picpath' +-+-+ | Variable_name | Value | +-+-+ | binlog_row_p_w_picpath | FULL | +-+-+ 1 row in set (0.00 sec)

3. Flashback to actual combat

* create databases and test tables and insert data

Mysql > create table users (- > id BIGINT NOT NULL AUTO_INCREMENT,-> name VARCHAR (255th) NOT NULL,-> sex ENUM ('age INT UNSIGNED NOT NULL DEFAULT,' F') NOT NULL DEFAULT 'NOT NULL,-> age INT UNSIGNED NOT NULL DEFAULT' 0mm,-> PRIMARY KEY (id)->) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 Query OK, 0 rows affected (0.04 sec) mysql > insert into users values (null, 'tom',' null, 32), (null, 'ses',' Mises, 45), (null, 'lisea',' sec, 35); Query OK, 4 rows affected (0.13 sec) Records: 4 Duplicates: 0 Warnings: 0mysql > select * from users +-+ | id | name | sex | age | +-+ | 1 | tom | M | 25 | 2 | jak | F | 32 | 3 | ses | M | 45 | 4 | lisea | M | 35 | +-+ 4 rows in set (0.00 sec)

* download the flashback tool binlog2sql [produced by Shanghai Meituan DBA team]

[root@MySQL] # git clone https://github.com/danfengcao/binlog2sql.gitInitialized empty Git repository in / root/binlog2sql/.git/remote: Counting objects: 244, done.remote: Total 244 (delta 0), reused 0 (delta 0), pack-reused 244Receiving objects: 100% (244 KiB), 121.72 KiB | 27 KiB/s, done.Resolving deltas: 100% (124 KiB), done.

* install related dependencies

[root@MySQL] # yum install pip-y [root@MySQL ~] # pip install-- upgrade pip [root@MySQL ~] # pip install-r binlog2sql/requirements.txt

* refresh binlog ahead of time [to distinguish files during testing]

Mysql > flush logs;Query OK, 0 rows affected (0.02 sec)

* View current binlog information

Mysql > show master status +-- + | File | Position | Binlog_Do_DB | Binlog_Ignore_ DB | Executed_Gtid_Set | +-- -+ | bin.000006 | 191 | c7f82640-6b2d-11e7-9316-000c29f0b169:1-22 | +-- -+ 1 row in set (0.01 sec)

* misoperation. Delete does not have where condition.

Mysql > delete from users;Query OK, 4 rows affected (0.01sec)

* the binlog2sql tool outputs operation information through files to locate the start and end locations of the SQL

Time can be located through-- start-datetime and-- stop-datetime.

As a result, the start position is 239 and the end position is 483.

[root@MySQL ~] # python binlog2sql/binlog2sql/binlog2sql.py-hlocalhost-P3306-uroot-prun123'-dmytest-tusers-- start-file='bin.000006' DELETE FROM `mytest`.`users`WHERE `age` = 25 AND `sex` ='M' AND `id` = 1 AND `name` = 'tom' LIMIT 1; # start 239 end 483 time 2017-07-19 01:02:49DELETE FROM `mytest`.`users`WHERE `age` = 32 AND `sex` =' F'AND `id` = 2 AND `name` = 'jak' LIMIT 1 # start 239 end 483 time 2017-07-19 01:02:49DELETE FROM `mytest`.`users`users`age` = 45 AND `sex` ='M 'AND `id` = 3 AND `name` =' ses' LIMIT 1; # start 239 end 483 time 2017-07-19 01:02:49DELETE FROM `mytest`.`users`WHERE `age` = 35 AND `sex` = 'M`AND `id` = 4 AND `name` =' lisea' LIMIT 1; # start 239 end 483 time 2017-07-19

* binlog2sql generates rollback SQL through flashback

[root@MySQL] # python binlog2sql/binlog2sql/binlog2sql.py-hlocalhost-P3306-uroot-pendant 123'-dmytest-tusers-- start-file='bin.000006'-- start-position=239-- stop-position=483-B > rollback.sql

* Import rollback SQL [check whether the SQL statement is normal before import]

[root@MySQL] # mysql-hlocalhost-uroot-pendant 123'

< rollback.sql * 查看 mysql>

Select * from mytest.users +-+ | id | name | sex | age | +-+ | 1 | tom | M | 25 | 2 | jak | F | 32 | 3 | ses | M | 45 | 4 | lisea | M | 35 | +-+ 4 rows in set (0.00 sec)

4. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report