How to Configure InnoDB ReplicaSet

mysqlshell AdminAPI includes support for InnoDB ReplicaSet, which enables you to create and administer a set of MySQL instances running asynchronous GTID-based replication. An InnoDB ReplicaSet consists of a single primary and multiple replicas.

InnoDB ReplicaSet Prerequisites

Only instances running MySQL version 8.0 and later are supported.
Only GTID-based replication is supported, binary log file position replication is not compatible with InnoDB ReplicaSet.
Only Row Based Replication (RBR) is supported, Statement Based Replication (SBR) is unsupported
Replication filters are not supported.

InnoDB ReplicaSet Limitations

Support only async GITD Based replication.
No automatic failover.
InnoDB ReplicaSet does not support a multi-primary mode.

For this demonstration, we have already configured 2 Servers running 2 mysql instances. Below are the details.

Hostname	IP	Role	Server_ID	Port	Installation Link
test-machine01	192.168.114.177	Master	1	3306	Click Here
test-machine01	192.168.114.177	Replica-1	3	3307	Click Here
test-machine02	192.168.114.176	Replica-2	2	3306	Click Here
test-machine02	192.168.114.176	Replica-3	4	3307

test-machine01 Configuration

mysql> prompt master >
PROMPT set to 'master >'
master >
master >select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

master >select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

mysql> prompt replica-1 >
PROMPT set to 'replica-1 >'
replica-1 >
replica-1 >select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

replica-1 >select @@port;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.00 sec)

[root@test-machine01 ~]# cat /etc/my.cnf
[mysqld@inst1]
#General
user = mysql
port = 3306
server_id = 1
socket=/var/lib/mysql/mysql.sock
pid_file=/var/run/mysqld/mysqld.pid

# Data Storage
datadir=/var/lib/mysql
innodb_directories="/u01/mysql/"

#Logging
log_bin = /var/lib/mysql/binlog
log_error = /var/lib/mysql/mysqld.log
expire_logs_days = 7
relay_log = /var/lib/mysql/relay_bin01
relay_log_index = /var/lib/mysql/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE

# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates= 1


[mysqld@inst2]
#General
user = mysql
port=3307
server_id = 3
socket=/u01/mysql-2/mysql.sock
pid_file=/u01/mysql-2/mysqld.pid

# Data Storage
basedir=/u01/mysql-2
datadir=/u01/mysql-2/data
innodb_directories="/u01/mysql/"
plugin_dir=/usr/lib64/mysql/plugin

#Logging
log_bin = /u01/mysql-2/data/binlog
log_error = /u01/mysql-2/data/mysqld.log
expire_logs_days = 7
relay_log = /u01/mysql-2/data/relay_bin01
relay_log_index = /u01/mysql-2/data/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE

# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates= 1
[root@test-machine01 ~]#

test-machine02 Configuration

mysql> prompt replica-2>
PROMPT set to 'replica-2>'
replica-2>
replica-2>select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

replica-2>select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)


mysql> prompt replica-3>
PROMPT set to 'replica-3>'
replica-3>select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

replica-3>select @@port;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.00 sec)

replica-3>

[root@test-machine02 ~]# cat /etc/my.cnf
[mysqld@inst1]
#General
user = mysql
port = 3306
server_id = 2
bind-address = 0.0.0.0
socket=/u01/mysql-8/mysql.sock
pid_file=/u01/mysql-8/mysqld.pid

# Data Storage
basedir=/u01/mysql-8
datadir=/u01/mysql-8/data
innodb_directories="/u01/mysql-8;/u01/mysql"

#Logging
log_bin = /u01/mysql-8/data/binlog
log_error = /u01/mysql-8/data/mysqld.log
expire_logs_days = 7
relay_log = /u01/mysql-8/data/relay_bin01
relay_log_index = /u01/mysql-8/data/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE

# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates=1

[mysqld@inst2]
#General
user = mysql
port = 3307
server_id = 4
socket=/u01/mysql-2/mysql.sock
pid_file=/u01/mysql-2/mysqld.pid
require_secure_transport = ON

# Data Storage
basedir=/u01/mysql-2
datadir=/u01/mysql-2/data
plugin_dir=/u01/mysql-8/lib/plugin

