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

SELECT * FROM xxx ORDER BY id ASC LIMIT 2 INTO OUTFILE '/home/xwsoul/xxx.tmp';

执行上述SQL时, 出现以下错误

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

此错误是因为mysql限制的了导出目录导致

使用SQL查询当前的安全目录:

mysql> show variables like '%secure_file_priv%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

修改SQL语句, 既可以:

SELECT * FROM xxx ORDER BY id ASC LIMIT 2 INTO OUTFILE '/var/lib/mysql-files/xxx.tmp';