导出MySQL数据库模式及数据的Bash脚本 真的很好用！

导出MySQL数据库模式及数据的Bash脚本

本文链接：http://codingstandards.iteye.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.

使用实例

[root@sunrise16 scripts]# ./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.
[root@sunrise16 scripts]# 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
[root@sunrise16 scripts]#

相关博文

【1】导入MySQL数据库模式及数据的Bash脚本

【2】我使用过的Linux命令之mysql - MySQL客户端命令行工具

【3】Bash中使用MySQL导入导出CSV格式数据