MySQL备份之mysqlpump工具备份

从5.7.8开始，mysql官方提供一个逻辑备份工具：mysqlpump。

和mysqldump工具类似，也是用来备份数据库的。

功能和mysqldump也差不多，但是速度却比mysqldump快很多。我在笔记本上测试至少快50%（但是我mysqlpump线程开到5时，io几乎成为瓶颈）。

mysqldump是单线程工作，如果要备份库超过百gb，那么mysqldump几乎不可用。

相比于mysqldump，mysqlpump是多线程工具，意思就是可以平行导出，导出速度大大加快。

并且可以通过如下的参数来控制通道数和每个通道的线程数。

下面是文档上的描述：

比如：

shell> mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
mysqlpump要为导出db1和db2库启动一个队列，并且要为导出db3库启动一个队列，余下的其他库还要启动一个队列（默认队列）。这些队列都是默认的两个线程每队列。

shell> mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3 --default-parallelism=4
This is the same as the previous example except that all queues use four threads.
和上面相同，只是每个队列启动4个线程（包含默认那个队列）。

shell> mysqlpump --parallel-schemas=5:db1,db2 --parallel-schemas=3:db3
db1和db2启动一个队列（5个线程），db3启动一个队列（3个线程），其他库启动一个队列（默认队列，默认2个线程）。

下面是我测试的导出过程：

