导出数据库脚本-KeepStudy

导出数据库脚本

```shell
#!bin/bash
#导出数据库脚本

HOSTNAME="127.0.0.1"
PORT="3306"
USERNAME="root"
PASSWORD="root"
LOGBACKPATH="/data"

#DROP TRIGGER AND PROCEDURE
dropSql[0]="DROP TRIGGER IF EXISTS changeLevel"
dropSql[1]="DROP TRIGGER IF EXISTS vipTrigger"
dropSql[2]="DROP TRIGGER IF EXISTS loginTrigger"
dropSql[3]="DROP PROCEDURE IF EXISTS statTaskProcedure"
dropSql[4]="DROP PROCEDURE IF EXISTS statSurplusProduceProcedure"
dropSql[5]="DROP PROCEDURE IF EXISTS statSurplusConsumProcedure"
dropSql[6]="DROP PROCEDURE IF EXISTS statBindSurplusProduceProcedure"
dropSql[7]="DROP PROCEDURE IF EXISTS statBindSurplusConsumProcedure"
dropSql[8]="DROP PROCEDURE IF EXISTS onlimeTime"

#DROP INDEX
dropSql[9]="ALTER TABLE gamecurrency DROP INDEX time"
dropSql[10]="ALTER TABLE reg DROP INDEX account"
dropSql[11]="ALTER TABLE reg DROP INDEX time"
dropSql[12]="ALTER TABLE vip1 DROP INDEX roleid"
dropSql[13]="ALTER TABLE pay DROP INDEX orderid"

#获取所有表
SQL="SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA='game_log_9'"
declare TABLES=`mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${SQL}"`

#获取所有game_log数据库名
SQL="SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME LIKE '%game_log%' AND SCHEMA_NAME NOT IN ('game_log_11','game_log_12')"
declare ROWS=`mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${SQL}"`

#循环所有game_log数据库
for dbName in ${ROWS}
do
  #if [ ${dbName} == 'game_log_test' -o ${dbName} == 'game_log_test2' ]
  if [ ${dbName} != 'SCHEMA_NAME' ]
  then
     echo ${dbName}

#连接数据库
     conDb="mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${dbName}"

#drop触发器和存储过程
     echo "DROP TRIGGER AND PROCEDURE"
     for loop in 0 1 2 3 4 5 6 7 8 9 10 11 12 13
     do
        ${conDb} -e "${dropSql[${loop}]}"
     done
     ####

#循环表 去掉字段id
     echo "DROP table id"
     for tb in ${TABLES}
     do
       if [ ${tb} != 'table_name' ]
       then
         ${conDb} -e "ALTER TABLE ${tb} DROP COLUMN id"
       fi
     done
     ####

#导出库
     echo "mysqldump"
     mysqldump -tc --skip-comments --skip-add-drop-table --skip-add-locks --no-create-db --no-tablespaces --compact -u${USERNAME} -p${PASSWORD} ${dbName} > ${LOGBACKPATH}/${dbName}.sql

#压缩导出的库 并删除原来的库
    echo "zip"
    cd ${LOGBACKPATH}
    zip -rmq ${dbName}.zip ${dbName}.sql

#传送zip文件到另一台服务器
    echo "rsync"
    #rsync -ve'ssh -p10220' ${LOGBACKPATH}/${dbName}.zip 193.112.193.164:/data/mysql_backup

#删除zip文件
    #rm -rf ${dbName}.zip
  fi
done

echo "success"
exit 0
```

顶部

Shell应用

目录

相关推荐