重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
MySQLMydumper的工作原理
这篇文章主要讲解了“MySQL Mydumper的工作原理”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL Mydumper的工作原理”吧！
大渡口ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为成都创新互联的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：028-86922220（备注：SSL证书合作）期待与您的合作！
⒈在理解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的工作原理这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！
文章名称：MySQLMydumper的工作原理
网站地址：http://cqcxhl.com/article/iegidj.html