mysql导出报错_mysql 导出报错

转自：running with the --secure-file-priv-feelpurple-ITPUB博客

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版本引入。