一行命令

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

-C 壓縮

---

Shell 導(dǎo)入導(dǎo)出

【導(dǎo)出】

#!/bin/bash# 以下配置信息請根據(jù)現(xiàn)場實(shí)際情況修改 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") #要備份的數(shù)據(jù)庫名稱，多個(gè)用空格分開隔開 backup_location=/home/ap/mysqlbackup03 #備份數(shù)據(jù)存放位置，末尾請不要帶"/", 不存在則創(chuàng)建文件夾# 本行開始以下不需要修改# 連接到mysql數(shù)據(jù)庫，無法連接則備份退出 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 endflag=`echo $?` if [ $flag != "0" ]; thenecho "ERROR:Can't connect mysql server! please check config or server ! backup stop!"exit elseecho "MySQL connect ok! Please wait......"# 判斷有沒有定義備份的數(shù)據(jù)庫，如果定義則開始備份，否則退出備份if [ "$backup_db_arr" != "" ];thenfor dbname in ${backup_db_arr[@]}doecho "database【$dbname】backup start...may cost servral minutes depends on the size of database , please waiting for a while ..."`mkdir -p $backup_location`# 如果有創(chuàng)建數(shù)據(jù)庫的權(quán)限 就使用下面這一行帶有 -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`# 前提：數(shù)據(jù)庫用戶創(chuàng)建好。 建行分配的應(yīng)用賬號沒有創(chuàng)建DB的權(quán)限，所以導(dǎo)出的腳本中 不包含 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" ];thenecho "database $dbname success backup to $backup_location/$dbname.sql"echo " "elseecho "database $dbname backup fail!"fidoneelseecho "ERROR:No database to backup! Please check config ! backup stop"exitfiecho "All database backup success!"exit fi

【導(dǎo)入】

#!/bin/bash# 以下配置信息請根據(jù)現(xiàn)場實(shí)際情況修改 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數(shù)據(jù)庫，無法連接則備份退出 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 endflag=`echo $?` if [ $flag != "0" ]; thenecho "ERROR:Can't connect mysql server! please check config or server ! import stop!"exit elseecho "MySQL connect ok! Please wait......"# 判斷有沒有配置DB文件存放的位置if [ "$backup_location" != "" ];thenfor zffile in `ls $backup_location`;doecho "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" ];thenecho "$zffile import successfully"echo " "elseecho "$zffile import fail!!!"fidoneelseecho "ERROR:No file to import! Import stop"exitfiecho "All database import success!"exit fi

總結(jié)

以上是生活随笔為你收集整理的MySQL - mysqldump多种方式实现数据迁移的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。