1. Loading data in batches through remote binlog

New business needs, online database data pulled to this time, but slave service is not allowed to open, database account of direct external network is not established, and the network of database external network can not be connected, so our test environment can not obtain real-time data through common slave master-slave replication mechanism.

A way to do this is to put the binlog on an accessible security server, then pull the binlog into the test environment, and parse the binlog load data in the test environment.

2. Getting binlog log log records remotely

Write a script that can obtain binlog log records remotely, and pull binlog records once an hour

[root@db12162bright]# more get_binlog.sh 
#!/bin/bash

loadlogfile=/home/mysql/binlogs/bright/get_binlogs.log
dir=$(date +%Y%m%d%H)
echo "---- "$dir" begin-----------------------------------------------------------------------------------------">> $loadlogfile 
mkdir -p /home/mysql/binlogs/bright/$dir
echo "begin to get bright binlogs ......"  >> $loadlogfile
ftp -n<<!
open 120.132.27.91 21
user downdata RakudespuH3bAk+ruy
binary
cd bright/$dir
lcd /home/mysql/binlogs/bright/$dir
prompt
mget mysql-bin*
close
bye
!

ls -l /home/mysql/binlogs/bright/$dir >> $loadlogfile

echo $(date +%Y-%m-%d-%H:%M:%S) >> $loadlogfile
echo "get binlogs end ..." >> $loadlogfile
echo "" >> $loadlogfile
echo "" >> $loadlogfile
echo "" >> $loadlogfile
echo "" >> $loadlogfile
echo "" >> $loadlogfile

3. Load the binlog from a remote location

After the last step 2, load the binlog immediately to get the data

#sh load_data_frombinlog.sh $dir 



if [ ! -n "$1" ]; then
        echo -e "Input date parameters such as 2017060909\n"
        echo -e "Input date parameters such as 2017060909\n"  >> $loadlogfile

#        exit
fi

loadlogfile=/home/mysql/binlogs/bright/bright_load_data.log
filename=`date '+%Y%m%d_%H%M%S'`.txt
#find /home/mysql/binlogs/bright -amin -1300 -name mysql-bin.*.zip |sort -n> $filename
find /home/mysql/binlogs/bright/$dir -name mysql-bin.*.zip |sort -n> $filename
echo `date '+%Y-%m-%d %H:%M:%S'`  >> $loadlogfile
echo "begin to load data from binlog ......" >> $loadlogfile
echo $filename >> $loadlogfile
for 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/mysqlbinlog $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 "" >> $loadlogfile
done
echo "---- "$dir" end----------------------------------------------------------------------------------------------" >> $loadlogfile
echo "" >> $loadlogfile
echo "" >> $loadlogfile
echo "" >> $loadlogfile
[root@db12162bright]#