[sh]update sql

编程技术  /  houtizong 发布于 3年前   108

 

 

======================================== 单库升级

 

 

 

 

#!/bin/shSVN配置#svn_username="username"#svn_password="password"#svn_url="http://192.168.1.2/svn/test/"SQL脚本路径sql_path="/var/www/data/scheme"#MySQL用户名，格式：空或者-h hostname或者-h ipdb_host="-h 192.168.1.7"#MySQL用户名，格式：空或者-h hostname或者-h ipdb_port="-P 3301"#MySQL用户名，格式：-u root或者-u usernamedb_root_name="-uroot"#MySQL用户密码，格式：空或者-p passworddb_root_pass="-p123456"#########参数配置clearif [ -z "$*" ]; 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    exitfi#循环创建数据库的存储过程、函数、视图for db_name in $*; do    echo ----------    echo "开始创建${db_name}数据库的存储过程、函数、视图结构..."     mysql $db_root_name $db_root_pass $db_port $db_host $db_name < $sql_path/TEST_f_common.sql     mysql $db_root_name $db_root_pass $db_port $db_host $db_name < $sql_path/TEST_Update_20120114.sql    echo "结束创建${db_name}数据库的存储过程、函数、视图结构..."    echo "----------"done

 

 

 

 

 

======================================== 测试环境：多(公司)库升级

 

 

 sql_boss.sql 用来查询前缀库

 select CONCAT('test_',db_suffix) from corp;

 

 

 sql_boss.txt 用来保存所有公司的库名

 

 

 

 

 

 

 #!/bin/sh
#SVN配置
#svn_username="username"
#svn_password="password"
#svn_url="http://192.168.1.201:8080/svn/sche
me"

#SQL脚本路径
sql_path="/var/www/WWW_TEST_TEST/protected/data/scheme"

#MySQL用户名，格式：空或者-h hostname或者-h ip
db_host="-h 192.168.1.78"

#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 "update mysql dbs..."
db_boss="test_boss"
mysql $db_root_name $db_root_pass $db_port $db_host $db_hub < sql_boss.sql |sed '1d'|tr -s '\r\n' ' ' > sql_boss.txt
all_corp=sql_boss.txt
if [[ ! -s "sql_boss.txt" ]]; then
   echo "corp is empty!"
fi
if [[ -z "$*" &&  ! -s "sql_boss.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
cat $all_corp
#循环创建数据库的存储过程、函数、视图
for db_name in `cat $all_corp` ;do
    echo ----------
    echo "开始创建${db_name}数据库的存储过程、函数、视图结构..."



        mysql $db_root_name $db_root_pass $db_port $db_host $db_name < $sql_path/p_common.sql



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


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

 

 

 

 

======================================== 正式环境

 

 

sql_hub_sc.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 WHER
E game_corpdb.game_id=2;

 

 

 

输出格式：

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

 

 

 

 

 

update_hub_mysql_sc.sh

 

#!/bin/sh

#SQL脚本路径
sql_path="/var/www/sc/protected/data/scheme"

#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

#hub库名
db_hub="ssc_hub"

mysql $db_root_name $db_root_pass $db_port $db_host_hub $db_hub < sql_hub_sc.sql |sed '1d' > sql_hub_sc.txt

all_corp=sql_hub_sc.txt

if [[ ! -s "sql_hub_sc.txt" ]]; then
   echo "corp is empty!"
fi


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}数据库的存储过程、函数、视图结构..."


mysql  -h $corp_db_host $db_root_name $db_root_pass -P $corp_db_port $corp_name < $sql_path/SC_p_order_rerve.sql


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

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

done


exit 0