Deploying InnoDB ReplicaSet In Production

InnoDB ReplicaSet, that enables you to administer a set of MySQL instances running asynchronous GTID-based replication.
MySQL Replica Set is a set of three components:

•  MySQL Shell
•  MySQL Router
•  Set of MySQL Servers(min no of server – 02

It works only with Single Primary and multiple secondary servers, which is in ASYNC mode.

MySQL Router

MySQL Router is a building block for high availability (HA) solutions. It simplifies application development by intelligently routing connections to MySQL servers for increased performance and reliability

MySQL Shell

MySQL Shell is an advanced client and code editor for MySQL Server. In addition to the provided SQL functionality, similar to MySQL, MySQL Shell provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL

An InnoDB ReplicaSet has several limitations, Therefor I will highly recommend  InnoDB Cluster as the InnoDB cluster is based on Group replication.

Limitations of InnoDB ReplicaSet are:

• No automatic failover
• No protection from partial data loss due to an unexpected halt or unavailability.
• No protection against inconsistencies after an unexpected exit or unavailability.

Servers I will be using in this tutorial:

Mysql1:- 192.168.66.16

Mysql2:- 192.168.66.17

MysqlRouter: 192.168.66.18

MysqlRouter is basically will be the application where I have configured the Router and the application will connect to the router and the router will be connecting the primary and secondary depends upon the connection you create.

Prerequisites:

On Mysql1 and mysql2 Server:
MySQL Server is installed
MySQL Shell is installed

on MysqlRouter Server:
Mysql Router is installed
Mysql Shell is installed

Here you can see how to install MySQL from tar:

Here you can see How to configure Mysql:

Install MysqlShell and Router:

wget https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm
rpm -ivh mysql80-community-release-el7-3.noarch.rpm 
yum install mysql-shell -y

Install MysqlRouter only on the application server from yum repo.

yum install mysql-shell -y

In InnoDB Cluster and InnoDB ReplicaSet you must use the admin account to create and add instances to It. We will be using dba.configureReplicaSetInstance() function for this purpose on both Mysql Servers.

############## ON Servers: mysql1:####################

[root@mysql1 ~]# cat /etc/hosts

192.168.66.16 mysql1 mysql1
192.168.66.17 mysql2 mysql2

[root@mysql1 ~]# mysqlsh --uri root@localhost
Please provide the password for 'root@localhost': ***************
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.0.21

Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.
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.
Creating a session to 'root@localhost'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 9 (X protocol)
Server version: 8.0.21 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:33060+ ssl JS >

####################Servers: mysql1:####################

MySQL localhost:33060+ ssl JS > dba.configureReplicaSetInstance()
Configuring local MySQL instance listening at port 3306 for use in an InnoDB ReplicaSet...

This instance reports its own address as mysql1: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.

ERROR: User 'root' can only connect from 'localhost'. New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster.

1) Create a remotely usable account for 'root' with the same grants and password
2) Create a new admin account for InnoDB ReplicaSet with minimal required grants
3) Ignore and continue
4) Cancel

Please select an option [1]: 2
Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: InnodbReplicaSet
Password for new account: ***************
Confirm password: ***************

NOTE: Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable | Current Value | Required Value | Note |
+--------------------------+---------------+----------------+--------------------------------------------------+
| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |
| gtid_mode | OFF | ON | Update read-only variable and restart the server |
| server_id | 1 | <unique ID> | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+

Some variables need to be changed, but cannot be done dynamically on the server.
Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: y
Cluster admin user 'InnodbReplicaSet'@'%' created.
Configuring instance...
The instance 'mysql1:3306' was configured to be used in an InnoDB ReplicaSet.
Restarting MySQL...
NOTE: MySQL server at mysql1:3306 was restarted.

 

Now We will Create the Replicaset admin account on mysql2 server as well

####################Server: Mysql2:####################

[root@mysql2 ~]# cat /etc/hosts

192.168.66.16 mysql1 mysql1
192.168.66.17 mysql2 mysql2

[root@mysql2 ~]# mysqlsh --uri root@localhost
Please provide the password for 'root@localhost': ***************
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.0.21

Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.
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.
Creating a session to 'root@localhost'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 9 (X protocol)
Server version: 8.0.21 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:33060+ ssl JS >

