MySQL Replication Data Recovery using 'mysqlbinlog' - Part II

MySQL Replication Data Recovery using 'mysqlbinlog' - Part II

The previous post (PART-I)
http://mysqlhk.blogspot.com/2018/10/mysql-replication-recovery-from-binlog.html

It describes the Replication Recovery from binlog by using those binlog files to be treated as Relay Log.  The Relay Log mechanism when the server is startup, the recovery is the SQL_THREAD applier to apply data to the database.    Check on the PART-I post for details.

Part II is about using the MySQL utility "mysqlbinlog" to dump the content from binlog files and apply the SQL to the Database.

Documentation
https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html
https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.html

The following sections describe the tutorial for Replication Data Recovery using 'mysqlbinlog'. 

The tutorial includes the followings topics (Note: Using GTID replication)
1. Initialize MySQL Instances (3316, 3326) installation & Configure Replication (Master and Slave)
2. Create Database and Table (test1.mytable1) with 8 rows
3. Stop the IO_THREAD on the Slave Server to simulate the situation where Data cannot transport to the RELAY Log on Slave Server.  Creating more data on Master and using "FLUSH LOGS;" to switching log files on Master server.
4. Checking the Replication Status on Slave Server for the Last retrieved position and file on Master.
5. Using 'mysqlbinlog' utility to extract SQL from the Master's binlog files starting from the position and file registered on Slave status.
6. Apply the SQLs to the Slave Server to recover the data and Check the Slave Status

7. Resume Replication "START SLAVE IO_THREAD FOR CHANNEL '<channel name>';"

Installation & Configurationo Assumptions
1. basedir = /usr/local/mysql   : The MySQL Binary Package
2. datadir = /home/mysql/data/server1 : The Server 1 Data Directory
3. datadir = /home/mysql/data/server2 : The Server 2 Data Directory
4. Using GTID and Channel for the replication setup
5. OS user for executing the commands - OS user : 'mysql'  (Do not use root) 
6. Configuration Files   (/home/mysql/data/my1.cnf & /home/mysql/data/my2.cnf)

/home/mysql/data/my1.cnf
[mysqld]
server-id=1
datadir=/home/mysql/data/server1
basedir=/usr/local/mysql

port=3316
socket=/home/mysql/data/server1/mysqld.sock

mysqlx-port=33160
mysqlx-socket=/home/mysql/data/server1/mysqlx.sock

log-error=/home/mysql/data/server1/mysqld.error
log-bin=mysqllog
relay-log=relay.bin

gtid-mode=on
enforce-gtid-consistency
master_info_repository=TABLE
relay_log_info_repository=TABLE


/home/mysql/data/my2.cnf
[mysqld]
server-id=2
datadir=/home/mysql/data/server2
basedir=/usr/local/mysql

port=3326
socket=/home/mysql/data/server2/mysqld.sock

mysqlx-port=33260
mysqlx-socket=/home/mysql/data/server2/mysqlx.sock

log-error=/home/mysql/data/server2/mysqld.error
log-bin=mysqllog
relay-log=relay.bin

gtid-mode=on
enforce-gtid-consistency
master_info_repository=TABLE
relay_log_info_repository=TABLE


STEP 1 : Initialize MySQL Instances and Configure Replication

Initialize Data Folder for 2 instances

# /usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/data/my1.cnf --initialize-insecure
# /usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/data/my2.cnf --initialize-insecure

Start up MySQL Instances
# /usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/data/my1.cnf &
# /usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/data/my2.cnf &

 Configure Replication User on Master and Slave
# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3316 -e "
drop user if exists repl@'localhost';
create user repl@'localhost' identified with mysql_native_password by 'repl';
grant replication slave on *.* to repl@'localhost';
"

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 -e "
drop user if exists repl@'localhost';
create user repl@'localhost' identified with mysql_native_password by 'repl';
grant replication slave on *.* to repl@'localhost';
"

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3316 << EOL3316
reset master;
reset slave;
EOL3316

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 << EOL3326
reset master;
reset slave;
EOL3326

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 << EOL1

change master to
master_host='127.0.0.1',
master_user='repl',
master_password='repl',
master_port=3316,
master_auto_position=1
for channel 'channel1';

start slave for channel 'channel1';

show slave status for channel 'channel1'\G

EOL1

 STEP 2 : Create Database and Table (test1.mytable1) with 8 rows of data

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3316

