??? mydumper與myloader是一個優秀的第三方mysql數據庫邏輯備份恢復工具，使用多線程的導出與導入。彌補了mysqldump單線程的不足。本文描述的是如何加快mydumper與myloader的導出與導入供大家參考。
??? 有關mydumper與myloader其他事項可以參考：
??????? mydumper安裝及安裝故障匯總
??????? mydumper備份mysql數據庫示例
?????? ?myloader恢復mysql數據庫示例
1、基于MyIsam引擎導出導入
a、表不分塊導出及導入
[root@GZAPP tmp]# mydumper -u inno -p xxx -B bsom -T tb_access_log -o /backup/tmp/
[root@GZAPP tmp]# ls -hltr?? ###導出的數據文件為單個文件，大小在2.6GB
total 2.6G
-rw-r–r– 1 root root 1.6K Jul 24 08:51 bsom.tb_access_log-schema.sql
-rw-r–r– 1 root root? 214 Jul 24 08:52 metadata
-rw-r–r– 1 root root 2.6G Jul 24 08:52 bsom.tb_access_log.sql
###基于缺省線程數導入，且設定每個事務查詢數為10000，此參數此時其實作用不大，因為表為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]> show processlist;
+———+———+———-+——–+———+——–+———+————————————————+
| Id????? | User??? | Host???? | db???? | Command | Time?? | State?? | Info?????????????????????????????????????????? |
+———+———+———-+——–+———+——–+———+————————————————+
| 4452079 | root??? | localhost| tempdb | Query?? |????? 0 | init??? | show processlist?????????????????????????????? |
| 4453793 | inno??? | localhost| tempdb | Sleep?? |??? 420 |???????? | NULL?????????????????????????????????????????? |
| 4453794 | inno??? | localhost| tempdb | Query?? |????? 4 | update? | INSERT INTO `tb_access_log` VALUES (506873,”325|
+———+———+———-+——–+———+——–+———+————————————————+
###從上面的線程數可以看出只有一個單線程在執行insert操作
b、表分塊導出及導入
###下面的示例中使用500MB進行分塊
[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
###由上可知，大表tb_access_log按接近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）！
