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.
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-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 >
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-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.
