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

vlambda
2021-07-08

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

背景

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

脚本

vim imex.sh

#!/bin/sh
#导入环境变量PATH=/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/bin:/sbin:~/binexport 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 linedo 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"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 linedo 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 linedo echo "create database: "$linedonemysql -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`}#执行函数，安装实际情况注释下面函数，实现分步导出导入，下面只是导出，要是源和目标库网络联通，可去掉注释实现一步导出导入exportdb #导出数据cleardefiner #清空definer#renamedb #修改库名#createdb #创建数据库#renameproc #修改存储过程/函数/触发器/视图等所有者#importdb #导入数据#rm -f $DB_NAME"."*#rm -f $BACKUP_PATH"/"*