溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要講解了“MySQL Mydumper的工作原理”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“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導出的表是分開的.
感謝各位的閱讀，以上就是“MySQL Mydumper的工作原理”的內容了，經過本文的學習后，相信大家對MySQL Mydumper的工作原理這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