D:\>mysqlpump --default-parallelism=5 --triggers --routines --events -B cms > d:\temp\mysqlpump.cms.sql
Dump progress: 1/2 tables, 0/63 rows
Dump progress: 15/151 tables, 12817/2992059 rows
Dump progress: 35/152 tables, 25653/3023185 rows
Dump progress: 36/152 tables, 41556/3023185 rows
Dump progress: 37/152 tables, 64291/3023185 rows
Dump progress: 38/152 tables, 92376/3023185 rows
Dump progress: 39/152 tables, 116675/3023185 rows
Dump progress: 39/152 tables, 146425/3023185 rows
Dump progress: 39/152 tables, 176675/3023185 rows
Dump progress: 39/152 tables, 204925/3023185 rows
Dump progress: 39/152 tables, 216175/3023185 rows
Dump progress: 53/152 tables, 237097/3023185 rows
Dump progress: 53/152 tables, 255847/3023185 rows
Dump progress: 54/152 tables, 276141/3023185 rows
Dump progress: 54/152 tables, 297141/3023185 rows
Dump progress: 56/152 tables, 322639/3023185 rows
Dump progress: 59/152 tables, 347521/3023185 rows
Dump progress: 63/152 tables, 379211/3023185 rows
Dump progress: 65/152 tables, 411196/3023185 rows
Dump progress: 70/152 tables, 456707/3023185 rows
Dump progress: 87/152 tables, 500586/3023185 rows
Dump progress: 93/152 tables, 544025/3023185 rows
Dump progress: 94/152 tables, 570001/3023185 rows
Dump progress: 94/152 tables, 588501/3023185 rows
Dump progress: 95/152 tables, 623694/3023185 rows
Dump progress: 95/152 tables, 662444/3023185 rows
Dump progress: 95/152 tables, 701694/3023185 rows
Dump progress: 98/152 tables, 731820/3023185 rows
Dump progress: 98/152 tables, 755070/3023185 rows
Dump progress: 98/152 tables, 779320/3023185 rows
Dump progress: 99/152 tables, 812747/3023185 rows
Dump progress: 99/152 tables, 845997/3023185 rows
Dump progress: 99/152 tables, 886247/3023185 rows
Dump progress: 99/152 tables, 915247/3023185 rows
Dump progress: 101/152 tables, 958350/3023185 rows
Dump progress: 101/152 tables, 993100/3023185 rows
Dump progress: 101/152 tables, 1032350/3023185 rows
Dump progress: 104/152 tables, 1061425/3023185 rows
Dump progress: 105/152 tables, 1091103/3023185 rows
Dump progress: 105/152 tables, 1125603/3023185 rows
Dump progress: 105/152 tables, 1160353/3023185 rows
Dump progress: 105/152 tables, 1195353/3023185 rows
Dump progress: 105/152 tables, 1221853/3023185 rows
Dump progress: 105/152 tables, 1251603/3023185 rows
Dump progress: 105/152 tables, 1280103/3023185 rows
Dump progress: 105/152 tables, 1307853/3023185 rows
Dump progress: 105/152 tables, 1334103/3023185 rows
Dump progress: 105/152 tables, 1361103/3023185 rows
Dump progress: 105/152 tables, 1387603/3023185 rows
Dump progress: 105/152 tables, 1413603/3023185 rows
Dump progress: 106/152 tables, 1436227/3023185 rows
Dump progress: 106/152 tables, 1459477/3023185 rows
Dump progress: 107/152 tables, 1482598/3023185 rows
Dump progress: 107/152 tables, 1502098/3023185 rows
Dump progress: 109/152 tables, 1517841/3023185 rows
Dump progress: 110/152 tables, 1542158/3023185 rows
Dump progress: 114/152 tables, 1569151/3023185 rows
Dump progress: 114/152 tables, 1602401/3023185 rows
Dump progress: 114/152 tables, 1627651/3023185 rows
Dump progress: 114/152 tables, 1655901/3023185 rows
Dump progress: 114/152 tables, 1679401/3023185 rows
Dump progress: 114/152 tables, 1699651/3023185 rows
Dump progress: 116/152 tables, 1726741/3023185 rows
Dump progress: 116/152 tables, 1749241/3023185 rows
Dump progress: 121/152 tables, 1777677/3023185 rows
Dump progress: 125/152 tables, 1800450/3023185 rows
Dump progress: 132/152 tables, 1827093/3023185 rows
Dump progress: 132/152 tables, 1850843/3023185 rows
Dump progress: 132/152 tables, 1870343/3023185 rows
Dump progress: 132/152 tables, 1894343/3023185 rows
Dump progress: 132/152 tables, 1924093/3023185 rows
Dump progress: 132/152 tables, 1952343/3023185 rows
Dump progress: 132/152 tables, 1980843/3023185 rows
Dump progress: 132/152 tables, 2009093/3023185 rows
Dump progress: 132/152 tables, 2035343/3023185 rows
Dump progress: 132/152 tables, 2065343/3023185 rows
Dump progress: 132/152 tables, 2095093/3023185 rows
Dump progress: 133/152 tables, 2117458/3023185 rows
Dump progress: 136/152 tables, 2137426/3023185 rows
Dump progress: 136/152 tables, 2165926/3023185 rows
Dump progress: 137/152 tables, 2213536/3023185 rows
Dump progress: 137/152 tables, 2262786/3023185 rows
Dump progress: 137/152 tables, 2310786/3023185 rows
Dump progress: 137/152 tables, 2331036/3023185 rows
Dump progress: 137/152 tables, 2345286/3023185 rows
Dump progress: 137/152 tables, 2363286/3023185 rows
Dump progress: 137/152 tables, 2387786/3023185 rows
Dump progress: 137/152 tables, 2403786/3023185 rows
Dump progress: 137/152 tables, 2421536/3023185 rows
Dump progress: 137/152 tables, 2468786/3023185 rows
Dump progress: 137/152 tables, 2516536/3023185 rows
Dump progress: 138/152 tables, 2564790/3023185 rows
Dump progress: 141/152 tables, 2601835/3023185 rows
Dump progress: 142/152 tables, 2634744/3023185 rows
Dump progress: 142/152 tables, 2666744/3023185 rows
Dump progress: 147/152 tables, 2694293/3023185 rows
Dump progress: 147/152 tables, 2714293/3023185 rows
Dump progress: 147/152 tables, 2733043/3023185 rows
Dump progress: 147/152 tables, 2751043/3023185 rows
Dump progress: 147/152 tables, 2769293/3023185 rows
Dump progress: 147/152 tables, 2788543/3023185 rows
Dump progress: 147/152 tables, 2806793/3023185 rows
Dump progress: 148/152 tables, 2824989/3023185 rows
Dump progress: 148/152 tables, 2843239/3023185 rows
Dump progress: 149/152 tables, 2857000/3023185 rows
Dump progress: 150/152 tables, 2865550/3023185 rows
Dump progress: 150/152 tables, 2874300/3023185 rows
Dump progress: 150/152 tables, 2881550/3023185 rows
Dump progress: 151/152 tables, 2892317/3023185 rows
Dump progress: 151/152 tables, 2898067/3023185 rows
Dump progress: 151/152 tables, 2911817/3023185 rows
Dump progress: 151/152 tables, 2919817/3023185 rows
Dump progress: 151/152 tables, 2935317/3023185 rows
Dump progress: 151/152 tables, 2953567/3023185 rows
Dump progress: 151/152 tables, 2971817/3023185 rows
Dump progress: 151/152 tables, 2974817/3023185 rows
Dump progress: 151/152 tables, 2977317/3023185 rows
Dump progress: 151/152 tables, 2979567/3023185 rows
Dump progress: 151/152 tables, 2981817/3023185 rows
Dump progress: 151/152 tables, 2984067/3023185 rows
Dump progress: 151/152 tables, 2986067/3023185 rows
Dump progress: 151/152 tables, 2988317/3023185 rows
Dump progress: 151/152 tables, 2990817/3023185 rows
Dump progress: 151/152 tables, 2993317/3023185 rows
Dump progress: 151/152 tables, 2995317/3023185 rows
Dump progress: 151/152 tables, 2997567/3023185 rows
Dump progress: 151/152 tables, 3000067/3023185 rows
Dump progress: 151/152 tables, 3002567/3023185 rows
Dump progress: 151/152 tables, 3005067/3023185 rows
Dump progress: 151/152 tables, 3007317/3023185 rows
Dump progress: 151/152 tables, 3009817/3023185 rows
Dump progress: 151/152 tables, 3012317/3023185 rows
Dump progress: 151/152 tables, 3014817/3023185 rows
Dump progress: 151/152 tables, 3017067/3023185 rows
Dump progress: 151/152 tables, 3019817/3023185 rows
Dump progress: 151/152 tables, 3022317/3023185 rows
Dump progress: 151/152 tables, 3024817/3023185 rows
Dump progress: 151/152 tables, 3027317/3023185 rows
Dump progress: 151/152 tables, 3029817/3023185 rows
Dump progress: 151/152 tables, 3032567/3023185 rows
Dump progress: 151/152 tables, 3035067/3023185 rows
Dump progress: 151/152 tables, 3037567/3023185 rows
Dump progress: 151/152 tables, 3040317/3023185 rows
Dump progress: 151/152 tables, 3043067/3023185 rows
Dump progress: 151/152 tables, 3045317/3023185 rows
Dump progress: 151/152 tables, 3047817/3023185 rows
Dump progress: 151/152 tables, 3050067/3023185 rows
Dump progress: 151/152 tables, 3053317/3023185 rows
Dump progress: 151/152 tables, 3056067/3023185 rows
Dump progress: 151/152 tables, 3058567/3023185 rows
Dump progress: 151/152 tables, 3061317/3023185 rows
Dump progress: 151/152 tables, 3064067/3023185 rows
Dump progress: 151/152 tables, 3067067/3023185 rows
Dump progress: 151/152 tables, 3069817/3023185 rows
Dump progress: 151/152 tables, 3072067/3023185 rows
Dump progress: 151/152 tables, 3074567/3023185 rows
Dump progress: 151/152 tables, 3077817/3023185 rows
Dump progress: 151/152 tables, 3080567/3023185 rows
Dump progress: 151/152 tables, 3083317/3023185 rows
Dump progress: 151/152 tables, 3086567/3023185 rows
Dump progress: 151/152 tables, 3091067/3023185 rows
Dump progress: 151/152 tables, 3106067/3023185 rows
Dump completed in 171082 milliseconds


D:\>

Dump progress: 151/152 tables, 3106067/3023185 rows

ps，这个显示有些误导人。