資料庫線上遷移的設想

壹頁書發表於2015-11-11
資料庫
在分散式資料庫的環境下,應該經常有資料庫遷移的事情.

一個基於庫的資料庫線上遷移.
先做一個mysqldump到目標機,
因為這個過程很長,
完成之後,不斷追這段時間的binlog

然後,停止應用伺服器,應用最後一段binlog
最後手工檢查資料一致性.

這個只是相關的想法,指令碼未經過測試.不能用於生產環境.

  1. #/bin/bash
  2. source ~/.bashrc
  3. sourcehost='127.0.0.1'
  4. sourceport=3306
  5. sourceuser='root'
  6. sourcepwd='root'
  7. targethost='192.168.16.114'
  8. targetport=3306
  9. targetuser='xx'
  10. targetpwd='xx'
  11. binlogdir='/data'
  12. db='probe'

  14. #設定master_host
  15. mysql -u$targetuser -p$targetpwd -h$targethost -P$targetport -e "stop slave;reset slave all;change master to master_host='127.0.0.1';";

  17. #初次同步
  18. mysqldump -u$sourceuser -p$sourcepwd --master-data --single-transaction --databases $db | mysql -u$targetuser -p$targetpwd -h$targethost -P$targetport

  20. #獲取源資料庫的binlog資訊
  21. 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}')
  22. Master_Log_File=$(echo $sourcebinloginfo | awk -F " " '{print $1}')
  23. Read_Master_Log_Pos=$(echo $sourcebinloginfo | awk -F " " '{print $2}')

  25. echo "Master_Log_File:"$Master_Log_File
  26. echo "Read_Master_Log_Pos:"$Read_Master_Log_Pos

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

  1. function applyBinlog()
  2. {
  3.     info=$(getSourceBinlog);
  4.     echo "info:"$info
  5.     endfile=$(echo $info | awk -F " " '{print $1}')
  6.     endpos=$(echo $info | awk -F " " '{print $2}')
  8.     while [ "$endfile" != "$startfile" ]
  9.     do
  10.         echo "Apply Binlog:"$startfile","$Read_Master_Log_Pos
  11.         mysqlbinlog $binlogdir"/"$startfile --database $db --start-position=$Read_Master_Log_Pos | mysql -u$targetuser -p$targetpwd -h$targethost -P$targetport
  12.         startfile=$(echo $startfile | awk -F "." '{printf("%s.%06d",$1,$2+1)}')
  13.         startpos=0
  14.     done
  15.     mysqlbinlog $binlogdir"/"$startfile --database $db --stop-position=$endpos | mysql -u$targetuser -p$targetpwd -h$targethost -P$targetport
  16.     startfile=$endfile
  17.     startpos=$endpos
  18. }
  19. startfile=$Master_Log_File
  20. startpos=$Read_Master_Log_Pos
  22. applyBinlog;
  23. applyBinlog;
  24. applyBinlog;
  25. applyBinlog;
  27. echo "確認停止中介軟體(Y/N)"
  28. read sure
  29. while [ "Y" != "$sure" ]
  30. do
  31.     echo "確認停止中介軟體(Y/N)"
  32.     read sure;
  33. done
  34. applyBinlog;


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1829484/，如需轉載，請註明出處，否則將追究法律責任。

相關文章