mysql快速导出导入脚本，修改库名和所有者，实现快速迁移

背景

开发库迁移部署时，需要部署的库越来越多，部署时需要统一修改目标库名称，根绝创建用户统一修改存储过程所有者，所以此脚本在于快速解决这一问题

脚本

vim imex.sh

#!/bin/sh

#导入环境变量
PATH=/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/bin:/sbin:~/bin
export PATH

#备份路径和需要备份数据库的记录文件
BACKUP_PATH='/opt/bak/dump'
DB_NAME='/opt/bak/db_name.sql'
#源端数据库连接
USERBANE1='root'
PASSWD1='root123'
HOST1='192.168.8.223'
PORT1='3306'
#目标端数据库连接
USERBANE2='root'
PASSWD2='root123'
HOST2='10.11.40.212'
PORT2='3306'

#修改存储过程所有者
OLDUSERNAME='root'
NEWUSERNAME='test'

#修改数据库
OLDDBNAME='db1'
NEWDBNAME='db2'

#批量导出数据库
function exportdb(){
cat $DB_NAME | while read line
do
    echo "export database: "$line
	mysqldump -u$USERBANE1 -p$PASSWD1 -h$HOST1 -P$PORT1 -R -E --column-statistics=0 --single-transaction --master-data=2 --set-gtid-purged=OFF  $line > $BACKUP_PATH"/"$line".sql"
    echo "mysqldump -u$USERBANE1 -p$PASSWD1 -h$HOST1 -P$PORT1 -R -E --column-statistics=0 --single-transaction --master-data=2 --set-gtid-purged=OFF  $line >$BACKUP_PATH/$line.sql"
done
}

#批量修改库名
function renamedb(){
cp $DB_NAME  $DB_NAME".rename"
sed -i "s/$OLDDBNAME/$NEWDBNAME/g" `grep $OLDDBNAME -rl $DB_NAME".rename"`
awk '{print "create database if not exists "$0}' $DB_NAME".rename" >$DB_NAME".create1"
awk '{print $0";"}' $DB_NAME".create1" >$DB_NAME".create"
rm -f $DB_NAME".create1"
#修改导出脚本名称
for file in `ls $BACKUP_PATH"/"*.sql`;do mv $file `echo $file|sed "s/$OLDDBNAME/$NEWDBNAME/g"`;done;
}

#批量导入数据库
function importdb(){
cat $DB_NAME".rename" | while read line
do
    echo "import database: "$line
	mysql -u$USERBANE2 -p$PASSWD2 -h$HOST2 -P$PORT2 -D $line < $BACKUP_PATH"/"$line".sql"
done
}

#批量创建数据库
function createdb(){
cat $DB_NAME".create" | while read line
do
    echo "create database: "$line
done
mysql -u$USERBANE2 -p$PASSWD2 -h$HOST2 -P$PORT2  < $DB_NAME".create"
}


#批量修改存储过程所有者
function renameproc(){
sed -i "s/$OLDUSERNAME/$NEWUSERNAME/g" `grep $OLDUSERNAME -rl $BACKUP_PATH`
}

#清空导出脚本DEFINER所有者
function cleardefiner(){
sed -i "s/DEFINER=.*%\`//g" `grep DEFINER= -rl $BACKUP_PATH`
}

#去掉字符集标识
function clearcharacter(){
sed -i "s/DEFAULT.*utf8mb4_0900_ai_ci//g" `grep DEFAULT -rl $BACKUP_PATH`
sed -i "s/CHARACTER.*utf8_general_ci//g" `grep CHARACTER -rl $BACKUP_PATH`
sed -i "s/CHARACTER.*utf8mb4_0900_ai_ci//g" `grep CHARACTER -rl $BACKUP_PATH`
sed -i "s/SET.*utf8mb4_0900_ai_ci//g" `grep SET -rl $BACKUP_PATH`
}


#MYSQL8.0脚本转换成5.7脚本
#with语法5.7不支持，需要在存储过程结尾加 /*with_flag*/
function mysql80to57(){
sed -i "s/DEFAULT (1)/DEFAULT 1/g" `grep DEFAULT -rl $BACKUP_PATH`
sed -i "s/DEFAULT (0)/DEFAULT 0/g" `grep DEFAULT -rl $BACKUP_PATH`
sed -i "s/DEFAULT (now())/DEFAULT CURRENT_TIMESTAMP/g" `grep DEFAULT -rl $BACKUP_PATH`
sed -i "s/CREATE.*prCsQueryE6VehicleAndCorp\`/\/\*CREATE  PROCEDURE prCsQueryE6VehicleAndCorp/g" `grep CREATE -rl $BACKUP_PATH`
sed -i "s/with_flag.*;;/END\*\/;;/g" `grep with_flag -rl $BACKUP_PATH`
}

#执行函数
exportdb
cleardefiner
renamedb
createdb
renameproc
clearcharacter
mysql80to57
importdb

rm -f $DB_NAME"."*
rm -f $BACKUP_PATH"/"*