MySQL localhost:33060+ ssl JS > dba.configureReplicaSetInstance()
Configuring local MySQL instance listening at port 3306 for use in an InnoDB ReplicaSet...

This instance reports its own address as mysql2: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.

ERROR: User 'root' can only connect from 'localhost'. New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster.

1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB ReplicaSet with minimal required grants
3) Ignore and continue
4) Cancel

Please select an option [1]: 2
Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: InnodbReplicaSet
Password for new account: ***************
Confirm password: ***************

NOTE: Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable | Current Value | Required Value | Note |
+--------------------------+---------------+----------------+--------------------------------------------------+
| enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server |
| gtid_mode | OFF | ON | Update read-only variable and restart the server |
| server_id | 1 | <unique ID> | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+

Some variables need to be changed, but cannot be done dynamically on the server.
Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: y
Cluster admin user 'InnodbReplicaSet'@'%' created.
Configuring instance...
The instance 'mysql2:3306' was configured to be used in an InnoDB ReplicaSet.
Restarting MySQL...
NOTE: MySQL server at mysql2:3306 was restarted.
MySQL localhost:33060+ ssl JS >

 

Now login with the InnodbReplicaSet User and Create Replica Set and Add database node ‘mysql2’ to form Replica Set.

####################Servers: mysql1:####################

MySQL localhost:33060+ ssl JS > \c InnodbReplicaSet@mysql1
Creating a session to 'InnodbReplicaSet@mysql1'
Please provide the password for 'InnodbReplicaSet@mysql1': ***************
Save password for 'InnodbReplicaSet@mysql1'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for autocompletion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 8 (X protocol)
Server version: 8.0.21 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL mysql1:33060+ ssl JS >

MySQL mysql1:33060+ ssl JS > rs=dba.createReplicaSet('MySQLReplicaSet')
A new replicaset with instance 'mysql1:3306' will be created.

* Checking MySQL instance at mysql1:3306

This instance reports its own address as mysql1:3306
mysql1:3306: Instance configuration is suitable.

* Updating metadata...

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

<ReplicaSet:MySQLReplicaSet>

Now as we can see from the status our replicaset has been created acting as PRIMARY, Now we need to add a node to this replicaset.

MySQL mysql1:33060+ ssl JS > rs.status()
{
"replicaSet": {
"name": "MySQLReplicaSet",
"primary": "mysql1:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"mysql1:3306": {
"address": "mysql1:3306",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}

Now We will add the mysql2 server to the replicaset as and secondary server acting as a replica. We will choose the clone method by default this will hardly take 30sec to create a clone as there is nothing on the primary server.

MySQL mysql1:33060+ ssl JS > rs.addInstance('InnodbReplicaSet@mysql2:3306')
Adding instance to the replicaset...

* Performing validation checks

This instance reports its own address as mysql2:3306
mysql2:3306: Instance configuration is suitable.

* Checking async replication topology...

* Checking transaction state of the instance...

NOTE: The target instance 'mysql2: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 'mysql2: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):
* Updating topology
Waiting for clone process of the new member to complete. Press ^C to abort the operation.
* Waiting for clone to finish...
NOTE: mysql2:3306 is being cloned from mysql1:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
** Stage RECOVERY: |
NOTE: mysql2:3306 is shutting down...

* Waiting for server restart... ready
* mysql2:3306 has restarted, waiting for clone to finish...
* Clone process has finished: 59.64 MB transferred in about 1 second (~59.64 MB/s)

** Configuring mysql2:3306 to replicate from mysql1:3306
** Waiting for new instance to synchronize with PRIMARY...
The instance 'mysql2:3306' was added to the replicaset and is replicating from mysql1:3306.

Now let's check the status of our ReplicaSet


MySQL mysql1:33060+ ssl JS > rs.status()
{
"replicaSet": {
"name": "MySQLReplicaSet",
"primary": "mysql1:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"mysql1:3306": {
"address": "mysql1:3306",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
},
"mysql2:3306": {
"address": "mysql2: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"
}
}

We are done here out Replicaset has been deployed you can see the ‘applierThreadState’ it’s
totaly in sync

#################################Mysql Router Server: ###########################

We will bootstrap the router using user mysqlrouter it will create this user automatically

 

[root@localhost ~]# mysqlrouter --bootstrap InnodbReplicaSet@mysql1 --user mysqlrouter
Please enter MySQL password for InnodbReplicaSet:
# Bootstrapping system MySQL Router instance...