mysql> create database if not exists test1;
mysql> create table if not exists test1.mytable1 (f1 int not null auto_increment primary key, f2 varchar(20));

mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');



The data should now be replicated to Slave.  To show the data on Slave and Check the Replication Status, Execute :

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 -e "select count(*) from test1.mytable1;"

 /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 -e "show slave status for channel 'channel1'\G"

STEP 3 : Stop the IO_THREAD on Slave Server

This is to simulate the connection between master and slave being disconnected.  So there is no more NEW update to RELAY LOG on Slave Server.   To do this :

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 -e "stop slave IO_THREAD for channel 'channel1';"

Creating 16 more rows on Master Server, however they are not replicated to Slave Server.

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3316

mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');

At this point, Master Server has 32 rows.  However the Slave Server has ONLY 16 rows.

Executing "FLUSH LOGS" to flush the binlog and switch to new logs.  Inserting 16 more rows.
# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3316

mysql> FLUSH LOGS;
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');

mysql> FLUSH LOGS;
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');

After executing 2 times of "FLUSH LOGS;" commands, there are 3 binlog files created in the datadir "/home/mysql/data/server1" of the Master Server.   They are
mysqllog.000001
mysqllog.000002
mysqllog.000003
mysqllog.index

Number of ROWS on MASTER : 48
Number of ROWS on SLAVE : 16

***************************************************************************************************************
***  To recover the DATA from BINLOG of the Master Server to Slave Server   
***************************************************************************************************************

STEP 4 : Check the status on Slave  Server for what the last position of the BINLOG is and which file is the LAST file on Master Server being used.

Assuming we have the binlog files from MASTER Files - from datadir "/home/mysql/data/server1"
-- mysqllog.000001
-- mysqllog.000002
-- mysqllog.000003
-- mysqllog.index

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 -e "show slave status\G"
E.g. The output from command may look like this

 *************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3316
                Connect_Retry: 60
              Master_Log_File: mysqllog.000001
          Read_Master_Log_Pos: 5422
               Relay_Log_File: relay-channel1.000002
                Relay_Log_Pos: 5634
        Relay_Master_Log_File: mysqllog.000001
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
...
...
       Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-18
            Executed_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-18
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: channel1
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
 **************************************************
The Master Log File : mysqllog.000001
and Master Log Pos : 5422

This is the last position that the Slave IO_THREAD has read from Master Binlog file.

STEP 5 : Use 'mysqlbinlog' utility to extract SQL from MASTER Binlog files
Note : There are 3 binlog files from datadir "/home/mysql/data/server1"
-- mysqllog.000001
-- mysqllog.000002
-- mysqllog.000003
-- mysqllog.index

The command should include ALL binlog files starting from the one indicated by the Slave Status "Master Log Fie"

# cd /home/mysql/data/server1/
# /usr/local/mysql/bin/mysqlbinlog --start-position=5422 mysqllog.000001 mysqllog.000002 mysqllog.000003 > /tmp/my.sql

The output '/tmp/my.sql' contains ALL SQL statements with data after the transaction from position = 5422.   The FIRST mysqllog.000001 in the command is important which is the Master Log File from the Slave Status.

STEP 6 : Apply the SQL to the Slave Server and Check the Slave Status

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 < /tmp/my.sql

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 -e "show slave status\G"
E.g. The output from command may look like this

*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3316
                Connect_Retry: 60
              Master_Log_File: mysqllog.000001
          Read_Master_Log_Pos: 5422
               Relay_Log_File: relay-channel1.000002
                Relay_Log_Pos: 5634
        Relay_Master_Log_File: mysqllog.000001
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
...
...
           Retrieved_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-18
            Executed_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-54
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: channel1
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0

************************************************************
Comparing the output from the previous Slave Status, the Master Log File and Post do not change.  But the Retrieved GTID_Set and Executed_Gtid_set indicated the execution has already applied the extra rows on the Master.

BEFORE data recovery from BINLOG
           Retrieved_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-18
            Executed_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-18
AFTER data recovery from BINLOG 
           Retrieved_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-18
            Executed_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-54

The outcome is from recovery is to get more transactions applied to slave.




STEP 7 :  Resume Replication

# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 -e "START SLAVE IO_THREAD FOR CHANNEL 'channel1';"

Creating extra 16 rows on MASTER and check data on SLAVE
# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3316

