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导出导入的内容。