select into outfile

 

persona版本：5.7.25.28

 

 

 

SELECT ... INTO OUTFILE   可以从本地或远程客户端主机启动，但输出文件仅仅只在服务器实例所在的主机上创建。

 

 

问题现象：

 

(apple) > select * INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' from user;  
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Fri Sep  4 11:37:49 2020
(apple) > show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+
3 rows in set (0.00 sec)

Fri Sep  4 11:38:35 2020

 

 

 

 

 

 my.cnf配置：

[mysqld]


secure_file_priv =/tmp

 

 导出表数据：

不管是在本地机器上执行，还是远程执行导出命令，都会导出到本地机器上:路径必须是/tmp/开头

select * from e into outfile "/tmp/data.txt" fields terminated by ',';

select...into outfile 的结果只包含了表数据，默认以 Tab 分隔，也可指定分隔符：

select * INTO OUTFILE '/tmp/data.txt' FIELDS TERMINATED BY ',' from user;

数据类似如下：

1,guolicheng,422802199909136854,1
2,yuguocong,4228021998069513652,2

 

mysqldump热备工具使用

 

#################################