#Logging
log_bin = /u01/mysql-2/data/binlog
log_error = /u01/mysql-2/data/mysqld.log
expire_logs_days = 7
relay_log = /u01/mysql-2/data/relay_bin01
relay_log_index = /u01/mysql-2/data/relay_bin.index
relay_log_recovery = on
master_info_repository = TABLE
relay_log_info_repository = TABLE

# GTID Based Replication Parameter
gtid-mode=on
enforce-gtid-consistency=1
log_slave_updates= 1

[root@test-machine02 ~]#

Step 1. Install mysqlshell: Use the system yum command to install mysqlshell utility in both Servers.

[root@test-machine01]# yum install mysql-shell
Loaded plugins: langpacks, ulninfo
Dependencies Resolved
 
==============================================================================================================================================================================================
 Package                                     Arch                                   Version                                       Repository                                             Size
==============================================================================================================================================================================================
Updating:
 mysql-shell                                 x86_64                                 8.0.23-1.el7                                  mysql-tools-community                                  32 M
 
Transaction Summary
==============================================================================================================================================================================================
Upgrade  1 Package
 
Total download size: 32 M
Is this ok [y/d/N]: y
Downloading packages:

Step 2. Configuring InnoDB ReplicaSet Instances: Use function dba.configureReplicaSetInstance to configure each instance you want to use in your replica set. We will create clusteradmin user: rsadmin in all 4 mysql instances. Provide new password on the prompt for user rsadmin.

test-machine01

[root@test-machine01 ~]# mysqlsh
MySQL Shell 8.0.23

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
MySQL JS > dba.configureReplicaSetInstance('root@localhost:3306', {clusterAdmin: "'rsadmin'@'test-machine01'"});
Configuring local MySQL instance listening at port 3306 for use in an InnoDB ReplicaSet...

This instance reports its own address as test-machine01:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Password for new account: *********
Confirm password: *********

The instance 'test-machine01:3306' is valid to be used in an InnoDB ReplicaSet.
Cluster admin user 'rsadmin'@'test-machine01%' created.
The instance 'test-machine01:3306' is already ready to be used in an InnoDB ReplicaSet.
MySQL JS >

MySQL JS > dba.configureReplicaSetInstance('root@localhost:3307', {clusterAdmin: "'rsadmin'@'test-machine01'"});
Configuring local MySQL instance listening at port 3307 for use in an InnoDB ReplicaSet...

This instance reports its own address as test-machine01:3307
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Password for new account: *********
Confirm password: *********

The instance 'test-machine01:3307' is valid to be used in an InnoDB ReplicaSet.
Cluster admin user 'rsadmin'@'test-machine01%' created.
The instance 'test-machine01:3307' is already ready to be used in an InnoDB ReplicaSet.
MySQL JS >

test-machine02

[root@test-machine02 ~]# mysqlsh
MySQL Shell 8.0.23

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

This instance reports its own address as test-machine02:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Password for new account: *********
Confirm password: *********

The instance 'test-machine02:3306' is valid to be used in an InnoDB ReplicaSet.
Cluster admin user 'rsadmin'@'test-machine02%' created.
The instance 'test-machine02:3306' is already ready to be used in an InnoDB ReplicaSet.
MySQL JS >

This instance reports its own address as test-machine02:3307
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Password for new account: *********
Confirm password: *********

The instance 'test-machine02:3307' is valid to be used in an InnoDB ReplicaSet.
Cluster admin user 'rsadmin'@'test-machine02%' created.
The instance 'test-machine02:3307' is already ready to be used in an InnoDB ReplicaSet.
MySQL JS >

Step 3. Creating an InnoDB ReplicaSet: Quit the mysqlshell and connect with clusteradmin user: rsadmin, We will use function dba.createReplicaSet to create metadata and to create test-machine01 mysql@3306 as Primary Instance. We are using here rs variable to hold the object ReplicaSet result.

MySQL  JS >
 MySQL  JS > \quit
Bye!

