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 value

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次数，加快了速度