【MySQL】Mydumper工作原理
⒈在理解Mydumper原理之前，我們先準備記錄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;
⒉檢視通用日誌記錄(刪除了很多沒必要的資料)
mysql> select event_time,thread_id,argument from general_log;
+----------------------------+-----------+------------------------------------------------------------------------------------+
| event_time | thread_id | argument |
+----------------------------+-----------+------------------------------------------------------------------------------------+
| 2018-04-12 18:51:15.561645 | 21 | root@localhost on Fish_Adventure using Socket |
| 2018-04-12 18:51:15.566477 | 21 | SET SESSION wait_timeout = 2147483 |
| 2018-04-12 18:51:15.566787 | 21 | SET SESSION net_write_timeout = 2147483 |
| 2018-04-12 18:51:15.567052 | 21 | SHOW PROCESSLIST |
| 2018-04-12 18:51:15.567301 | 21 | FLUSH TABLES WITH READ LOCK |
| 2018-04-12 18:51:15.576656 | 21 | START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ |
| 2018-04-12 18:51:15.580067 | 21 | /*!40101 SET NAMES binary*/ |
| 2018-04-12 18:51:15.580271 | 21 | SHOW MASTER STATUS |
| 2018-04-12 18:51:15.580575 | 21 | SHOW SLAVE STATUS |
| 2018-04-12 18:51:15.581728 | 22 | root@localhost on using Socket |
| 2018-04-12 18:51:15.582002 | 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:51:15.582334 | 22 | START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ |
| 2018-04-12 18:51:15.582568 | 22 | /*!40103 SET TIME_ZONE='+00:00' */ |
| 2018-04-12 10:51:15.582729 | 22 | /*!40101 SET NAMES binary*/ |
| 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:51:15.584122 | 23 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ |
| 2018-04-12 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:15.584562 | 23 | /*!40101 SET NAMES binary*/ |
| 2018-04-12 18:51:15.584803 | 21 | Fish_Adventure |
| 2018-04-12 18:51:15.584978 | 21 | SHOW TABLE STATUS |
| 2018-04-12 18:51:15.636023 | 21 | SHOW CREATE DATABASE `Fish_Adventure` |
| 2018-04-12 10:51:15.636771 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`Operation` |
| 2018-04-12 10:51:15.636801 | 23 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`account_unionid_map` |
| 2018-04-12 10:51:15.637312 | 23 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`admin_log` |
| 2018-04-12 18:51:15.642609 | 21 | UNLOCK TABLES /* FTWRL */ |
| 2018-04-12 10:51:15.642643 | 23 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`BlueRed20RoundRecord` |
| 2018-04-12 18:51:15.642771 | 21 | |
| 2018-04-12 10:51:15.643613 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`BlueRedBet` |
| 2018-04-12 10:51:15.643698 | 23 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`BlueRedOfflinePlayerInfo` |
| 2018-04-12 10:51:15.644064 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`BlueRedRank` |
| 2018-04-12 10:51:15.645180 | 23 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`BlueRedReward` |
| 2018-04-12 10:51:15.646225 | 23 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`BonusExchange` |
| 2018-04-12 10:51:15.646982 | 23 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`Cannon` |
| 2018-04-12 10:51:15.648696 | 23 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`Chuanying` |
| 2018-04-12 10:51:15.656970 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`PlayerMahjong` |
| 2018-04-12 10:51:15.658623 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`PlayerPurchase` |
| 2018-04-12 10:51:15.659403 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`PlayerPurchaseRank` |
| 2018-04-12 10:51:15.659747 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`PlayerRank` |
| 2018-04-12 10:51:15.660186 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`PlayerRedeem` |
| 2018-04-12 10:51:15.660479 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`PlayerRoom` |
| 2018-04-12 10:51:15.660758 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`PlayerShare` |
...
| 2018-04-12 10:51:15.708328 | 22 | SHOW CREATE TABLE `Fish_Adventure`.`Name_Room` |
| 2018-04-12 10:51:15.708613 | 22 | SHOW CREATE TABLE `Fish_Adventure`.`NoticeInfo` |
| 2018-04-12 10:51:15.708862 | 22 | SHOW CREATE TABLE `Fish_Adventure`.`OfflineChat` |
| 2018-04-12 10:51:15.709106 | 22 | SHOW CREATE TABLE `Fish_Adventure`.`Operation` |
| 2018-04-12 10:51:15.709303 | 22 | SHOW CREATE TABLE `Fish_Adventure`.`Orders` |
...
| 2018-04-12 10:51:15.742110 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.742289 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`GroupFisheryStack` |
| 2018-04-12 10:51:15.742731 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`GroupFisheryStack` |
| 2018-04-12 10:51:15.747712 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.747894 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`LoginRewardInfo` |
| 2018-04-12 10:51:15.748337 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`LoginRewardInfo` |
| 2018-04-12 10:51:15.748596 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.748775 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`MahjongInfo` |
| 2018-04-12 10:51:15.749369 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`MahjongInfo` |
| 2018-04-12 10:51:15.749721 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.749907 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`MailInfo` |
| 2018-04-12 10:51:15.750364 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`MailInfo` |
| 2018-04-12 10:51:15.750634 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.750811 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`MatchRewardInfo` |
| 2018-04-12 10:51:15.751279 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`MatchRewardInfo` |
| 2018-04-12 10:51:15.751589 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.751780 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`MissionFish` |
| 2018-04-12 10:51:15.752199 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`MissionFish` |
| 2018-04-12 10:51:15.754378 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.754624 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`PlayerLevelInfo` |
| 2018-04-12 10:51:15.755115 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`PlayerLevelInfo` |
| 2018-04-12 10:51:15.755363 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.755564 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`RechargeInfo` |
| 2018-04-12 10:51:15.756095 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`RechargeInfo` |
| 2018-04-12 10:51:15.756350 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.756561 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`RedeemCode` |
| 2018-04-12 10:51:15.757055 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`RedeemCode` |
| 2018-04-12 10:51:15.757289 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.757471 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`RewardList` |
| 2018-04-12 10:51:15.758020 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`RewardList` |
| 2018-04-12 10:51:15.758284 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.758480 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`ShipInfo` |
| 2018-04-12 10:51:15.759167 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`ShipInfo` |
...
| 2018-04-12 10:51:15.761377 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.761627 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`UpgradeInfo` |
| 2018-04-12 10:51:15.762157 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`UpgradeInfo` |
| 2018-04-12 10:51:15.762440 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.762670 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`VipBonous` |
| 2018-04-12 10:51:15.763208 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`VipBonous` |
| 2018-04-12 10:51:15.763631 | 22 | |
| 2018-04-12 10:51:15.770707 | 23 | |
| 2018-04-12 18:51:23.241235 | 17 | set global general_log=0 |
+----------------------------+-----------+------------------------------------------------------------------------------------+
⒊閱讀表中執行緒關係
17號mysql執行緒是我當前的操作的會話，而除了17號mysql執行緒外，還有21/22/23這三個執行緒
21號mysql執行緒沒有執行 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ，說明是Mydumper主程式發起的
22/23號mysql執行緒在匯出資料，所以是Mydumper發起的兩個執行緒（由mydumper -t 2指定的）
⒋原理分析：
⑴FLUSH TABLE WITH READ LOCK;
這句話的意思是鎖住所有的表，他的作用是獲取當前的二進位制位置，以及實現一致性備份。
⑵SHOW MASTER STATUS/SHOW SLAVE STATUS;
獲取二進位制日誌的位置。
⑶21號mysql執行緒執行：START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
這裡並不需要把隔離級別設定為可重複讀，因為21好執行緒對應Mydumper的主程式，不參與匯出資料。
⑷22/23對應的mydumper執行緒設定 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ以及 START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
22/23是再mydumper -t 2設定的2個執行緒，目的是匯出資料，所有需要開啟可重複讀隔離級別。
⑸UNLOCK TABLES
在獲取binlog位置以及開啟可重複讀之後即可釋放表鎖，這樣就可以進行熱備，鎖表的時間也不長，對業務基本沒影響
⑹匯出資料檔案以及表結構檔案
這裡是資料先用mysql匯出，然後匯出表結構以及檢視(這裡其實還有一個小細節，就是先匯出資料，然後匯出表結構，其實這是開啟了兩個任務佇列queue，一個是innodb表，另外一個是schema與表結構的定義；如果有MyISAM表的話，還會有第三個佇列，非事務的表，他的資料會在第(5)步之前匯出，這裡的目的是保證非事務表與事務表的一致性，同時不會鎖整個備份的過程，如上面的`Fish_Adventure`.`admin_log` ，前面自己忘了寫了，這裡補上)。
⒌總結：
其實mydumper跟mysqldump的工作原理相似，不同之處是mydumper在匯出資料的過程中用了多執行緒，所以速度相對與mysqldump要快，很難想象mysqldump竟然不實現這個功能
⒍mydumper優點
mydumper可以對多執行緒備份，更可貴的是可以進行單個表進行多執行緒的備份（前提條件，備份的主鍵一定是要整形的並且不能是複合索引）;myloader可以指定某個表來恢復，因為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;
⒉檢視通用日誌記錄(刪除了很多沒必要的資料)
mysql> select event_time,thread_id,argument from general_log;
+----------------------------+-----------+------------------------------------------------------------------------------------+
| event_time | thread_id | argument |
+----------------------------+-----------+------------------------------------------------------------------------------------+
| 2018-04-12 18:51:15.561645 | 21 | root@localhost on Fish_Adventure using Socket |
| 2018-04-12 18:51:15.566477 | 21 | SET SESSION wait_timeout = 2147483 |
| 2018-04-12 18:51:15.566787 | 21 | SET SESSION net_write_timeout = 2147483 |
| 2018-04-12 18:51:15.567052 | 21 | SHOW PROCESSLIST |
| 2018-04-12 18:51:15.567301 | 21 | FLUSH TABLES WITH READ LOCK |
| 2018-04-12 18:51:15.576656 | 21 | START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ |
| 2018-04-12 18:51:15.580067 | 21 | /*!40101 SET NAMES binary*/ |
| 2018-04-12 18:51:15.580271 | 21 | SHOW MASTER STATUS |
| 2018-04-12 18:51:15.580575 | 21 | SHOW SLAVE STATUS |
| 2018-04-12 18:51:15.581728 | 22 | root@localhost on using Socket |
| 2018-04-12 18:51:15.582002 | 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:51:15.582334 | 22 | START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ |
| 2018-04-12 18:51:15.582568 | 22 | /*!40103 SET TIME_ZONE='+00:00' */ |
| 2018-04-12 10:51:15.582729 | 22 | /*!40101 SET NAMES binary*/ |
| 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:51:15.584122 | 23 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ |
| 2018-04-12 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:15.584562 | 23 | /*!40101 SET NAMES binary*/ |
| 2018-04-12 18:51:15.584803 | 21 | Fish_Adventure |
| 2018-04-12 18:51:15.584978 | 21 | SHOW TABLE STATUS |
| 2018-04-12 18:51:15.636023 | 21 | SHOW CREATE DATABASE `Fish_Adventure` |
| 2018-04-12 10:51:15.636771 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`Operation` |
| 2018-04-12 10:51:15.636801 | 23 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`account_unionid_map` |
| 2018-04-12 10:51:15.637312 | 23 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`admin_log` |
| 2018-04-12 18:51:15.642609 | 21 | UNLOCK TABLES /* FTWRL */ |
| 2018-04-12 10:51:15.642643 | 23 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`BlueRed20RoundRecord` |
| 2018-04-12 18:51:15.642771 | 21 | |
| 2018-04-12 10:51:15.643613 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`BlueRedBet` |
| 2018-04-12 10:51:15.643698 | 23 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`BlueRedOfflinePlayerInfo` |
| 2018-04-12 10:51:15.644064 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`BlueRedRank` |
| 2018-04-12 10:51:15.645180 | 23 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`BlueRedReward` |
| 2018-04-12 10:51:15.646225 | 23 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`BonusExchange` |
| 2018-04-12 10:51:15.646982 | 23 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`Cannon` |
| 2018-04-12 10:51:15.648696 | 23 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`Chuanying` |
| 2018-04-12 10:51:15.656970 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`PlayerMahjong` |
| 2018-04-12 10:51:15.658623 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`PlayerPurchase` |
| 2018-04-12 10:51:15.659403 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`PlayerPurchaseRank` |
| 2018-04-12 10:51:15.659747 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`PlayerRank` |
| 2018-04-12 10:51:15.660186 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`PlayerRedeem` |
| 2018-04-12 10:51:15.660479 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`PlayerRoom` |
| 2018-04-12 10:51:15.660758 | 22 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `Fish_Adventure`.`PlayerShare` |
...
| 2018-04-12 10:51:15.708328 | 22 | SHOW CREATE TABLE `Fish_Adventure`.`Name_Room` |
| 2018-04-12 10:51:15.708613 | 22 | SHOW CREATE TABLE `Fish_Adventure`.`NoticeInfo` |
| 2018-04-12 10:51:15.708862 | 22 | SHOW CREATE TABLE `Fish_Adventure`.`OfflineChat` |
| 2018-04-12 10:51:15.709106 | 22 | SHOW CREATE TABLE `Fish_Adventure`.`Operation` |
| 2018-04-12 10:51:15.709303 | 22 | SHOW CREATE TABLE `Fish_Adventure`.`Orders` |
...
| 2018-04-12 10:51:15.742110 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.742289 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`GroupFisheryStack` |
| 2018-04-12 10:51:15.742731 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`GroupFisheryStack` |
| 2018-04-12 10:51:15.747712 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.747894 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`LoginRewardInfo` |
| 2018-04-12 10:51:15.748337 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`LoginRewardInfo` |
| 2018-04-12 10:51:15.748596 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.748775 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`MahjongInfo` |
| 2018-04-12 10:51:15.749369 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`MahjongInfo` |
| 2018-04-12 10:51:15.749721 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.749907 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`MailInfo` |
| 2018-04-12 10:51:15.750364 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`MailInfo` |
| 2018-04-12 10:51:15.750634 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.750811 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`MatchRewardInfo` |
| 2018-04-12 10:51:15.751279 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`MatchRewardInfo` |
| 2018-04-12 10:51:15.751589 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.751780 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`MissionFish` |
| 2018-04-12 10:51:15.752199 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`MissionFish` |
| 2018-04-12 10:51:15.754378 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.754624 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`PlayerLevelInfo` |
| 2018-04-12 10:51:15.755115 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`PlayerLevelInfo` |
| 2018-04-12 10:51:15.755363 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.755564 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`RechargeInfo` |
| 2018-04-12 10:51:15.756095 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`RechargeInfo` |
| 2018-04-12 10:51:15.756350 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.756561 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`RedeemCode` |
| 2018-04-12 10:51:15.757055 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`RedeemCode` |
| 2018-04-12 10:51:15.757289 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.757471 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`RewardList` |
| 2018-04-12 10:51:15.758020 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`RewardList` |
| 2018-04-12 10:51:15.758284 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.758480 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`ShipInfo` |
| 2018-04-12 10:51:15.759167 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`ShipInfo` |
...
| 2018-04-12 10:51:15.761377 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.761627 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`UpgradeInfo` |
| 2018-04-12 10:51:15.762157 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`UpgradeInfo` |
| 2018-04-12 10:51:15.762440 | 22 | Fish_Adventure |
| 2018-04-12 10:51:15.762670 | 22 | SHOW FIELDS FROM `Fish_Adventure`.`VipBonous` |
| 2018-04-12 10:51:15.763208 | 22 | SHOW CREATE VIEW `Fish_Adventure`.`VipBonous` |
| 2018-04-12 10:51:15.763631 | 22 | |
| 2018-04-12 10:51:15.770707 | 23 | |
| 2018-04-12 18:51:23.241235 | 17 | set global general_log=0 |
+----------------------------+-----------+------------------------------------------------------------------------------------+
⒊閱讀表中執行緒關係
17號mysql執行緒是我當前的操作的會話，而除了17號mysql執行緒外，還有21/22/23這三個執行緒
21號mysql執行緒沒有執行 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ，說明是Mydumper主程式發起的
22/23號mysql執行緒在匯出資料，所以是Mydumper發起的兩個執行緒（由mydumper -t 2指定的）
⒋原理分析：
⑴FLUSH TABLE WITH READ LOCK;
這句話的意思是鎖住所有的表，他的作用是獲取當前的二進位制位置，以及實現一致性備份。
⑵SHOW MASTER STATUS/SHOW SLAVE STATUS;
獲取二進位制日誌的位置。
⑶21號mysql執行緒執行：START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
這裡並不需要把隔離級別設定為可重複讀，因為21好執行緒對應Mydumper的主程式，不參與匯出資料。
⑷22/23對應的mydumper執行緒設定 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ以及 START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
22/23是再mydumper -t 2設定的2個執行緒，目的是匯出資料，所有需要開啟可重複讀隔離級別。
⑸UNLOCK TABLES
在獲取binlog位置以及開啟可重複讀之後即可釋放表鎖，這樣就可以進行熱備，鎖表的時間也不長，對業務基本沒影響
⑹匯出資料檔案以及表結構檔案
這裡是資料先用mysql匯出，然後匯出表結構以及檢視(這裡其實還有一個小細節，就是先匯出資料，然後匯出表結構，其實這是開啟了兩個任務佇列queue，一個是innodb表，另外一個是schema與表結構的定義；如果有MyISAM表的話，還會有第三個佇列，非事務的表，他的資料會在第(5)步之前匯出，這裡的目的是保證非事務表與事務表的一致性，同時不會鎖整個備份的過程，如上面的`Fish_Adventure`.`admin_log` ，前面自己忘了寫了，這裡補上)。
⒌總結：
其實mydumper跟mysqldump的工作原理相似，不同之處是mydumper在匯出資料的過程中用了多執行緒，所以速度相對與mysqldump要快，很難想象mysqldump竟然不實現這個功能
⒍mydumper優點
mydumper可以對多執行緒備份，更可貴的是可以進行單個表進行多執行緒的備份（前提條件，備份的主鍵一定是要整形的並且不能是複合索引）;myloader可以指定某個表來恢復，因為mydumper匯出的表是分開的.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30221425/viewspace-2156952/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mydumper工作流程圖流程圖
- mydumper使用及原理淺析
- MySQL備份遷移之mydumperMySql
- mydumper
- mysql多執行緒備份與還原工具mydumperMySql執行緒
- Navicat、into outfile、mysql命令、mysqldump、mysqlpump、mydumper匯出匯入資料MySql
- mydumper使用詳解
- MyDumper實戰分享
- mydumper備份恢復
- KubernetesAPIserver工作原理APIServer
- Mybatis工作原理MyBatis
- require工作原理UI
- HTTPS工作原理HTTP
- Nginx工作原理Nginx
- pr工作原理
- SpringMVC工作原理SpringMVC
- MySQL 官方出品，比 mydumper 更快的多執行緒邏輯備份工具-MySQL Shell Dump & LoadMySql執行緒
- MySQL備份與恢復——基於MyDumper/MyLoader 邏輯備份恢復MySql
- MyDumper/MyLoader的進階玩法
- MySQL原理簡介—9.MySQL索引原理MySql索引
- Mirror 的工作原理
- Web Scraper工作原理Web
- LiveData的工作原理LiveData
- OAuth的工作原理OAuth
- Spring Session工作原理SpringSession
- javascript引擎工作原理JavaScript
- Feign的工作原理
- ZStack基本工作原理
- pr工作原理文件
- Docker 工作原理分析Docker
- Spark的工作原理Spark
- MySQL索引原理MySql索引
- MySQL truncate原理MySql
- ZooKeeper 工作、選舉 原理
- SpringMVC工作原理詳解SpringMVC
- Kubernetes API server工作原理APIServer
- Rxjava工作原理總結RxJava
- YUM工作原理及使用