shell备份mysqldump

 

 

======================= 内网测试环境代码

 

----------------------- sql_hub.sql

 select db_suffix from corp;

 

 

----------------------- sql_hub.txt

aa bb cc dd

 

 

 

----------------------- mysqldump_alldb.sh 【测试环境,三台db :hub,sc.kc】

#!/bin/sh

#bak路径

date_path=`date "+%Y-%m-%d"`
bak_path="/usr/local/shell/bak_mysql/${date_path}"
mkdir -p $bak_path

#MySQL用户名，格式：空或者-h hostname或者-h ip
db_host_hub="-h 192.168.1.78"
db_host_sc="-h 192.168.1.78"
db_host_kc="-h 192.168.1.87"
#MySQL用户名，格式：空或者-h hostname或者-h ip
db_port="-P 3301"

#MySQL用户名，格式：-u root或者-u username
db_root_name="-ureplication"

#MySQL用户密码，格式：空或者-p password
db_root_pass="-p123456"
#########参数配置

clear

echo "mysqldump all db..."
db_hub="sc_hub"
mysql $db_root_name $db_root_pass $db_port $db_host_hub $db_hub < sql_hub.sql |sed '1d'|tr -s '\r\n' ' ' > sql_hub.txt
all_corp=sql_hub.txt
if [[ ! -s "sql_hub.txt" ]]; then
   echo "corp is empty!"
fi
if [[ -z "$*" &&  ! -s "sql_hub.txt" ]]; then
    echo "如果没有SQL文件请运行下面命令"
    #echo "/usr/bin/svn export --username ${svn_username} --password ${svn_password} --force ${svn_url} ${sql_path}"
    echo
    echo "请输入数据库名称。"
    echo "多个数据库名称请用空格分隔。"
    echo "$0 db_name1 [db_name2 db_name3 ...]"
    echo
    exit
fi

 

# 备份hub webauth

mysqldump $db_root_name $db_root_pass $db_port $db_host_hub --default-character-set=utf8 -E -R --add-drop-table --triggers=false sc_hub > $bak_path/sc_hub.sql
mysqldump $db_root_name $db_root_pass $db_port $db_host_hub --default-character-set=utf8 -E -R --add-drop-table --triggers=false webauth > $bak_path/webauth.sql

 

cat $all_corp
#循环创建数据库的存储过程、函数、视图
for db_name in `cat $all_corp` ;do
    echo ----------
    echo "开始备份${db_name}数据库的存储过程、函数、视图结构..."

mysqldump $db_root_name $db_root_pass $db_port $db_host_sc --default-character-set=utf8 -E -R --add-drop-table --triggers=false "sc_${db_name}" > $bak_path/"sc_${db_name}.sql"
mysqldump $db_root_name $db_root_pass $db_port $db_host_kc --default-character-set=utf8 -E -R --add-drop-table --triggers=false "kc_${db_name}" > $bak_path/"kc_${db_name}.sql"


    echo "结束备份${db_name}数据库的存储过程、函数、视图结构..."

    echo "----------"
done

 

 

 

======================= 正式测试环境代码

 

 

 

 

----------------------- sql_hub.sql

  SELECT corp.db_suffix,game_corpdb.game_id,game_corpdb.db_host FROM corp LEFT JOIN game_corpdb ON corp.id = game_corpdb.corp_id;

 

 

----------------------- sql_hub.txt

sys     2       192.168.102.71:3301
sys     3       192.168.102.71:3301
tem     2       192.168.102.71:3301
tem     3       192.168.102.71:3301
ta      2       192.168.102.71:3301
ta      3       192.168.102.71:3301
tb      2       192.168.102.71:3301
tb      3       192.168.102.71:3301
ce1     2       192.168.102.71:3302
ce1     3       192.168.102.71:3302
ce2     2       192.168.102.71:3302
ce2     3       192.168.102.71:3302
ce3     2       192.168.102.71:3302
ce3     3       192.168.102.71:3302
ce4     2       192.168.102.71:3302
ce4     3       192.168.102.71:3302
ce5     2       192.168.102.71:3302
ce5     3       192.168.102.71:3302

 

 

----------------------- mysqldump_alldb.sh【正式环境，各公司可以发布再不同的ip和port上】

 

#!/bin/sh

#SQL脚本路径
date_path=`date "+%Y-%m-%d"`
bak_path="/usr/local/shell/bak_mysql/${date_path}"
mkdir -p $bak_path

#MySQL用户名，格式：空或者-h hostname或者-h ip
db_host_hub="-h 192.168.102.71"
#MySQL用户名，格式：空或者-h hostname或者-h ip
db_port="-P 3301"

#MySQL用户名，格式：-u root或者-u username
db_root_name="-ureplication"

#MySQL用户密码，格式：空或者-p password
db_root_pass="-p123456"
#########参数配置

clear

echo "mysqldump all db..."
db_hub="sc_hub"
mysql $db_root_name $db_root_pass $db_port $db_host_hub $db_hub < sql_hub.sql |sed '1d' > sql_hub.txt
all_corp=sql_hub.txt
if [[ ! -s "sql_hub.txt" ]]; then
   echo "corp is empty!"
fi
#cat $all_corp

