MySQL 不開啟slave如何完成異地複寫

標籤：ret   無法   int   外網   tle   inf   word   ash   home   

1，分批次通過遠端binlog來進行資料載入

業務新需求，線上資料庫資料拉到本次，但是不允許開啟slave服務，不建立直接外網的資料庫帳號，也不能打通資料庫對外網的網路，所以我們測試環境無法通過普通的slave主從複製機制來獲得即時資料。

想了一個辦法，將binlog放在一個可擷取的安全伺服器上，然後把binlog拉到測試環境，在測試環境解析binlog載入資料。

2，遠程擷取binlog日誌記錄

寫一個遠程能擷取binlog日誌記錄的指令碼，一個小時拉一次binlog記錄

[[email protected]]# more get_binlog.sh #!/bin/bashloadlogfile=/home/mysql/binlogs/bright/get_binlogs.logdir=$(date +%Y%m%d%H)echo "---- "$dir" begin-----------------------------------------------------------------------------------------">> $loadlogfile mkdir -p /home/mysql/binlogs/bright/$direcho "begin to get bright binlogs ......"  >> $loadlogfileftp -n<<!open 120.132.27.91 21user downdata RakudespuH3bAk+ruybinarycd bright/$dirlcd /home/mysql/binlogs/bright/$dirpromptmget mysql-bin*closebye!ls -l /home/mysql/binlogs/bright/$dir >> $loadlogfileecho $(date +%Y-%m-%d-%H:%M:%S) >> $loadlogfileecho "get binlogs end ..." >> $loadlogfileecho "" >> $loadlogfileecho "" >> $loadlogfileecho "" >> $loadlogfileecho "" >> $loadlogfileecho "" >> $loadlogfile

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29

3，載入遠程過來的binlog

在上個step2之後，立即載入binlog，獲得資料顯示

#sh load_data_frombinlog.sh $dir if [ ! -n "$1" ]; then        echo -e "需輸入日期參數如：2017060909\n"        echo -e "需輸入日期參數如：2017060909\n"  >> $loadlogfile#        exitfiloadlogfile=/home/mysql/binlogs/bright/bright_load_data.logfilename=`date ‘+%Y%m%d_%H%M%S‘`.txt#find /home/mysql/binlogs/bright -amin -1300 -name mysql-bin.*.zip |sort -n> $filenamefind /home/mysql/binlogs/bright/$dir -name mysql-bin.*.zip |sort -n> $filenameecho `date ‘+%Y-%m-%d %H:%M:%S‘`  >> $loadlogfileecho "begin to load data from binlog ......" >> $loadlogfileecho $filename >> $loadlogfilefor binlogfile in `cat $filename`do    echo ""    echo "begin to load binlog"  >> $loadlogfile    echo $binlogfile    chmod +x  $binlogfile    dir_binlog=`/usr/bin/dirname $binlogfile`    realbinlogfile=${binlogfile%.zip}    rm -rf $realbinlogfile    unzip $binlogfile -d  $dir_binlog    echo "unzip binlog $binlogfile"  >> $loadlogfile    echo "binlog file is: $realbinlogfile" >> $loadlogfile    rm -rf $realbinlogfile.sql    /usr/local/mysql3309/bin/m7888500ysqlbinlog $realbinlogfile > $realbinlogfile.sql    echo "create sql file $realbinlogfile.sql from $realbinlogfile" >> $loadlogfile    echo "begin to load data" >> $loadlogfile    #mysqlruninfo=`/usr/local/mysql3309/bin/mysql -uroot --password="bright_yueworld" -S /usr/local/mysql3309/mysql.sock < $realbinlogfile.sql`    #$mysqlruninfo >> $loadlogfile    /usr/local/mysql3309/bin/mysql -uroot --password="bright_yueworld" -S /usr/local/mysql3309/mysql.sock < $realbinlogfile.sql    #echo $mysqlruninfo >> $loadlogfile    echo "load $realbinlogfile data end" >> $loadlogfile    echo "" >> $loadlogfile    echo "" >> $loadlogfiledoneecho "---- "$dir" end----------------------------------------------------------------------------------------------" >> $loadlogfileecho "" >> $loadlogfileecho "" >> $loadlogfileecho "" >> $loadlogfile[[email protected]]# 

MySQL 不開啟slave如何完成異地複寫