MyDumper/MyLoader的进阶玩法

南松

一、前言
从mydumper v0.11.5版本开始，mydumper提供了--load-data参数，使用此参数导出的sql文件将不再是insert语句，而是load data语句。在MySQL官方文档中关于load data是这么描述的：When loading a table from a text file, use LOAD DATA. This is usually 20 times faster than using INSERT statements。load data的导入比insert快20倍，虽然实际中可能很难达到这个效率，但是对于MyDumper/MyLoader来说，使用--load-data选项，对导入速度的提升是显而易见的。
二、--load-data选项
--load-data选项的参数主要有以下几个。使用该选项导出将默认生成dat数据文件，而加上--csv参数将生成csv数据文件。此外也可以自定义数据文件里字段、行的分隔符等，如果不定义，将使用默认的，一般使用默认的就行。

1. --load-data                      Instead of creating INSERT INTO statements, it creates LOAD DATA statements and .dat files
2. --csv                            Automatically enables --load-data and set variables to export in CSV format.
3. --fields-terminated-by           Defines the character that is written between fields
4. --fields-enclosed-by             Defines the character to enclose fields. Default: "
5. --fields-escaped-by              Single character that is going to be used to escape characters in theLOAD DATA stament, default: '\'
6. --lines-starting-by              Adds the string at the begining of each row. When --load-data is usedit is added to the LOAD DATA statement. Its affects INSERT INTO statementsalso when it is used.
7. --lines-terminated-by            Adds the string at the end of each row. When --load-data is used it isadded to the LOAD DATA statement. Its affects INSERT INTO statementsalso when it is used.

复制代码

三、数据导出
使用--load-data选项进行单库的数据导出：

1. mydumper -h $host -u $user -p $password -B test -o /mydata/backup/shemafile --load-data --less-locking

复制代码

从以下导出的内容中可以看到，对于每个表除了建表语句的sql文件，还生成了一个包含load语句sql文件，还有一个dat数据文件，里面包含了该表的所有数据。

1. [root@wusl shemafile]# ll
2. total 7651704
3. -rw-r--r-- 1 root root        698 Apr  2 11:18 metadata
4. -rw-r--r-- 1 root root 1958809525 Apr  2 11:18 test.sbtest1.00000.dat
5. -rw-r--r-- 1 root root        319 Apr  2 11:18 test.sbtest1.00000.sql
6. -rw-r--r-- 1 root root        402 Apr  2 11:18 test.sbtest1-schema.sql
7. -rw-r--r-- 1 root root 1958809801 Apr  2 11:18 test.sbtest2.00000.dat
8. -rw-r--r-- 1 root root        319 Apr  2 11:18 test.sbtest2.00000.sql
9. -rw-r--r-- 1 root root        402 Apr  2 11:18 test.sbtest2-schema.sql
10. -rw-r--r-- 1 root root 1958809332 Apr  2 11:18 test.sbtest3.00000.dat
11. -rw-r--r-- 1 root root        319 Apr  2 11:18 test.sbtest3.00000.sql
12. -rw-r--r-- 1 root root        402 Apr  2 11:18 test.sbtest3-schema.sql
13. -rw-r--r-- 1 root root 1958809678 Apr  2 11:18 test.sbtest4.00000.dat
14. -rw-r--r-- 1 root root        319 Apr  2 11:18 test.sbtest4.00000.sql
15. -rw-r--r-- 1 root root        402 Apr  2 11:18 test.sbtest4-schema.sql
16. -rw-r--r-- 1 root root        153 Apr  2 11:18 test-schema-create.sql
17. -rw-r--r-- 1 root root          0 Apr  2 11:18 test-schema-triggers.sql
19. [root@wusl shemafile]# more test.sbtest1.00000.dat
20. 1       5014614 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 22195207048-70116052123-74140395089-76317954521-98
21. 694025897
22. 2       5024801 13241531885-45658403807-79170748828-69419634012-13605813761-77983377181-01582588137-21344716829-87370944992-02457486289 28733802923-10548894641-11867531929-71265603657-36
23. 546888392
24. 3       4989423 51185622598-89397522786-28007882305-52050087550-68686337807-48942386476-96555734557-05264042377-33586177817-31986479495 00592560354-80393027097-78244247549-39135306455-88
25. 936868384
26. 4       5026450 54133149494-75722987476-23015721680-47254589498-40242947469-55055884969-23675271222-20181439230-74473404563-55407972672 88488171626-98596569412-94026374972-58040528656-38
27. 000028170
29. [root@wusl shemafile]# more test.sbtest1.00000.sql
30. /*!40101 SET NAMES binary*/;
31. /*!40014 SET FOREIGN_KEY_CHECKS=0*/;
32. /*!40103 SET TIME_ZONE='+00:00' */;
33. LOAD DATA LOCAL INFILE 'test.sbtest1.00000.dat' REPLACE INTO TABLE `sbtest1` CHARACTER SET binary FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED
34. BY '\n' (`id`,`k`,`c`,`pad`);

