How to Deploy MySQL InnoDB Replica Set in Production?
Before i talk about Deployment process of MySQL InnoDB Replica Set , it is more important to know below details:-
- What is MySQL InnoDB Replica Set?
- What is prerequisite and limitation of using MySQL Replica Set?
- In what kind of scenarios MySQL Replica Set is not recommended.
- How to configure and deploy MySQL Replica Set- (step by step guide )
- How to use InnoDB Replica Set?
- What if Primary goes down? Does select query re-routed to another server?
- What if Secondary goes down while executing select queries?
§ I will answer these all question in this blog.
What is Replica Set ?
MySQL InnoDB ReplicaSet a quick and easy way to get MySQL
replication(Master-Slave), making it well suited to scaling out reads, and
provides manual failover capabilities in use cases that do not require the high
availability offered by MySQL InnoDB cluster.
Suppose you have one server is running for deriving workloads and you have to bring high availability
in place for an application, basic says in MySQL to achieve high availability
you require minimum 02 MySQL Server running in two different host.
And to set up link between these two host until earlier we
have to prepare and qualify server to be part of HA, which requires you must
know basis of MySQL BUT from MySQL 8.0.19 you don’t have to spend time on
preparation and qualification and doing configuration level changes , MySQL InnoDB REPLICA SET makes your JOB AUTOMATED.
MySQL Replica Set is set of three components which is
·
MySQL Shell
·
MySQL Router
·
Set of MySQL Servers(min no of server – 02)
It works only with Single Primary and multiple secondary server,
which is in ASYNC mode.
MySQL Shell includes AdminAPI, which enables
you to easily configure, administrator, and deploy a group of MySQL Servers.
MySQL Router which
is part of Replica Set and is lightweight middleware that provides transparent
routing between your application and back-end MySQL Servers. Purpose is to
serve R/W request to primary instance through port 6446 and R/O request to
multiple primary instance through port 6447.
It is always recommended to Install MySQL Router into app
server because of below reasons
·
app is the one who has to send request to .
Application------->Router------->List of MySQL Servers.
|
·
To decrease network latency
What is prerequisite and limitation of using MySQL Replica Set?
§
Manual Failover.
§
No Multi Primary Topology.
§
All Secondary members replicate from primary.
§
GTID based.
§
All MySQL Server version 8.0.19.
§
Rows based replication supported.
§
Replication Filter is not supported.
§
Replica Set must be managed by MySQL Shell.
§
Try to always use MySQL Cloning over Incremental
Recovery as Recovery Method.
More Limitations:-
In what kind of scenarios MySQL Replica Set is Recommended ?
Below are top Features which makes life of DBA simple:-
ü
To scale Read workloads.
ü
Manual failover in event of primary node goes
down.
ü
Useful where we can compromise RPO/RTO time.
ü
MySQL Shell Automatically configures users and
Replication.
ü
Easy to deploy without editing into
my.cnf/my.ini file.
ü
Not to spend time on Backup àRestore to provision
new node , MySQL CLONE feature in-built which will save a lot time to bring another server for replication. More
on Cloning:- https://mysqlserverteam.com/clone-create-mysql-instance-replica/
ü
Integrated MySQL Router Load balancing .
ü
Easy to getting started into MySQL high
availability for all tier type applications.
How to configure and deploy MySQL Replica Set
Step by step guide to deploy MySQL Replica Set in Production
In this tutorial I will use two machine where MySQL is
running
Machine 01:- 10.0.10.33
Machine 02:-
10.0.10.38
Make sure below software is installed:-
1.
Mysql Server 8.0.19
2.
MySQL Shell
3.
MySQL Router. (it can install on either MySQL
Server or Application Server which is Recommended).
Step 1:- Configure Machine to participate into InnoDB
Replica Set
##In Machine 01
mysqlsh
shell.connect("root@10.0.10.33:3306");
Creating a session to
'root@10.0.10.33:3306'
Please provide the password for
'root@10.0.10.33:3306': ********
Save password for
'root@10.0.10.33: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 13
Server version: 8.0.19-commercial
MySQL Enterprise Server - Commercial
No default schema selected; type
\use <schema> to set one.
<ClassicSession:root@10.0.10.33:3306>
MySQL
10.0.10.33:3306 ssl JS >
dba.configureReplicaSetInstance("root@10.0.10.33:3306",{clusterAdmin:
"'rsadmin'@'10.0.10.33%'"});
Configuring local MySQL instance listening
at port 3306 for use in an InnoDB ReplicaSet...
This instance reports its own
address as Workshop-33: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: ********
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
'rsadmin'@'10.0.10.33%' created.
Configuring instance...
The instance 'Workshop-33:3306'
was configured to be used in an InnoDB ReplicaSet.
Restarting MySQL...
NOTE: MySQL server at
Workshop-33:3306 was restarted.
##In Machine 2
mysqlsh
shell.connect("root@10.0.10.38:3306");
Creating a session to
'root@10.0.10.38:3306'
Please provide the password for
'root@10.0.10.38:3306': ********
Save password for
'root@10.0.10.38: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 10
Server version: 8.0.19-commercial
MySQL Enterprise Server - Commercial
No default schema selected; type
\use <schema> to set one.
<ClassicSession:root@10.0.10.38:3306>
dba.configureReplicaSetInstance("root@10.0.10.38:3306",{clusterAdmin:
"'rsadmin'@'10.0.10.38%'"});
Configuring local MySQL instance
listening at port 3306 for use in an InnoDB ReplicaSet...
This instance reports its own
address as Workshop-38: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: ********
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
'rsadmin'@'10.0.10.38%' created.
Configuring instance...
The instance 'Workshop-38:3306'
was configured to be used in an InnoDB ReplicaSet.
Restarting MySQL...
NOTE: MySQL server at
Workshop-38:3306 was restarted.
MySQL
10.0.10.38:3306 ssl JS >
|
Step 2:- Create Replica Set and Add database node to form
Replica Set.
##Connect to Machine
01 :-
mysqlsh
shell.connect("root@10.0.10.33:3306");
var rs =
dba.createReplicaSet("MyReplicatSet")
A new replicaset with instance
'Workshop-33:3306' will be created.
* Checking MySQL instance at Workshop-33:3306
This instance reports its own
address as Workshop-33:3306
Workshop-33:3306: Instance
configuration is suitable.
* Updating metadata...
ReplicaSet object successfully
created for Workshop-33:3306.
Use rs.addInstance() to add more
asynchronously replicated instances to this replicaset and rs.status() to
check its status.
MySQL
10.0.10.33:3306 ssl JS >
rs.addInstance("10.0.10.38:3306");
Adding instance to the
replicaset...
* Performing validation checks
This instance reports its own
address as Workshop-38:3306
Workshop-38:3306: Instance
configuration is suitable.
* Checking async replication
topology...
* Checking transaction state of
the instance...
The safest and most convenient way
to provision a new instance is through automatic clone provisioning, which
will completely overwrite the state of 'Workshop-38: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'.
Incremental state recovery was
selected because it seems to be safely usable.
* Updating topology
** Configuring Workshop-38:3306 to
replicate from Workshop-33:3306
** Waiting for new instance to
synchronize with PRIMARY...
The instance 'Workshop-38:3306'
was added to the replicaset and is replicating from Workshop-33:3306.
MySQL
10.0.10.33:3306 ssl JS >
rs.status();
{
"replicaSet": {
"name":
"ReplicatSet",
"primary":
"Workshop-38:3306",
"status": "AVAILABLE",
"statusText": "All
instances available.",
"topology": {
"10.0.10.39:3306": {
"address":
"10.0.10.39: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"
},
"Workshop-38:3306": {
"address":
"Workshop-38:3306",
"instanceRole":
"PRIMARY",
"mode":
"R/W",
"status":
"ONLINE"
}
},
"type": "ASYNC"
}
}
|
Step 3:- Configure Router to talk from App to Replica Set.
mysqlrouter --force
--user=root --bootstrap root@10.0.10.38:3306 --directory myrouter
#In Case
Router from Remote Machine:-cluster in 10.0.10.14
mysqlrouter --bootstrap root@10.0.10.14:3310 --directory
myrouter
|
Step 4: Start Router
myrouter/start.sh
|
Step 5: Using Replica Set
mysqlsh
MySQL
JS>
shell.connect("root@127.0.0.1:6446");
\sql
SQL>SELECT * FROM
performance_schema.replication_group_members;
|
CREATE DATABASE sales;USE sales;
CREATE TABLE if not exists sales.employee(empid int
primary key auto_increment,empname varchar(100),salary int,deptid int);
|
INSERT sales.employee(empname,salary,deptid)
values('Ram',1000,10);
|
INSERT sales.employee(empname,salary,deptid)
values('Raja',2000,10);
|
INSERT sales.employee(empname,salary,deptid)
values('Sita',3000,20);
SELECT * FROM
sales.employee;
|
Connect
Router to another machine to verify changes.
mysqlsh
JS>shell.connect("root@127.0.0.1:6447");
\sql
SQL>SELECT * FROM sales.employee;
INSERT sales.employee values(100,'Ram',1000,10);
<Error> because this machine is not allowed to
execute DML,DDL statements.>
|
##Create
Disaster
#service
mysqld stop
|
RS1= dba.getReplicaSet()
RS1.status();
MySQL 10.0.10.38:3306 ssl JS > RS1.status()
ReplicaSet.status:
Failed to execute query on Metadata server 10.0.10.38:3306: Lost connection
to MySQL server during query (MySQL Error 2013)
MySQL
10.0.10.38:3306 ssl JS >
RS1.status()
ReplicaSet.status:
The Metadata is inaccessible (MetadataError)
MySQL
10.0.10.38:3306 ssl JS >
RS1.status()
ReplicaSet.status:
The Metadata is inaccessible (MetadataError)
MySQL
10.0.10.38:3306 ssl JS >
|
MySQL-JS>shell.connect("root@localhost:6446");
Creating a session to
'root@10.0.10.38:6446'
Please provide the password
for 'root@10.0.10.38:6446': ********
Shell.connect: Can't connect to remote MySQL
server for client connected to '0.0.0.0:6446' (MySQL Error 2003)
|
#service mysqld start
MySQL 10.0.10.38:3306 ssl JS > RS1.status()
ReplicaSet.status: The Metadata
is inaccessible (MetadataError)
MySQL
10.0.10.38:3306 ssl JS >
RS1=dba.getReplicaSet()
You are connected to a member of
replicaset 'ReplicatSet'.
<ReplicaSet:ReplicatSet>
RS1=dba.getReplicaSet()
RS1.status()
|
##Again Connect to Router to send the traffic
mysqlsh
shell.connect("root@localhost:6447");
\sql
SQL>SELECT * FROM sales.employee;
|
Scenario#1 Assume
primary goes down :and if you run
MySQL 10.0.10.38:3306
ssl JS > RS1.status()
Error :- ReplicaSet.status:
The Metadata is inaccessible (MetadataError)
MySQL 10.0.10.38:3306 ssl JS >
Now Primary machine UP and if you run
MySQL 10.0.10.38:3306
ssl JS > RS1.status()
ReplicaSet.status: The Metadata is inaccessible
(MetadataError)
>>It not get refreshed.
Fix :-
RS1= dba.getReplicaSet()
RS1.status();
|
Scenario #02
Create Disaster # What if Primary
Node Fails while executing below query from application
while
[ 1 ]; do sleep 1; mysql -h127.0.0.1 -uroot -p123456 -P6446 -e " INSERT
sales.employee(empname,salary,deptid) values('Ram',1000,10); select count(*)
from sales.employee"; done
\JS
#Stop Primary MySQL Instance
service mysqld stop
You can see Insert Query is stopped working , Ended with ERROR
Now Lets execute only SELECT query let see what happens... since primary
node goes down which means mysql router will stopped send any query
into 6446 BUT router has another port OPEN for sending ONLY SELECT
query. which meant router will use port 6447 to send select query.
see below
Let's re-execute same query with only SELECT query connecting to R/O port 6447
while [ 1 ]; do sleep 1; mysql -h127.0.0.1 -uroot -p123456 -P6447 -e " Select count(*) from sales.employee"; done
You are able to access another machine which is Replica (10.0.0.38).
Now , Let's Re-connect to Primary Node(10.0.10.33) what will happen? it will work or not?...
Which means that even if primary node goes down and second
replicas are alive then select query will work
select @@hostname; --> 10.0.10.38
Scenario #03
Create Disaster # What if Secondary
Node Fails…
#Stop MySQL Instance
10.0.10.38$service mysqld stop
Primary will still works even though Secondary node goes
down… that’s by design of MySQL Replication.
Now since secondary node goes down let’s connect to 6447 and
send only SELECT query
while
[ 1 ]; do sleep 1; mysql -h127.0.0.1 -uroot -p123456 -P6447 -e " select count(*) from
sales.employee"; done
What will happen?
Even though Secondary Node goes down , MySQL Router will re-routing to
Primary server and return results as you see in above image.
Re-confirm:-
Can you Observe one important observation? why port 6447 is executing R/W query?
When we execute R/W and R/O on 6447
port Router does routing to Primary Node 6446.
Because as per documentation:-
When you use MySQL Router
with a replica set, be aware that:
· The
read-write port of MySQL Router directs client connections to the primary
instance of the replica set
· The
read-only port of MySQL Router direct client connections to a secondary
instance of the replica set, although it could also direct them to the primary
Please try this brand new features to set up MySQL Replication with the help of MySQL Shell.
No comments:
Post a Comment