mysql数据迁移脚本_mysql数据迁移shell 脚本

有两个脚本第一个mysql_db_trans.sh，第二个mysql_all_db_trans.sh。这个两个脚本可能存在缺陷，放在这里只是为了方便自己查看。

mysql_db_trans.sh

#!/bin/sh

tmp_dbname=$1

db_old_name=$2

db_new_name=$3

db_ip=$4

db_port=$5

db_user=$6

trans_type=$7

db_pwd=$8

start_date=

end_date=

#---------function list

DO_LOG()

{

time=`date "+%Y-%m-%d %H:%M:%S"`

echo "[$time] $*";

}

do_db_trans()

{

if [ $trans_type -eq 0 ];then

DO_LOG "(IP:$db_ip Port:$db_port) Prepare:(IP=$db_ip PORT=$db_port)..."

mysql -h${db_ip} -P${db_port} -u${db_user} -D${db_new_name} -e"CREATE DATABASE ${tmp_dbname} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;create table ${tmp_dbname}.gcjl_hphm_tmp as select * from ${db_new_name}.gcjl_hphm where 1=2;create table ${tmp_dbname}.gcjl_hphm_info_tmp as select * from ${db_new_name}.gcjl_hphm_info where 1=2;alter table ${db_old_name}.sjkk_gcjl add index idx_hphm_info_tmp(hphm, hpzl, hpys, clpp,cllx);"

elif [ $trans_type -eq 1 ];then

DO_LOG "(IP:$db_ip Port:$db_port) Table:gcjl_hphm_tmp..."

mysql -h${db_ip} -P${db_port} -u${db_user} -D${db_new_name} -e"insert into ${tmp_dbname}.gcjl_hphm_tmp (hphm) select distinct hphm from ${db_old_name}.sjkk_gcjl;"

DO_LOG "(IP:$db_ip Port:$db_port) Table:gcjl_hphm_info_tmp..."

mysql -h${db_ip} -P${db_port} -u${db_user} -D${db_new_name} -e"insert into ${tmp_dbname}.gcjl_hphm_info_tmp (hphm, hpzl, hpys, clpp, clzpp, clnk, cllx) select distinct hphm, hpzl, hpys, clpp,null, null, cllx from ${db_old_name}.sjkk_gcjl;alter table ${db_old_name}.sjkk_gcjl drop index idx_hphm_info_tmp;"

elif [ $trans_type -eq 2 ];then

for((i=1;i<=730;i=i+1))

do

end_date=`mysql -h${db_ip} -P${db_port} -u${db_user} -e "select CONCAT(DATE_FORMAT(DATE_ADD(now(),INTERVAL 2-$i day),'%Y-%m-%d'),' 00:00:00') mysqltime;"`

start_date=`mysql -h${db_ip} -P${db_port} -u${db_user} -e "select CONCAT(DATE_FORMAT(DATE_ADD(now(),INTERVAL 1-$i day),'%Y-%m-%d'),' 00:00:00') mysqltime;"`

end_date=`echo $end_date|awk -F ' ' '{print $2 " " $3}'`

start_date=`echo $start_date|awk -F ' ' '{print $2 " " $3}'`

DO_LOG "(IP:$db_ip Port:$db_port) Table:sjkk_gcjl start_date:$start_date end_date:$end_date..."

mysql -h${db_ip} -P${db_port} -u${db_user} -D${db_new_name} -e"insert into ${db_new_name}.sjkk_gcjl (jlbh, xzqh, kkbh, cdbh, hphm, hpzl, hpys, jgsj, clsd, cllx, csys, clpp) select jlbh,xzqh, kkbh, cdbh, hphm, hpzl, hpys, jgsj, clsd, cllx, csys, clpp from ${db_old_name}.sjkk_gcjl where jgsj>='$start_date' and jgsj

DO_LOG "(IP:$db_ip Port:$db_port) Table:sjkk_gcxq start_date:$start_date end_date:$end_date..."

mysql -h${db_ip} -P${db_port} -u${db_user} -D${db_new_name} -e"insert into ${db_new_name}.sjkk_gcxq (jlbh, sbbh, cdfx, xszt, cwkc, tplx, tztp, qjtp, rksj, yzsj, sjcz, yzd1, yzd2, yzd3, yzd4) select jlbh, sbbh, cdfx, xszt, cwkc, tplx, tztp, qjtp, rksj, yzsj, sjcz, ylzd1, ylzd2, ylzd3, ylzd4 from ${db_old_name}.sjkk_gcjl where jgsj>='$start_date' and jgsj

done

fi

}

