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热备工具使用

 

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

posted @ 2019-12-25 21:12  igoodful  阅读(654)  评论(1编辑  收藏  举报