导入MySQL数据库模式及数据的Bash脚本（改进版本）

导入MySQL数据库模式及数据的Bash脚本（改进版本）

本文链接：http://codingstandards.iteye.com/blog/1190349

 

Bash脚本：import_db.sh

#!/bin/bash

# 脚本：import_db.sh
#
# v1: 2006-12-04
# v2: 2011-10-08/09
# v3: 2011-10-12 support for .tar.gz or .tgz
#

# 数据库连接参数
DBOPTS="-psunrise --default-character-set=gbk"

# 检查命令行参数
if [ $# -ne 2 ]; then
        echo "usage: $0 <db-name> <db-sql-dir>"
        echo "usage: $0 <db-name> <db-tgz-file>"        # 2011.10.12
        exit 1
fi

# 打印当前时间的函数
now()
{
        date "+%F %T.%N"
}

# 保存命令行参数
DB=$1
DIR_OR_TGZ=$2   # 2011.10.12 有可能是.tar.gz或.tgz文件
DIR=${DIR_OR_TGZ%.tar.gz}
DIR=${DIR%.tgz}
if [ "$DIR_OR_TGZ" != "$DIR" ]; then
        if [ ! -d "$DIR" ]; then
                echo "$(now)  uncompress $DIR_OR_TGZ ..."
                tar zxvf "$DIR_OR_TGZ" -C "$(dirname "$DIR_OR_TGZ")"
                echo "$(now)  ok."
        fi
fi

# 到sql文件所在目录
cd "$DIR" || exit 2

# 如果没有数据文件就退出
if [ ! "$(ls *.sql 2>/dev/null)" ]; then
        if [ -d "$DB" ]; then   # 2011.10.12 如果有DB子目录，进入
                cd "$DB" || exit 2
                if [ ! "$(ls *.sql 2>/dev/null)" ]; then
                        echo "$0: no *.sql found in $(pwd)"
                        exit 3
                fi
        else
                echo "$0: no *.sql found in $(pwd)"
                exit 3
        fi
fi

# 创建数据库
#2006.12.11
#第一种语法在5.0下支持
#第二种语法可用于其他版本
#肯定只有一个能执行成功
echo "$(now)  create database $DB if not exists"
mysql $DBOPTS <<EOF
CREATE DATABASE IF NOT EXISTS $DB CHARACTER SET gbk COLLATE gbk_chinese_ci;
EOF
mysql $DBOPTS <<EOF
CREATE DATABASE IF NOT EXISTS $DB;
EOF

# 执行导入
doit()
{
        echo "$(now)  import to $DB ..."
        for f in *.sql;
        do
                echo "$(now)  import $f ..."
                mysql $DBOPTS $DB < $f
                echo "$(now)  ok."
        done
        echo "$(now)  done."
}

# main
doit 2>&1 | tee -a import_db.log

# END.

 

使用实例

[root@liunx0918 mysql]# ./import_db.sh
usage: ./import_db.sh <db-name> <db-sql-dir>
usage: ./import_db.sh <db-name> <db-tgz-file>

[root@liunx0918 mysql]# ./import_db.sh imx /root/backup/db20111012094304.tar.gz
2011-10-12 09:55:15.539624750  uncompress /root/backup/db20111012094304.tar.gz ...
db20111012094304/
db20111012094304/imx/
db20111012094304/imx/imx_email_verify.sql
db20111012094304/imx/imx_support_guest.sql
db20111012094304/imx/imx_initial_info.sql
db20111012094304/imx/imx_candidate_set_type.sql
db20111012094304/imx/imx_blog_info.sql
db20111012094304/imx/imx_address_book.sql
db20111012094304/imx/imx_account_info.sql
db20111012094304/imx/imx_blog_guest.sql
db20111012094304/imx/imx_notice_info.sql
db20111012094304/imx/imx_stat_account.sql
db20111012094304/imx/imx_invite_code.sql
db20111012094304/imx/imx_file_inbox.sql
db20111012094304/imx/imx_candidate_type.sql
db20111012094304/imx/imx_friend_info.sql
db20111012094304/imx/imx_contact_detail.sql
db20111012094304/imx/imx_organ_info.sql
db20111012094304/imx/imx_score_info.sql
db20111012094304/imx/imx_support_website.sql
db20111012094304/imx/imx_online_info.sql
db20111012094304/imx/imx_file_info.sql
db20111012094304/imx/imx_staff_info.sql
db20111012094304/imx/imx_mobile_verify.sql
db20111012094304/imx/imx_candidate_score.sql
db20111012094304/imx/imx_account_score.sql
db20111012094304/imx/imx_support_account.sql
db20111012094304/imx/imx_table_usage.sql
db20111012094304/imx/imx_invite_mail.sql
db20111012094304/imx/imx_chat_info.sql
db20111012094304/imx/imx_notice_file.sql
db20111012094304/imx/imx_candidate_staff.sql
db20111012094304/imx/imx_notice_target.sql
db20111012094304/imx/imx_contact_info.sql
db20111012094304/imx/imx_candidate_set_staff.sql
db20111012094304/imx/imx_voter_staff.sql
db20111012094304/imx/imx_account_invite.sql
db20111012094304/imx/imx_chat_submit.sql
db20111012094304/imx/imx_support_group.sql
db20111012094304/imx/imx_dept_info.sql
db20111012094304/imx/imx_voter_type.sql
db20111012094304/imx/imx_friend_group.sql
db20111012094304/imx/imx_chat_deliver.sql
db20111012094304/imx/imx_voter_voted.sql
db20111012094304/imx/imx_voter_candidate.sql
db20111012094304/export_db.log
2011-10-12 09:55:15.691899106  ok.
2011-10-12 09:55:15.697114176  create database imx if not exists
2011-10-12 09:55:15.706611417  import to imx ...
2011-10-12 09:55:15.707964932  import imx_account_info.sql ...
2011-10-12 09:55:22.511765873  ok.
2011-10-12 09:55:22.512824271  import imx_account_invite.sql ...
2011-10-12 09:55:22.595701407  ok.
2011-10-12 09:55:22.597295499  import imx_account_score.sql ...
2011-10-12 09:55:22.626224528  ok.
2011-10-12 09:55:22.627742229  import imx_address_book.sql ...
2011-10-12 09:55:22.657232257  ok.
2011-10-12 09:55:22.658800696  import imx_blog_guest.sql ...
2011-10-12 09:55:22.685601903  ok.
2011-10-12 09:55:22.687303659  import imx_blog_info.sql ...
2011-10-12 09:55:22.713716619  ok.
2011-10-12 09:55:22.715360744  import imx_candidate_score.sql ...
2011-10-12 09:55:22.756770527  ok.
2011-10-12 09:55:22.758363084  import imx_candidate_set_staff.sql ...
2011-10-12 09:55:22.806626651  ok.
2011-10-12 09:55:22.808177474  import imx_candidate_set_type.sql ...
2011-10-12 09:55:22.834132030  ok.
2011-10-12 09:55:22.835702009  import imx_candidate_staff.sql ...
2011-10-12 09:55:22.864735561  ok.
2011-10-12 09:55:22.866387560  import imx_candidate_type.sql ...
2011-10-12 09:55:22.898687218  ok.
2011-10-12 09:55:22.900264364  import imx_chat_deliver.sql ...
2011-10-12 09:55:25.539170115  ok.
2011-10-12 09:55:25.540288016  import imx_chat_info.sql ...
2011-10-12 09:55:28.562270338  ok.
2011-10-12 09:55:28.563371201  import imx_chat_submit.sql ...
2011-10-12 09:55:29.582725333  ok.
2011-10-12 09:55:29.583792000  import imx_contact_detail.sql ...
2011-10-12 09:55:31.739669903  ok.
2011-10-12 09:55:31.741277743  import imx_contact_info.sql ...
2011-10-12 09:55:31.782839587  ok.
2011-10-12 09:55:31.784411855  import imx_dept_info.sql ...
2011-10-12 09:55:31.810571330  ok.
2011-10-12 09:55:31.812144210  import imx_email_verify.sql ...
2011-10-12 09:55:31.845733083  ok.
2011-10-12 09:55:31.847350977  import imx_file_inbox.sql ...
2011-10-12 09:55:32.018435504  ok.
2011-10-12 09:55:32.019571222  import imx_file_info.sql ...
2011-10-12 09:55:32.223091461  ok.
2011-10-12 09:55:32.224817994  import imx_friend_group.sql ...
2011-10-12 09:55:32.281588849  ok.
2011-10-12 09:55:32.283117465  import imx_friend_info.sql ...
2011-10-12 09:55:32.352469737  ok.
2011-10-12 09:55:32.353599725  import imx_initial_info.sql ...
2011-10-12 09:55:32.768651908  ok.
2011-10-12 09:55:32.769782898  import imx_invite_code.sql ...
2011-10-12 09:55:33.727045807  ok.
2011-10-12 09:55:33.728694536  import imx_invite_mail.sql ...
2011-10-12 09:55:33.770391648  ok.
2011-10-12 09:55:33.771906349  import imx_mobile_verify.sql ...
2011-10-12 09:55:33.802596093  ok.
2011-10-12 09:55:33.804146300  import imx_notice_file.sql ...
2011-10-12 09:55:33.837360778  ok.
2011-10-12 09:55:33.838869549  import imx_notice_info.sql ...
2011-10-12 09:55:33.871463636  ok.
2011-10-12 09:55:33.873102290  import imx_notice_target.sql ...
2011-10-12 09:55:33.904068780  ok.
2011-10-12 09:55:33.905662507  import imx_online_info.sql ...
2011-10-12 09:55:33.964979634  ok.
2011-10-12 09:55:33.966095908  import imx_organ_info.sql ...
2011-10-12 09:55:34.040437248  ok.
2011-10-12 09:55:34.042039511  import imx_score_info.sql ...
2011-10-12 09:55:34.085358393  ok.
2011-10-12 09:55:34.086935599  import imx_staff_info.sql ...
2011-10-12 09:55:34.122942442  ok.
2011-10-12 09:55:34.124487621  import imx_stat_account.sql ...
2011-10-12 09:55:34.317791382  ok.
2011-10-12 09:55:34.318910362  import imx_support_account.sql ...
2011-10-12 09:55:34.415522781  ok.
2011-10-12 09:55:34.417132697  import imx_support_group.sql ...
2011-10-12 09:55:34.443188764  ok.
2011-10-12 09:55:34.444804092  import imx_support_guest.sql ...
2011-10-12 09:55:34.517196246  ok.
2011-10-12 09:55:34.518350658  import imx_support_website.sql ...
2011-10-12 09:55:34.601287105  ok.
2011-10-12 09:55:34.602892014  import imx_table_usage.sql ...
2011-10-12 09:55:34.630632566  ok.
2011-10-12 09:55:34.632244462  import imx_voter_candidate.sql ...
2011-10-12 09:55:34.659212229  ok.
2011-10-12 09:55:34.660774478  import imx_voter_staff.sql ...
2011-10-12 09:55:34.686763371  ok.
2011-10-12 09:55:34.688372574  import imx_voter_type.sql ...
2011-10-12 09:55:34.715961108  ok.
2011-10-12 09:55:34.717619823  import imx_voter_voted.sql ...
2011-10-12 09:55:34.743695710  ok.
2011-10-12 09:55:34.745283607  done.
[root@liunx0918 mysql]# 

 

相关博文

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

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

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

【4】导入MySQL数据库模式及数据的Bash脚本（v2）