1

I've configured a MySQL InnoDB Cluster for high availability with two servers on a Windows system (Server 1: h01xxxxxxxxx1, IP: 10.xxx.xx.61, Server 2: h01xxxxxxxxx2, IP: 10.xxx.xx.71), running smoothly under the cluster name 'H01xxxxxE.'. When one instance goes down, the other takes charge and becomes primary. Here's my current cluster status:

**MySQL  h01xxxxxxxx1:3306 ssl  JS > cluster.status();**
{
    "clusterName": "H01xxxxxE",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "h01xxxxxxxx1:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "h01xxxxxxxx1:3306": {
                "address": "h01xxxxxxxx1:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.34"
            },
            "h01xxxxxxx2:3306": {
                "address": "h01xxxxxxx2:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.34"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "h01xxxxxxx1:3306"
}

To enhance the high availability of my setup, I'm now seeking to ensure the availability of my MySQL Router. My objective is to create a robust, high-availability setup by installing a second MySQL Router on the second instance. The aim is to have them work together, monitoring each other's health, and automatically taking over if one router fails. After doing some research, I found a potential solution: clustering the routers together. Here's what I've done so far:

  1. Ensured that MySQL Routers are correctly installed and running on each instance.
  2. Configured each router with its own unique configuration file (mysqlrouter.conf). Here are snippets of the important parameters:

First instance

# File automatically generated during MySQL Router bootstrap
[DEFAULT]
logging_folder=C:/ProgramData/MySQL/MySQL Router/log
runtime_folder=C:/ProgramData/MySQL/MySQL Router/run
data_folder=C:/ProgramData/MySQL/MySQL Router/data
keyring_path=C:/ProgramData/MySQL/MySQL Router/data/keyring
master_key_path=C:/ProgramData/MySQL/MySQL Router/mysqlrouter.key
connect_timeout=5
read_timeout=30
dynamic_state=C:/ProgramData/MySQL/MySQL Router/data/state.json
client_ssl_cert=C:/ProgramData/MySQL/MySQL Router/data/router-cert.pem
client_ssl_key=C:/ProgramData/MySQL/MySQL Router/data/router-key.pem
client_ssl_mode=PREFERRED
server_ssl_mode=AS_CLIENT
server_ssl_verify=DISABLED
unknown_config_option=error

[logger]
level=INFO

[metadata_cache:bootstrap]
cluster_type=gr
router_id=1
user=mysql_router1_zw288yxxxxx
metadata_cluster=H01xxxxxE
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0


[routing:bootstrap_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://H01xxxxxE/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:bootstrap_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://H01xxxxxE/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

Second instance

# File automatically generated during MySQL Router bootstrap
[DEFAULT]
logging_folder=C:/ProgramData/MySQL/MySQL Router/log
runtime_folder=C:/ProgramData/MySQL/MySQL Router/run
data_folder=C:/ProgramData/MySQL/MySQL Router/data
keyring_path=C:/ProgramData/MySQL/MySQL Router/data/keyring
master_key_path=C:/ProgramData/MySQL/MySQL Router/mysqlrouter.key
connect_timeout=5
read_timeout=30
dynamic_state=C:/ProgramData/MySQL/MySQL Router/data/state.json
client_ssl_cert=C:/ProgramData/MySQL/MySQL Router/data/router-cert.pem
client_ssl_key=C:/ProgramData/MySQL/MySQL Router/data/router-key.pem
client_ssl_mode=PREFERRED
server_ssl_mode=AS_CLIENT
server_ssl_verify=DISABLED
unknown_config_option=error

[logger]
level=INFO

[metadata_cache:bootstrap]
cluster_type=gr
router_id=2
user=mysql_router2_a5sng6xxxxxx
metadata_cluster=H01xxxxxE
ttl=0.5
auth_cache_ttl=-1
auth_cache_refresh_interval=2
use_gr_notifications=0

[routing:bootstrap_rw]
bind_address=0.0.0.0
bind_port=6450
destinations=metadata-cache://H01xxxxxE/?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:bootstrap_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://H01xxxxxxE/?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

Each router needs a unique 'router_id' and 'user'.

The 'metadata_cluster' in both routers should match the cluster name, which is "H01MYSQLE."

In the [routing:bootstrap_rw] section, the 'destinations' line should also match the cluster name.

The 'bind_port' in [routing:bootstrap_rw] must be unique port number and not used by other routing sections.

These configurations ensure the routers collaborate effectively to provide high availability for database connections."

Now, I've reached a point where I need to test failover in my setup to check if it's working. But before that, I needed to know which MySQL Router is currently the primary one. To identify the primary router, I used the command “SELECT @@global.read_only”. Here's the problem:

When I connect directly to the MySQL instances without using routers, I correctly see one instance as primary (read-write) and the other as not primary (read-only).

First Node:

MySQL  h01xxxxxxxxx1:3306 ssl  SQL > SELECT @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.0003 sec)

Second Node:

MySQL  h01xxxxxxxxx2:3306 ssl  SQL > SELECT @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.0004 sec)

However, when I connect via the routers (e.g., h01xxxxxxxxx1:6446 for primary and h01xxxxxxxxx2:6450 for secondary), I see both as primary (read-write), which is not the expected behavior.

First Node:

MySQL  h01xxxxxxxxx1:6446 ssl  SQL > SELECT @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.0008 sec)

Second Node:

MySQL  h01xxxxxxxxx2:6450 ssl  SQL > SELECT @@global.read_only;
+--------------------+


| @@global.read_only |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.0005 sec)

In a correctly functioning setup, I should observe different results for @@global.read_only depending on whether I connect to the primary or secondary router.

What could be causing both routers to appear as primary (read-write) when connected via the routers? Is there a specific step, parameter, or configuration that I might have overlooked, preventing proper failover recognition and primary router identification?

Are there alternative best practices and methods for achieving high availability with MySQL Routers on Windows.

1 Answer 1

0

I think MySQL Router does not have active-passive architecture. To ensure the availability of your MySQL Router, you can setup 2 VMs run MySQL Router and setup keepalived service for both. So, you can connect to database through a Virtual Network. I think this solution can ensure the availability for MySQL Router.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.