ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option

最新推荐文章于 2024-02-12 10:48:45 发布

bitko

最新推荐文章于 2024-02-12 10:48:45 发布

阅读量1.2k

点赞数

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/gumengkai/article/details/78589691

版权

mysql 专栏收录该内容

21 篇文章

订阅专栏

在尝试将CSV文件导入MySQL时遇到错误1290（HY000），该错误与--secure-file-priv选项有关。可以通过设置secure_file_priv为空，允许mysqld的导入导出操作，然后重启数据库来解决问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CSV文件导入mysql报错

mysql> load data infile '/u01/my3306/data/test/test.csv'
    -> into table ld_csv1
    -> fields terminated by ',' optionally enclosed by '"' escaped by '"'  
    -> lines terminated by '\r\n';  
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

1. 查看mysql secure_file_prive的值

mysql> show variables like "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  | # NULL：限制mysqld不允许导出
+------------------+-------+
1 row in set (0.00 sec)

secure_file_priv=/tmp/ ：限制mysqld的导入导出只能发生在/tmp/目录下

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

2. 更改secure_file_priv的值并重启数据库

[root@mysql my3306]# vi my.cnf
secure_file_priv=''

3. 导入csv正常

mysql> load data infile '/u01/my3306/data/test/test.csv'
    -> into table ld_csv1
    -> fields terminated by ',' optionally enclosed by '"' escaped by '"'  
    -> lines terminated by '\r\n';  
Query OK, 5 rows affected (0.01 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from ld_csv1;
+---------+----------+------+-------+
| id      | username | city | email |
+---------+----------+------+-------+
| 1000001 | a        | a1   | a2    |
| 1000002 | b        | b1   | b2    |
| 1000003 | c        | c1   | c2    |
| 1000004 | d        | d1   | d2    |
| 1000005 | e        | e1   | e2    |
+---------+----------+------+-------+
5 rows in set (0.00 sec)