MySQL Replication Recovery from BINLOG on MASTER

The recorded video on YouTube
https://youtu.be/STk0GThsRjc


Reference
https://lefred.be/content/howto-make-mysql-point-in-time-recovery-faster/

Background
MySQL Replication using binlog, transported to relay log and applied to Database on Slave is the basic mechanism.

The binary log and relay log in Replication to a certain extend have the structure.

There are few scenarios that we may consider using the Binary Log to recover the data.
1. To speed up replication on start up (https://lefred.be/content/howto-make-mysql-point-in-time-recovery-faster/)
2. With Async Replication in DR setup, if the MASTER's binlog file is available on DR site (using Storage Replication or any other way), recovering the data from binlog provides the last second data to be in-sync with the MASTER.   By using the MASTER's binlog, the Slave is able to recover all data from MASTER without data lost.

Assumption
1. MASTER and SLAVE in GTID replication setup
2. Replication is done with CHANNEL - channel name is 'channel1'
    mysql> CHANGE MASTER to ..... for channel 'channel1';

3. The recovery channel is named as 'channel2'
4.  The binary log prefix is mysqllog.  (log-bin=mysqllog)
     With this binlog prefix, the binlog filenames in datadir will be given with mysqllog.<number> and mysqllog.index
5.  The relay log prefix is relay (relay-log=relay)
     With this relay log prefix, the relay log filenames in datadir will be given with relay-<channelname>.<number> and relay-<channelname>.index

Steps Description
1. Stop the slave on channel1 (stop slave for channel 'channel1')
2. Add more records on MASTER.  Those records will not be replicated to SLAVE but only on MASTER (also they are written to Binary Log)

Recovery Steps on Slave
3. Copy the binary log files from MASTER (mysqllog.*) to somewhere (e.g. /tmp/binlogs)
4. Rename the file to be named as RELAY log channel2.  and create the index file
    mv mysqllog.*    to relay-channel2.* 

for i in $(ls /tmp/binlogs/*.0*) 

do  
  ext=$(echo $i | cut -d'.' -f2); 
  cp $i relay-channel2.$ext; 
done

cd /tmp/binlogs;ls -1 *.0* > relay-channel2.index 

 
5. Copy the 'channel2' relay log files to Slave's datadir

6. Create the channel2 channel on Slave and Start Slave for channel 'channel2'.
On Slave Server ---

mysql> CHANGE MASTER TO RELAY_LOG_FILE='relay-channel2.000001', 
       RELAY_LOG_POS=1, MASTER_HOST='dummy';

mysql > start slave for channel 'channel2'; 

7. After recovery, Stop the channel 'channel2' and clean up.

8. Resume the Channel1, the data is in normal operation