MySQL数据库一键导出数据到CSV

#!/bin/bash
#@author：【既然笨到家，就要努力到家】
#@date：2017-12-05
#@TARGET:一键导出mysql数据到 csv 
#####################################################################################
#### 常量池 ####
IP=127.0.0.1
user=root
database=msyql
passwd=root
port=3306

#导出路径，默认取当前路径下tmp
basepath=$(cd `dirname $0`; pwd)
data_path=${basepath}/tmp
mkdir -p ${data_path} && cd ${data_path}

#编码
unicode=utf8
#分隔符
separator="|"
#转义符- 谨记注：能不该不要改
escape_character="\\"
#####################################################################################
MYSQL=`which mysql`
istar=

function baktable(){
statement="use $database;set names ${unicode};select * from $1;"
echo "下载转换$database：$1 ..."
$MYSQL -h"${IP}" -u"${user}" -p"${passwd}" -P"${port}" -e "${statement}" > 1.log

cat 1.log|sed 's/\t/|/g' > $database"_"$1.csv
if [ ""x = ${istar}x ]; then 
tar -zcf "$database"_"$1.csv.tar.gz" "$database"_"$1.csv"
rm -rf $database"_"$1.csv

fi

}

function main(){
#show databases in mysql
echo "正在导出库：$database"
if [ -z $database ] ; then
echo "database in mysql:"
echo "*******************"
$MYSQL -h"${IP}" -u"${user}" -p"${passwd}" -P"${port}" -e "set names ${unicode};show databases;"
echo "*******************"
#choose a database 
read -t 60 -p "您未定义需要导出的数据库，请在上表选择一个数据库：" database
fi

#show tables in the database

database_tables=`$MYSQL -h"${IP}" -u"${user}" -p"${passwd}" -P"${port}" -e "use ${database};show tables;"`
#echo "test:"$MYSQL -h"${IP}" -u"${user}" -p"${passwd}" -P"${port}" -e "use ${database};show tables;"
echo "*******************"

#choose a table
read -t 60 -p "请选择一个表，默认为导出全部表【点击回车】:" table
read -t 60 -p "是否需要压缩，默认压缩【点击回车】:" istar

if [ -z $table ] ; then
tables_tmp=`echo "${database_tables}" |tail -n +3|sed 's/^[ \t|]*//g' `

for line in `echo ${tables_tmp}`
do
#echo "正在导出表：${line} "
baktable ${line}
done
#baktable 
else
baktable ${table}

fi

}

main 
#baktable tb_company_base

echo "Done successfully!Please check the file!"