mysql load大文件时报错：ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option

引言

在使用load data infile导入百万级数据时报错。

异常复现

1、连接客户端

mysql --local-infile -uroot -proot

2、选择数据库

use database;

3、设置全局参数local_infile为1，开启从本地加载文件导入数据的开关

set global local_infile = 1;

4、开始导入本地数据

mysql> load data infile '/tb_sku4.sql' into table tb_sku fields terminated by ',' lines terminated by '\n';

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

报错原因

1、什么是secure-file-priv

安全的文件权限（pri -> privileges），那么，这个secure-file-priv又是什么玩意儿呢？查看mysql referman，如下：

2、查看secure-file-priv环境变量

mysql> select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| NULL		         |
+--------------------+

或者

mysql> show variables like '%secure_file_priv%';
+------------------+----------------+
| Variable_name    | Value          |
+------------------+----------------+
| secure_file_priv | NULL		    |
+------------------+----------------+
1 row in set (0.01 sec)

根据上述的NULL值，可以看到是不允许导出（入）到文件。

3、修改mysql配置文件，vim my.cnf

# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql

secure-file-priv=NULL

# 开启MySQL慢日志查询开关
slow_query_log=1
# # 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

# Custom config should go here
!includedir /etc/mysql/conf.d/

将secure-file-priv=NULL改为secure-file-priv="/secure-files"

这样，便可以将/secure-files/文件夹下的文件进行导入导出操作

然后重新执行导入文件步骤即可