docker mysql导出表数据xls csv

1、登录到docker的mysql上，输入脚本

	SELECT *
	FROM VehicleDataAndLocation 
	INTO OUTFILE '/etc/mysql/VehicleDataAndLocation.xls';

2、遇到错误

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

3、没有权限所致。查看安全策略

show variables like '%secure%';

4、找到my.conf。找到安装路径

whereis mysql

5、进入 /etc/mysql

6、打开my.cnf。一般docker上的源十分老旧，无法安装vim。

先换源，然后一路安装。

mv /etc/apt/sources.list /etc/apt/sources.list.bak
echo "deb http://mirrors.163.com/debian/ jessie main non-free contrib" >> /etc/apt/sources.list
echo "deb http://mirrors.163.com/debian/ jessie-proposed-updates main non-free contrib" >>/etc/apt/sources.list
echo "deb-src http://mirrors.163.com/debian/ jessie main non-free contrib" >>/etc/apt/sources.list
echo "deb-src http://mirrors.163.com/debian/ jessie-proposed-updates main non-free contrib" >>/etc/apt/sources.list

apt-get update
apt-get install vim

打开my.cnf

vim my.cnf

修改路径

注：该地址必须为你的下载地址，而且目录权限为可写

7、修改 /etc/mysql为可读写权限

chmod +666 mysql/

否则会出现以下错误

ERROR 1 (HY000): Can't create/write to file '/etc/mysql/VehicleDataAndLocation.xls' (OS errno 13 - Permission denied)

8、进入数据库，然后下载

use test;
SELECT * 
FROM VehicleDataAndLocation 
INTO OUTFILE '/etc/mysql/VehicleDataAndLocation.xls';

9、由于是在docker虚拟机上，安装lrzsz进行下载。我们换到国外的源，已经可以下了

apt-get install lrzsz

完成。导出csv，数据会连在一块，mysql不支持xlsx格式。最好的还是xls格式