导出MySQL数据库模式及数据的Bash脚本 真的很好用！
导出MySQL数据库模式及数据的Bash脚本
本文链接：http://codingstandards.iyunv.com/blog/1188375
Bash脚本文件：export_db.sh
#!/bin/sh
# export_db.sh
# 导出数据库模式及数据
# codingstandards@gmail.com
# v1: 2010-05-23
# v2: 2011-10-08/09
# 命令使用说明
# 从标准输入读取数据库名称和表名称
# usage: export_db.sh
# usage: export_db.sh -
# 从指定文件读取数据库名称和表名称
# usage: export_db.sh db_and_tables.txt
# 通过here string指定数据库名称和表名称
# usage: export_db.sh <<<"dbname"
# usage: export_db.sh <<<"dbname tablename"
# 输入格式说明
# 由一行或多行组成，可以是下列之一：
# (1) 空行：将被忽略
# (2) 注释：以#开头的行，将被忽略
# (3) db_name：只指定数据库名字，该数据库中的所有表都将被导出
# (4) db_name table_name：指定数据库名称和表名称，只导出该表
# 数据库连接参数
# TODO: 修改下面的参数以适应数据库的设置
DBOPTS="-pxxx --default-character-set=gbk"
# 保存到目录
# TODO: 可设置为本脚本所在目录，也可以设置在别的目录
SAVETO=$(dirname "$0")
# 如果在crontab中执行，需加上下面这行，以保证环境变量正确设置
. /etc/profile
# 进入到保存目录
cd "$SAVETO" || exit 1
# 导出目录，格式为：dbYYYYmmddHHMMSS，比如 db20111008100923
DIR=db$(date +%Y%m%d%H%M%S)
mkdir -p "$DIR"
# 打印当前时间
now()
{
date "+%F %T.%N"
}
# 执行SQL语句，输出执行结果
# usage: db_exec <db> <sql>
db_exec()
{
mysql $DBOPTS -s "$1" -e "$2"
}
# 导出数据库到文件，分目录保存数据库，分文件保存数据表
# usage: db_dump <db> <table>
db_dump()
{
#echo -n "$1 $2 "
#db_exec "$1" "select count(*) from $2"
local count=$(db_exec "$1" "select count(*) from $2")
printf "$(now)%-32s%16s\n" "$1.$2" "$count"
mkdir -p "$DIR/$1"
mysqldump $DBOPTS --quick --add-drop-table "$1" "$2" >"$DIR/$1/$2.sql"
echo "$(now)ok."
}
# 从命令行的第一个参数指定的文件读取需要导出的数据库和表的名称，然后导出
#cat "${1:-export_db.txt}" |
#cat "$1" |
cat $1 |
while read db_name table_name;
do
#echo "$db_name $table_name"
if [ "$db_name" -a "${db_name:0:1}" != "#" ]; then # 忽略空行和注释
if [ ! "$table_name" ]; then # 如果没有指定表的名称，就认为是导出整个数据库
db_exec "$db_name" "show tables" |
while read table_name;
do
db_dump "$db_name" "$table_name";
done
else # 导出指定的数据库中的表
db_dump "$db_name" "$table_name"
fi
fi
#done &> $DIR/export_db.log
done 2>&1 | tee $DIR/export_db.log
# 如果没有任何表导出，就把$DIR目录删除
if [ ! "$(ls $DIR/*/*.sql 2>/dev/null)" ]; then
echo "$(now)no tables dumped!"
rm -rf $DIR
exit 2
fi
# 将导出的文件压缩
tar zcf $DIR.tar.gz $DIR
echo "$(now)$DIR.tar.gz generated." | tee -a $DIR/export_db.log
# END.
使用实例
# ./export_db.sh <<<"imx"
2011-10-10 10:01:02.354521930 imx.imx_account_info 1814
2011-10-10 10:01:02.599235299 ok.
2011-10-10 10:01:02.607423679 imx.imx_account_invite 3
2011-10-10 10:01:02.636247793 ok.
2011-10-10 10:01:02.645773939 imx.imx_account_score 204
2011-10-10 10:01:02.659308422 ok.
2011-10-10 10:01:02.667428147 imx.imx_address_book 0
2011-10-10 10:01:02.678375340 ok.
2011-10-10 10:01:02.686491205 imx.imx_blog_guest 0
2011-10-10 10:01:02.697385973 ok.
2011-10-10 10:01:02.705563054 imx.imx_blog_info 0
2011-10-10 10:01:02.716411413 ok.
2011-10-10 10:01:02.725761396 imx.imx_candidate_score 1560
2011-10-10 10:01:02.779894375 ok.
2011-10-10 10:01:02.788334192 imx.imx_candidate_set_staff 0
2011-10-10 10:01:02.799278729 ok.
2011-10-10 10:01:02.816682546 imx.imx_candidate_set_type 0
2011-10-10 10:01:02.827634837 ok.
2011-10-10 10:01:02.835702877 imx.imx_candidate_staff 156
2011-10-10 10:01:02.853453367 ok.
2011-10-10 10:01:02.861528243 imx.imx_candidate_type 0
2011-10-10 10:01:02.872491956 ok.
2011-10-10 10:01:02.892560159 imx.imx_chat_deliver 122181
2011-10-10 10:01:09.973991871 ok.
2011-10-10 10:01:09.986801726 imx.imx_chat_info 2985
2011-10-10 10:01:10.132049666 ok.
2011-10-10 10:01:10.148555010 imx.imx_chat_submit 38783
2011-10-10 10:01:12.066791839 ok.
2011-10-10 10:01:12.083448281 imx.imx_contact_detail 0
2011-10-10 10:01:12.094639631 ok.
2011-10-10 10:01:12.106523345 imx.imx_contact_info 0
2011-10-10 10:01:12.117118053 ok.
2011-10-10 10:01:12.127521043 imx.imx_dept_info 34
2011-10-10 10:01:12.139877683 ok.
2011-10-10 10:01:12.166498673 imx.imx_email_verify 146
2011-10-10 10:01:12.189277872 ok.
2011-10-10 10:01:12.197395616 imx.imx_file_inbox 4475
2011-10-10 10:01:12.448522775 ok.
2011-10-10 10:01:12.755795734 imx.imx_file_info 476
2011-10-10 10:01:13.584651069 ok.
2011-10-10 10:01:13.992323738 imx.imx_friend_group 25
2011-10-10 10:01:14.087335222 ok.
2011-10-10 10:01:14.541748370 imx.imx_friend_info 1746
2011-10-10 10:01:15.517113264 ok.
2011-10-10 10:01:15.553167179 imx.imx_initial_info 22382
2011-10-10 10:01:16.555686514 ok.
2011-10-10 10:01:16.573180784 imx.imx_invite_code 67
2011-10-10 10:01:16.594144070 ok.
2011-10-10 10:01:16.602299380 imx.imx_invite_mail 6
2011-10-10 10:01:16.622453217 ok.
2011-10-10 10:01:16.634810130 imx.imx_mobile_verify 131
2011-10-10 10:01:16.650483030 ok.
2011-10-10 10:01:16.662518780 imx.imx_notice_file 0
2011-10-10 10:01:16.673121562 ok.
2011-10-10 10:01:16.681317944 imx.imx_notice_info 138
2011-10-10 10:01:16.718013398 ok.
2011-10-10 10:01:16.734655172 imx.imx_notice_target 0
2011-10-10 10:01:16.745284656 ok.
2011-10-10 10:01:16.760210358 imx.imx_online_info 993
2011-10-10 10:01:16.830574327 ok.
2011-10-10 10:01:16.838868472 imx.imx_organ_info 4
2011-10-10 10:01:16.851086794 ok.
2011-10-10 10:01:16.859600254 imx.imx_score_info 0
2011-10-10 10:01:16.870817549 ok.
2011-10-10 10:01:16.878974270 imx.imx_staff_info 257
2011-10-10 10:01:16.937265547 ok.
2011-10-10 10:01:16.948347469 imx.imx_stat_account 4742
2011-10-10 10:01:17.121570803 ok.
2011-10-10 10:01:17.135223653 imx.imx_support_account 7
2011-10-10 10:01:17.146104366 ok.
2011-10-10 10:01:17.153290478 imx.imx_support_group 4
2011-10-10 10:01:17.163811216 ok.
2011-10-10 10:01:17.173859372 imx.imx_support_guest 1501
2011-10-10 10:01:17.241321120 ok.
2011-10-10 10:01:17.248754779 imx.imx_support_website 3
2011-10-10 10:01:17.259606462 ok.
2011-10-10 10:01:17.266724062 imx.imx_table_usage 0
2011-10-10 10:01:17.276016715 ok.
2011-10-10 10:01:17.283055274 imx.imx_voter_candidate 0
2011-10-10 10:01:17.292275163 ok.
2011-10-10 10:01:17.299291947 imx.imx_voter_staff 0
2011-10-10 10:01:17.308706276 ok.
2011-10-10 10:01:17.315633319 imx.imx_voter_type 0
2011-10-10 10:01:17.325035422 ok.
2011-10-10 10:01:17.332355040 imx.imx_voter_voted 0
2011-10-10 10:01:17.343331839 ok.
2011-10-10 10:01:19.490116346 db20111010100102.tar.gz generated.
# tree db20111010100102
db20111010100102
|-- export_db.log
`-- imx
|-- imx_account_info.sql
|-- imx_account_invite.sql
|-- imx_account_score.sql
|-- imx_address_book.sql
|-- imx_blog_guest.sql
|-- imx_blog_info.sql
|-- imx_candidate_score.sql
|-- imx_candidate_set_staff.sql
|-- imx_candidate_set_type.sql
|-- imx_candidate_staff.sql
|-- imx_candidate_type.sql
|-- imx_chat_deliver.sql
|-- imx_chat_info.sql
|-- imx_chat_submit.sql
|-- imx_contact_detail.sql
|-- imx_contact_info.sql
|-- imx_dept_info.sql
|-- imx_email_verify.sql
|-- imx_file_inbox.sql
|-- imx_file_info.sql
|-- imx_friend_group.sql
|-- imx_friend_info.sql
|-- imx_initial_info.sql
|-- imx_invite_code.sql
|-- imx_invite_mail.sql
|-- imx_mobile_verify.sql
|-- imx_notice_file.sql
|-- imx_notice_info.sql
|-- imx_notice_target.sql
|-- imx_online_info.sql
|-- imx_organ_info.sql
|-- imx_score_info.sql
|-- imx_staff_info.sql
|-- imx_stat_account.sql
|-- imx_support_account.sql
|-- imx_support_group.sql
|-- imx_support_guest.sql
|-- imx_support_website.sql
|-- imx_table_usage.sql
|-- imx_voter_candidate.sql
|-- imx_voter_staff.sql
|-- imx_voter_type.sql
`-- imx_voter_voted.sql
1 directory, 44 files
#
相关博文
【1】导入MySQL数据库模式及数据的Bash脚本
【2】我使用过的Linux命令之mysql - MySQL客户端命令行工具
【3】Bash中使用MySQL导入导出CSV格式数据
页:
[1]