线上维护数据库时,有时需要将一些数据导出到文件。
例如:客户要求将指定条件的数据查询出来,制成Excel表格发给他们。
如果网络方面没有限制,Navicat能直连数据库,直接利用工具导出即可。
如果Navicat不能直连,只能通过命令行界面操作数据库,该如何操作呢?
MySql将查询结果导出到文件的两种方式
方式一
into outfile
需要进入Mysql终端并选择数据库,然后执行如下命令:
SELECT * from 【表名】【where条件】 into outfile '【文件路径】';
eg:SELECT * from org_info into outfile '/home/data.xls';
可能出现的问题
1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
出现以上提示,则说明Mysql的安全设置不允许导出到外部文件。可以通过以下命令查看设置:
show variables like '%secure%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | NULL |
+--------------------------+-------+
secure_file_priv配置说明
- secure_file_prive=null
限制mysqld 不允许导入 | 导出 - secure_file_priv=/tmp/
限制mysqld 的导入 | 导出 只能发生在/tmp/目录下 - secure_file_priv
不对mysqld 的导入 | 导出做限制 - secure_file_priv="/"
可以导出至任意目录
修改配置
在my.cnf(Linux)末尾追加:secure_file_priv="/"
重启数据库,配置即可生效。
方式二 :
相对方式一更简单,且没有权限限制。
命令:
mysql --default-character-set=utf8 -u【用户名】 -p【密码】 -h【IP】 -D【数据库】 -e "【sql查询语句】" > 【文件路径】
eg:mysql --default-character-set=utf8 -uroot -proot -h127.0.0.1 -Dtest -e "select * from user_info;" > /home/a.xls
#参数说明
--default-character-set=utf8 字符集设为utf-8,防止输出中文乱码
如果加上 -N 则不输出表头,只保存数据。