这篇文章主要讲解了“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进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。