MySQL Load Data VS MySQL Shell Utility

1. Install MySQL-Shell
[root@sys-1635 ~]# wget https://cdn.mysql.com//Downloads/MySQL-Shell/mysql-shell-8.0.19-1.el7.x86_64.rpm

[root@sys-1635 ~]# rpm -ivh mysql-shell-8.0.19-1.el7.x86_64.rpm

#Install MySQL 8

2. Loading Data with Traditional load data infile method

[paul.f@sys-1635 ~]$ ls -lrth
-rw-r--r--.  1 paul.f dba          1.8G Jan 31 17:56 DM_Loan_LS.sql

mysql> select now();load data infile '/var/lib/mysql-files/DM_Loan_LS.sql' into table db3.DM_Loan_LS;select now();
+---------------------+
| now()               |
+---------------------+
| 2020-01-31 18:27:17 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 3007281 rows affected, 65535 warnings (1 min 39.92 sec)
Records: 3007281  Deleted: 0  Skipped: 0  Warnings: 201487826

+---------------------+
| now()               |
+---------------------+
| 2020-01-31 18:28:57 |
+---------------------+
1 row in set (0.00 sec)

It took 99.92 seconds 

3. Create a Session
#User must be passed on the connection string
# --mc must be specified to create a mysql session
# \sql to be used to switch to SQL mode

[paul.f@sys-1635 ~]$ mysqlsh -u'root' -p'FRANK@74fr' --mc -A
MySQL Shell 8.0.19

Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
WARNING: Using a password on the command line interface can be insecure.
Creating a Classic session to 'root@localhost'
Your MySQL connection id is 17
Server version: 8.0.19 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost  JS > \sql
Switching to SQL mode... Commands end with ;

4. Enable local_infile variable

ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.

mysql> set global local_infile=ON;

5. Import using multi threaded importTable utility in MySQL Shell.

 MySQL  localhost  JS > util.importTable ("/var/lib/mysql-files/DM_Loan_LS.sql", {schema:"db1",threads: 8,showProgress: true})

Importing from file '/var/lib/mysql-files/DM_Loan_LS.sql' to table `db1`.`DM_Loan_LS` in MySQL Server at /var%2Flib%2Fmysql%2Fmysql.sock using 8 threads

Check mysqlsh.log for 1019 more warnings.

100% (1.90 GB / 1.90 GB), 134.71 MB/s

File '/var/lib/mysql-files/DM_Loan_LS.sql' (1.90 GB) was imported in 15.7800 sec at 120.32 MB/s

Total rows affected in db1.DM_Loan_LS: Records: 3007281  Deleted: 0  Skipped: 3007279  Warnings: 204495105

It took 15.78 seconds 

importTable is 6x faster than the single-threaded method and improved the speed tremendously.