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
 



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

# 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