liunx下mysql資料遷移

1、需要一台每天備份mysql資料的備份伺服器，也就是資料災備伺服器。

備份資料的腳本如下，也可以手工執行腳本，進行備份。

#!/bin/bash
precheck () {
n=0
while [ $n -le 60 ]
do
${mysqlhome}/bin/mysql -P ${myport} -u ${myuser} -p${mypass} -h 10.3.51.203 << EOF > In_use.tmp
show OPEN TABLES where In_use > 0;
exit
EOF
reto=$?
if [ "`cat In_use.tmp`" != "" -a $reto -eq 0 ]
then
sleep 30
if [ $n -lt 60 ]
then
let n=$n+1
echo "retry $n"
cat In_use.tmp
continue
else
echo "give up after 60 times retry"
exit
fi
else
echo "clear, start backup now"
break
fi
let n=$n+1
sleep 30
done
}

dat=`date +%Y-%m-%d`
day=`date +%d`
month=`date +%Y%m`
datime=`date +'%Y-%m-%d %H:%M:%S'`
bkhome=/home/mysqlbackup
mysqlhome=/usr/local/mysql
mybin=${mysqlhome}/bin
bkdir=${bkhome}/mysql_${dat}_bk
myuser=XXXX
mypass=XXXXXX
myport=XXXX
date
rm -rf $bkdir
mkdir $bkdir
echo ${datime} > ${bkdir}/bktime.t

while read db
do
mkdir ${bkdir}/${db}
precheck
${mybin}/mysqldump -P ${myport} -u ${myuser} -p${mypass} -h 10.3.51.203 --opt --hex-blob --force --single-transaction --master-data=2 -R $db > $bkdir/${db}/${db}.sql
grep "CHANGE MASTER TO" ${bkdir}/${db}/${db}.sql | sed 's/-- CHANGE MASTER TO //g' | sed 's/, /;\n/' > ${bkdir}/${db}/position
/bin/gzip $bkdir/${db}/${db}.sql
date
echo "backup $db is done."
done << Eaf
dlmls
framework
h
logs
mls
mls_up_log
mlsxy
pjmls
shmls
test
txnh
ultrax
Eaf

if [ $day -eq 28 ]
then
cp -r ${bkhome}/mysql_${dat}_bk ${bkhome}/month_bk_${month}
fi

find ${bkhome} -type d -mtime +30 -name "mysql_*_bk" | xargs rm -rf

      

第2步、将需要遷移的資料庫檔案，拷貝到新裝的那台資料庫伺服器上。

scp  /home/mysqlbackup/mysql_2019-09-03_bk/mls.sql.gz  10.3.51.204:～        

scp  /home/mysqlbackup/mysql_2019-09-03_bk/ultrax.sql.gz  10.3.51.204:～        

第3步、将拷貝的檔案解壓

gzip -d mls.sql.gz
gzip -d ultrax.sql.gz      

mysql -uroot -p
use mls;
source /root/mls.sql
use ultrax;
source /root/ultrax.sql;      

mysql -uroot -p
show slave   status；