复制代码

四、导入速度对比
实验中使用的是sysbench生成的表，数据量9.5G。可以看到使用load-data导入对比insert导入，时间上提升了15%。有一定的提升，但不是特别明显，主要因为是myloader在insert时，也做了很多优化，比如多个insert批量提交，每次提交1000行。

1. #--load-data导入
2. [root@wusl soft]# time myloader -h 10.xx.xx.xx -u root -p xxxxxxxx -d /mydata/backup/shemafile
3. ** (myloader:119586): WARNING **: 13:36:34.783: zstd command not found on any static location, use --exec-per-thread for non default locations
5. real    2m49.765s
6. user    0m4.755s
7. sys     0m4.599s
9. #insert导入
10. [root@wusl soft]# time myloader -h 10.xx.xx.xx -u root -p xxxxxxxx -d /mydata/backup/shemafile
11. ** (myloader:122550): WARNING **: 13:45:00.895: zstd command not found on any static location, use --exec-per-thread for non default locations
13. real    3m20.258s
14. user    0m11.874s
15. sys     0m5.455s

复制代码

五、对比MySQL官方mysqlshell的导数工具
MySQL Shell 8.0.21 中推出的Dump & Load工具，同样也是使用load data命令来导入数据，所以理论上和mydumper/myloader使用--load-data效率是一样的。关于mysqlshell的Dump&Load工具，可以参考一下官方陈臣老师的这篇文章：https://mp.weixin.qq.com/s/RC6MykrGbZ850xh3AOjrtw。我们对这两个工具进行一下对比实验，既然是工具对比那就得相对公平，参数环境什么的保持一致，首先导数前重启一下mysql释放buffer pool。mysqlshell导出会默认开压缩和表分片，需要把它关闭。此外，mysqlshell默认是会生成binlog，因此myloader导入时也要加上-e参数开启binlog。

1. #使用mysqlshell进行数据导出
2. MySQL  10.xx.xx.xx:33060+ ssl  JS > util.dumpSchemas(['test'],'/mydata/backup/shemafile',{compression: "none",chunking: "false"})
3. Acquiring global read lock
4. Global read lock acquired
5. Initializing - done
6. 1 schemas will be dumped and within them 8 tables, 0 views.
7. Gathering information - done
8. All transactions have been started
9. Locking instance for backup
10. Global read lock has been released
11. Writing global DDL files
12. Running data dump using 4 threads.
13. NOTE: Progress information uses estimated values and may not be accurate.
14. Writing schema metadata - done      
15. Writing DDL - done      
16. Writing table metadata - done      
17. Starting data dump
18. 106% (40.00M rows / ~37.59M rows), 323.53K rows/s, 63.45 MB/s                 
19. Dump duration: 00:01:05s                                    
20. Total duration: 00:01:05s                                    
21. Schemas dumped: 1                                            
22. Tables dumped: 8                                             
23. Data size: 7.84 GB                                          
24. Rows written: 40001025                                       
25. Bytes written: 7.84 GB                                       
26. Average throughput: 119.52 MB/s
28. #使用mysqlshell进行数据导入
29. MySQL  10.xx.xx.xx:33060+ ssl  JS > util.loadDump("/mydata/backup/shemafile")
30. Loading DDL and Data from '/mydata/backup/shemafile' using 4 threads.
31. Opening dump...
32. Target is MySQL 8.0.31. Dump was produced from MySQL 8.0.31
33. Scanning metadata - done      
34. Checking for pre-existing objects...
35. Executing common preamble SQL
36. Executing DDL - done      
37. Executing view DDL - done      
38. Starting data load
39. 2 thds loading | 100% (7.84 GB / 7.84 GB), 24.59 MB/s, 6 / 8 tables done
40. Recreating indexes - done      
41. Executing common postamble SQL                                          
42. 8 chunks (40.00M rows, 7.84 GB) for 8 tables in 1 schemas were loaded in 4 min 6 sec (avg throughput 32.03 MB/s)
43. 0 warnings were reported during the load.
45. #使用myloader导入（写binlog）
46. [root@wusl-test-db1-1 soft]# time myloader -h 10.xx.xx.xx -u root -p xxxxxxxx -e -d /mydata/backup/shemafile
47. ** (myloader:24020): WARNING **: 15:48:03.796: zstd command not found on any static location, use --exec-per-thread for non default locations
49. real    3m44.150s
50. user    0m4.855s
51. sys     0m4.606s

复制代码

从以上实验可以看出myloader还比mysqlshell导数工具快一点点，我想原因大概是myloader更轻量级，mysqlshell导数工具在数据迁移时可以查看进度、速度等，功能上丰富了不少。

来源:https://www.cnblogs.com/coygfly/p/18112776
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！