问题背景

项目上同事使用下面语句把select的查询结果导出到操作系统的文件中时报错，请求协助：

mysql> select * 
    -> into outfile '/tmp/tt.csv'
    -> fields terminated by ','
    -> optionally enclosed by '"'
    -> lines terminated by '\n' 
    -> from tt;

报错如下：

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

其实这个问题以前也处理过，处理方式比较简单，为了避免下次还碰到相同的问题，现整理出来处理思路供借鉴。

模拟错误

查询表中现有数据

mysql> select * from tt;
+------+------+
| id   | sal  |
+------+------+
|    1 | NULL |
|    2 |  100 |
|    3 |  100 |
|    4 |  100 |
|    5 |  100 |
+------+------+
5 rows in set (0.00 sec)

导出数据

mysql> select * 
    -> into outfile '/tmp/tt.csv'
    -> fields terminated by ','
    -> optionally enclosed by '"'
    -> lines terminated by '\n' 
    -> from tt;
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 可以执行文件操作的目录，通常在使用 LOAD DATA INFILE 或 SELECT … INTO OUTFILE 语句时出现。

解决方案

方案一：直接导出/导入文件到secure_file_priv参数指定的目录下。

1、检查参数secure_file_priv的设置

mysql> show variables like 'secure%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

mysql> 

说明：secure_file_priv参数显示允许导入/导出文件的目录（/var/lib/mysql-files/），若值为NULL表示禁止所有操作。
2、直接导出到secure_file_priv参数指定的目录下

mysql> select * 
    -> into outfile '/var/lib/mysql-files/tt.csv'
    -> fields terminated by ','
    -> optionally enclosed by '"'
    -> lines terminated by '\n' 
    -> from tt;
Query OK, 5 rows affected (0.00 sec)

mysql>

数据成功导出。

方案二：设置secure_file_priv参数为导出/导入文件的路径

在my.cnf中设置secure_file_priv参数，然后重启数据库，执行导出。过程如下：
1、设置/etc/my.cnf中secure_file_priv参数为""
vi /etc/my.cnf

在[mysqld]段中添加或修改：
secure-file-priv = "/your/custom/path"  -- 指定新目录，如果指定了新目录，记得给新目录授予mysql读写权限
或
secure-file-priv = ""                   -- 禁用限制（不推荐）

2、创建目录并设置权限（如指定新路径）：

sudo mkdir -p  /your/custom/path
sudo chown mysql:mysql /your/custom/path  # 确保MySQL用户有权访问

3、重启数据库

systemctl restart mysqld

4、导出数据

mysql> select * 
    -> into outfile '/tmp/tt.csv'
    -> fields terminated by ','
    -> optionally enclosed by '"'
    -> lines terminated by '\n' 
    -> from tt;
Query OK, 5 rows affected (0.01 sec)

mysql> 

数据成功导出。

总结

方案二需要重启数据库，生产环境中需要申请变更窗口，使用方案一更简单。具体使用哪种方案，需要根据实际情况而定。

关于作者：
网名：飞天，墨天轮2024年度优秀原创作者，拥有 Oracle 10g OCM 认证、PGCE认证以及OBCA、KCP、ACP、磐维等众多国产数据库认证证书，目前从事Oracle、Mysql、PostgresSQL、磐维数据库管理运维工作，喜欢结交更多志同道合的朋友，热衷于研究、分享数据库技术。
微信公众号：飞天online
墨天轮：https://www.modb.pro/u/15197
如有任何疑问，欢迎大家留言，共同探讨~~~