[root@test-machine01 ~]#
[root@test-machine01 ~]# mysqlsh
MySQL Shell 8.0.23

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > \connect rsadmin@test-machine01:3306
Creating a session to 'rsadmin@test-machine01:3306'
Please provide the password for 'rsadmin@test-machine01:3306': *********
Save password for 'rsadmin@test-machine01:3306'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 38
Server version: 8.0.22 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  test-machine01:3306 ssl  JS >



 MySQL  test-machine01:3306 ssl  JS  > var rs = dba.createReplicaSet("TestReplicaSet")
A new replicaset with instance 'test-machine01:3306' will be created.

* Checking MySQL instance at test-machine01:3306

This instance reports its own address as test-machine01:3306
test-machine01:3306: Instance configuration is suitable.

* Updating metadata...

ReplicaSet object successfully created for test-machine01:3306.
Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.

 MySQL  test-machine01:3306 ssl  JS  >

 MySQL  test-machine01:3306 ssl  JS  >  rs.status();
{
    "replicaSet": {
        "name": "TestReplicaSet",
        "primary": "test-machine01:3306",
        "status": "AVAILABLE",
        "statusText": "All instances available.",
        "topology": {
            "test-machine01:3306": {
                "address": "test-machine01:3306",
                "instanceRole": "PRIMARY",
                "mode": "R/W",
                "status": "ONLINE"
            }
        },
        "type": "ASYNC"
    }
}

Step 4. Adding Instances to a ReplicaSet: Use function addInstance to add an instance as a read-only secondary replica of the current primary of the ReplicaSet. While adding mysql instance mysqlshell will prompt for recovery method, we can use Clone [C] option to sync master and replica. Once addition operation is done use rs.status() function to check replicaset status.

,5,7 MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS > rs.addInstance('rsadmin@test-machine01:3307')
ReferenceError: rs is not defined
 MySQL  test-machine01:3306 ssl  JS > var rs =dba.getReplicaSet()
You are connected to a member of replicaset 'TestReplicaSet'.
 MySQL  test-machine01:3306 ssl  JS > rs.addInstance('rsadmin@test-machine01:3307')
Adding instance to the replicaset...

* Performing validation checks

This instance reports its own address as test-machine01:3307
test-machine01:3307: Instance configuration is suitable.

* Checking async replication topology...

* Checking transaction state of the instance...

NOTE: The target instance 'test-machine01:3307' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether replication can completely recover its state.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'test-machine01:3307' with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

WARNING: It should be safe to rely on replication to incrementally recover the state of the new instance if you are sure all updates ever executed in the replicaset were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the replicaset or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.


Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
* Updating topology
Waiting for clone process of the new member to complete. Press ^C to abort the operation.
* Waiting for clone to finish...
NOTE: test-machine01:3307 is being cloned from test-machine01:3306
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: test-machine01:3307 is shutting down...

* Waiting for server restart... ready
* test-machine01:3307 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 250.61 MB transferred in 6 sec (41.77 MB/s)

** Configuring test-machine01:3307 to replicate from test-machine01:3306
** Waiting for new instance to synchronize with PRIMARY...

The instance 'test-machine01:3307' was added to the replicaset and is replicating from test-machine01:3306.

 MySQL  test-machine01:3306 ssl  JS >



 MySQL  test-machine01:3306 ssl  JS > rs.status()
{
    "replicaSet": {
        "name": "TestReplicaSet",
        "primary": "test-machine01:3306",
        "status": "AVAILABLE",
        "statusText": "All instances available.",
        "topology": {
            "test-machine01:3306": {
                "address": "test-machine01:3306",
                "instanceRole": "PRIMARY",
                "mode": "R/W",
                "status": "ONLINE"
            },
            "test-machine01:3307": {
                "address": "test-machine01:3307",
                "instanceRole": "SECONDARY",
                "mode": "R/O",
                "replication": {
                    "applierStatus": "APPLIED_ALL",
                    "applierThreadState": "Slave has read all relay log; waiting for more updates",
                    "receiverStatus": "ON",
                    "receiverThreadState": "Waiting for master to send event",
                    "replicationLag": null
                },
                "status": "ONLINE"
            }
        },
        "type": "ASYNC"
    }
}
 MySQL  test-machine01:3306 ssl  JS >

Step 5. Adding one more instance to ReplicaSet: Now we will add test-machine02 mysql instance. Follow same procedure like above. In rs.status() function you will notice “test-machine01:3306” as Primary “mode”: “R/W” “test-machine01:3307”, “instanceRole”: “SECONDARY”, “mode”: “R/O” “address”: “test-machine01:3307”, “instanceRole”: “SECONDARY”, “mode”: “R/O”.

MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS > rs.addInstance('rsadmin@test-machine02:3306')
Adding instance to the replicaset...

* Performing validation checks

This instance reports its own address as test-machine02:3306
test-machine02:3306: Instance configuration is suitable.

* Checking async replication topology...

* Checking transaction state of the instance...

NOTE: The target instance 'test-machine02:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether replication can completely recover its state.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'test-machine02:3306' with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

WARNING: It should be safe to rely on replication to incrementally recover the state of the new instance if you are sure all updates ever executed in the replicaset were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the replicaset or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.


Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
* Updating topology
* Waiting for the donor to synchronize with PRIMARY...

Waiting for clone process of the new member to complete. Press ^C to abort the operation.
* Waiting for clone to finish...
NOTE: test-machine02:3306 is being cloned from test-machine01:3307
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: test-machine02:3306 is shutting down...

* Waiting for server restart... ready
* test-machine02:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 250.59 MB transferred in 2 sec (125.30 MB/s)

** Configuring test-machine02:3306 to replicate from test-machine01:3306
** Waiting for new instance to synchronize with PRIMARY...

The instance 'test-machine02:3306' was added to the replicaset and is replicating from test-machine01:3306.

 MySQL  test-machine01:3306 ssl  JS >


 MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS > rs.status()
{
    "replicaSet": {
        "name": "TestReplicaSet",
        "primary": "test-machine01:3306",
        "status": "AVAILABLE",
        "statusText": "All instances available.",
        "topology": {
            "test-machine01:3306": {
                "address": "test-machine01:3306",
                "instanceRole": "PRIMARY",
                "mode": "R/W",
                "status": "ONLINE"
            },
            "test-machine01:3307": {
                "address": "test-machine01:3307",
                "instanceRole": "SECONDARY",
                "mode": "R/O",
                "replication": {
                    "applierStatus": "APPLIED_ALL",
                    "applierThreadState": "Slave has read all relay log; waiting for more updates",
                    "receiverStatus": "ON",
                    "receiverThreadState": "Waiting for master to send event",
                    "replicationLag": null
                },
                "status": "ONLINE"
            },
            "test-machine02:3306": {
                "address": "test-machine02:3306",
                "instanceRole": "SECONDARY",
                "mode": "R/O",
                "replication": {
                    "applierStatus": "APPLIED_ALL",
                    "applierThreadState": "Slave has read all relay log; waiting for more updates",
                    "receiverStatus": "ON",
                    "receiverThreadState": "Waiting for master to send event",
                    "replicationLag": null
                },
                "status": "ONLINE"
            }
        },
        "type": "ASYNC"
    }
}
 MySQL  test-machine01:3306 ssl  JS >

Step 6. Check the status of Replication: You can also check the status from mysqlshell with SQL mode.

MySQL  test-machine01:3306 ssl  JS >
 MySQL  test-machine01:3306 ssl  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  test-machine01:3306 ssl  SQL >

 MySQL  test-machine01:3306 ssl  SQL > \connect root@localhost:3307
Creating a session to 'root@localhost:3307'
Fetching schema names for autocompletion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 31
Server version: 8.0.22 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:3307 ssl  SQL > show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: test-machine01
                  Master_User: mysql_innodb_rs_3
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 54063
               Relay_Log_File: relay_bin01.000002
                Relay_Log_Pos: 28074
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 54063
              Relay_Log_Space: 28279
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 13ecba9c-444a-11eb-a397-000c29f9d9e6
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 13ecba9c-444a-11eb-a397-000c29f9d9e6:76-117
            Executed_Gtid_Set: 13ecba9c-444a-11eb-a397-000c29f9d9e6:1-117
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 1
            Network_Namespace:
1 row in set (0.0027 sec)
ERROR: 1065 (42000): Query was empty
 MySQL  localhost:3307 ssl  SQL >

Part II : How to perform Switchover/Failover in MySql InnoDB ReplicaSet Click here to read more.

Part III : How To Bootstrap MySQL Router For InnoDB ReplicaSet Click here to read more.

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.