mysqlrouter service is running ports are listening tcp 0 0 0.0.0.0:6446 0.0.0.0:* LISTEN 6150/mysqlrouter tcp 0 0 0.0.0.0:6447 0.0.0.0:* LISTEN 6150/mysqlrouter tcp 0 0 0.0.0.0:6448 0.0.0.0:* LISTEN 6150/mysqlrouter tcp 0 0 0.0.0.0:6449 0.0.0.0:* LISTEN 6150/mysqlrouter tcp 0 0 0.0.0.0:8443 0.0.0.0:* LISTEN 6150/mysqlrouter but when I try to connect using mysql client from different computer mysql -u innodbcluster -h 192.168.11.124 -P 6446 -p it says > ERROR 2003 (HY000): Can't connect to MySQL server on > '192.168.11.124:6446' (10060) what am I missing? PS I installed the mysqlrouter in the primary mysqlserver am I correct or it should be installed in the application thats going to connect to the innodbcluster?

I have set up an InnoDB cluster with one primary(R/W) and two secondaries (R/O) and bootstrapped a MySQL Router. The router has 2 port 6646(R/W port) and 6647(R/O) port. My application is currently connected to the cluster through the R/W port. Now I am looking for a way to implement load balancing in the cluster. Suppose there are 2 reading requests, how can I route the traffic to a different available database?

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.

I have three MySQL servers running with group replication and inside a InnoDB Cluster. I have two app servers that will be connecting to the cluster and I need to configure mysql router on both app servers. When I run router on server1 I use the --bootstrap option. When I try to do it on server2 with --bootstrap I get an error >a router instance named has been previously configured on this host. My question is do I need to start mysql router with --bootstrap option? Or since it will be configured on two servers --bootstrap option is not required? Do I need to start mysql router with a personalized conf file?

I have 4 MySQL-Servers (Version: 8.0.40) set up in a multi-primary group replication configuation. Recently, I added MySQL Router (Version 8.0.4) in order to implement failover to the other servers in case the MySQL-Server on the localhost becomes unavailable. The failover part using the routing-strategy "first-available" works flawlessly. However, what doesn't work flawlessly is reverting back to the first MySQL-Server in the list after failover. It sticks with the second server in the list. When the second server becomes unavailable, it'll failover to the third server in the list. In other words: It mimicks the behaviour of the routing_strategy "next-available". > **first-available**: the new connection is routed to the first available > server from the destinations list. In case of failure, the next > available server is used. This cycle continues until all servers are > unavailable. > > **next-available**: like first-available, in that the new connection is > routed to the first available server from the destinations list. > Unlike first-available, if a server is marked as unreachable then it > gets discarded and is never used again as a destination. MySQL Router Doc I have also tried the "next-available" option just to make sure that the doc doesn't have it backwards. But in my current setting those two options appear to behave exactly the same way. The relevant section in my config file looks like this: > [routing:simple_redirect] > > bind_address = 127.0.0.1:7001 destinations = 127.0.0.1:3306, > server2.com:3306, server3.com:3306, server4.com:3306 routing_strategy > = first-available connect_timeout = 5 Does anyone know what the culprit might be? I tried uninstalling and re-installing the MySQL-Router, tried using the now deprecated mode setting and read the docs to no end. I also installed MySQL Router 9.1.0 directly from the .deb package to see if this was a bug that they fixed in the last major release. So far no changed behaviour. Is it possible that MySQL Router "sees" that the currently failed-over server is marked as a Primary in the replication scheme and decides to stick with it? From what I read in the Docs, such behaviour should only work for a properly configured Cluster. However, this is not a cluster, just a very simple group replication.

