Maxscale started supporting Amazon Aurora lately from its version 2.1 which comes with a BSL license, we are good until we use only 3 nodes, Amazon Aurora (Our Previous blog ) is a brilliant technology built by AWS team which imitates features of MySQL, Aurora is getting better and better with regards to scaling and feature with each release of its Aurora engine current version is 1.16 (at time of writing ) , Aurora architecture and features can be seen here. In this blog i will be explaining Maxscale deployment for Aurora.
Maxscale version : maxscale-2.1.13-1.x86_64 OS version : Amazon Linux AMI 2016.09 Cores : 4 RAM : 8GB
Note: Make sure to have the EC2 machine with in same Availability Zone ( AZ ) where the Aurora resides which could greatly help in reducing the network latency.
For the purpose of this blog i have used 3 instances of Aurora (1Master + 2 Read Replica).
Now lets speaks about the end-points that Aurora provides.
AuroraEndpoints:
Endpoints are the correction URI’s provide by AWS to connect to the Aurora database. Listed below the endpoints provided by Aurora.
Cluster Endpoint
Reader Endpoint
Instance Endpoint
Cluster Endpoint:
An endpoint for a Aurora DB cluster that connects to the current primary instance for that DB cluster,The cluster endpoint provides failover support for read/write connections to the DB cluster. If the current primary instance of a DB cluster fails, Aurora automatically fails over to a new primary instance
Reader Endpoint:
An endpoint for an Aurora DB cluster that connects to one of the available Aurora Replicas for that DB cluster. Each Aurora DB cluster has a reader endpoint.The reader endpoint provides load balancing support for read-only connections to the DB cluster
Instance Endpoint:
An endpoint for a DB instance in an Aurora DB cluster that connects to that specific DB instance.Each DB instance in a DB cluster, regardless of instance type, has its own unique instance endpoint
Among these different end-point we will be using the “Instance Endpoint” ie., individual end-point in maxscale config.
The problem here is application should have reads and writes split at application layer. So that it can use the Reader and writer end points efficiently. But if a user migrates to Aurora for scalability then we need a intelligent proxy like Maxscale / ProxySQL. Currently Maxscale has inbuilt support for Aurora.
How is Aurora Monitored by Maxscale?
Maxscale uses a special monitor module called ‘auroramon’, since the Aurora does not follow standard MySQL replication protocol for replicating data to its under replica.
How ‘auroramon’ identifies master and replica from ‘Instance Endpoint’ ?
Each node inside the aurora cluster( in our use case its 1master + 2 Replica), has a aurora_server_id (@@aurora_server_id), which is a unique identifier for each instance/node.
And also Aurora all the relevant information about replication including the aurora_server_id inside the table information_schema.replica_host_status, Below is the structure of the table.
The above structure of table is subjected to change as per Aurora version.
Another important variable to check is the ‘SESSION_ID’ which is a unique identifier value for replica nodes, but for the master server it will defined as ‘MASTER_SESSION_ID’.
Based on these two variables the master and replica is segregated by maxscale monitor and which sets the status flag based on which the router sends the traffic to nodes.
Now let get into the configuration part, of maxscale for Aurora.
Installation and administration has been covered in previous blogs part1 and part2.
In the above server i have enabled connection pool by defining persistpoolmax and persistmaxtime, this would greatly help in overcoming restart of instance due to memory handling with aurora.
Once all the config is done you can reload Maxscale config / restart Maxscale service.
Below is the method to check the RW split from command line, by default a query inside ‘start transaction;’ goes to the master node, Since am connecting directly from max scale node i have used local socket, you can also use IP and Port instead.
[root@ip-XXXXXX mydbops]# mysql -u mydbops -pXXXXXX -S /tmp/ClusterMaster -e "show global variables like '%aurora_server_id%';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------------------------+
| Variable_name | Value |
+------------------+----------------------------+
| aurora_server_id | proddtest-rr-tier1ZZ |
+------------------+----------------------------+
[root@ip-XXXXXXX mydbops]# mysql -u mydbops -pXXXXX -S /tmp/ClusterMaster -e "start transaction;show global variables like '%aurora_server_id%';commit;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------------------------+
| Variable_name | Value |
+------------------+----------------------------+
| aurora_server_id | proddtest-rr-tier1XX |
+------------------+----------------------------+
To monitor the percentage of RW split between master & replica and configuration stats as below.
MaxScale> show service "Splitter Service"
Service: Splitter Service
Router: readwritesplit
State: Started
use_sql_variables_in: all
slave_selection_criteria: LEAST_BEHIND_MASTER
master_failure_mode: fail_instantly
max_slave_replication_lag: 30
retry_failed_reads: true
strict_multi_stmt: true
strict_sp_calls: false
disable_sescmd_history: true
max_sescmd_history: 0
master_accept_reads: true
Number of router sessions: 5
Current no. of router sessions: 2
Number of queries forwarded: 20
Number of queries forwarded to master:8 (40.00%)
Number of queries forwarded to slave: 12 (60.00%)
Number of queries forwarded to all: 4 (20.00%)
Started: Mon Feb 19 15:53:48 2018
Root user access: Disabled
Backend databases:
[prodtest-rr-tier1XX.xxxxxx.us-east-1.rds.amazonaws.com]:3306 Protocol: MySQLBackend Name: nodeA
[proddtest-rr-tier1YY.xxxxxxx.us-east-1.rds.amazonaws.com]:3306 Protocol: MySQLBackend Name: nodeB
[proddtest-rr-tier1ZZ.XXXXXXX.us-east-1.rds.amazonaws.com]:3306 Protocol: MySQLBackend Name: nodeC
Total connections: 7
Currently connected: 2
Now you can start using Maxscale and scale your queries for Aurora cluster. It is becomes no more mandatory for the segregating the reads and writes queries for Aurora.
%20vs%20systemctl%20stop%20(2).png)
.png)
.svg)
