ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
select * from table into outfile '/tmp/t.sql'; 此语句在mysql5.6.34之前版本能正常执行,在mysql5.6.34以后版本报ERROR 1238错误,因为mysql5.6.34开始受secure_file_priv参数限制
This variable is used to limit the effect of data import and export operations, such as those performed
by the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function.
These operations are permitted only to users who have the FILE privilege.
secure_file_priv may be set as follows:
• If empty, the variable has no effect. This is not a secure setting.
• If set to the name of a directory, the server limits import and export operations to work only with
files in that directory. The directory must exist; the server does not create it.
• If set to NULL, the server disables import and export operations. This value is permitted as of
MySQL 5.6.34.
mysql> show global variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | NULL |
+------------------+-------+
1 row in set (0.00 sec)
mysql> select * into outfile '/tmp/t.csv' from t;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
修改secure_file_priv参数:
mysql> set global secure_file_priv='';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
只读参数,只能在启动命令行或my.cnf添加:
[root@db01 ~]# mysqladmin -uroot -p shutdown
[root@db01 ~]# mysqld_safe --user=mysql --secure-file-priv='' &
或者在my.cnf添加:
[root@db01 ~]# vi /etc/my.cnf
[mysqld]
secure_file_priv=''
mysql> select * from t into outfile '/tmp/t.csv';
Query OK, 6 rows affected (0.00 sec)
[root@db01 ~]# more /tmp/t.csv
1
2
3
4
5
6