mydumper myloader表分塊導入導出

  mydumper與myloader是一個優秀的第三方mysql資料庫邏輯備份恢複工具，使用多線程的導出與導入。彌補了mysqldump單線程的不足。本文描述的是如何加快mydumper與myloader的導出與導入供大家參考。

    有關mydumper與myloader其他事項可以參考：

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

** 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: 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: 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

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: dropping table (if exists) `tempdb`.`tb_mobile_access_log`

** 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

real    253m42.460s   ###此時導入時間并無明顯減少

user    0m5.924s

sys     0m1.637s

2、基于innodb引擎的導出導入

a、表未分塊導出，資料檔案大小為3.9gb

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個

** message: creating table `tempdb`.`tb_message`

** message: thread 1 restoring `bscom`.`tb_message` part 0

b、表分塊導出

[root@gzapp tmp]# mydumper -u inno -p xxx -b bscom -t tb_message -f 500 -o /backup/tmp/

-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: 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

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導出時不會導出存儲過程，函數，觸發器等。