mysql 导入大数据sql文件

导出Sql文件

在导出时合理使用几个参数，可以大大加快导入的速度。

-e 使用包括几个VALUES列表的多行INSERT语法;
–max_allowed_packet=XXX 客户端/服务器之间通信的缓存区的最大大小;
–net_buffer_length=XXX TCP/IP和套接字通信缓冲区大小,创建长度达net_buffer_length的行

注意：max_allowed_packet和net_buffer_length不能比目标数据库的配置数值大，否则可能出错。
例子：

mysql>mysqldump -uroot -p discuz  -e --max_allowed_packet=1048576 --net_buffer_length=16384 > discuz.sql

查看配置文件路径

如果不清楚MySQL当前使用的配置文件路径，可以尝试这样查看:

which mysqld
/usr/sbin/mysqld
/usr/sbin/mysqld --verbose --help |grep -A 1 ‘Default options‘

从上图可以看出, 服务器首先会读取/etc/my.cnf文件，如果发现该文件不存在，再依次尝试从后面的几个路径进行读取。

SHOW STATUS; #服务器状态变量，运行服务器的统计和状态指标
SHOW VARIABLES; #服务器系统变量，实际上使用的变量的值
SHOW STATUS LIKE  ‘%变量名%‘

max_allowed_packet

客户端/服务器之间通信的缓存区的最大大小,适量调大可以加快mysql导入数据的速度。

show VARIABLES like ‘%max_allowed_packet%‘;

1. 可以编辑my.cnf来修改（windows下my.ini）,在[mysqld]段或者mysql的server配置段进行修改。(在[mysqld]下加上max_allowed_packet=16M，保存重启mysql后)
   max_allowed_packet = 20M
   如果找不到my.cnf可以通过
   mysql --help | grep my.cnf
   去寻找my.cnf文件。

1. 进入mysql server
   在mysql 命令行中运行
   set global max_allowed_packet = 2*1024*1024*10
   然后关闭掉这此mysql server链接，再进入。
   show VARIABLES like ‘%max_allowed_packet%‘;
   查看下max_allowed_packet是否编辑成功

innodb_flush_log_at_trx_commit

配置有0,1,2三种配置

• 如果innodb_flush_log_at_trx_commit的值为0, log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作。（执行是由mysql的master thread线程来执行的。主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件(REDO LOG)中。不论事务是否已经提交。）默认的日志文件是ib_logfile0,ib_logfile1

• 当设为默认值1的时候，每次提交事务的时候，都会将log buffer刷写到日志。

• 如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。每次事务提交的时候将数据写入事务日志，而这里的写入仅是调用了文件系统的写入操作，而文件系统是有 缓存的，所以这个写入并不能保证数据已经写入到物理磁盘

• 默认值1是为了保证完整的ACID。当然，你可以将这个配置项设为1以外的值来换取更高的性能，但是在系统崩溃的时候，你将会丢失1秒的数据。设为0的话，mysqld进程崩溃的时候，就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。

故在导入数据时可以暂时设置为0，以获得更大的性能，加快导入。

加快插入数据方法

[^footnote][footnote2]Bulk Data Loading for InnoDB Tables

SET autocommit=0;
SET UNIQUE_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0;
insert into tablename values (...),(...),(...)
SET UNIQUE_CHECKS=1;
SET FOREIGN_KEY_CHECKS=1;
COMMIT;

innodb_autoinc_lock_mode变量设置为2

即使insert采用了上述那么多策略但是速度依旧没有load [local] data infile快

[^footnote]: Bulk Data Loading for InnoDB Tables

[^footnote2]:Speed of INSERT Statements