# 备份hub webauth
mysqldump $db_root_name $db_root_pass $db_port $db_host_hub --default-character-set=utf8 -E -R --add-drop-table --triggers=false sc_hub > $bak_path/sc_hub.sql
mysqldump $db_root_name $db_root_pass $db_port $db_host_hub --default-character-set=utf8 -E -R --add-drop-table --triggers=false webauth > $bak_path/webauth.sql

#循环创建数据库的存储过程、函数、视图
#cat $all_corp
cat $all_corp | while read line
do
    echo ----------
    flag1=`echo "$line" | awk 'BEGIN {FS=" "}{print $1}'`
    flag2=`echo "$line" | awk 'BEGIN {FS=" "}{print $2}'`
    flag3=`echo "$line" | awk 'BEGIN {FS=" "}{print $3}'`
    corp_db_host=`echo "$flag3" | awk 'BEGIN {FS=":"}{print $1}'`
    corp_db_port=`echo "$flag3" | awk 'BEGIN {FS=":"}{print $2}'`
    if [[ $flag2 -eq 2 ]];then
        corp_name="sc_${flag1}"
    else
        corp_name="kc_${flag1}"
    fi
    echo "开始备份${corp_name}数据库的存储过程、函数、视图结构..."

    mysqldump -h $corp_db_host $db_root_name $db_root_pass -P $corp_db_port --default-character-set=utf8 -E -R --add-drop-table --triggers=false $corp_name > $bak_path/"${corp_name}.sql"

    echo "结束备份${corp_name}数据库的存储过程、函数、视图结构..."

    echo "----------"

done

exit 0

 

 

======================= mysqldump_alldb.sh【正式环境，各公司可以发布再不同的ip和port上,增加导出单个公司和删除单个公司库 】

 

 

 

 

#!/bin/sh
# $1 表示公司数据库后缀db_suffix，只导出该公司;为空表示导出所有公司
# $2 delete表示删除$1所指定的公司ssc和klc数据库

#SQL脚本路径
date_path=`date "+%Y-%m-%d"`
bak_path="/usr/local/shell/bak_mysql/${date_path}"
mkdir -p $bak_path
root_path="/usr/local/shell"

#MySQL用户名，格式：空或者-h hostname或者-h ip
db_host_hub="-h 192.168.102.71"
#db_host_ssc="-h 192.168.102.71"
#db_host_klc="-h 192.168.102.71"
#MySQL用户名，格式：空或者-h hostname或者-h ip
db_port="-P 3301"

#MySQL用户名，格式：-u root或者-u username
db_root_name="-ureplication"

#MySQL用户密码，格式：空或者-p password
db_root_pass="-p123456"
#########参数配置

clear

echo "mysqldump db..."
db_hub="sc_hub"
#mysql $db_root_name $db_root_pass $db_port $db_host_hub $db_hub < sql_hub.sql |sed '1d'|tr -s '\r\n' ' ' > sql_hub.txt
mysql $db_root_name $db_root_pass $db_port $db_host_hub $db_hub < "${root_path}/sql_hub.sql" |sed '1d' > "${root_path}/sql_hub.txt"
all_corp="${root_path}/sql_hub.txt"
if [[ ! -s "${root_path}/sql_hub.txt" ]]; then
   echo "corp is empty!"
fi
#cat $all_corp

# 导出all db 时(不带参数)才备份hub webauth
if [[ -z "$*" ]]; then
mysqldump $db_root_name $db_root_pass $db_port $db_host_hub --default-character-set=utf8 -E -R --add-drop-table --triggers=false sc_hub > $bak_path/sc_hub.sql
mysqldump $db_root_name $db_root_pass $db_port $db_host_hub --default-character-set=utf8 -E -R --add-drop-table --triggers=false webauth > $bak_path/webauth.sql
fi


#循环创建数据库的存储过程、函数、视图
#cat $all_corp
cat $all_corp | while read line
do
    flag1=`echo "$line" | awk 'BEGIN {FS=" "}{print $1}'`
    flag2=`echo "$line" | awk 'BEGIN {FS=" "}{print $2}'`
    flag3=`echo "$line" | awk 'BEGIN {FS=" "}{print $3}'`
    corp_db_host=`echo "$flag3" | awk 'BEGIN {FS=":"}{print $1}'`
    corp_db_port=`echo "$flag3" | awk 'BEGIN {FS=":"}{print $2}'`
    if [[ $flag2 -eq 2 ]];then
        corp_name="sc_${flag1}"
    else
        corp_name="kc_${flag1}"
    fi

    #匹配公司数据库后缀名称，只导出该公司数据库备份
    if [[ -n "$*" ]]; then
        if [[ "$1" == "${flag1}" ]]; then
            if [[ "$2" == "delete" ]]; then
                echo "开始删除数据库${corp_name}"
                #echo "drop database $corp_name"
                #drop database $corp_name
                echo "结束删除数据库${corp_name}"
                continue
            fi
        else
            continue
        fi
    fi
   
    echo ----------
    echo "开始备份${corp_name}数据库的存储过程、函数、视图结构..."
    mysqldump -h $corp_db_host $db_root_name $db_root_pass -P $corp_db_port --default-character-set=utf8 -E -R --add-drop-table --triggers=false $corp_name > $bak_path/"${corp_name}.sql"
    echo "结束备份${corp_name}数据库的存储过程、函数、视图结构..."
    echo "----------"

done


exit 0