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

mysql 导入出错

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

查看参数

root@mysqldb 15:00:  [school]>show variables like '%priv%';
+-------------------------+-----------------------+
| Variable_name           | Value                 |
+-------------------------+-----------------------+
| automatic_sp_privileges | ON                    |
| secure_file_priv        | /var/lib/mysql-files/ |
+-------------------------+-----------------------+
2 rows in set (0.00 sec)

修改变量在my.cnf 添加 secure_file_priv

$ cat /etc/my.cnf |grep -i priv
secure_file_priv=/data

重启mysql

$ service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

两次导入

root@mysqldb 15:03:  [school]>load data infile '/data/olympics.csv' into table olympics fields terminated by ',' lines terminated by '\n';
Query OK, 29217 rows affected, 29048 warnings (0.18 sec)
Records: 29217  Deleted: 0  Skipped: 0  Warnings: 29048