1, data loading via remote Binlog in batches
Business new requirements, online database data pull to this time, but do not allow to open slave services, do not set up a direct external network Database account, also can not get through the database external network, so we can not test the environment through the normal slave master-slave replication mechanism to obtain real-time data.
Think of a way to put Binlog on a secure server that can be obtained, and then pull the binlog into the test environment to load the data in the test environment parsing binlog.
2, remote get Binlog log records
Write a remote can get Binlog log records of the script, one hours to pull the Binlog record
[[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<<!open120.132.27.9121user Downdata Rakudespuh3bak+ruybinaryCD bright/$DIRLCD/home/mysql/binlogs/bright/$dirpromptmget Mysql-bin*closebye!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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21st
- 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
- 21st
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
3, load the remote over the Binlog
After the last Step2, load the binlog immediately, get the data display
#sh load_data_frombinlog.sh $dirif [!-N"$ "];ThenEcho-E"Input date parameters such as: 2017060909\n"Echo-E"Input date parameters such as: 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 BinlogfileIn ' Cat$filename 'DoEcho""Echo"Begin to load Binlog" >>$loadlogfileEcho$binlogfile chmod +x$binlogfile dir_binlog= '/usr/bin/dirname$binlogfile ' realbinlogfile=${binlogfile%.zip} RM-RF$realbinlogfile Unzip$binlogfile-D$dir _binlogEcho"Unzip Binlog$binlogfile ">>$loadlogfileEcho"Binlog file is:$realbinlogfile ">>$loadlogfile RM-RF$realbinlogfile. Sql/usr/local/mysql3309/bin/m7888500ysqlbinlog$realbinlogfile >$realbinlogfile. sqlEchoThe Create SQL file$realbinlogfile. SQL from$realbinlogfile ">>$loadlogfileEcho"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 >> $loadlogfileEchoThe load$realbinlogfile Data End ">>$loadlogfileEcho"" >> $loadlogfile echo "" >> Span class= "hljs-variable" > $loadlogfile doneecho "----" $dir $loadlogfile echo "" >> Span class= "hljs-variable" > $loadlogfile echo "" >> Span class= "hljs-variable" > $loadlogfile echo "" >> Span class= "hljs-variable" > $loadlogfile [[Email protected]]#
MySQL does not turn on slave how to complete geo-replication