Solution to slow MySQL Data Import

The SQL statement exported from MySQL may be very slow during import. It took nearly three hours to import only 0.45 million records. Using a few parameters properly during export can greatly speed up the import.

-E uses the multiline INSERT syntax that includes several VALUES lists;
-- Max_allowed_packet = maximum size of the cache for communications between the XXX client/server;
-- Net_buffer_length = XXX TCP/IP and socket communication buffer size, create line with length up to net_buffer_length

Note: max_allowed_packet and net_buffer_length cannot be greater than the configuration value of the target database. Otherwise, an error may occur.

First, determine the parameter value of the target database.
Mysql> show variables like 'max _ allowed_packet ';
Mysql> show variables like 'net _ buffer_length ';

Write the mysqldump Command Based on the parameter value, for example:
Mysql> mysqldump-uroot-p database name-e -- max_allowed_packet = 1048576 -- net_buffer_length = 16384> SQL File

For example:

Mysql> mysqldump-uroot-p discuz-e -- max_allowed_packet = 1048576 -- net_buffer_length = 16384> discuz. SQL

The SQL statements that can be imported in the past two hours can be completed in dozens of seconds.