ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement - 牧人池塘 - DBA杂谈录

分类: MySQL

2017-09-01

暂无评论

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

MySQL5.7.17使用select into outfile导出文件时报错如下：

mysql> SELECT CONT.BRAND_NAME, CONT.CONT_NO, CONT.STORE_NOS,
    -> SUM(CHARGE_DETAIL.RECEIVABLE_MONEY), SUM(CHARGE_DETAIL.RECEIVED_MONEY),
    -> (SUM(CHARGE_DETAIL.RECEIVABLE_MONEY) - SUM(CHARGE_DETAIL.RECEIVED_MONEY)) AS QIANFEI
    -> FROM BS_CONT_CHARGE_DETAIL CHARGE_DETAIL 
    -> INNER JOIN BS_CONT CONT ON CONT.ID = CHARGE_DETAIL.CONT_ID 
    -> AND CONT.MALL_ID = 6 AND CONT.IS_DEL = 0 AND CONT.`STATUS` = 1
    -> WHERE CHARGE_DETAIL.IS_DEL = 0 AND CHARGE_DETAIL.`STATUS` = 1
    -> AND CHARGE_DETAIL.RECEIVABLE_TIME <= '2017-06-30 00:00:00'
    -> GROUP BY CONT.CONT_NO, CONT.BRAND_NAME, CONT.STORE_NOS
    -> HAVING QIANFEI > 0
    -> ORDER BY CONT.BRAND_NAME, CONT.CONT_NO, CONT.STORE_NOS
    -> into outfile '/tmp/test.xls';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

在MySQL5.7.16和5.6以下版本中没这个问题；

This option sets the secure_file_priv system variable, which is used to limit the effect of data import and export operations, such as those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. For more information, see the description of secure_file_priv.

MySQL官方文档https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_secure-file-priv
secure_file_priv这个变量不支持动态修改，需要重启MySQL服务；
解决问题方法：
查看当前设置：

mysql> SHOW VARIABLES LIKE "%secure_file%";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.01 sec)

secure_file_prive = NULL   -- 限制mysqld 不允许导入导出
secure_file_priv = '/tmp/'   -- 限制mysqld的导入导出只能发生在/tmp/目录下
secure_file_priv = ''     -- 不对mysqld 的导入 导出做限制

修改my.cnf在mysqld处增加

secure_file_priv = ''

重启MySQL服务，但是导出的时候目标地址，mysql用户必须有写权限否则也导不出来；

Tag标签:「导出 mysql secure_file_priv」更新时间:「2021-11-03 22:38:37」阅读次数:「623」

相关博文：