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

 

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

 

测试使用“select ...into outfile”的逻辑备份方法的时候，报错如下：

(root@localhost 10:50:20) [(none)]> select * from zkm.test into outfile '/root/daily/20230227/test.sql';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

 

查资料后，确定跟参数secure_file_priv有关系，当前数据库没做特殊设置，值如下：

(root@localhost 10:51:57) [(none)]> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.02 sec)

secure-file-priv的值有三种情况：

secure_file_prive = NULL 　　––限制mysqld不允许导入导出
secure_file_priv = /path/ 　　 ––限制mysqld的导入导出只能发生在设置的/path/目录下
secure_file_priv = '' 　　　　  ––不对mysqld的导入导出做限制，不对导入导出目录做限制

 

创建目录，并且参数文件添加该参并重启数据库，再次测试：

[root@dev-app81 ~]# ll -d /root
dr-xr-x---. 4 root root 232 Feb 27 09:50 /root
[root@dev-app81 ~]# ll -d /root/daily/
drwxr-xr-x 8 mysql mysql 102 Feb 27 09:42 /root/daily/
[root@dev-app81 ~]# ll -d /root/daily/20230227/
drwxr-xr-x 2 mysql mysql 6 Feb 27 09:42 /root/daily/20230227/

vi /etc/my.cnf
[mysqld]下添加一下参数：
secure-file-priv=/root/daily/20230227

--重启mysql服务

(root@localhost 10:52:04) [(none)]> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_file_priv         | /root/daily/20230227/ |
+--------------------------+-----------------------+
2 rows in set (0.06 sec)

(root@localhost 11:03:31) [(none)]> select * from zkm.test into outfile '/root/daily/20230227/test.sql';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

 

但是设置后还是报错，后来发现是mysql用户对上层目录/root没有权限，MySQL这个报错提示......

由于该目录是root用户家目录不便更改权限，只能换掉成其他目录，对于目录权限包括所有子文件全部权限需要设置正确，这里不做测试。

 

下边测试将secure_file_priv设置为空，并测试：

vi /etc/my.cnf
[mysqld]下添加一下参数：
secure-file-priv=

--重启mysql服务

(root@localhost 11:09:11) [(none)]> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.00 sec)

(root@localhost 11:09:12) [(none)]> select * from zkm.test into outfile '/root/daily/20230227/test.sql';
ERROR 1 (HY000): Can't create/write to file '/root/daily/20230227/test.sql' (OS errno 13 - Permission denied)

这次的报错倒是比较清晰。

换成另外一个有权限的位置：

(root@localhost 11:11:24) [(none)]> select * from zkm.test into outfile '/data/test.sql';
Query OK, 5 rows affected (0.55 sec)

 

至此。