mysql导入数据报错ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it

参考：https://blog.csdn.net/u011677147/article/details/64129606

参考：http://blog.itpub.net/31015730/viewspace-2152273/

导入数据报错

mysql> LOAD DATA INFILE '/tmp/1.txt' INTO TABLE hr.employees; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

原因：安装MySQL的时候限制了导入与导出的目录权限

查看secure_file_priv该变量的设置：

mysql> SHOW GLOBAL VARIABLES LIKE '%secure%'; +--------------------------+-----------------------+ | Variable_name | Value | +--------------------------+-----------------------+ | require_secure_transport | OFF | | secure_auth | ON | | secure_file_priv | /var/lib/mysql-files/ | +--------------------------+-----------------------+ 3 rows in set (0.01 sec)

secure_file_priv的值为/var/lib/mysql-files/，即导入导出的目录必须是/var/lib/mysql-files/

解决办法一：

临时修改这个值

mysql> SET GLOBAL secure_file_priv=''; ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable

解决办法二：

把要导入的文件复制到/var/lib/mysql-files/目录下，然后导入，显然麻烦

[root@nfs ~]# cp /tmp/1.txt /var/lib/mysql-files/ [root@nfs ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. mysql> LOAD DATA INFILE '/var/lib/mysql-files/1.txt' INTO TABLE hr.employees; Query OK, 1 row affected (0.01 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

解决办法三：

修改my.ini配置文件

选项说明：

secure_file_prive=null   限制mysqld 不允许导入导出

secure_file_priv=/var/lib/mysql-files/   限制mysqld的导入导出只能发生在/var/lib/mysql-files/目录下

secure_file_priv=''     不对mysqld的导入导出做限制

[root@nfs ~]# vim /etc/my.cnf [root@nfs ~]# egrep -v "^$|#" /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid secure_file_priv=''

重启mysql

[root@nfs ~]# systemctl restart mysqld

就可以了：

[root@nfs ~]# cat /tmp/2.txt 008,berry,john,example@qq.com,010222567,admin,20110302 [root@nfs ~]# mysql -uroot -p Enter password: mysql> LOAD DATA INFILE '/tmp/2.txt' INTO TABLE hr.employees FIELDS TERMINATED BY ','; Query OK, 1 row affected (0.15 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from hr.employees; +-------------+------------+-----------+-------------------+------------+------------+------------+ | employee_id | first_name | last_name | e_mail | telephone | department | hire_date | +-------------+------------+-----------+-------------------+------------+------------+------------+ | 1 | eric | william | test@gmail.com | 1065103488 | tech | 2011-01-13 | | 2 | quan | hope | NULL | NULL | NULL | NULL | | 3 | wei | shen | NULL | NULL | NULL | NULL | | 4 | boyi | liu | NULL | NULL | NULL | NULL | | 5 | lucy | black | NULL | NULL | NULL | NULL | | 6 | tangsh | quan | test@gmail.com | 1065103488 | tech | 2012-01-13 | | 7 | ellis | jim | example@gmail.com | 1065103488 | sale | 2013-01-02 | | 8 | berry | john | example@qq.com | 10222567 | admin | 2011-03-02 | +-------------+------------+-----------+-------------------+------------+------------+------------+ 8 rows in set (0.00 sec)

再次查看secure_file_priv的值

mysql> show global variables like "%secure%"; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | require_secure_transport | OFF | | secure_auth | ON | | secure_file_priv | | +--------------------------+-------+ 3 rows in set (0.01 sec)

 

总结

以上是生活随笔为你收集整理的mysql导入数据报错ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。