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

This article mainly explains the working principle of MySQL Mydumper. The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn the working principle of MySQL Mydumper.

Before ⒈ understands the principle of Mydumper, let's prepare to record some operations of Mydumper.

① set global general_log=1

② set global log_output='table'

③ mydumper-B Fish_Adventure-t 2-o / tmp/backup4

④ set global general_log=0

⒉ views general logging (deletes a lot of unnecessary data)

Mysql > select event_time,thread_id,argument from general_log

+-- +

| | event_time | thread_id | argument | |

+-+

| | 2018-04-12 18 51V 15.561645 | 21 | root@localhost on Fish_Adventure using Socket |

| | 2018-04-12 18 51V 15.566477 | 21 | SET SESSION wait_timeout = 2147483 |

| | 2018-04-12 18-18 51-15. 566787 | 21 | SET SESSION net_write_timeout = 2147483 | |

| | 2018-04-12 18-18-51-15-51-15. 567052 | 21 | SHOW PROCESSLIST |

| | 2018-04-12 18 51V 15.567301 | 21 | FLUSH TABLES WITH READ LOCK |

| | 2018-04-12 18-18 51-15 57 6656 | 21 | START TRANSACTION / *! 40108 WITH CONSISTENT SNAPSHOT * / |

| | 2018-04-12 18-18 SET NAMES binary*/ 51-15. 580067 | 21 | / *! 40101 |

| | 2018-04-12 18 51V 15.580271 | 21 | SHOW MASTER STATUS |

| | 2018-04-12 18 51V 15.580575 | 21 | SHOW SLAVE STATUS |

| | 2018-04-12 18 51-15. 581728 | 22 | root@localhost on using Socket |

| | 2018-04-12 18-18-51-15-58-2002 | 22 | SET SESSION wait_timeout = 2147483 |

| | 2018-04-12 18 51-15 582191 | 22 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ |

| | 2018-04-12 18-18 51-15. 582334 | 22 | START TRANSACTION / *! 40108 WITH CONSISTENT SNAPSHOT * / |

| | 2018-04-12 18-18 SET TIME_ZONE='+00:00' 51-15. 582568 | 22 | / *! 40103 SET TIME_ZONE='+00:00' * / |

| | 2018-04-12 10 51 SET NAMES binary*/ 15.582729 | 22 | / *! 40101 |

| | 2018-04-12 18 51-15. 583656 | 23 | root@localhost on using Socket |

| | 2018-04-12 18 51-15. 583930 | 23 | SET SESSION wait_timeout = 2147483 |

| | 2018-04-12 18-18-51-15-58-4122 | 23 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ |

| | 2018-04-12 18-18 51-15. 584242 | 23 | START TRANSACTION / *! 40108 WITH CONSISTENT SNAPSHOT * / |

| | 2018-04-12 18 51-15. 584378 | 23 | / *! 40103 SET TIME_ZONE='+00:00' * / | |

| | 2018-04-12 10 51 SET NAMES binary*/ 15.584562 | 23 | / *! 40101 |

| | 2018-04-12 18-18-51-15-58-4803 | 21 | Fish_Adventure |

| | 2018-04-12 18 51-15. 584978 | 21 | SHOW TABLE STATUS |

| | 2018-04-12 18 51VR 15.636023 | 21 | SHOW CREATE DATABASE `Adventure` | |

| | 2018-04-12 10 Fish_ 51VR 15.636771 | 22 | SELECT / *! 40001 SQL_NO_CACHE * / * FROM `Fish_ Adventure`.`Operation` |

| | 2018-04-12 10 FROM 51 SQL_NO_CACHE 15.636801 | 23 | SELECT / *! 40001 FROM * / * FROM `Fish_ Adventure`.`account _ unionid_ map` |

| | 2018-04-12 10 Fish_ 51VR 15.637312 | 23 | SELECT / *! 40001 SQL_NO_CACHE * / * FROM `Fish_ Adventure`.`admin _ log` |

| | 2018-04-12 18-18 51-15. 642609 | 21 | UNLOCK TABLES / * FTWRL * / |

| | 2018-04-12 10 FROM 51 SQL_NO_CACHE 15.642643 | 23 | SELECT / *! 40001 RoundRecord` / * FROM `BlueRed20RoundRecord` |

| | 2018-04-12 18-18-51-15-64-2771 | 21 | |

| | 2018-04-12 10 FROM 51 SQL_NO_CACHE 15.643613 | 22 | FROM / *! 40001 Fish_ Adventure`.BlueRedBet` |

| | 2018-04-12 10 FROM 51VR 15.643698 | 23 | SELECT / *! 40001 SQL_NO_CACHE * / * FROM `BlueRedOfflinePlayerInfo` |

| | 2018-04-12 10 FROM 51 SQL_NO_CACHE 15.644064 | 22 | FROM / *! 40001 Fish_ Adventure`.BlueRedRank` |

| | 2018-04-12 10 FROM 51VR 15.645180 | 23 | SELECT / *! 40001 SQL_NO_CACHE * / * FROM `Fish_ Adventure`.`BlueRedReward` |

| | 2018-04-12 10 FROM 51 SQL_NO_CACHE 15.646225 | 23 | FROM / *! 40001 Fish_ Adventure`BonusExchange` |

| | 2018-04-12 10 Fish_ 51VR 15.646982 | 23 | SELECT / *! 40001 SQL_NO_CACHE * / * FROM `Fish_ Adventure`.`Cannon` |

| | 2018-04-12 10 FROM 51VR 15.648696 | 23 | SELECT / *! 40001 SQL_NO_CACHE * / * FROM `Fish_ Adventure`.`Chuanying` |

| | 2018-04-12 10 FROM 51 SQL_NO_CACHE 15.656970 | 22 | SELECT / *! 40001 FROM * / * Fish_ Adventure`.`PlayerMahjong` |

