MySQL - mysqldump多种方式实现数据迁移
【摘要】
一行命令
mysqldump -h172.168.15.222 -P3406 -uroot -p123456 -C --databases artisan |mysql -h172.168.15.2...
一行命令
mysqldump -h172.168.15.222 -P3406 -uroot -p123456 -C --databases artisan |mysql -h172.168.15.221 -P3406 -uroot -p123456 zfdc
-C 压缩
Shell 导入导出
【导出】
#!/bin/bash
# 以下配置信息请根据现场实际情况修改
mysql_user="root" #MySQL备份用户
mysql_password="123456" #MySQL备份用户的密码
mysql_host="172.168.15.222"
mysql_port="3406"
mysql_charset="utf8" #MySQL编码
backup_db_arr=("chkproof" "framework" "zfdc" "zfmg") #要备份的数据库名称，多个用空格分开隔开
backup_location=/home/ap/mysqlbackup03 #备份数据存放位置，末尾请不要带"/", 不存在则创建文件夹
# 本行开始以下不需要修改
# 连接到mysql数据库，无法连接则备份退出
mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password <<end
use mysql;
select host,user from user where user='root' and host='localhost';
exit
end
flag=`echo $?`
if [ $flag != "0" ]; then
echo "ERROR:Can't connect mysql server! please check config or server ! backup stop!"
exit
else
echo "MySQL connect ok! Please wait......"
# 判断有没有定义备份的数据库，如果定义则开始备份，否则退出备份
if [ "$backup_db_arr" != "" ];then
for dbname in ${backup_db_arr[@]}
do
echo "database【$dbname】backup start...may cost servral minutes depends on the size of database , please waiting for a while ..."
`mkdir -p $backup_location`
# 如果有创建数据库的权限 就使用下面这一行带有 -B 的指令
#`mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -B $dbname --default-character-set=$mysql_charset > $backup_location/$dbname.sql`
# 前提：数据库用户创建好。 建行分配的应用账号没有创建DB的权限，所以导出的脚本中 不包含 create database.
`mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --default-character-set=$mysql_charset > $backup_location/$dbname.sql`
flag=`echo $?`
if [ $flag == "0" ];then
echo "database $dbname success backup to $backup_location/$dbname.sql"
echo " "
else
echo "database $dbname backup fail!"
fi
done
else
echo "ERROR:No database to backup! Please check config ! backup stop"
exit
fi
echo "All database backup success!"
exit
fi
【导入】
#!/bin/bash
# 以下配置信息请根据现场实际情况修改
mysql_user="root"
mysql_password="123456"
mysql_host="172.168.15.200"
mysql_port="3406"
mysql_charset="utf8" #MySQL编码
backup_location=/home/ap/mysqlbackup03
# 本行开始以下不需要修改
# 连接到mysql数据库，无法连接则备份退出
mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password <<end
use mysql;
select host,user from user where user='root' and host='localhost';
exit
end
flag=`echo $?`
if [ $flag != "0" ]; then
echo "ERROR:Can't connect mysql server! please check config or server ! import stop!"
exit
else
echo "MySQL connect ok! Please wait......"
# 判断有没有配置DB文件存放的位置
if [ "$backup_location" != "" ];then
for zffile in `ls $backup_location`;
do
echo "file【$zffile】import start...may cost servral minutes depends on the size of database , please waiting for a while ..."
database=`echo $zffile | awk -F "." '{ print $1}'`
echo "$database importing..."
`mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $database < $backup_location/$zffile`
flag=`echo $?`
if [ $flag == "0" ];then
echo "$zffile import successfully"
echo " "
else
echo "$zffile import fail!!!"
fi
done
else
echo "ERROR:No file to import! Import stop"
exit
fi
echo "All database import success!"
exit
fi
文章来源: artisan.blog.csdn.net，作者：小小工匠，版权归原作者所有，如需转载，请联系作者。
原文链接：artisan.blog.csdn.net/article/details/114681792
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
作者其他文章
评论（0）