利用Shell将MySQL数据表导出为csv文件

完整的Shell代码如下：

#!/bin/bash
MYSQL=`which mysql`

#show databases in mysql
echo "database in mysql:"
echo "*******************"
$MYSQL -u root -p****** << EOF
show databases;
EOF
echo "*******************"
#choose a database 
read -t 60 -p "choose a database:" database

#show tables in the database
echo "tables in $database"
echo "*******************"
$MYSQL -u root -p****** << EOF
use $database
show tables;
EOF
echo "*******************"

#choose a table
read -t 60 -p "choose a table:" table

statement="use $database;select * from $table;"

#write the table into 1.log file
$MYSQL -u root -p****** >1.log << EOF
$statement
EOF

echo "Downloading $table from $database in mysql..."
sleep 1
echo "now converting it to csv file..."
sleep 1

#cat the 1.log file and convert it to csv file
cat 1.log | while read line
do
echo $line | tr " " ","
done > $database"_"$table.csv

sleep 1
#remove the temporal file 1.log
rm -rf 1.log

#echo the infomation
echo "Convert $table into $database"_"$table.csv."
sleep 1
echo "Done successfully!Please check the file!"

其中$MYSQL -u root -p******的“******”为mysql登陆密码。
操作如下：

去文档中查看文件是否存在：

文件里面内容如下：

Bingo，操作成功！