在分布式数据库的环境下,应该经常有数据库迁移的事情.

一个基于库的数据库在线迁移.
先做一个mysqldump到目标机,
因为这个过程很长,
完成之后,不断追这段时间的binlog

然后,停止应用服务器,应用最后一段binlog
最后手工检查数据一致性.

这个只是相关的想法,脚本未经过测试.不能用于生产环境.

#/bin/bash
source ~/.bashrc
sourcehost='127.0.0.1'
sourceport=3306
sourceuser='root'
sourcepwd='root'
targethost='192.168.16.114'
targetport=3306
targetuser='xx'
targetpwd='xx'
binlogdir='/data'
db='probe'
#设置master_host
mysql -u$targetuser -p$targetpwd -h$targethost -P$targetport -e "stop slave;reset slave all;change master to master_host='127.0.0.1';";
#初次同步
mysqldump -u$sourceuser -p$sourcepwd --master-data --single-transaction --databases $db | mysql -u$targetuser -p$targetpwd -h$targethost -P$targetport
#获取源数据库的binlog信息
sourcebinloginfo=$(mysql -u$targetuser -p$targetpwd -h$targethost -P$targetport -Be "show slave status\G" | grep 'Master_Log_File\|Read_Master_Log_Pos' | grep -v Relay | awk '{print $2}')
Master_Log_File=$(echo $sourcebinloginfo | awk -F " " '{print $1}')
Read_Master_Log_Pos=$(echo $sourcebinloginfo | awk -F " " '{print $2}')
echo "Master_Log_File:"$Master_Log_File
echo "Read_Master_Log_Pos:"$Read_Master_Log_Pos

function getSourceBinlog()
{
binloginfo=$(mysql -u$sourceuser -p$sourcepwd -e "flush tables with read lock;show master status\Gunlock tables" | grep "File\|Position" | awk '{print $2}');
echo $binloginfo;
}

function applyBinlog()
{
info=$(getSourceBinlog);
echo "info:"$info
endfile=$(echo $info | awk -F " " '{print $1}')
endpos=$(echo $info | awk -F " " '{print $2}')
while [ "$endfile" != "$startfile" ]
do
echo "Apply Binlog:"$startfile","$Read_Master_Log_Pos
mysqlbinlog $binlogdir"/"$startfile --database $db --start-position=$Read_Master_Log_Pos | mysql -u$targetuser -p$targetpwd -h$targethost -P$targetport
startfile=$(echo $startfile | awk -F "." '{printf("%s.%06d",$1,$2+1)}')
startpos=0
done
mysqlbinlog $binlogdir"/"$startfile --database $db --stop-position=$endpos | mysql -u$targetuser -p$targetpwd -h$targethost -P$targetport
startfile=$endfile
startpos=$endpos
}
startfile=$Master_Log_File
startpos=$Read_Master_Log_Pos
applyBinlog;
applyBinlog;
applyBinlog;
applyBinlog;
echo "确认停止中间件(Y/N)"
read sure
while [ "Y" != "$sure" ]
do
echo "确认停止中间件(Y/N)"
read sure;
done
applyBinlog;

ITPub博客

数据库在线迁移的设想

最新文章