☰

无法在 mysql 5.7 Ubuntu 16.04 上设置 secure_file_priv

tag-icon • tag-icon mysql my.cnf
无法在 mysql 5.7 Ubuntu 16.04 上设置 secure_file_priv

我如何设置 的值secure_file_priv？

我发现这个可以告诉您哪些设置可以使用 https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv

mysql 服务器无需任何命令行选项即可启动。没有任何内容可以覆盖其 .cnf 文件。

user@server:~$ ps aux | grep [m]ysql
mysql     4495  0.0  7.0 544368 144924 ?       Ssl  09:16   0:02 /usr/sbin/mysqld

跑步:~$ mysqld --verbose --help告诉我

默认选项按给定顺序从以下文件读取：
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

仅存在第二个文件。它是符号链接链的开头， /etc/mysql/my.cnf.migrated如下所示...

user@server:~$ ls -l /etc/mysql/my.cnf
lrwxrwxrwx 1 root root 24 Aug 12 15:15 /etc/mysql/my.cnf -> /etc/alternatives/my.cnf
user@server:~$ ls -l /etc/alternatives/my.cnf
lrwxrwxrwx 1 root root 26 Aug 12 15:15 /etc/alternatives/my.cnf -> /etc/mysql/my.cnf.migrated
user@server:~$ ls -l /etc/mysql/my.cnf.migrated
-rw-r--r-- 1 root root 4455 Dec 13 03:18 /etc/mysql/my.cnf.migrated

我尝试secure_file_priv在最后一个文件中设置值，重新启动 mysql 服务器，甚至重新启动 Ubuntu 服务器。无论设置的值是什么，命令

mysql> SELECT @@GLOBAL.secure_file_priv;

总会回来/var/lib/mysql-files/。

secure_file_priv我还搜索了其他 .cnf 文件并尝试在每个文件中设置值

user@server:~$ find /etc -iname "m*.cn*" -type f
/etc/mysql/conf.d/mysql.cnf
/etc/mysql/my.cnf.migrated
/etc/mysql/my.cnf.fallback
/etc/mysql/mysql.conf.d/mysqld.cnf
/etc/mysql/mysql.cnf

没关系。更改后，重新启动服务器，然后使用

mysql> SELECT @@GLOBAL.secure_file_priv;

结果/var/lib/mysql-files/总是一样的。它不会改变。

我需要做什么来设置一个值secure_file_priv？

答案1

[mysqld]在以下部分下添加变量/etc/mysql/my.cnf：

secure_file_priv=/absolute/path

然后重新启动mysql服务。

检查SELECT @@secure_file_priv;

它对我有用：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) > select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| /tmp/              |
+--------------------+
1 row in set (0.00 sec)

为了避免出现 ERROR 1290，例如：

 mysql [localhost] {msandbox} (test) > select * from t1 into outfile 'file.sql';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

确保使用这样的绝对路径：

mysql [localhost] {msandbox} (test) > select * from t1 into outfile '/tmp/file.sql';
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > \! ls /tmp/file.sql
/tmp/file.sql

mysql [localhost] {msandbox} (test) > truncate t1;
Query OK, 0 rows affected (0.04 sec)

mysql [localhost] {msandbox} (test) > load data infile '/tmp/file.sql' into table t1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

相关内容