do_db_trans

mysql_all_db_trans.sh

#!/bin/sh

#---variables list

trans_step=`echo $1|tr '[A-Z]' '[a-z]'`

count=2

opaq_id=192.168.60.150

opaq_port=3377

tmp_dbname=trans_tmp

db_ip[1]=192.168.60.150

db_port[1]=3306

db_user[1]=root

db_pwd[1]=

db_old_name[1]=shanghai_old_test

db_new_name[1]=shanghai_test

db_ip[2]=192.168.60.151

db_port[2]=3306

db_user[2]=root

db_pwd[2]=

db_old_name[2]=shanghai_old_test

db_new_name[2]=shanghai_test

#---------tmp dir & log file

mkdir -p /home/mysql_trans_tmp

chmod -R 777 /home/mysql_trans_tmp

tmp_dir=/home/mysql_trans_tmp

log_file=$tmp_dir/trans.log

rm -f $log_file

#---------function list

usage()

{

echo "usage: sh mysql_all_db_trans.sh "

echo "trans_step: first|second|clear"

echo "example:"

echo " sh mysql_all_db_trans.sh first"

echo " sh mysql_all_db_trans.sh second"

echo " sh mysql_all_db_trans.sh clear"

}

DO_LOG()

{

time=`date "+%Y-%m-%d %H:%M:%S"`

echo "[$time] $*"

echo "[$time] $*">>$log_file

}

DO_WARN_LOG()

{

time=`date "+%Y-%m-%d %H:%M:%S"`

echo -e "\033[33m[$time]WARNING: $*\033[0m"

echo "[$time] $*">>$log_file

}

DO_ERR_LOG()

{

time=`date "+%Y-%m-%d %H:%M:%S"`

echo -e "\033[31m[$time]ERROR: $*\033[0m"

echo "[$time] $*">>$log_file

}

DO_SUCC_LOG()

{

time=`date "+%Y-%m-%d %H:%M:%S"`

echo -e "\033[32m[$time]SUCCESS: $*\033[0m"

echo "[$time] $*">>$log_file

}

do_db_check()

