這篇文章主要講解了“ Mydumper的工作原理”，文中的講解內容簡(jiǎn)單清晰，易于學(xué)習與理解，下面請大家跟著(zhù)小編的思路慢慢深入，一起來(lái)研究和學(xué)習“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;
⒉查看通用日志記錄(刪除了很多沒(méi)必要的數據)
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 |
+----------------------------+-----------+------------------------------------------------------------------------------------+
⒊閱讀表中線(xiàn)程關(guān)系
17號mysql線(xiàn)程是我當前的操作的會(huì )話(huà)，而除了17號mysql線(xiàn)程外，還有21/22/23這三個(gè)線(xiàn)程
21號mysql線(xiàn)程沒(méi)有執行 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ，說(shuō)明是Mydumper主進(jìn)程發(fā)起的
22/23號mysql線(xiàn)程在導出數據，所以是Mydumper發(fā)起的兩個(gè)線(xiàn)程（由mydumper -t 2指定的）
⒋原理分析：
⑴FLUSH TABLE WITH READ LOCK;
這句話(huà)的意思是鎖住所有的表，他的作用是獲取當前的二進(jìn)制位置，以及實(shí)現一致性備份。
⑵SHOW MASTER STATUS/SHOW SLAVE STATUS;
獲取二進(jìn)制日志的位置。
⑶21號mysql線(xiàn)程執行：START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
這里并不需要把隔離級別設置為可重復讀，因為21好線(xiàn)程對應Mydumper的主進(jìn)程，不參與導出數據。
⑷22/23對應的mydumper線(xiàn)程設置 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ以及 START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
22/23是再mydumper -t 2設置的2個(gè)線(xiàn)程，目的是導出數據，所有需要開(kāi)啟可重復讀隔離級別。
⑸UNLOCK TABLES
在獲取binlog位置以及開(kāi)啟可重復讀之后即可釋放表鎖，這樣就可以進(jìn)行熱備，鎖表的時(shí)間也不長(cháng)，對業(yè)務(wù)基本沒(méi)影響
⑹導出數據文件以及表結構文件
這里是數據先用mysql導出，然后導出表結構以及視圖(這里其實(shí)還有一個(gè)小細節，就是先導出數據，然后導出表結構，其實(shí)這是開(kāi)啟了兩個(gè)任務(wù)隊列queue，一個(gè)是innodb表，另外一個(gè)是schema與表結構的定義；如果有MyISAM表的話(huà)，還會(huì )有第三個(gè)隊列，非事務(wù)的表，他的數據會(huì )在第(5)步之前導出，這里的目的是保證非事務(wù)表與事務(wù)表的一致性，同時(shí)不會(huì )鎖整個(gè)備份的過(guò)程，如上面的`Fish_Adventure`.`admin_log` ，前面自己忘了寫(xiě)了，這里補上)。
⒌總結：
其實(shí)mydumper跟mysqldump的工作原理相似，不同之處是mydumper在導出數據的過(guò)程中用了多線(xiàn)程，所以速度相對與mysqldump要快，很難想象mysqldump竟然不實(shí)現這個(gè)功能
⒍mydumper優(yōu)點(diǎn)
mydumper可以對多線(xiàn)程備份，更可貴的是可以進(jìn)行單個(gè)表進(jìn)行多線(xiàn)程的備份（前提條件，備份的主鍵一定是要整形的并且不能是復合索引）;myloader可以指定某個(gè)表來(lái)恢復，因為mydumper導出的表是分開(kāi)的.
免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng )、來(lái)自本網(wǎng)站內容采集于網(wǎng)絡(luò )互聯(lián)網(wǎng)轉載等其它媒體和分享為主，內容觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如侵犯了原作者的版權，請告知一經(jīng)查實(shí)，將立刻刪除涉嫌侵權內容，聯(lián)系我們QQ:712375056，同時(shí)歡迎投稿傳遞力量。
Copyright ? 2009-2022 56dr.com. All Rights Reserved. 特網(wǎng)科技 特網(wǎng)云 版權所有 特網(wǎng)科技 粵ICP備16109289號
域名注冊服務(wù)機構:阿里云計算有限公司（萬(wàn)網(wǎng)） 域名服務(wù)機構：煙臺帝思普網(wǎng)絡(luò )科技有限公司（DNSPod） CDN服務(wù)：阿里云計算有限公司 百度云 中國互聯(lián)網(wǎng)舉報中心 增值電信業(yè)務(wù)經(jīng)營(yíng)許可證B2
建議您使用Chrome、Firefox、Edge、IE10及以上版本和360等主流瀏覽器瀏覽本網(wǎng)站
