MySQL Transparent Read-Write Splitting with MySQL Router 8.2

May 22, 2024

Mins to Read

All

Efficient load balancing among servers is crucial to ensure that all servers contribute efficiently to serving application traffic. Without proper load distribution, a single server may become overwhelmed, causing bottlenecks and impacting overall application performance. Therefore, it is imperative to balance the load among servers for optimal performance and equitable utilization of resources.

MySQL Router 8.2

MySQL Router 8.2 has introduced a read-write split feature that directs read requests to the secondary server and writes requests to the primary server based on the transaction type. This means applications can now connect to the router, and the router will efficiently distribute the load between the primary and secondary servers, optimizing the overall performance. This blog explores the implementation of read-write split functionality using MySQL Router.

Read Write split-up using MySQL-Router in 8.2 - Flowchart

Routing strategies

First Available

The router will route the traffic to one single server. In case the particular server goes down, then the router will route the traffic to the next available server. Once the old server comes back online, the router will start sending the traffic to the old server itself.

Next Available

The router will redirect traffic to a single server. If that server fails, the router will divert traffic to the next available server. When the old server comes back online, the router will continue to direct traffic to the new server only.

Round Robin

The router will route traffic to all accessible servers on the list. If one of the nodes goes down. The traffic will be routed through the remaining servers.

Round Robin with Fallback

The router will route traffic to all accessible servers on the list. If all nodes go down, traffic is directed to the cluster's primary servers.

Bootstrapping MySQL Router

To get started, we can download the necessary package from the official website and easily install it using either yum or apt, depending on your operating system.

After successfully installing MySQL Router, the next step is to bootstrap it. Bootstrap involves collecting metadata about the cluster, which is essential for the router to efficiently route queries among the nodes in the cluster.

MySQL user

To facilitate the bootstrap process of MySQL Router, it's necessary to create a dedicated MySQL user with the minimum required grants outlined below.

 
CREATE USER ON . TO 'router'@'%' WITH GRANT OPTION;

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON mysql_innodb_cluster_metadata.* TO 'router'@'%';

GRANT SELECT ON mysql.user TO 'router'@'%';

GRANT SELECT ON performance_schema.replication_group_members TO 'router'@'%';

GRANT SELECT ON performance_schema.replication_group_member_stats TO 'router'@'%';

GRANT SELECT ON performance_schema.global_variables TO 'router'@'%';

Initialisation

After successfully creating the MySQL user, the next step involves utilizing this user to initiate the bootstrap process for MySQL Router.

 
Mydbopslabs:/home/ubuntu# mysqlrouter --user=mysql --bootstrap router@Node1:3306 --directory /router/myrouter  --conf-use-sockets --account routerfriend --account-create always

Please enter MySQL password for router:

# Bootstrapping MySQL Router 8.2.0 (MySQL Community - GPL) instance at '/router/myrouter'...

Please enter MySQL password for routerfriend:

- Creating account(s)

- 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 /router/myrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'Blog'

After this MySQL Router has been started with the generated configuration

    $ mysqlrouter -c /router/myrouter/mysqlrouter.conf

InnoDB Cluster 'Blog' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446, /router/myrouter/mysql.sock

- Read/Only Connections:  localhost:6447, /router/myrouter/mysqlro.sock

- Read/Write Split Connections: localhost:6450, /router/myrouter/mysqlsplit.sock

## MySQL X protocol

- Read/Write Connections: localhost:6448, /router/myrouter/mysqlx.sock

- Read/Only Connections:  localhost:6449, /router/myrouter/mysqlxro.sock

Base directory

While bootstrapping, MySQL Router gathers metadata from the entire cluster and stores it in the specified directory. This process involves creating default configurations, startup scripts, and other necessary files in the mentioned directory. Along with that routerfriend user will also be automatically created in the MySQL cluster by MySQL Router.

 
Mydbopslabs:/router/myrouter# ls -ltrh

total 32K

drwx------ 2 mysql mysql 4.0K Nov 21 05:42 run

-rw------- 1 mysql mysql   87 Nov 21 05:42 mysqlrouter.key

drwx------ 2 mysql mysql 4.0K Nov 21 05:42 log

-rwx------ 1 mysql mysql  158 Nov 26 04:46 stop.sh

-rwx------ 1 mysql mysql  286 Nov 26 04:46 start.sh

-rw------- 1 mysql mysql 2.4K Nov 26 04:46 mysqlrouter.conf

