20230522-MySQL ERROR 1290 错误解决

操作：

select * from cjc.t1 into outfile '/home/mysql/t1_xxx.csv';

报错：

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

原因：

–secure-file-priv参数设置问题。

解决方案：

调整–secure-file-priv参数，建议改成指定目录

注意事项：
–secure-file-priv为只读参数，不支持在线修改，修改后重启数据库生效。

测试：

测试1：secure-file-priv=''和secure-file-priv=时，无限制，任何目录都可以导出

[mysql@cjc-db-03 13309]$ cat my.cnf |grep secure
secure-file-priv=''

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.01 sec)

mysql> select * from cjc.t1 into outfile '/home/mysql/t1_xxx.csv';
Query OK, 3 rows affected (0.00 sec)

[mysql@cjc-db-03 13309]$ cat my.cnf |grep secure
secure-file-priv=

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.02 sec)

mysql> select * from cjc.t1 into outfile '/home/mysql/t2_xxx.csv';
Query OK, 3 rows affected (0.05 sec)

测试2：secure-file-priv=NULL时，无权限，任何目录都不能导出

[mysql@cjc-db-03 13309]$ cat my.cnf |grep secure
secure-file-priv=NULL

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.01 sec)

mysql> select * from cjc.t1 into outfile '/home/mysql/t2_xxx.csv';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

测试3：secure-file-priv=指定路径 时，指定路径有导出权限，其他目录不能导出

[mysql@cjc-db-03 13309]$ cat my.cnf |grep secure
secure-file-priv='/home/mysql/tmp'

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+------------------+
| Variable_name    | Value            |
+------------------+------------------+
| secure_file_priv | /home/mysql/tmp/ |
+------------------+------------------+
1 row in set (0.01 sec)

mysql> select * from cjc.t1 into outfile '/home/mysql/t3_xxx.csv';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

mysql> select * from cjc.t1 into outfile '/home/mysql/tmp/t3_xxx.csv';
Query OK, 3 rows affected (0.00 sec)

mysql> system cat /home/mysql/tmp/t3_xxx.csv
3
2
1

###chenjuchao 20230522###
欢迎关注我的公众号《IT小Chen》