I'm running MySQL 8.0.37 on three different servers, configured as a master with two slaves. All of the versions are exactly the same. My applications connect to the cluster via [MySQL's Community Router](https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-installation-docker.html) . My replication system uses GTID replication, so that I can manage the cluster with MySQL Shell. Every once in a while after the pods are recreated I get Error 1827 on the two slaves about creating a "router" username/password. This causes replication to stop (but doesn't prevent select requests from continuing to hit that slave, causing old data to get returned). My current workaround is to skip those transactions individually on each slave using the SET GTID_NEXT command. I've tried setting print_identified_with_as_hex=1 which doesn't prevent the error either. It seems that the query being attempted works on the master but not on any of the slaves. It also seems to be transferring passwords in plaintext, which probably violates some security threshold. How do I prevent this from happening? Is it a problem with my Replication settings, or my MySQL Router settings?

I have set up a set of three MySQL 8.0.17 databases using group replication. They are all on my Windows 10 PC, using ports 3306,3307,3308. I am now trying to set up mysqlrouter. I have installed it in c:/mysqlrouter/. But when I run: mysqlrouter --bootstrap root@localhost:3306 I simply get: Error: Unable to connect to the metadata server: Error connecting to MySQL server at 127.0.0.1:3306: Lost connection to MySQL server at 'waiting for initial communication packet', system error: 10060 (2013) This is the same on all three ports, and using each of the different users (localhost, 127.0.0.1 etc) I have confirmed that the MySQL user exists (I have root @ localhost / 127.0.0.1 / ::1 / % all set up) and I can login to MySQL using each of them. Is there something I need to configure? I can't see anything obvous in the documentation?

Memory is slowly used by mysql router. I am running mysql router version 8.0.20 (I found there was a memory leak on mysql router 2.1.2 but mysql router 8.x is newer) I have a 3 node single-primary cluster on MySQL 5.7.30 with 13 application servers pointing to it from localhost and 1 server dedicated to mysql-router to developers. Each application server has its own mysql router locally configured as well as a tomcat server. There are about 14 mysql routers pointed at this cluster. All mysql routers are configured the same. mysqlrouter.conf [DEFAULT] user=mysqlrouter logging_folder=/var/log/mysqlrouter runtime_folder=/etc/mysqlrouter/run data_folder=/etc/mysqlrouter/data keyring_path=/etc/mysqlrouter/data/keyring master_key_path=/etc/mysqlrouter/mysqlrouter.key connect_timeout=15 read_timeout=30 dynamic_state=/etc/mysqlrouter/data/state.json [logger] level = INFO [metadata_cache:prod1] cluster_type=gr router_id=5 user=mysql_router5_qgi8an1mm3s9 metadata_cluster=prod1 ttl=0.5 use_gr_notifications=0 [routing:prod1_rw] bind_address=0.0.0.0 bind_port=6446 destinations=metadata-cache://prod1/?role=PRIMARY routing_strategy=first-available protocol=classic [routing:prod1_ro] bind_address=0.0.0.0 bind_port=6447 destinations=metadata-cache://prod1/?role=SECONDARY routing_strategy=round-robin-with-fallback protocol=classic [routing:prod1_x_rw] bind_address=0.0.0.0 bind_port=64460 destinations=metadata-cache://prod1/?role=PRIMARY routing_strategy=first-available protocol=x [routing:prod1_x_ro] bind_address=0.0.0.0 bind_port=64470 destinations=metadata-cache://prod1/?role=SECONDARY routing_strategy=round-robin-with-fallback protocol=x The memory allocated to mysql router on these 13 production servers continues to grow until it crashes the server. Currently one is using 3gb of ram. If I restart the server it frees up the memory. It seems to randomly go through these 13 and start flaring up. The one dedicated to developers, only running mysql router has not had an issue. On average the mysql router grows in memory usage by 1gb a week. About every three weeks I need to restart mysql router on these servers so that the tomcat server does not crash. Here is the mysqlconf from the developer machine #File automatically generated during MySQL Router bootstrap [DEFAULT] user=mysqlrouter logging_folder=/var/log/mysqlrouter runtime_folder=/etc/mysqlrouter/run data_folder=/etc/mysqlrouter/data keyring_path=/etc/mysqlrouter/data/keyring master_key_path=/etc/mysqlrouter/mysqlrouter.key connect_timeout=15 read_timeout=30 dynamic_state=/etc/mysqlrouter/data/state.json [logger] level = INFO [metadata_cache:prod1] cluster_type=gr router_id=1 user=mysql_router2_6tobldcfht67 metadata_cluster=prod1 ttl=0.5 use_gr_notifications=0 [routing:prod1_rw] bind_address=0.0.0.0 bind_port=6446 destinations=metadata-cache://prod1/?role=PRIMARY routing_strategy=first-available protocol=classic [routing:prod1_ro] bind_address=0.0.0.0 bind_port=6447 destinations=metadata-cache://prod1/?role=SECONDARY routing_strategy=round-robin-with-fallback protocol=classic [routing:prod1_x_rw] bind_address=0.0.0.0 bind_port=64460 destinations=metadata-cache://prod1/?role=PRIMARY routing_strategy=first-available protocol=x [routing:prod1_x_ro] bind_address=0.0.0.0 bind_port=64470 destinations=metadata-cache://prod1/?role=SECONDARY routing_strategy=round-robin-with-fallback protocol=x

I have innodb cluster setup with 3 mysql instances. Each instance is on different server and each server has mysql-router also. I would like each mysql-router on each server to try to connect to the mysql instance on the same server. Is this possible? Currently all mysql-router will check the "destinations=metadata-cache://mycluster/default?role=PRIMARY" option and try to connect to the top server. Is there a way so each mysql-router trys to first try to connect to different instances? (the instance on the same server as mysql-router)

I configure sql router under three machines and found if I use simple command mysql -u ia -pTest@123 --port=7001 then its not applying round rubin connection request but when i use with protocol parameter then its working fine "mysql -u ia -pTest@123 --port=7001 --protocol=TCP" I want to connect with normal command like mysql -u ia -pTest@123 --port=7001 and round rubin should work in this case. === Mysql Configuration [logger] level = INFO [routing:secondary] bind_address = localhost bind_port = 7001 destinations = 192.168.0.187:3306,192.168.0.190:3306 routing_strategy = round-robin [routing:primary] bind_address = localhost bind_port = 7002 destinations = 192.168.0.186:3306 routing_strategy = first-available (edited)