{

declare db_old_name=$1

declare db_new_name=$2

declare db_ip=$3

declare db_port=$4

declare db_user=$5

declare db_pwd=$6

if [ -z ${db_old_name} -o -z ${db_new_name} -o -z ${db_ip} -o -z ${db_port} -o -z ${db_user} ];then

#没有配置必要的参数

DO_ERR_LOG "Cann't connect to mysql. db_old_name=$1 db_new_name=$2 db_ip=$3 db_port=$4 db_user=$5 db_pwd=$6"

exit 1

elif [ -z ${db_pwd} ];then

#无密码登录，数据库链接检测，和需要的database是否存在的检测

if [ $(mysql -h${db_ip} -u${db_user} -P${db_port} -e "select 'db_db_check';" 2>>/dev/null |wc -l) -ne 2 ];then

DO_ERR_LOG "Cann't connect to mysql. db_old_name=$1 db_new_name=$2 db_ip=$3 db_port=$4 db_user=$5 db_pwd=$6"

exit 1

elif [ $(mysql -h${db_ip} -u${db_user} -P${db_port} -D${db_old_name} -e "select 'db_db_check';" 2>>/dev/null |wc -l) -ne 2 -o $(mysql -h${db_ip} -u${db_user} -P${db_port} -D${db_new_name} -e "select 'db_db_check';" 2>>/dev/null |wc -l) -ne 2 ];then

DO_ERR_LOG "sechma $db_old_name or $db_new_name is not exists. db_old_name=$1 db_new_name=$2 db_ip=$3 db_port=$4 db_user=$5 db_pwd=$6"

exit 1

fi

else

#有密码登录，数据库链接检测，和需要的database是否存在的检测

if [ $(mysql -h${db_ip} -u${db_user} -P${db_port} -p{db_pwd} -e "select 'db_db_check';" 2>>/dev/null |wc -l) -ne 2 ];then

DO_ERR_LOG "Cann't connect to mysql(db_old_name=$1 db_new_name=$2 db_ip=$3 db_port=$4 db_user=$5 db_pwd=$6). "

exit 1

elif [ $(mysql -h${db_ip} -u${db_user} -P${db_port} -D${db_old_name} -e "select 'db_db_check';" 2>>/dev/null |wc -l) -ne 2 -o $(mysql -h${db_ip} -u${db_user} -P${db_port} -D${db_new_name} -e "select 'db_db_check';" 2>>/dev/null |wc -l) -ne 2 ];then

DO_ERR_LOG "sechma $db_old_name or $db_new_name is not exists. db_old_name=$1 db_new_name=$2 db_ip=$3 db_port=$4 db_user=$5 db_pwd=$6"

exit 1

fi

fi

DO_SUCC_LOG "DB(db_old_name=$1 db_new_name=$2 db_ip=$3 db_port=$4 db_user=$5 db_pwd=$6) check is OK."

sleep 0.5

return 0

}

do_opaq_check()

{

if [ $(mysql -h${opaq_id} -P${opaq_port} -e"show tables;" 2>>/dev/null |wc -l) -lt 1 ];then

DO_ERR_LOG "Cann't connect to OPAQ. opaq_id=${opaq_id} opaq_port=${opaq_port} "

exit 1

else

DO_SUCC_LOG "OPAQ(opaq_id=${opaq_id} opaq_port=${opaq_id}) check is OK."

fi

}

do_check()

{

for ((i=1;i<=$count;i=i+1));

do

do_db_check ${db_old_name[$i]} ${db_new_name[$i]} ${db_ip[$i]} ${db_port[$i]} ${db_user[$i]} ${db_pwd[$i]}

done

}

do_hphm_and_info_trans()

{

for ((i=2;i<=$count;i=i+1));

do

mysqldump -h${db_ip[$i]} -P${db_port[$i]} -u${db_user[$i]} --skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys --no-tablespaces --complete-insert --no-create-db=TRUE --no-create-info=TRUE --skip-quote-names --max-allowed-packet=4096 --net_buffer_length=4096 ${tmp_dbname} gcjl_hphm_tmp |mysql -h${db_ip[1]} -P${db_port[1]} -u${db_user[1]} -D${tmp_dbname} &

mysqldump -h${db_ip[$i]} -P${db_port[$i]} -u${db_user[$i]} --skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys --no-tablespaces --complete-insert --no-create-db=TRUE --no-create-info=TRUE --skip-quote-names --max-allowed-packet=4096 --net_buffer_length=4096 ${tmp_dbname} gcjl_hphm_info_tmp |mysql -h${db_ip[1]} -P${db_port[1]} -u${db_user[1]} -D${tmp_dbname} &

done

wait

mysql -h${db_ip[1]} -P${db_port[1]} -u${db_user[1]} -D${db_new_name[1]} -e"alter table ${tmp_dbname}.gcjl_hphm_tmp add index idx_hphm(hphm);create table ${tmp_dbname}.gcjl_hphm as select * from ${db_new_name[1]}.gcjl_hphm where 1=2;insert into ${tmp_dbname}.gcjl_hphm select distinct hphm from ${tmp_dbname}.gcjl_hphm_tmp;"

mysql -h${db_ip[1]} -P${db_port[1]} -u${db_user[1]} -D${db_new_name[1]} -e"alter table ${tmp_dbname}.gcjl_hphm_info_tmp add index idx_hphm_info(hphm, hpzl, hpys, clpp, clzpp, clnk, cllx);create table ${tmp_dbname}.gcjl_hphm_info as select * from ${db_new_name[1]}.gcjl_hphm_info where 1=2;insert into ${tmp_dbname}.gcjl_hphm_info (hphm, hpzl, hpys, clpp, clzpp, clnk, cllx) select distinct hphm, hpzl, hpys, clpp,null, null, cllx from ${tmp_dbname}.gcjl_hphm_info_tmp;"

mysqldump -h${db_ip[1]} -P${db_port[1]} -u${db_user[1]} --skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys --no-tablespaces --complete-insert --no-create-db=TRUE --no-create-info=TRUE --skip-quote-names --max-allowed-packet=4096 --net_buffer_length=4096 ${tmp_dbname} gcjl_hphm|grep -v ^/|mysql -h${opaq_id} -P${opaq_port}

mysqldump -h${db_ip[1]} -P${db_port[1]} -u${db_user[1]} --skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys --no-tablespaces --complete-insert --no-create-db=TRUE --no-create-info=TRUE --skip-quote-names --max-allowed-packet=4096 --net_buffer_length=4096 ${tmp_dbname} gcjl_hphm_info|grep -v ^/|mysql -h${opaq_id} -P${opaq_port}

}