drwx------ 2 mysql mysql 4.0K Nov 26 04:46 data

-rw-r--r-- 1 mysql mysql    5 Nov 26 05:48 mysqlrouter.pid

srwxrwxrwx 1 mysql mysql    0 Nov 26 05:48 mysqlx.sock

srwxrwxrwx 1 mysql mysql    0 Nov 26 05:48 mysqlro.sock

srwxrwxrwx 1 mysql mysql    0 Nov 26 05:48 mysqlsplit.sock

srwxrwxrwx 1 mysql mysql    0 Nov 26 05:48 mysqlxro.sock

srwxrwxrwx 1 mysql mysql    0 Nov 26 05:48 mysql.sock

Starting

After completing the bootstrap process, you can start the MySQL router by executing the startup script.

 
Mydbopslabs:/router# myrouter/start.sh

Mydbopslabs:/router# PID 1540 written to '/router/myrouter/mysqlrouter.pid'

stopping to log to the console. Continuing to log to filelog

service mysqMydbopslabs:/home/ubuntu# service mysqlrouter status

● mysqlrouter.service - LSB: Start / Stop MySQL Router

     Loaded: loaded (/etc/init.d/mysqlrouter; generated)

     Active: active (exited) since Sun 2023-11-26 04:53:09 UTC; 1h 21min ago

       Docs: man:systemd-sysv-generator(8)

    Process: 1128 ExecStart=/etc/init.d/mysqlrouter start (code=exited, status=0/SUCCESS)

        CPU: 15ms

Nov 26 04:53:09 localhost systemd[1]: Starting LSB: Start / Stop MySQL Router...

Nov 26 04:53:09 localhost mysqlrouter[1128]:  * Starting MySQL Router

Nov 26 04:53:09 localhost mysqlrouter[1128]:    ...done.

Nov 26 04:53:09 localhost systemd[1]: Started LSB: Start / Stop MySQL Router.

In our current setup, a two-node InnoDB cluster is in place, and the metadata for this cluster is stored within the data directory of the MySQL router.

Cluster log

The cluster status provides information about the current server's membership within the cluster.

 
MySQL  localhost:3306 ssl  JS > cluster.status()

{

    "clusterName": "Blog",

    "defaultReplicaSet": {

        "name": "default",

        "primary": "Node1:3306",

        "ssl": "REQUIRED",

        "status": "OK_NO_TOLERANCE",

        "statusText": "Cluster is NOT tolerant to any failures.",

        "topology": {

            "Node2:3306": {

                "address": "Node2:3306",

                "memberRole": "SECONDARY",

                "mode": "R/O",

                "readReplicas": {},

                "replicationLag": "applier_queue_applied",

                "role": "HA",

                "status": "ONLINE",

                "version": "8.0.34"

            },

            "Node1:3306": {

                "address": "Node1:3306",

                "memberRole": "PRIMARY",

                "mode": "R/W",

                "readReplicas": {},

                "replicationLag": "applier_queue_applied",

                "role": "HA",

                "status": "ONLINE",

                "version": "8.0.34"

            }

        },

        "topologyMode": "Single-Primary"

    },

    "groupInformationSourceMember": "Node1:3306"

}

Router config

The cluster information is stored in the state.json file, serving the purpose of query routing.

 
Mydbopslabs:/router/myrouter/data# cat state.json

{

    "metadata-cache": {

        "group-replication-id": "72e7e658-8748-11ee-8baa-06e6380e502d",

        "cluster-metadata-servers": [

            "mysql://Node1:3306",

            "mysql://Node2:3306"

        ]

    },

    "version": "1.0.0"

}

Configuration

In the specified directory, we'll find the mysqlrouter.conf file containing all the MySQL router configurations, including details about query routing, data directory, and cluster metadata.

 
[metadata_cache:bootstrap]

cluster_type=gr

router_id=1

user=routerfriend

metadata_cluster=Blog

ttl=0.5

auth_cache_ttl=-1

auth_cache_refresh_interval=2

use_gr_notifications=0

[routing:bootstrap_ro]

bind_address=0.0.0.0

bind_port=6447

socket=/router/myrouter/mysqlro.sock

destinations=metadata-cache://Blog/?role=SECONDARY

routing_strategy=round-robin-with-fallback

protocol=classic

[routing:bootstrap_rw_split]

bind_address=0.0.0.0

bind_port=6450

socket=/router/myrouter/mysqlsplit.sock

destinations=metadata-cache://Blog/?role=PRIMARY_AND_SECONDARY

