MySql灾难大营救，数据迁移

  刚接任mysql的linux运维的伙伴最怕的是上任留下的坑，即不主动告诉你是有没做数据备份，也不是告诉你系统是否有备份，等mysql服务崩了，那就要苦逼了

        针对以上情况，就只有走数据迁移过去，本文用shell脚本自动完成,人机交互，少去人工敲命令的失误

首先搭建一台新的mysql服务器，网上教程很多，也很容易，这就不赘述了，

务必保证Mysql服务器版本的一致！！！

把mysqltransfer目录拷贝到旧服务器上

在旧的出问题的mysql服务器执行mysqltransfer目录下tranfrom.sh

输入数字即可

将得到一个当前日期的文件夹，里面含以下内容

在旧服务上把mysqltransfer目录拷贝到新的服务器上 执行mysqltransfer目录下tranto.sh

同样选择序号，选择刚刚备份出的数据库目录 如2

输入mysql 用户名，和密码

然后提示

数据库迁移成功啦！！

mysqltransfer文件夹下tranfrom.sh的内容

clear
mysqlc=(pid-file datadir log-error)
regex=`echo ${mysqlc[@]}`
find / -name "my.cnf" | xargs -i cat {} |grep -E "${regex// /|}"|awk -F "=" '{gsub("-","",$0);print $0}' >tmp.txt
while read line;do
 eval $line	
done <tmp.txt
#echo ${pidfile}
echo your mysql datapath:${datadir}
ibdata1=${datadir}/ibdata1
if [ -e "${ibdata1}" -a -e "${datadir}" ]; then #-o
	du -Sh ${ibdata1}
else
	echo ibdata1 loss!
	exit
fi

#echo ${logerror}

find "${datadir}/" -type d|awk '{i++}{print "a["i-1"]="$0}'>tmp.txt
while read line;do
 eval $line	
done <tmp.txt
echo "select your transfer data"
for (( i = 1; i < ${#a[@]}; i++ )); do
	echo "[${i}]${a[i]##*/}  Path:${a[i]}"
done

read -p "" sb 

des=`date +%F`
if [[ ! -d $des ]];then
	mkdir -p $des
fi

systemctl stop mysqld
sleep 2
	file="${des}/${a[$sb]##*/}.gz"
	echo "${file} ${a[$sb]}"
	tar cvzf "${file}" "${a[$sb]}"
	cp ${ibdata1} ${des}
systemctl start mysqld
sleep 2
systemctl list-units --type=service|grep  -i -E "mysqld"|awk '{if($4=="running"&&$3=="active"){print $1",服务启动成功"}else{print $1",服务启动失败,请检查"}}'

mysqltransfer文件夹下tranto.sh的内容

clear
find ./ -name "*.gz" -type f|awk '{i++}{print "a["i-1"]="$0 }' >tmp.txt
while read line;do
	eval $line	
done <tmp.txt

echo "select your transfer data [n]"
for (( i = 0; i < ${#a[@]}; i++ )); do
	name[$i]=`echo ${a[i]##*/}|sed -r "s/.gz//g"`
	echo "[${i}]${name[$i]}  Path:${a[i]}"
done
read -p "" sb 
ibdata1=${a[$sb]%/*}/ibdata1
if [ -e "${ibdata1}" ]; then 
	du -Sh ${ibdata1}
else
	echo ibdata1 loss!
	exit
fi


n=`tar -tzf ${a[$sb]}|head -n 1| awk '{split($0,arr,"/");print length(arr)-2}'`
tar -C ./ -xvf ${a[$sb]} --strip-components $n > /dev/null

mysqlc=(pid-file datadir log-error)
regex=`echo ${mysqlc[@]}`
find / -name "my.cnf" | xargs -i cat {} |grep -E "${regex// /|}"|awk -F "=" '{gsub("-","",$0);print $0}' >tmp.txt
while read line;do
 eval $line	
done <tmp.txt
#echo ${pidfile}
echo your mysql datapath:${datadir}
echo your mysql logfile:
find ${datadir} -name "ib_logfile*"|xargs -i du -Sh {}

read -p "input your mysql username:" user
read -p "input your mysql password:" password
echo $user,$password
mysqladmin -u${user} -p${password} create ${name[$sb]}
systemctl stop mysqld
sleep 2
datapath=${datadir}/${name[$sb]}
rm -rf "$datapath"
cp -rf "./${name[$sb]}" "$datapath"
rm -f "${datadir}/ibdata1"
cp -f ${ibdata1} ${datadir}/
chown mysql.mysql "$datapath"
chown mysql.mysql "${datadir}/ibdata1"
chmod -R 777 "$datapath"
chmod  777 "${datadir}/ibdata1"
find ${datadir} -name "ib_logfile*"|xargs -i rm -f {}
systemctl start mysqld
sleep 2
systemctl list-units --type=service|grep  -i -E "mysqld"|awk '{if($4=="running"&&$3=="active"){print $1",服务启动成功"}else{print $1",服务启动失败,请检查"}}'
#tb=$(mysqlshow -u${user} -p${password} ${name[$sb]} --count|awk 'NR==5{print $2 }')
mysqlshow -u${user} -p${password} ${name[$sb]} --count

资源下载:https://download.csdn.net/download/u014332402/88389415