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

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)


展开阅读全文

没有更多推荐了,返回首页