Ways to accelerate MySQL import and export

Http://www.21andy.com/new/20100917/1952.html

MySQL exported SQL statements can be very, very slow to import, and 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-p123 21andy-e--max_allowed_packet=16777216--net_buffer_length=16384 > 21andy.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.

Incoming Search terms:

• Net_buffer_length
• Max_allowed_packet
• MySQL Net_buffer_length
• Max allowed Packet
• MySQL Import acceleration
• Mysqldump Max_allowed_packet
• Mysql Net Buffer length
• MySQL Import speed
• MySQL accelerates import
• MySQL acceleration

Tags: max_allowed_packet, MySQL, net_buffer_length, import, export

This address: http://www.21andy.com/new/20100917/1952.html

» Previous: Linux Batch compressed HTML file command» Next: PHP Google Search API

Ways to accelerate MySQL import and export