MySQL Mydumper的运作原理
发布时间：2022-01-16 03:11:58 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要讲解了MySQL Mydumper的工作原理，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习MySQL Mydumper的工作原理吧！ ⒈在理解Mydumper原理之前，我们先准备记录Mydumper的一些操作 ①set global gene
|
这篇文章主要讲解了“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的工作原理这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。 （编辑：好传媒网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
