MySQL exported SQL statements may take several hours to import when processing millions data. Reasonable use of several parameters during export can greatly speed up the import.
-e uses multi-line insert syntax that includes several values lists;
--max_allowed_packet=xxx the maximum size of the buffer between client/server communication;
--net_buffer_length=xxx TCP/IP and socket communication buffer sizes, creating rows up to net_buffer_length length.
Note: Max_allowed_packet and net_buffer_length cannot be larger than the target database setting, otherwise there may be an error.
First determine the parameter values of the target database
Mysql> Show variables like ' Max_allowed_packet ';
Mysql> Show variables like ' net_buffer_length ';
Write mysqldump commands based on parameter values, such as:
# mysqldump-uroot-p123456 dbname-e--max_allowed_packet=16777216--net_buffer_length=16384 > Alldb.sql
OK, now the speed is fast, the main note is the import and export side of the Max_allowed_packet and net_buffer_length these 2 parameter value settings, make big point on OK
In fact, the quickest way is to copy the database directory directly, but remember to stop the MySQL service first
Speed up MySQL import and export