routing_strategy=round-robin

protocol=classic

connection_sharing=1

client_ssl_mode=PREFERRED

server_ssl_mode=PREFERRED

access_mode=auto

Query Re-routing

End users don't need to manage servers or users for query routing; MySQL router autonomously retrieves active servers and users from the MySQL cluster. When a new user is added to MySQL, it can be immediately utilized for traffic routing via the router.

Select the appropriate port based on your use case, as specified in the configuration file. For managing read-only traffic, connect via port 6447 for handling read-write split, connect via port 6450, as outlined in the mysqlrouter.conf file. The read-write split is determined by the server's read-only variable, which is enabled by the cluster on the secondary server.

 
Mydbopslabs:/home/ubuntu# mysql -h 127.0.0.1 -u router -p -P 6450 -e "start transaction;select @@hostname;commit;"

Enter password:

+------------------+

| @@hostname       |

+------------------+

|            Node1 |

+------------------+

Mydbopslabs:/home/ubuntu# mysql -h 127.0.0.1 -u router -p -P 6450 -e "select @@hostname;"

Enter password:

+----------------+

| @@hostname     |

+----------------+

|          Node2 |

+----------------+

Handling servers

If a new node is added to the cluster, MySQL router will automatically detect it and include it for query routing.

Cluster log

 
MySQL  localhost:3306 ssl  JS > cluster.status()

{

    "clusterName": "Blog",

    "defaultReplicaSet": {

        "name": "default",

        "primary": "Node1:3306",

        "ssl": "REQUIRED",

        "status": "OK",

        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

        "topology": {

            "Node3:3306": {

                "address": "Node3:3306",

                "memberRole": "SECONDARY",

                "mode": "R/O",

                "readReplicas": {},

                "replicationLag": "applier_queue_applied",

                "role": "HA",

                "status": "ONLINE",

                "version": "8.0.34"

            },

            "Node2:3306": {

                "address": "Node2:3306",

                "memberRole": "SECONDARY",

                "mode": "R/O",

                "readReplicas": {},

                "replicationLag": "applier_queue_applied",

                "role": "HA",

                "status": "ONLINE",

                "version": "8.0.34"

            },

            "Node1:3306": {

                "address": "Node1:3306",

                "memberRole": "PRIMARY",

                "mode": "R/W",

                "readReplicas": {},

                "replicationLag": "applier_queue_applied",

                "role": "HA",

                "status": "ONLINE",

                "version": "8.0.34"

            }

        },

        "topologyMode": "Single-Primary"

    },

    "groupInformationSourceMember": "Node1:3306"

}

Upon successful addition of the new node to the cluster, the metadata of the MySQL router is automatically updated.

Router config

 
Mydbopslabs:/router/myrouter/data# cat state.json

{

    "metadata-cache": {

        "group-replication-id": "72e7e658-8748-11ee-8baa-06e6380e502d",

        "cluster-metadata-servers": [

            "mysql://Node1:3306",

            "mysql://Node2:3306",

            "mysql://Node3:3306"

        ]

    },

    "version": "1.0.0"

}

The traffic is now seamlessly routed to the newly added secondary server.

 
Mydbopslabs:/home/ubuntu# mysql -h 127.0.0.1 -u router -p -P 6450 -e "start transaction;select @@hostname;commit;"

Enter password:

+------------------+

| @@hostname       |

+------------------+

|            Node1 |

+------------------+

Mydbopslabs:/home/ubuntu# mysql -h 127.0.0.1 -u router -p -P 6450 -e "select @@hostname;"

Enter password:

+----------------+

| @@hostname     |

+----------------+

|          Node2 |

+----------------+

Mydbopslabs:/home/ubuntu# mysql -h 127.0.0.1 -u router -p -P 6450 -e "select @@hostname;"

Enter password:

+-----------------+

| @@hostname      |

+-----------------+

|           Node3 |

+-----------------+

In conclusion, using MySQL Router in the InnoDB cluster makes it easy to direct queries effectively. This not only improves how the database performs but also ensures that the system is responsive and robust. MySQL Router adds an efficient layer to the InnoDB cluster, making it simpler to manage and enhancing the overall performance of the database.

Interested in learning more about how MySQL Router can optimize your database traffic and improve performance? Mydbops offers a comprehensive suite of opensource database managed and consulting services. Reach out to our team at info@mydbops.com to discuss your specific needs. Contact Us now to get a free consultation.

No items found.