mysql> FLUSH LOGS;
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');
mysql>insert into test1.mytable1 (f2) values ('aaaaaaaaaaaaaaa');


Check the Slave Status
# /usr/local/mysql/bin/mysql -uroot -h127.0.0.1 -P3326 -e "show slave status\G"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3316
                Connect_Retry: 60
              Master_Log_File: mysqllog.000003
          Read_Master_Log_Pos: 10689
               Relay_Log_File: relay-channel1.000003
                Relay_Log_Pos: 5700
        Relay_Master_Log_File: mysqllog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...
...
           Retrieved_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-18:55-72
            Executed_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-72
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: channel1
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
*********************************************************************
The Replication Channel has already been resumed.  The IO and SQL Threads are up and running :
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

The Master_log_file and Read_Master_log_Pos has already been started with new number and file name:
               Master_Log_File: mysqllog.000003
          Read_Master_Log_Pos: 10689

The Retrieved_Gtid_Set and Executed_Gtid_Set on Slave Server has already been updated with latest information :
           Retrieved_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-18:55-72
            Executed_Gtid_Set: 98424208-e3f5-11e8-bf4f-0800271b198a:1-72


The Master and Slave Servers are UP and RUNNING.

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
 

MySQL Document Store building Index Search

MySQL Document Store Building Index Search

This is a tutorial to go through setting Up MySQL 8.0 Document Store, Importing World_x Sample database and building GENERATED COLUMN with Index and lastly to show the QUERY PLAN using collection.find() that uses the INDEX built.

INSTALLATION Pre-requisite
1. MySQL 8.0.11+
2. MySQL Shell 8.0.11+
3. Download  Sample Database "world_x database" from https://dev.mysql.com/doc/index-other.html




MySQL Database Initialization  
Using --initialize-insecure for this tutorial which means it initializes a database with empty password for root@localhost user.
Assuming we have 'mysql', 'mysqld' setup properly.

mysqld --initialize-insecure --datadir=<data directory>


Create my.cnf  (example E:/temp/data1/my.cnf)













Startup MySQL Server
 

Connecting to MySQL Server
Using "mysql" client, connect to Server "3306"
# mysql -uroot -h127.0.0.1 -P3306




Import world_x database to MySQL Server
Assuming we have downloaded the 'world_x database' and unzipped.

Using "source  <the world_x SQL file from the download>"







 Showing the database and Tables
Command> show databases;
Command> show tables;


 Listing the content of the Document (countryinfo) using SQL

COMMAND> select count(*) from countryinfo;
COMMAND> select * from countryinfo limit 2;


 The Table Structure of the countryinfo is as follows



Create GENERATED COLUMN myname referring to the Document "$.Name"
COMMAND> alter table world_x.countryinfo add column myname varchar(100) generated always as (doc->>'$.Name');



Create an INDEX on the GENERATED COLUMN
COMMAND> use world_x;
COMMAND> create index countryinfo_myname on  countryinfo (myname);


Turn On GENERAL LOG for MySQL Server
COMMAND> set global general_log=true;




Using MySQL Shell to connect to the MySQL Server (3306) and Retrieve the Collection 'countryinfo'

1. CONNECTING to defaultt portx (X-Protocol) 33060 for 3306 MySQL Server

 

2. Switch CURRENT db to world_x
MySQL SHELL Command> \use world_x



3. List COLLECTIONS
The "db" variables is defined as CURRENT Schema.
MySQL SHELL Command> db.getCollections()
 

4. Retrieve Document from COLLECTION 'countryinfo' [Only showing 1 documents]
MySQL SHELL Command> db.countryinfo.find().limit(1)



4. Retrieve Document with CRITERIA " Name='Aruba' "
MySQL SHELL Command> db.countryinfo.find(" $.Name='Aruba' ")


5. Check the GENERAL Log on MySQL Server

GENERAL LOG : Under the DataDir folder, there is a <hostname>.log
The SQL statements were logged while MySQL Shell was executing collecction retrieval.



6. Explain the Query Plan due to the db.countryinfo.find( " $.Name='Acruba' ")

SQL COMMAND> explain SELECT doc FROM `world_x`.`countryinfo` WHERE (JSON_EXTRACT(doc,'$.Name') = 'Aruba')




The COLLECTION Retrieval "find" statement is able to take the index created from the GENERATED COLUMN.

VISUALLY, we can also see QUERY PLAN in MySQL WORKBENCH as follows