| | 2018-04-12 10 FROM 51VR 15.658623 | 22 | SELECT / *! 40001 SQL_NO_CACHE * / * FROM `Fish_ Adventure`.`PlayerPurchase` |

| | 2018-04-12 10 FROM 51VR 15.659403 | 22 | SELECT / *! 40001 SQL_NO_CACHE * / * FROM `Fish_ Adventure`.`PlayerPurchaseRank` |

| | 2018-04-12 10 FROM 51VR 15.659747 | 22 | SELECT / *! 40001 SQL_NO_CACHE * / * FROM `Fish_ Adventure`.`PlayerRank` |

| | 2018-04-12 10 FROM 51VR 15.660186 | 22 | SELECT / *! 40001 SQL_NO_CACHE * / * FROM `Fish_ Adventure`.`PlayerRedeem` |

| | 2018-04-12 10 FROM 51VR 15.660479 | 22 | SELECT / *! 40001 SQL_NO_CACHE * / * FROM `Fish_ Adventure`.`PlayerRoom` |

| | 2018-04-12 10 FROM 51VR 15.660758 | 22 | SELECT / *! 40001 SQL_NO_CACHE * / * FROM `Fish_ Adventure`.`PlayerShare` |

...

| | 2018-04-12 10 51VR 15.708328 | 22 | SHOW CREATE TABLE `Adventure`.`Name _ Room` | |

| | 2018-04-12 10 51VR 15.708613 | 22 | SHOW CREATE TABLE `Adventure`.`NoticeInfo` | |

| | 2018-04-12 10 51VR 15.708862 | 22 | SHOW CREATE TABLE `Adventure`.`OfflineChat` | |

| | 2018-04-12 10 51VR 15.709106 | 22 | SHOW CREATE TABLE `Adventure`.`Operation` | |

| | 2018-04-12 10 51VR 15.709303 | 22 | SHOW CREATE TABLE `Adventure`.`Orders` | |

...

| | 2018-04-12 10 51V 15.742110 | 22 | Fish_Adventure |

| | 2018-04-12 10 51VR 15.742289 | 22 | SHOW FIELDS FROM `GroupFisyStack` |

| | 2018-04-12 10 51VR 15.742731 | 22 | SHOW CREATE VIEW `GroupFisyStack` |

| | 2018-04-12 10 51V 15.747712 | 22 | Fish_Adventure |

| | 2018-04-12 10 Fish_ 51VR 15.747894 | 22 | SHOW FIELDS FROM `LoginRewardInfo` |

| | 2018-04-12 10 51VR 15.748337 | 22 | SHOW CREATE VIEW `LoginRewardInfo` |

| | 2018-04-12 10 51V 15.748596 | 22 | Fish_Adventure |

| | 2018-04-12 10 51VR 15.748775 | 22 | SHOW FIELDS FROM `Adventure`.`MahjongInfo` | |

| | 2018-04-12 10 51VR 15.749369 | 22 | SHOW CREATE VIEW `Adventure`.`MahjongInfo` | |

| | 2018-04-12 10 51V 15.749721 | 22 | Fish_Adventure |

| | 2018-04-12 10 51 15.749907 | 22 | SHOW FIELDS FROM `Adventure`.`MailInfo` | |

| | 2018-04-12 10 51V 15.750364 | 22 | SHOW CREATE VIEW `Adventure`.`MailInfo` | |

| | 2018-04-12 10 51V 15.750634 | 22 | Fish_Adventure |

| | 2018-04-12 10 51VR 15.750811 | 22 | SHOW FIELDS FROM `Adventure`.`MatchRewardInfo` | |

| | 2018-04-12 10 51 15.751279 | 22 | SHOW CREATE VIEW `Adventure`.`MatchRewardInfo` | |

| | 2018-04-12 10 51VR 15.751589 | 22 | Fish_Adventure |

| | 2018-04-12 10 51VR 15.751780 | 22 | SHOW FIELDS FROM `Adventure`.`Manners Fish` | |

| | 2018-04-12 10 51VR 15.752199 | 22 | SHOW CREATE VIEW `Adventure`.`MainFish` | |

| | 2018-04-12 10 51-15. 754378 | 22 | Fish_Adventure |

| | 2018-04-12 10 51VR 15.754624 | 22 | SHOW FIELDS FROM `Adventure`.`PlayerLevelInfo` | |

