加快mydumper与myloader导出导入

深藏阁楼爱情的钟 2022-08-05 08:42 290阅读 0赞

mydumper与myloader是一个优秀的第三方mysql数据库逻辑备份恢复工具，使用多线程的导出与导入。弥补了mysqldump单线程的不足。本文描述的是如何加快mydumper与myloader的导出与导入供大家参考。

有关mydumper与myloader其他事项可以参考：  
        [mydumper安装及安装故障汇总][mydumper]  
        [mydumper备份mysql数据库示例  
][mydumper_mysql_]        [myloader恢复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

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

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]: http://blog.csdn.net/leshami/article/details/46740831
[mydumper_mysql_]: http://blog.csdn.net/leshami/article/details/46815553
[myloader_mysql]: http://blog.csdn.net/leshami/article/details/46817937