Maison > Article > base de données > Accélérez l'export et l'import de mydumper et myloader
Accélérez l'export et l'import de mydumper et myloader
- 黄舟original
- 2017-02-13 11:04:181665parcourir
Mydumper et myloader sont d'excellents outils tiers de sauvegarde et de récupération logiques de bases de données MySQL qui utilisent l'exportation et l'importation multithread. Cela compense les lacunes du thread unique de mysqldump. Cet article décrit comment accélérer l'exportation et l'importation de mydumper et myloader pour votre référence.
Pour d'autres questions concernant mydumper et myloader, veuillez vous référer à :
Résumé de l'installation et de l'échec de l'installation de Mydumper
Exemple de base de données mysql de sauvegarde Mydumper
Exemple de base de données mysql de restauration de myloader
1 Exportation et importation basées sur le moteur MyIsam
a. et importé en morceaux
[root@GZAPP tmp]# mydumper -u inno -p xxx -B bsom -T tb_access_log -o /backup/tmp/
[root@GZAPP tmp]# ls -hltr ### Le fichier de données exporté est un fichier unique, la taille est de 2,6 Go
total 2,6G
-rw-r--r-- 1 racine racine 1,6K 24 juillet 08:51 bsom.tb_access_log-schema.sql
-rw-r-- r-- 1 racine racine 214 24 juillet 08:52 métadonnées
-rw-r--r-- 1 racine racine 2.6G 24 juillet 08:52 bsom.tb_access_log.sql
###Importez en fonction du nombre de threads par défaut, et définissez le nombre de requêtes par transaction sur 10000. Ce paramètre est en fait de peu d'utilité pour le moment, car la table est le moteur myisam
[root@GZAPP tmp]# myloader -u inno -p xxx -B tempdb -d /backup/tmp -v 3 -q 10000 [root@GZAPP tmp]# myloader -u inno -p xxx -B tempdb -d /backup/tmp -v 3 -q 10000 ** Message: 4 threads created ** Message: Creating table `tempdb`.`tb_access_log` ** Message: Thread 4 shutting down ** Message: Thread 1 restoring `bsom`.`tb_access_log` part 0 ** Message: Thread 3 shutting down ** Message: Thread 2 shutting down
root@localhost[tempdb]> liste de processus;
------- -- --------- ---------- -------- --------- -------- ---- -------------------------------------- --------------- ---
| Identifiant | Hôte | Commande Heure |
--------- --------- ------ ---- -------- --------- -------- ------ --- ------------ ----------------------------------- -
| 4452079 | localhost| tempdb | | Requête | 4 | mettre à jour | INSÉRER DANS LES VALEURS `tb_access_log` (506873,"325|
--------- --------- ---------- -------- ----- ---- -------- --------- ---------------- ------------- ------------------
###D'après le nombre de fils de discussion ci-dessus, nous pouvons voir qu'il n'y a qu'un seul thread effectuant l'opération d'insertion
## #Dans l'exemple suivant, 500 Mo sont utilisés pour le bloc
[root@GZAPP tmp]# mydumper -u inno -p xxx -B bsom -T tb_access_log -F 500 -o /backup/tmp/ [root@GZAPP tmp]# ls -hltr total 2.6G -rw-r--r-- 1 root root 1.6K Jul 24 08:21 bsom.tb_access_log-schema.sql -rw-r--r-- 1 root root 478M Jul 24 08:21 bsom.tb_access_log.00001.sql -rw-r--r-- 1 root root 478M Jul 24 08:21 bsom.tb_access_log.00002.sql -rw-r--r-- 1 root root 478M Jul 24 08:21 bsom.tb_access_log.00003.sql -rw-r--r-- 1 root root 478M Jul 24 08:21 bsom.tb_access_log.00004.sql -rw-r--r-- 1 root root 478M Jul 24 08:22 bsom.tb_access_log.00005.sql -rw-r--r-- 1 root root 214 Jul 24 08:22 metadata -rw-r--r-- 1 root root 241M Jul 24 08:22 bsom.tb_access_log.00006.sql### Comme le montre ce qui précède, la grande table tb_access_log est divisée en plusieurs fichiers par près de 500M
[root@GZAPP tmp]# myloader -u inno -p xxx -B tempdb -t 6 -d /backup/tmp -v 3 ** Message: 6 threads created ** Message: Creating database `tempdb` ** Message: Creating table `tempdb`.`tb_access_log` ** Message: Thread 1 restoring `bsom`.`tb_access_log` part 3 ** Message: Thread 2 restoring `bsom`.`tb_access_log` part 5 ** Message: Thread 5 restoring `bsom`.`tb_access_log` part 4 ** Message: Thread 3 restoring `bsom`.`tb_access_log` part 6 ** Message: Thread 4 restoring `bsom`.`tb_access_log` part 1 ** Message: Thread 6 restoring `bsom`.`tb_access_log` part 2
#在下面的processlist可以看到，存在表级锁等待
+---------+-------+-----------+---------+---------+--------+-----------------------------+------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-------+-----------+---------+---------+--------+-----------------------------+------------------------------------------------+
| 4452079 | root | localhost | bsom | Query | 0 | init | show processlist |
| 4452167 | inno | localhost | tempdb | Sleep | 769 | | NULL |
| 4452168 | inno | localhost | tempdb | Query | 36 | update | INSERT INTO `tb_access_log` VALUES (6367402,"0,|
| 4452169 | inno | localhost | tempdb | Query | 21 | Waiting for table level lock| INSERT INTO `tb_access_log` VALUES (12593865," |
| 4452170 | inno | localhost | tempdb | Query | 26 | Waiting for table level lock| INSERT INTO `tb_access_log` VALUES (15643029,""|
| 4452171 | inno | localhost | tempdb | Query | 6 | Waiting for table level lock| INSERT INTO `tb_access_log` VALUES (173947,"70 |
| 4452172 | inno | localhost | tempdb | Query | 15 | Waiting for table level lock| INSERT INTO `tb_access_log` VALUES (9490507,"7 |
| 4452173 | inno | localhost | tempdb | Query | 30 | Waiting for table level lock| INSERT INTO `tb_access_log` VALUES (3271602,"4 |
+---------+---------+-----------+---------+---------+--------+-----------------------------+----------------------------------------------+
c、调整myisam有关参数后导入
[root@GZ-APP-BAK01 tmp]# time myloader -u innobk -p InnoBK -B tempdb -t 6 -d /backup/tmp -v 3 ** Message: 6 threads created ** Message: Creating table `tempdb`.`tb_mobile_access_log` ** Message: Thread 1 restoring `blossom`.`tb_mobile_access_log` part 3 ** Message: Thread 6 restoring `blossom`.`tb_mobile_access_log` part 6 ** Message: Thread 2 restoring `blossom`.`tb_mobile_access_log` part 5 ** Message: Thread 3 restoring `blossom`.`tb_mobile_access_log` part 4 ** Message: Thread 4 restoring `blossom`.`tb_mobile_access_log` part 1 ** Message: Thread 5 restoring `blossom`.`tb_mobile_access_log` part 2 ** Message: Thread 6 shutting down ** Message: Thread 5 shutting down ** Message: Thread 1 shutting down ** Message: Thread 2 shutting down ** Message: Thread 4 shutting down ** Message: Thread 3 shutting down
real 266m28.903s
user 0m6.008s
sys 0m1.681s
###调整以下相关参数，后尝试再次导入，
concurrent_insert AUTO 改成 ALWAYS
bulk_insert_buffer_size 8388608 改成 256M
myisam_sort_buffer_size 67108864 改成 128M
[root@GZ-APP-BAK01 tmp]# time myloader -u innobk -p InnoBK -B tempdb -t 6 -o -d /backup/tmp -v 3 ** Message: 6 threads created ** Message: Dropping table (if exists) `tempdb`.`tb_mobile_access_log` ** Message: Creating table `tempdb`.`tb_mobile_access_log` ** Message: Thread 1 restoring `blossom`.`tb_mobile_access_log` part 3 ** Message: Thread 2 restoring `blossom`.`tb_mobile_access_log` part 6 ** Message: Thread 3 restoring `blossom`.`tb_mobile_access_log` part 5 ** Message: Thread 4 restoring `blossom`.`tb_mobile_access_log` part 4 ** Message: Thread 6 restoring `blossom`.`tb_mobile_access_log` part 1 ** Message: Thread 5 restoring `blossom`.`tb_mobile_access_log` part 2 ** Message: Thread 2 shutting down ** Message: Thread 1 shutting down ** Message: Thread 6 shutting down ** Message: Thread 5 shutting down ** Message: Thread 3 shutting down ** Message: Thread 4 shutting down
real 253m42.460s ###此时导入时间并无明显减少
user 0m5.924s
sys 0m1.637s
2、基于innodb引擎的导出导入
a、表未分块导出，数据文件大小为3.9GB
[root@GZAPP tmp]# ls -hltr total 3.9G -rw-r--r-- 1 root root 1.8K Jul 24 00:09 bscom.tb_message-schema.sql -rw-r--r-- 1 root root 3.9G Jul 24 00:25 bscom.tb_message.sql -rw-r--r-- 1 root root 215 Jul 24 09:14 metadata
###下面使用6个线程导入，实际上可以看到，只有1个线程在工作，因为数据文件只有1个
[root@GZAPP tmp]# myloader -u inno -p xxx -B tempdb -t 6 -d /backup/tmp -v 3 ** Message: 6 threads created ** Message: Creating table `tempdb`.`tb_message` ** Message: Thread 1 restoring `bscom`.`tb_message` part 0 ** Message: Thread 5 shutting down ** Message: Thread 2 shutting down ** Message: Thread 6 shutting down ** Message: Thread 3 shutting down ** Message: Thread 4 shutting down
b、表分块导出
[root@GZAPP tmp]# mydumper -u inno -p xxx -B bscom -T tb_message -F 500 -o /backup/tmp/ [root@GZAPP tmp]# ls -hltr total 3.9G -rw-r--r-- 1 root root 1.8K Jul 24 09:55 bscom.tb_message-schema.sql -rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00001.sql -rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00002.sql -rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00003.sql -rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00004.sql -rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00005.sql -rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00006.sql -rw-r--r-- 1 root root 478M Jul 24 09:55 bscom.tb_message.00007.sql -rw-r--r-- 1 root root 481M Jul 24 09:55 bscom.tb_message.00008.sql -rw-r--r-- 1 root root 135 Jul 24 09:55 metadata -rw-r--r-- 1 root root 93M Jul 24 09:55 bscom.tb_message.00009.sql
###下面尝试使用6线程导入，可以看到有6个线程在并发导入
[root@GZAPP tmp]# myloader -u inno -p xxx -B tempdb -t 6 -d /backup/tmp/ -v 3 ** Message: 6 threads created ** Message: Creating database `tempdb` ** Message: Creating table `tempdb`.`tb_message` ** Message: Thread 2 restoring `bscom`.`tb_message` part 5 ** Message: Thread 1 restoring `bscom`.`tb_message` part 9 ** Message: Thread 3 restoring `bscom`.`tb_message` part 1 ** Message: Thread 4 restoring `bscom`.`tb_message` part 8 ** Message: Thread 5 restoring `bscom`.`tb_message` part 4 ** Message: Thread 6 restoring `bscom`.`tb_message` part 6 ** Message: Thread 1 restoring `bscom`.`tb_message` part 7 ** Message: Thread 6 restoring `bscom`.`tb_message` part 3 ** Message: Thread 2 restoring `bscom`.`tb_message` part 2 ** Message: Thread 3 shutting down ** Message: Thread 5 shutting down ** Message: Thread 4 shutting down ** Message: Thread 1 shutting down ** Message: Thread 2 shutting down ** Message: Thread 6 shutting down
3、小结
a、mydumper在导出的时候可以根据服务器可用资源来合理地设置线程数。
b、mydumper在导出的时候尽可能地指定chunk-filesize或者rows参数以分块导出。
c、myloader在针对myisam引擎时建议调整相关参数至合理值以提高无法提高性能，主要是表级锁的问题。
d、myloader在针对innodb引擎时建议调整参数至合理值以提高性能，如以下参数等：
innodb_buffer_pool_size innodb_flush_log_at_trx_commit innodb_log_buffer_size
e、通过使用分块导出与导入可以显著利用并发来加快inndbo表导入。
f、注意mydumper导出时不会导出存储过程，函数，触发器等。
以上就是加快mydumper与myloader导出导入的内容，更多相关内容请关注PHP中文网（www.php.cn）！