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 

MySQL InnoDB Cluster Setup and Server Failover / Restart

MySQL InnoDB Cluster Setup and Configuration

Reference :
https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-production-deployment.html

Pr-requisite  Assumption :
    MySQL Server 8.0.12+ installation : /usr/local/mysql
    MySQL Shell installation : /usr/local/shell
    MySQL Router Installation : /usr/local/router
    Hostname and IP must be resolvable. 
        Make sure the /etc/hosts valid to have the IP and Hostname entries correctly with ALL MySQL Server machines.

The Video [ https://youtu.be/_jR_bJGTf-o ] provides the full steps showing the Demonstration of
1. Setting up of 3 x MySQL Servers on 1 VM
    a.  Configuration my1.cnf, my2.cnf and my3.cnf   [Referring to the Appendix in this post]

    b.  MySQL  Server (mysqld) initialization
         # mysqld --defaults-file=<config file> --initialize-insecure

    c.  Start up MySQL Server
        # mysqld_safe --defaults-file=<config file> &
        
    Note : It is possible to setup MySQL Server on different machines.  Steps should be the same.
 
2. Using MySQL Shell (mysqlsh) to configure the MySQL Instance (LOCALLY)
    a. mysqlsh > dba.configureInstance( "root@localhost:<port>", {clusterAdmin:"gradmin", clusterAdminPassword:"grpass"})
 
    The configuration should be done on each of the server (LOCALLY).  Because, by default the MySQL installation creates 'root@localhost'  which can only access the Database LOCALLY.

3.  Execute SQL "reset master;reset slave;"  on all the servers to make sure ALL data to be in-sync.   (If data import to all nodes, it should be good to reset the state)
    # mysql -uroot -h127.0.0.1 -P<port> -e "reset master;reset slave;"

4. Creating the Cluster
     a.  Connect to any one of the MySQL Server with "mysqlsh"
     # mysqlsh --uri gradmin:grpass@<hostname1>:<port1>
     mysqlsh> var cl = dba.createCluster('mycluster')
     b. Add the 2 more instances to the Cluster
     mysqlsh> cl.addInstance('gradmin:grpass@<hostname2>:<port2>')
     mysqlsh> cl.addInstance('gradmin:grpass@<hostname3>:<port3>')
     c. Show the status of the Cluster
      mysqlsh> cl.status()

The MySQL InnoDB Cluster should have been configured and running.

5. MySQL Router Setup and Startup
   a. Bootstrapping the configuration (Assuming /home/mysql/config/ folder exists)
       # mysqlrouter --bootstrap=gradmin:grpass@<hostname1>:<port1> --directory /home/mysql/config/mysqlrouter01 --force
    b. Start up MySQL Router
       # cd /home/mysql/config/mysqlrouter01;./start.sh
    The 'start.sh" is created by the bootstrapping process.   Once it is started, the default PORT (6446) is for RW routing.  PORT(6447) is for RO routing.

6. Testing MySQL Routing for RW
    # while [ 1 ]
    # do
    #    sleep 1
    #    mysql -ugradmin -pgrpass -h127.0.0.1 -P6446 -e "select @@hostname, @@port;"
    # done

7. Kill (or shutdown) the RW server
    # mysql -uroot -h127.0.0.1 -P<port> -e "shutdown;"      Note : root can only access locally
    Check the Routing acess from Step 6, the Hostname/Port should have switched/failed over.

Enjoy and check the Video [ https://youtu.be/_jR_bJGTf-o ]


Appendix [ my1.cnf ]
[mysqld]
datadir=/home/mysql/data/3310
basedir=/usr/local/mysql
log-error=/home/mysql/data/3310/my.error
port=3310
socket=/home/mysql/data/3310/my.sock
mysqlx-port=33100
mysqlx-socket=/home/mysql/data/3310/myx.sock
log-bin=logbin
relay-log=logrelay
binlog-format=row
binlog-checksum=NONE
server-id=101

# enable gtid
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=true

# Table based repositories
master-info-repository=TABLE
relay-log-info-repository=TABLE

# Extraction Algorithm
transaction-write-set-extraction=XXHASH64

Appendix [ my2.cnf ]
[mysqld]
datadir=/home/mysql/data/3320
basedir=/usr/local/mysql
log-error=/home/mysql/data/3320/my.error
port=3320
socket=/home/mysql/data/3320/my.sock
mysqlx-port=33200
mysqlx-socket=/home/mysql/data/3320/myx.sock
log-bin=logbin
relay-log=logrelay
binlog-format=row
binlog-checksum=NONE
server-id=102

# enable gtid
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=true

# Table based repositories
master-info-repository=TABLE
relay-log-info-repository=TABLE

# Extraction Algorithm
transaction-write-set-extraction=XXHASH64