| | 2018-04-12 10 51VR 15.755115 | 22 | SHOW CREATE VIEW `Adventure`.`PlayerLevelInfo` | |

| | 2018-04-12 10 51-15. 755363 | 22 | Fish_Adventure |

| | 2018-04-12 10 51VR 15.755564 | 22 | SHOW FIELDS FROM `RechargeInfo` |

| | 2018-04-12 10 51VR 15.756095 | 22 | SHOW CREATE VIEW `RechargeInfo` |

| | 2018-04-12 10 51VR 15.756350 | 22 | Fish_Adventure |

| | 2018-04-12 10 51VR 15.756561 | 22 | SHOW FIELDS FROM `RedeemCode` |

| | 2018-04-12 10 51 15.757055 | 22 | SHOW CREATE VIEW `Adventure`.`RedeemCode` |

| | 2018-04-12 10 51-15. 757289 | 22 | Fish_Adventure |

| | 2018-04-12 10 51VR 15.757471 | 22 | SHOW FIELDS FROM `Adventure`.`RewardList` | |

| | 2018-04-12 10 51VR 15.758020 | 22 | SHOW CREATE VIEW `Adventure`.`RewardList` | |

| | 2018-04-12 10 51-15. 758284 | 22 | Fish_Adventure |

| | 2018-04-12 10 51VR 15.758480 | 22 | SHOW FIELDS FROM `Adventure`.`ShipInfo` | |

| | 2018-04-12 10 51VR 15.759167 | 22 | SHOW CREATE VIEW `Adventure`.`ShipInfo` | |

...

| | 2018-04-12 10 51VR 15.761377 | 22 | Fish_Adventure |

| | 2018-04-12 10 51VR 15.761627 | 22 | SHOW FIELDS FROM `Adventure`.`UpgradeInfo` | |

| | 2018-04-12 10 51VR 15.762157 | 22 | SHOW CREATE VIEW `Adventure`.`UpgradeInfo` | |

| | 2018-04-12 10 51VR 15.762440 | 22 | Fish_Adventure |

| | 2018-04-12 10 Fish_ 51VR 15.762670 | 22 | `VipBonous` | SHOW FIELDS FROM `VipBonous` |

| | 2018-04-12 10 51VR 15.763208 | 22 | SHOW CREATE VIEW `VipBonous` |

| | 2018-04-12 10 51VR 15.763631 | 22 | |

| | 2018-04-12 10-51-15. 770707 | 23 | |

| | 2018-04-12 18-18-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-2-1-2-4-12. | 17 | set global general_log=0 |

+-+

Thread relationship in ⒊ reading table

Thread 17 mysql is the session of my current operation, and in addition to thread 17 mysql, there are three threads on 21-22-23

The 21st mysql thread did not execute SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, indicating that it was initiated by the Mydumper main process.

Mysql thread 22Aver 23 is exporting data, so it is two threads initiated by Mydumper (specified by mydumper-T2)

Analysis of ⒋ principle:

⑴ FLUSH TABLE WITH READ LOCK

This means to lock all tables, and its role is to get the current binary position and to implement consistent backups.

⑵ SHOW MASTER STATUS/SHOW SLAVE STATUS

Gets the location of the binary log.

Mysql thread execution of ⑶ 21: START TRANSACTION / *! 40108 WITH CONSISTENT SNAPSHOT * /

There is no need to set the isolation level to be readable repeatedly, because 21 good threads correspond to the main process of Mydumper and do not participate in exporting data.

The mydumper thread corresponding to ⑷ 22 WITH CONSISTENT SNAPSHOT 23 sets SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ and START TRANSACTION / *! 40108 thread * /

22All 23 are 2 threads set up by mydumper-t 2 to export data, all of which need to turn on the repeatable read isolation level.

⑸ UNLOCK TABLES

The table lock can be released after obtaining the binlog location and opening the repeatable readable, so that the hot standby can be carried out, and the locking time of the table is not long, which basically has no impact on the business.

⑹ exports data files and table structure files

Here, the data is first exported with mysql, and then the table structure and view are exported. (in fact, there is a small detail here, that is, to export the data first, and then to export the table structure. In fact, this opens two task queues queue, one is the innodb table, and the other is the definition of schema and table structure. If there is a MyISAM table, there will be a third queue, the non-transactional table, whose data will be exported before step (5). The purpose here is to ensure the consistency between the non-transactional table and the transactional table, while not locking the entire backup process, such as `Adventure`.`admin _ log` above, which I forgot to write before and added here).

⒌ Summary:

In fact, the working principle of mydumper is similar to that of mysqldump, but the difference is that mydumper uses multithreading in the process of exporting data, so it is relatively faster than mysqldump, so it is hard to imagine that mysqldump does not implement this function.

Advantages of ⒍ mydumper

Mydumper can back up multithreaded backups, and what is more valuable is that it can perform multithreaded backups of a single table (the primary key of the backup must be shaped and cannot be a compound index); myloader can specify a table to restore because the tables exported by mydumper are separate.

Thank you for your reading, the above is the content of "the working principle of MySQL Mydumper", after the study of this article, I believe you have a deeper understanding of the working principle of MySQL Mydumper, 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.