4G数据导⼊mysql需要多久_记⼀次导⼊700W数据速度慢的解
决
今天部门需要迁移mysql数据库，有个库有700W条数据数据⽂件⼤⼩3.4G，我使⽤的是navicate导出sql然后在本地上执⾏。然后在本地使⽤source
sql⽂本的⽅式，结果速度⾮常慢，2分钟导⼊2M数据，导完数据需要⼏⼗⼩时的节奏啊
百度了下，可以直接复制数据⽂件到新库下。这个还没尝试，明天试下，估计也是⼀个⽅法。后来领导给了个链接很⼤幅度提⾼了导⼊速度，虽然也到 了近两⼩时，但确实有效果。
解决⽅法是在mysqldump的时候使⽤两个参数：
-e 使⽤包括⼏个VALUES列表的多⾏INSERT语法;
--max_allowed_packet=XXX 客户端/服务器之间通信的缓存区的最⼤⼤⼩;
--net_buffer_length=XXX TCP/IP和套接字通信缓冲区⼤⼩，创建长度达net_buffer_length的⾏。
注意：max_allowed_packet和net_buffer_length不能⽐⽬标数据库的设定数值⼤，否则可能出错。
确定⽬标库的参数值
mysql>show variables like 'max_allowed_packet';
mysql>show variables like 'net_buffer_length';
根据参数值书写mysqldump命令，如：
mysqldump -uroot -p**** goodclassification -e
--max_allowed_packet=1048576 --net_buffer_length=16384
>1.sql
后来不理解去官⽹查了下这两个参数的意思;
Command-Line
Format
--max_allowed_packet=#
System
Variable
Name
Variable Scope
Global, Session
Dynamic Variable
Yes
Permitted
Values (<= 5.6.5)
Type
integer
Default
1048576
Min Value
1024
Max Value
1073741824
Permitted
Values (>= 5.6.6)
Type
integer
Default
4194304
Min Value
1024
Max Value
1073741824
The maximum size of one packet or any generated/intermediate
string, or any parameter sent by the mysql_stmt_send_long_data() C API function. The default is 4MB as of MySQL 5.6.6, 1MB before that.
The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This valuevariable怎么记
by default is small, to catch large (possibly incorrect)
packets.
You must increase this value if you are using large BLOB
columns or long strings. It should be as big as the largest
BLOB
you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest
multiple.
When you change the message buffer size by changing the value of
the max_allowed_packet variable, you should also
change the buffer size on the client side if your client program
permits it. The default max_allowed_packet value built in to the client
library is 1GB, but individual client programs might override this.
For example, mysql
and mysqldump have defaults of 16MB and 24MB,
respectively. They also enable you to change the client-side value
by setting max_allowed_packet on the command line or in an
option file.
The session value of this variable is read only. The client can
receive up to as many bytes as the session value. However, the
server will not send to the client more bytes than the current
global max_allowed_packet value. (The global value could
be less than the session value if the global value is changed after
the client connects.)
最直观的改变是原来是1⾏1⾏影响的，改变之后变成了63⾏63⾏的影响。
我的理解是max_allowed_packet
指的是客户端与服务器段缓存区⼤⼩，5.6以上默认是4M之前是1M，改变之后变成了32M，缓存区增⼤了，⼀次性刷进数据库后影响的⾏数增⼤，减少了I/0次数，加快了速度