MySQL - mysqldump多种方式实现数据迁移

一行命令

代码语言：javascript复制

mysqldump -h172.168.15.222 -P3406 -uroot -p123456 -C --databases artisan |mysql -h172.168.15.221 -P3406 -uroot -p123456 zfdc

-C 压缩

---

Shell 导入导出

【导出】

代码语言：javascript复制

#!/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

【导入】

代码语言：javascript复制

#!/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

shell 压缩

0 人点赞