MySQL 报错 ERROR 1290 (HY000): running with the --secure-file-priv

「爱情、让人受尽委屈。」 2024-04-18 01:07 10阅读 0赞

mysql> select \* from test into outfile '/tmp/test\_out.txt' fields terminated by ',' optionally enclosed by '"';  
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement  
  
报错原因：  
secure\_file\_priv设置了指定目录，需要在指定的目录下进行数据导出  
  
mysql> 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.27 sec)  
  
mysql> select \* from test into outfile '/var/lib/mysql-files/test\_out.txt' fields terminated by ',' optionally enclosed by '"';  
Query OK, 5 rows affected (0.21 sec)  
  
如果secure\_file\_priv参数为空，则需要在配置文件中设置该参数，并重启数据库  
\[mysql@localhost percona\]$ bin/mysqladmin -uroot -S /u01/mysql\_data/mysql.sock shutdown -p  
Enter password:  
2017-03-13T10:38:21.340514Z mysqld\_safe mysqld from pid file /u01/mysql\_data/localhost.localdomain.pid ended  
\[1\]+  Done                    bin/mysqld\_safe --defaults-file=/u01/mysql\_data/my.cnf  
\[mysql@localhost percona\]$ vim /u01/mysql\_data/my.cnf  
\[mysqld\]  
secure\_file\_priv='/database/percona'  
  
\[mysql@localhost percona\]$ bin/mysqld\_safe --defaults-file=/u01/mysql\_data/my.cnf &  
\[1\] 28543  
\[mysql@localhost percona\]$  mysqld\_safe Adding '/database/percona/lib/mysql/libjemalloc.so.1' to LD\_PRELOAD for mysqld  
2017-03-13T10:46:33.142327Z mysqld\_safe Logging to '/u01/mysql\_data/mysqld\_error.log'.  
2017-03-13T10:46:33.143853Z mysqld\_safe Logging to '/u01/mysql\_data/mysqld\_error.log'.  
2017-03-13T10:46:33.166624Z mysqld\_safe Starting mysqld daemon with databases from /u01/mysql\_data  
  
  
secure\_file\_priv参数说明  
  
这个参数用来限制数据导入和导出操作的效果，例如执行LOAD DATA、SELECT ... INTO OUTFILE语句和LOAD\_FILE()函数。这些操作需要用户具有FILE权限。  
如果这个参数为空，这个变量没有效果；  
如果这个参数设为一个目录名，MySQL服务只允许在这个目录中执行文件的导入和导出操作。这个目录必须存在，MySQL服务不会创建它；  
如果这个参数为NULL，MySQL服务会禁止导入和导出操作。这个参数在MySQL 5.7.6版本引入。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2121850/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2121850/