- Creating account(s) (only those that are needed, if any)
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /etc/mysqlrouter/mysqlrouter.conf

Existing configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'

# MySQL Router configured for the InnoDB ReplicaSet 'MySQLReplicaSet'

After this MySQL Router has been started with the generated configuration

$ /etc/init.d/mysqlrouter restart
or
$ systemctl start mysqlrouter
or
$ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

the cluster 'MySQLReplicaSet' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447

## MySQL X protocol

- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447

Now as we have configured our router we will test the router by connecting to the router and pointing to the provided port by the router for Read/Write localhost:6446 and Read/Only localhost:6447

##############Servers: mysq11:####################

Let’s connect to the database and create a database, table, and insert some data to confirm our replicaset in working.

[root@mysql1 ~]# mysqlsh --uri root@localhost
MySQL mysql1:33060+ ssl JS > \c root@localhost
MySQL localhost:33060+ ssl JS > \sql
Switching to SQL mode... Commands end with ;

MySQL localhost:33060+ ssl SQL > CREATE DATABASE schoool;
Query OK, 1 row affected (0.0044 sec)
MySQL localhost:33060+ ssl SQL > USE schoool;
Default schema set to `schoool`.
Fetching table and column names from `schoool` for auto-completion... Press ^C to stop.
MySQL localhost:33060+ ssl schoool SQL > CREATE TABLE if not exists schoool.student(stndid int primary key auto_increment,stdname varchar(100),phone int,classid int);
Query OK, 0 rows affected (0.0225 sec)
MySQL localhost:33060+ ssl schoool SQL > INSERT schoool.student(stdname,phone,classid) values('Aslam',123456977,7);
Query OK, 1 row affected (0.0049 sec)
MySQL localhost:33060+ ssl schoool SQL > INSERT schoool.student(stdname,phone,classid) values('Mughees',123456977,8);
Query OK, 1 row affected (0.0086 sec)
MySQL localhost:33060+ ssl schoool SQL > INSERT schoool.student(stdname,phone,classid) values('Ahmed',123456977,9);
Query OK, 1 row affected (0.0031 sec)
MySQL localhost:33060+ ssl schoool SQL > SELECT * FROM schoool.student;
+--------+---------+-----------+---------+
| stndid | stdname | phone | classid |
+--------+---------+-----------+---------+
| 1 | Aslam | 123456977 | 7 |
| 2 | Mughees | 123456977 | 8 |
| 3 | Ahmed | 123456977 | 9 |
+--------+---------+-----------+---------+
3 rows in set (0.0006 sec)
MySQL localhost:33060+ ssl schoool SQL > select @@hostname
-> ;
+------------+
| @@hostname |
+------------+
| mysql1 |
+------------+
1 row in set (0.0003 sec)

 

##############Servers: mysq12:####################

Let’s check on the Replica side our data should be replicated to the replica.

[root@mysql2 ~]# mysqlsh root@localhost
MySQL Shell 8.0.21

Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.
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.
Creating a session to 'root@localhost'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 17280 (X protocol)
Server version: 8.0.21 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:33060+ ssl JS > \sql
Switching to SQL mode... Commands end with ;
MySQL localhost:33060+ ssl SQL > select @@hostname
-> ;
+------------+
| @@hostname |
+------------+
| mysql2 |
+------------+
1 row in set (0.0003 sec)
MySQL localhost:33060+ ssl SQL > SELECT * FROM schoool.student;
+--------+---------+-----------+---------+
| stndid | stdname | phone | classid |
+--------+---------+-----------+---------+
| 1 | Aslam | 123456977 | 7 |
| 2 | Mughees | 123456977 | 8 |
| 3 | Ahmed | 123456977 | 9 |
+--------+---------+-----------+---------+
3 rows in set (0.0009 sec)

Creating a connection from Mysql Workbench:

Provide the host address i.e. 192.168.66.18 and the port for Read/Write is 6446

You can see the above pic shows the connection from mysql1 server.  Please go ahead and check this blog and let me know if you have any to say.  Follow me on LinkedIn 

Mughees Ahmed Linkdin

Want to learn more:

https://dev.mysql.com/doc/refman/8.0/en/deploying-innodb-replicasets.html