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

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

vkingnew

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

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL

本文链接：https://blog.youkuaiyun.com/vkingnew/article/details/81133923

MySQL 专栏收录该内容

138 篇文章

订阅专栏

本文介绍了如何解决MySQL运行时因--secure-file-priv选项导致无法执行导出CSV文件的问题。通过修改配置文件my.cnf中的secure_file_priv参数并重启MySQL服务，成功实现了从MySQL表中导出CSV文件。

运行环境：CentOS7.4+MySQL5.7.22

将表数据导出为csv文件的时候报错

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

mysql> select * from  benchmark.benchmark order by thread into outfile '/tmp/benchmark.csv';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.22    |
+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'secur%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_auth      | ON    |
| secure_file_priv | NULL  |
+------------------+-------+
2 rows in set (0.01 sec)


mysql> set global secure_file_priv='/tmp';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
mysql> 
系统默认值为NULL，并不支持动态修改此参数。
需要写到配置文件my.cnf中重启mysql。

# cat /etc/my.cnf | grep -i priv
[mysqld]
secure_file_priv = /tmp
mysql> show variables like 'secur%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_auth      | ON    |
| secure_file_priv | /tmp/ |
+------------------+-------+
2 rows in set (0.00 sec)
---重试导出csv文件：
mysql> select * from  benchmark.benchmark order by 1 into outfile "/tmp/benchmark.csv";
Query OK, 11 rows affected (0.00 sec)
此时导出正常！