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

MYSQL匯出資料包錯”ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement”

在使用SELECT...INTO OUTPUT匯出資料時出現如下的報錯：

root@localhost [team]>select * from team.player into outfile "/tmp/sql/player.txt";

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

 

透過報錯的提示可以發現是由於secure-file-priv變數的設定所引起的，檢視該變數的設定，預設的路徑是/var/lib/mysql-files/

root@localhost [team]>show variables like '%secure%';

+--------------------------+-----------------------+

| Variable_name        | Value           |

+--------------------------+-----------------------+

| require_secure_transport | OFF            |

| secure_auth         | ON             |

| secure_file_priv     | /var/lib/mysql-files/|

+--------------------------+-----------------------+

3 rows in set (0.01 sec)

 

說明：

secure_file_prive=null   限制mysqld 不允許匯入匯出

secure_file_priv=/var/lib/mysql-files/   限制mysqld的匯入匯出只能發生在/var/lib/mysql-files/目錄下

secure_file_priv=' '     不對mysqld的匯入匯出做限制

 

解決方法：

修改secure_file_prive的值或者是按照預設的路徑進行匯出；

windows下在my.ini檔案 [mysqld]節點 下新增secure_file_priv=''，然後重啟mysql服務。linux下 在 /etc/my.cnf中新增secure_file_priv=''。

 

1、我選擇前者修改預設的路徑

[root@seiang mysql]# vim /etc/my.cnf

[mysqld]

secure_file_priv=''

 

2、重新啟動mysql服務

[root@seiang mysql]# systemctl restart mysqld.service

 

3、再次檢視該變數取值為NULL

root@localhost [team]>show variables like '%secure%';

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    2

Current database: team

 

+--------------------------+-------+

| Variable_name        | Value |

+--------------------------+-------+

| require_secure_transport | OFF  |

| secure_auth         | ON   |

| secure_file_priv     |    |

+--------------------------+-------+

3 rows in set (0.13 sec)

 

4、再次執行匯出操作，成功

root@localhost [team]>select * from team.player into outfile "/tmp/player.txt";

Query OK, 3 rows affected (0.00 sec)

 

 

作者：SEian.G（苦練七十二變，笑對八十一難）