So, as you’ve noticed, I’ve been playing around with MySQL Router v8.4.3 and read-write splitting, and now I’ve come across some issues that my environment has generated and I’ve opened some bugs against the fiend:

Bug 116950 – Router Read-Write splitting config causes PHP RSET_HEADER error

Bug 116951 – mysqlrouter Error parsing stats_updates_frequency errors

Bug 116952 – Router w/ RW split causes ERROR: 4501 if sql script contains comments lines (“– “)

(Yup, I prepared each bug description, repeatable tasks and suggested fix …

I’m just trying out the v8.4 Read-Write configuration for MySQL Router, that comes default in the community edition and, as they say, TL;DR “it’s default upon bootstrap”.

What does this mean and how can I go about it?

The overview is simple:

• Either install or upgrade your existing MySQL Router to v8.4 at least.
• Bootstrap the Router.
• Use the port 3310 or change it to fit your needs.

Let’s get to it:

Stop your running router:

systemctl stop mysqlrouter

Copy the old config, just in case:

cp /etc/mysqlrouter/mysqlrouter.conf /etc/mysqlrouter/mysqlrouter_v80.conf

I normally keep my mysql linux repository disabled so no “yum update” takes over version control and gives me a nasty surprise afterwards. As so, I uncomment the “mysql” entry:

vi /etc/yum.conf …

Different ways to get the status:

mysqlsh --login-path=icadmin -h$MYROUTER1 -- cluster status

mysqlsh --login-path=icadmin -h${HOSTNAME} --redirect-primary -- cluster status

mysqlsh icadmin:'P4ssw0rD'@db01:3306 -- cluster status --extended=0

mysqlsh icadmin@$MYROUTER1:3306 -- cluster status --extended=1

watch -n 5 “mysqlsh --login-path=icadmin -h$MYROUTER1 -- cluster status”

Set the Primary Instance (switching):

mysqlsh --login-path=icadmin -h${HOSTNAME} --redirect-primary -- cluster set_primary_instance "db01"

Obtaining MySQL InnoDB Cluster basics:

select cluster_id, cluster_name, description, cluster_type, primary_mode, clusterset_id from mysql_innodb_cluster_metadata.clusters;

Members of our cluster:

select * from performance_schema.replication_group_members order by MEMBER_ROLE;

Local & Remote Trans Q’s:

 …

Just thought I’d share a script I use daily and helps me redirect my attention if needed.

This is but a mere pointer, guideline and starting point in any task. I just thought I’d share and hope someone else’s day becomes slightly easier thanks to some brief investigation and command tweaking.

Now the really handy thing here is that I only hard code the router01 node name, as I’m using that as a potential endpoint (thinking cloud, XaaS, etc…) where it could also be a VIP, LBR or similar. It’s the entry point so I can query the P_S table error_log so I can get different views and act accordingly.

For example:

• First, give me the InnoDB Cluster ordered server list so I can take a step back from my usual pains and worries, and see the architecture view. And make me type “Y” or similar to move on. Here if there were any server missing, I’d see the summary right away so I don’t really need to …

I thought I’d share some quick intro steps into how we can monitor the MySQL Router.

This can be useful if we’re observing intermittent outages, network packet drops or you’re just not sure if everythings fine in your MySQL InnoDB Cluster.

My scenario: The drupal servers are connecting and sometimes the users are getting connection errors. I don’0t see anything at MySQL server level of any instance nor cluster problem. Let’s review the Routers.

On all MySQL Router servers, double check the config file for the log location and also the log level. At /etc/mysqlrouter/mysqlrouter.conf (default rpm install location):

[DEFAULT]
name=myrouter
user=mysqlrouter
..
..
logging_folder=/routerlog/log
..
[logger]
level=DEBUG
#level=INFO

I’ve changed my logger level to DEBUG which will give you a lot more info about connections and counters so you can see what’s happening …

Some time ago, we saw how we could deploy WordPress on OCI using MySQL HeatWave Database Service with Read Replicas. We had to modify WordPress to use a specific plugin that configures the Read/Write Splitting on the application (WordPress): LudicrousDB.

Today, we will not modify WordPress to split the Read and Write operations, but we will use MySQL Router 8.2.0 (see [1], [2], [3]).

Architecture

The …

We’ve seen in previous articles what Transparent Read/Write Splitting is with MySQL 8.2 and how to use it with MySQL Connector/Python.

I love this new feature, but I was wondering if it was really worth it.

Will the application benefit from offloading reads to another node, and won’t using the MySQL Router and parsing the request slow down connections?

These are the kinds of questions I’d like to cover and answer in this article.

The environment

To perform the test, I use the following environment:

• Linux Kernel 5.15.0 – aarch64
• MySQL Community Server 8.2.0
• MySQL Router 8.2.0
• sysbench 1.1.0 using LuaJIT 2.1.0-beta3
…

As you know, one of the most eagerly waited features was released with MySQL 8.2: the transparent read/write splitting.

In this post, we’ll look at how to use it with MySQL-Connector/Python.

Architecture

To play with our Python program, we will use an InnoDB Cluster.

This is an overview of the cluster in MySQL Shell:

JS > cluster.status()
{
    "clusterName": "fred", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "127.0.0.1:3310", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "127.0.0.1:3310": {
                "address": "127.0.0.1:3310", 
                "memberRole": "PRIMARY", …

When you connect to a server (or cluster) using a TCP proxy level 7, also referred to as an application-level or Layer 7 proxy (highest level of the OSI model), the application doesn’t connect directly to the back-end server(s). The proxy usually understands the protocol used and can eventually take some decisions or even change the request.

The problem when using such proxy (like HA Proxy, ProxySQL and MySQL Router) is that the server doesn’t really know from where the client is connecting. The server sees the IP address of the proxy/router as the source IP of the client.

HA Proxy initially designed the Proxy Protocol, a simple protocol that allows a TCP connection to transport proxy-related information between the client, the proxy server and the destination server. The main purpose of the Proxy Protocol is then to preserve the client’s original IP address (with some others metadata). See …

I recently received several messages from developers not being comfortable with command line when they need to create schemas, tables, … They were asking me for an easy alternative when testing MDS.

There is a very popular tool in the developer community to manage their databases in MySQL: PHPMyAdmin.

So if you are trying MDS (MySQL Database Service in Oracle Cloud Infrastructure), it’s possible to deploy such instance with a compute instance hosting PHPMyAdmin very easily using OCI’s Resource Manager and a stack (zip file) that you can find here: https://github.com/lefred/oci-phpmyadmin-mds/releases/tag/1.1.0

If you want to try MDS in OCI, you can get free credits using this url: …