do_prepare()

{

DO_LOG "--------------------STRAT PREPARE ENVIRONMENT---------------------"

for ((i=1;i<=$count;i=i+1));

do

sh mysql_db_trans.sh ${tmp_dbname} ${db_old_name[$i]} ${db_new_name[$i]} ${db_ip[$i]} ${db_port[$i]} ${db_user[$i]} 0 ${db_pwd[$i]} |tee -a $log_file &

done

wait

DO_LOG "--------------------END PREPARE ENVIRONMENT--------------------- "

}

do_trans_first()

{

DO_LOG "--------------------START TRANSFER DATA(tables:gcjl_hphm gcjl_hphm_info)---------------------"

for ((i=1;i<=$count;i=i+1));

do

sh mysql_db_trans.sh ${tmp_dbname} ${db_old_name[$i]} ${db_new_name[$i]} ${db_ip[$i]} ${db_port[$i]} ${db_user[$i]} 1 ${db_pwd[$i]} |tee -a $log_file &

done

wait

do_hphm_and_info_trans

DO_LOG "--------------------END TRANSFER DATA(tables:gcjl_hphm gcjl_hphm_info)--------------------- "

}

do_trans_second()

{

DO_LOG "--------------------START TRANSFER DATA(tables:sjkk_gcjl sjkk_gcxq)---------------------"

for ((i=1;i<=$count;i=i+1));

do

sh mysql_db_trans.sh ${tmp_dbname} ${db_old_name[$i]} ${db_new_name[$i]} ${db_ip[$i]} ${db_port[$i]} ${db_user[$i]} 2 ${db_pwd[$i]} |tee -a $log_file &

done

wait

DO_LOG "--------------------END TRANSFER DATA(tables:sjkk_gcjl sjkk_gcxq)--------------------- "

}

do_clear()

{

DO_LOG "--------------------START CLEAR TEMP DATABASE---------------------"

for ((i=1;i<=$count;i=i+1));

do

mysql -h${db_ip[$i]} -u${db_user[$i]} -P${db_port[$i]} -e "DROP DATABASE IF EXISTS ${tmp_dbname};"

done

DO_LOG "--------------------END CLEAR TEMP DATABASE---------------------"

}

if [ $# -ne 1 ];then

DO_ERR_LOG "error! please use right method. "

usage

elif [ "$1" != "first" -a "$1" != "second" -a "$1" != "clear" ];then

DO_ERR_LOG "error! please use right method. "

usage

fi

if [ "$trans_step" = "first" ];then

do_check

do_opaq_check

do_prepare

do_trans_first

elif [ "$trans_step" = "second" ];then

do_check

do_trans_second

do_clear

elif [ "$trans_step" = "clear" ];then

do_clear

fi