导出远程mysql至本地csv文件

2016-08-01
#mysql
数据库

远程数据库服务器信息（从此数据库下载数据）

#!/usr/bin/env bash
remoteMysqlServer="127.0.0.1"
remoteMysqlUser="testuser"
remoteMysqlPassword="testpassword"
remoteMysqlDB="testSchema"

下载哪个数据库表

tableName="testTable"

下载哪几个月的数据

array_month=(
    2015-09
    2015-10
    2015-11
    2015-12
    2016-01
    2016-02
    2016-03
)

本地数据下载目录

dataDownloadPath="data/"

if [ ! -d "${dataDownloadPath}" ]; then
  mkdir -p ${dataDownloadPath}
fi

导出数据至文件

for i in ${array_month[@]}
    do
        if [ ! -f "${dataDownloadPath}/${tableName}_${i}.csv" ]; then 
            touch ${dataDownloadPath}/${tableName}_${i}.csv
            mysql -h ${remoteMysqlServer} -P3306 -u${remoteMysqlUser} -p${remoteMysqlPassword} --compress ${remoteMysqlDB} -e "select * from ${tableName} where date_format(keytime,'%Y-%m')= ${i} order by keytime asc">${dataDownloadPath}/${tableName}_${i}.csv
        fi
    done


exit
异常
Can't create/write to file '/Users/XX/Desktop/data/XX_2015-11.csv' (Errcode: 13 - Permission denied)
产生原因
SELECT...INTO OUTFILE语句无权限在本地创建结果文件，使用“mysql –e "SELECT ..." > file_name”的命令，来生成文件。

Kommentare: