docker mysql导出查询结果文件

mysqldump导出整张表
当前应用场景为查询部分数据，并导出

mysql> select * from  node_status where node_id = '12020120' into outfile '/home/';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

• 1
• 2

AI写代码

导出文件失败，原因在于 没有导出文件到mysql设置的–secure_file_priv路径中，没有权限

解决：

mysql> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.01 sec)

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

AI写代码

查询配置，将文件导入指定路径，或者修改配置

mysql> select * from node_status where node_id = '12020120' and  time_stamp > '2020-03-27 00:00:00' into outfile '/var/lib/mysql-files/';
Query OK, 0 rows affected (0.01 sec)

• 1
• 2

AI写代码

到路径下看到导出文件

# cd /var/lib/mysql-files/
# ls
 

• 1
• 2
• 3

AI写代码

最后将文件导出至宿主机

[root@localhost datause]# docker cp docker-mysql:/var/lib/mysql-files/  /home/self/datause/
[root@localhost datause]# ll
total 263644
-rw-rw-rw- 1 root root         0 Mar 26 11:44 

• 1
• 2
• 3
• 4

AI写代码

导出成功