MySQL Mydumper的工作原理

这篇文章主要讲解了“ 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导出的表是分开的.