MySQL Forums - Router & Proxy

MySQL Router Failover - session on previous router existed in DB (no replies)

Nordiana Mohd Yusof — Wed, 26 Nov 2025 02:43:20 +0000

Would like to seek your input on the following behavior observed in our MySQL environment.

Our setup is as follows:
-MySQL Router configured with Keepalived (VIP) for high availability
-Database connection timeout configured to 10 minutes
-MySQL InnoDB Cluster as the backend
-Max connection for 1 router - 900, max connection for DB 1000

During a router failover, we observed that the existing SLEEP sessions on the database remain active, even though the router has already failed over to the secondary node. Which contribute to the max db connection.

Is this the expected behaviour for MySQL Router and InnoDB Cluster? Specifically:
-Does a MySQL Router failover inherently leave existing SLEEP sessions on the database intact until wait_timeout/interactive_timeout is reached?
-Is there any recommended configuration or best practice to automatically clean up or minimise these SLEEP connections during or after router failover?

Your clarification will help us validate our understanding and tune our configuration accordingly.

Inquiry: Expected Behaviour of DB Connections During MySQL Router Failover (no replies)

Nordiana Mohd Yusof — Wed, 26 Nov 2025 01:59:35 +0000

Asynch replication through MySQL ROuter (no replies)

Dimiter Dimitriev — Tue, 22 Apr 2025 09:16:34 +0000

Hi,

The router source code contains explicit handling for forwarding classical replication registration commands (classic_register_replica_forwarder.cc
etc.). Basic tests also show it works - but I couldn't find much in the way of documentation for this feature.

Is it supported for replicas to run CHANGE REPLICATION SOURCE with source_host=? If so, does it work correctly if automatic asynchronous source
fail-over is active? E.g., would the router pass through the group member addresses as alternative asynch replication sources to the replica, even though the switching between them should be done by the router?

Dimiter

Configurando Router (no replies)

DTI CREAGO DTI — Wed, 15 Jan 2025 19:30:40 +0000

Ola pessoal, sou novo na área. Configurei um cluster e um router, a replicação esta funcionando ok.
Meu cluster possui 3 instâncias, a instância de R/W esta na porta 6446 e as 2 de R/O esta na porta 6447.

Ao desenvolver aplicações preciso especificar o ip do router e a porta. Porém todas as aplicações que são desenvolvidas podem ler e escrever dependendo da requisição. Há uma maneira de configurar o router pra uma porta só e ao receber a requisição ele analisa se deve ser encaminhada para o servidor de R/W ou para algumas das instancia R/O? Ou caso eu usar a porta 6446 em todas as aplicações, as requisições de apenas leitura seram redirecionadas às instancias de R/O ou tudo irá ser processado na instancia de R/W ja que eu usei sua porta?

Closed connection before finishing handshake (no replies)

Fahd Bahoo — Sun, 24 Nov 2024 16:34:55 +0000

getting this error on MySQL Router

2024-11-22 12:24:03 routing INFO [7fe0c2082700] [routing:bootstrap_rw] 10.250.10.10:34688 closed connection before finishing handshake

2024-11-22 12:24:03 routing INFO [7fe0bb7fe700] [routing:bootstrap_rw] incrementing error counter for host of 10.250.10.10:34704 (now 2)

i still don't know why its happening and what is the impact of this on application and how to solve it. any one please guide

Primary switch zero-downtime architecture? (no replies)

Stefan Hoffmeister — Sat, 28 Sep 2024 15:27:28 +0000

I am struggling to find a zero-downtime setup for a MySQL InnoDB cluster.

As a lab installation of MySQL Router in front of a three-node MySQL InnoDB cluster, I use the stack at https://github.com/garutilorenzo/mysql-innodb-cluster

This works wonderfully, with one exception:

If I test a MySQL primary node switch under (heavy) write-load, clients get the error message "SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --read-only option so it cannot execute this statement"

From what I can tell, this specific error message is caused by an architectural problem in how this whole system is set up: MySQL Router is configured with a ttl, i.e. repeatedly _polls_ cluster for its (primary/secondary) topology. Once it has learnt about any change to the primary, it does redirect - but not before clients get the error message above.

Is there any means to have a server-side architecture which prevents this
* with MySQL Router
* using some alternative architecture
?

One of the implications of the current set up that a primary node switch will always disrupt clients, in a rather annoying way.

(On the client side, dropping that connection and retrying is the obvious solution; I am interested in making the server-side architecture friendlier towards clients.)

Many thanks!

router bootstrap sets all nodes to RO (no replies)

Clay Jackson — Fri, 06 Sep 2024 05:17:57 +0000

I'm trying to set up router in a 3 node cluster. I have the cluster set up and it looks like this
{
"clusterName": "N7QNMCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "mysql1:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"mysql1:3306": {
"address": "mysql1:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.4.2"
},
"mysql2:3306": {
"address": "mysql2:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.4.2"
},
"mysql3:3306": {
"address": "mysql3:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": "applier_queue_applied",
"role": "HA",
"status": "ONLINE",
"version": "8.4.2"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "mysql1:3306"

But when I bootstrap the router, I see this in the log:
2024-09-05 21:28:22 routing INFO [75e4f60006c0] [routing:bootstrap_rw] started: routing strategy = first-available
2024-09-05 21:28:22 routing INFO [75e4f60006c0] Start accepting connections for routing routing:bootstrap_rw listening on '0.0.0.0:6446'
2024-09-05 21:28:22 routing INFO [75e4f6a006c0] Start accepting connections for routing routing:bootstrap_ro listening on '0.0.0.0:6447'
2024-09-05 21:28:22 routing INFO [75e4f56006c0] Start accepting connections for routing routing:bootstrap_x_ro listening on '0.0.0.0:6449'
2024-09-05 21:28:22 routing INFO [75e4f6a006c0] Start accepting connections for routing routing:bootstrap_ro listening on '/tmp/router/mysqlro.sock'
2024-09-05 21:28:22 routing INFO [75e4f4c006c0] Start accepting connections for routing routing:bootstrap_x_rw listening on '0.0.0.0:6448'
2024-09-05 21:28:22 routing INFO [75e4f56006c0] Start accepting connections for routing routing:bootstrap_x_ro listening on '/tmp/router/mysqlxro.sock'
2024-09-05 21:28:22 routing INFO [75e4f60006c0] Start accepting connections for routing routing:bootstrap_rw listening on '/tmp/router/mysql.sock'
2024-09-05 21:28:22 routing INFO [75e4f4c006c0] Start accepting connections for routing routing:bootstrap_x_rw listening on '/tmp/router/mysqlx.sock'
2024-09-05 21:28:22 metadata_cache INFO [75e521fff6c0] Connected with metadata server running on mysql1:3306
2024-09-05 21:28:22 metadata_cache INFO [75e521fff6c0] Potential changes detected in cluster after metadata refresh (view_id=0)
2024-09-05 21:28:22 metadata_cache INFO [75e521fff6c0] Metadata for cluster 'N7QNMCluster' has 3 member(s), single-primary:
2024-09-05 21:28:22 metadata_cache INFO [75e521fff6c0] mysql1:3306 / 33060 - mode=RO
2024-09-05 21:28:22 metadata_cache INFO [75e521fff6c0] mysql2:3306 / 33060 - mode=RO
2024-09-05 21:28:22 metadata_cache INFO [75e521fff6c0] mysql3:3306 / 33060 - mode=RO

And I can connect to the RO port; but get this error when I connect to the RW port:

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1:6446' (111)

Anu ideas appreciated!

mysqlrouter won't run (2 replies)

Clay Jackson — Tue, 24 Sep 2024 14:56:29 +0000

I'm trying to set up mysqlrouter for the first time on an ubuntu system. I did the Oracle apt dpkg configuration and the apt install mysqlrouter - but when I execute mysqlrouter from the command line it just immediately returns, no messages, no log files, nothing!

Help!

Router bootstrap account question (1 reply)

Silvio Schloeffel — Fri, 09 Aug 2024 10:43:17 +0000

Hello,

it is our first contact with MySQL router, so apologies in advance if the question is too simple.

We test the migration from a MySQL 5.x Master - Master replication setup to a MySQL 8.x Innodb cluster.

The cluster is running, data transfer tests are done and now we think about some routers in front of the cluster.

We created a router user for our first router testsystem and tried to bootstrap the router.
And here the question starts.

The cluster is configured with ip rules for the root and admin users.
Also the created router user is bound to the system ip of the router server.
```
MySQL 10.234.16.40:33060+ ssl JS > info.setupRouterAccount('router-01@10.234.16.46')
Missing the password for new account router-01@10.234.16.46. Please provide one.
Password for new account: ************
Confirm password: ************
Creating user router-01@10.234.16.46.
Account router-01@10.234.16.46 was successfully created.
```
Our thinking was that we can bootstrap the router with the created router user but this wasn't working.

```
[root@blade-server-10 etc]# mysqlrouter --bootstrap router-01@db-01:3306 --directory /etc/mysqlrouter/ --user=mysqlrouter
Please enter MySQL password for router-01:

Bootstrapping MySQL Router 8.0.36 (MySQL Community - GPL) instance at '/etc/mysqlrouter'...

Fetching Cluster Members
trying to connect to mysql-server at db-03:3306

Creating account(s) (only those that are needed, if any)
Error: Error creating MySQL account for router (CREATE USER stage): Error executing MySQL query "CREATE USER IF NOT EXISTS 'mysql_router1_b113lfcw8rpz'@'%' IDENTIFIED BY ***": Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation (1227)
```

With the cluster admin user the boostraping is working but for productive use I will not open the cluster admin for worldwide connections.

Is there a secure way to bootstrap the router without needing the cluster admin account?
We want to install routers dynamically on various rented systems and configure the cluster as securely as possible for this purpose.

Best

Silvio

MySQL Router with MRS Support (1 reply)

Scott Klakken — Wed, 26 Jun 2024 14:12:41 +0000

I've been able to setup MySQL Router 8.0 and 8.4 with REST support but am unable to complete my installation because mysqlrouter_bootstrap isn't available on some instances of the router.

Reading the documentation in the MRS Developer's Guide, mysqlrouter_bootstrap command is required to bootstrap MRS (MySQL Rest Services) i.e.: mysqlrouter_bootstrap dba@127.0.0.1:3306 --mrs --directory ~/.mysqlrouter

In reviewing the mysql-router image/container for both 8.0 and 8.4, the command is not present in the filesystem. In addition, mysqlrouter_bootstrap present on my windows 8.0 version but not on the 8.4 version.

How do I invoke MRS on the router when the mysqlrouter_bootstrap command is missing? Do I need to download the command or is there a new method for bootstrapping MRS?

when update/insert, errors: Error 1064: prepared statements not allowed with access_mode = 'auto' (no replies)

Bing Ma — Mon, 22 Jan 2024 06:56:28 +0000

update/insert statemenets meet the error:
Error 1064: prepared statements not allowed with access_mode = 'auto'

MySQL: Transparent read/write splitting (no replies)

Edwin Desouza — Thu, 26 Oct 2023 17:46:16 +0000

MySQL: Transparent read/write splitting
- https://blogs.oracle.com/mysql/post/mysql-82-transparent-readwrite-splitting

Introducing JSON/Relational Duality for MySQL REST Service (no replies)

Edwin Desouza — Thu, 19 Oct 2023 19:15:38 +0000

Introducing JSON/Relational Duality for MySQL REST Service
- https://blogs.oracle.com/mysql/post/introducing-jsonrelational-duality-for-mysql-rest-service

MySQL Router increment error (no replies)

Kai Koppelmann — Fri, 11 Aug 2023 09:09:56 +0000

Hello,

I have a problem using MySQL Router on Windows Server systems (Linux works well). Version is 8.0.32 (tested with 8.0.34 as well).
Server plattforms are Server 2012 & 2019 (problem on both versions).

I bootstrapped a Router Instance on a single VM.
Bootstrapping worked like a charm:

mysqlrouter --bootstrap="" --conf-base-port=7446 --directory="C:\OracleMySQL\MySQLRouter80\Cluster-Production" --name="Cluster-Production" --account=mysqlrouter --account-create=always

The server itself starts correctly:
2023-08-11 10:52:53 io INFO [264c] starting 4 io-threads, using backend 'poll'
2023-08-11 10:52:53 http_server INFO [264c] listening on 0.0.0.0:8443
2023-08-11 10:52:53 metadata_cache_plugin INFO [2614] Starting Metadata Cache
2023-08-11 10:52:53 routing INFO [357c] [routing:bootstrap_ro] started: routing strategy = round-robin-with-fallback
2023-08-11 10:52:53 metadata_cache INFO [2614] Connections using ssl_mode 'PREFERRED'
2023-08-11 10:52:53 metadata_cache INFO [2ef4] Starting metadata cache refresh thread
2023-08-11 10:52:53 routing INFO [2be4] [routing:bootstrap_x_ro] started: routing strategy = round-robin-with-fallback
2023-08-11 10:52:53 routing INFO [2be4] Start accepting connections for routing routing:bootstrap_x_ro listening on 7449
2023-08-11 10:52:53 routing INFO [35d8] [routing:bootstrap_x_rw] started: routing strategy = first-available
2023-08-11 10:52:53 routing INFO [35d8] Start accepting connections for routing routing:bootstrap_x_rw listening on 7448
2023-08-11 10:52:53 routing INFO [1f14] [routing:bootstrap_rw] started: routing strategy = first-available
2023-08-11 10:52:53 routing INFO [1f14] Start accepting connections for routing routing:bootstrap_rw listening on 7446
2023-08-11 10:52:53 routing INFO [357c] Start accepting connections for routing routing:bootstrap_ro listening on 7447
2023-08-11 10:52:53 metadata_cache INFO [2ef4] Connected with metadata server running on VOMQMGTP0001.t1r.afomcs.com:3306
2023-08-11 10:52:53 metadata_cache INFO [2ef4] Potential changes detected in cluster after metadata refresh (view_id=0)
2023-08-11 10:52:53 metadata_cache INFO [2ef4] Metadata for cluster 'A41_DBS_Cluster_Icinga_Production_Site_A' has 1 member(s), single-primary:
2023-08-11 10:52:53 metadata_cache INFO [2ef4] :3306 / 33060 - mode=RW

Only one active member is intended at this point of the project.
Then I get error messages every 2-3 minutes logged in the MySQL Router log:

2023-08-11 10:57:16 routing INFO [34d4] [routing:bootstrap_rw] 127.0.0.1:51378 closed connection before finishing handshake
2023-08-11 10:57:16 routing INFO [34d4] [routing:bootstrap_rw] incrementing error counter for host of 127.0.0.1:51378 (now 1)
2023-08-11 10:57:43 routing INFO [3374] [routing:bootstrap_rw] 127.0.0.1:51450 closed connection before finishing handshake
2023-08-11 10:57:43 routing INFO [3374] [routing:bootstrap_rw] incrementing error counter for host of 127.0.0.1:51450 (now 2)
2023-08-11 10:58:27 routing INFO [326c] [routing:bootstrap_rw] 127.0.0.1:51558 closed connection before finishing handshake
2023-08-11 10:58:27 routing INFO [326c] [routing:bootstrap_rw] incrementing error counter for host of 127.0.0.1:51558 (now 3)
2023-08-11 10:58:53 routing INFO [204c] [routing:bootstrap_rw] 127.0.0.1:51630 closed connection before finishing handshake
2023-08-11 10:58:53 routing INFO [204c] [routing:bootstrap_rw] incrementing error counter for host of 127.0.0.1:51630 (now 4)
2023-08-11 11:00:04 routing INFO [34d4] [routing:bootstrap_rw] 127.0.0.1:51799 closed connection before finishing handshake
2023-08-11 11:00:04 routing INFO [34d4] [routing:bootstrap_rw] incrementing error counter for host of 127.0.0.1:51799 (now 5)

What I did so far:
- I started the Router using Verbose mode, no further information is logged.
- I changed the plugin for the created user "mysqlrouter" from mysql_native_password (seems to be the default in my case" to caching_sha2_password
- I bootstrapped into another directory on another drive
- I checked I bootstrapped using a cmd prompt ran as Administrator
- I checked the Connection Control Plugin - It tracks these entries:
''@''
The user part is empty - is that normal?
- I updated version 8.0.32 to 8.0.34

I am glad for every single idea or hint how to solve this issue.

Many regards
Kai

problem with docker build for mysql router (no replies)

Dennis Whipstock — Wed, 07 Jun 2023 15:37:53 +0000

nevermind. :). can someone delete this?

MySQL Router: How to get client’s IP address? (no replies)

Edwin Desouza — Thu, 04 May 2023 16:54:13 +0000

MySQL Router: How to get client’s IP address?
https://blogs.oracle.com/mysql/post/client-ip-address

Connexion to database interrupted at router bootstrap (2 replies)

Fred Milonni — Thu, 20 Apr 2023 16:26:55 +0000

Hello to all

I use my mysql-router 8.0.32 on Redhat 8.4, the remote dabase is a v8 cluster

Here is the error :

# mysqlrouter --bootstrap myuser@dbp01:3306 --conf-bind-address 127.0.0.1 --account myuser --user mysqlrouter --account-create never --force
Please enter MySQL password for myuser:
# Bootstrapping system MySQL Router instance...

Please enter MySQL password for myuser:
- Using existing certificates from the '/var/lib/mysqlrouter' directory
- 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 /etc/mysqlrouter/mysqlrouter.conf
Error: Error executing MySQL query "select view_id from mysql_innodb_cluster_metadata.v2_ar_members where CAST(member_id AS char ascii) = CAST(@@server_uuid AS char ascii)": Lost connection to MySQL server during query (2013)
#

In the database, I can see:

Aborted connection 5216418 to db: 'unconnected' user: 'myuser' host: 'vm43a' (Got an error reading communication packets)

The user myuser can connect remotely with mysql> without issue and the query is ok

What could be the cause please ? Thank you

MySQL REST Service (no replies)

Edwin Desouza — Wed, 15 Feb 2023 22:36:43 +0000

Introducing the MySQL REST Service:
- https://blogs.oracle.com/mysql/post/introducing-the-mysql-rest-service

Is there an example of Connection Sharing released in MySQL Router 8.0.32 (no replies)

place rock — Fri, 27 Jan 2023 08:29:59 +0000

The Connection Sharing document states that SQL statements that rely on the previous session state will not work when Connection Sharing is enabled unless it is inside the transaction.

It also does not support user variables, temporary tables, or prepared statements, and even transactions and LOCK TABLES do not support Connection Sharing until they are closed or unlocked.

So, what do they share? I would appreciate it if you could let me know if there is a suitable example.

Thank you

Help understand router conf file and routing mechanism (no replies)

asdsdf bruc — Mon, 19 Dec 2022 01:51:31 +0000

We have run mysqlrouter for several years pointing to 3 nodes((ie. named 1,2,3 )), I add 3 more nodes recently (ie. named 4,5, 6 ) and failover to node 4, and mysqlrouter can normally point to node 4 for RW. My question is: I checked the mysqlrouter conf file and find it still points to original 3 nodes, do I need to correct that manually? If the conf can change to correct nodes automatically after restart mysqlrouter service in next maintenance time?
Thanks in advance.

Unable to bootstrap the mysql router (1 reply)

Haashir Mohamed — Wed, 14 Dec 2022 08:30:15 +0000

Hello,

I have configured the 3 Node Innodb Cluster, which is running fine. And then I tried to install the mysql router (8.0.31) on my App server, its getting the below error. Please let me know what is the issue here.

# MySQL Router configuration file
#
# Documentation is available at
# http://dev.mysql.com/doc/mysql-router/en/

[DEFAULT]
logging_folder = /var/log/mysqlrouter
runtime_folder = /run/mysqlrouter
config_folder = /etc/mysqlrouter

[logger]
level = INFO

[root@eiacwebapp1test mysqlrouter]# cd /etc/mysqlrouter
[root@eiacwebapp1test mysqlrouter]# ls
mysqlrouter.conf
[root@eiacwebapp1test mysqlrouter]# ls -ld /etc/mysqlrouter
drwxr-xr-x 2 root root 30 Dec 14 09:32 /etc/mysqlrouter
[root@eiacwebapp1test mysqlrouter]#
[root@eiacwebapp1test mysqlrouter]# ls -ld /run/mysqlrouter
drwxr-xr-x 2 mysqlrouter mysqlrouter 40 Sep 13 21:57 /run/mysqlrouter
[root@eiacwebapp1test mysqlrouter]#
[root@eiacwebapp1test mysqlrouter]# ls -ld /var/log/mysqlrouter
drwxr-xr-x 2 mysqlrouter mysqlrouter 6 Sep 13 21:57 /var/log/mysqlrouter

[root@eiacwebapp1test mysqlrouter]# mysqlrouter --bootstrap mycluster@172.16.4.42:3306 -d myrouter --user=mysqlrouter
Please enter MySQL password for mycluster:
# Bootstrapping MySQL Router instance at '/etc/mysqlrouter/myrouter'...

Fetching Cluster Members
trying to connect to mysql-server at eiacdb2test:3306
Failed connecting to eiacdb2test:3306: Error connecting to MySQL server at eiacdb2test:3306: Unknown MySQL server host 'eiacdb2test' (-2) (2005), trying next
trying to connect to mysql-server at eiacdb3test:3306
Failed connecting to eiacdb3test:3306: Error connecting to MySQL server at eiacdb3test:3306: Unknown MySQL server host 'eiacdb3test' (-2) (2005), trying next
trying to connect to mysql-server at eiacdb1test:3306
Failed connecting to eiacdb1test:3306: Error connecting to MySQL server at eiacdb1test:3306: Unknown MySQL server host 'eiacdb1test' (-2) (2005), trying next
Error: no more nodes to fail-over too, giving up.
[root@eiacwebapp1test mysqlrouter]#
[root@eiacwebapp1test mysqlrouter]# ls
mysqlrouter.conf
[root@eiacwebapp1test mysqlrouter]# mysqlrouter --bootstrap mycluster@172.16.4.42:3306 -d myrouter --user=root
Please enter MySQL password for mycluster:
# Bootstrapping MySQL Router instance at '/etc/mysqlrouter/myrouter'...

Fetching Cluster Members
trying to connect to mysql-server at eiacdb2test:3306
Failed connecting to eiacdb2test:3306: Error connecting to MySQL server at eiacdb2test:3306: Unknown MySQL server host 'eiacdb2test' (-2) (2005), trying next
trying to connect to mysql-server at eiacdb3test:3306
Failed connecting to eiacdb3test:3306: Error connecting to MySQL server at eiacdb3test:3306: Unknown MySQL server host 'eiacdb3test' (-2) (2005), trying next
trying to connect to mysql-server at eiacdb1test:3306
Failed connecting to eiacdb1test:3306: Error connecting to MySQL server at eiacdb1test:3306: Unknown MySQL server host 'eiacdb1test' (-2) (2005), trying next
Error: no more nodes to fail-over too, giving up.

[root@eiacwebapp1test mysqlrouter]# mysqlrouter --bootstrap root@eiacdb1test --user=mysqlrouter
Please enter MySQL password for root:
Error: Unable to connect to the metadata server: Error connecting to MySQL server at eiacdb1test:0: Unknown MySQL server host 'eiacdb1test' (-2) (2005)
[root@eiacwebapp1test mysqlrouter]#
[root@eiacwebapp1test mysqlrouter]# mysqlrouter --bootstrap mycluster@eiacdb1test --user=mysqlrouter
Please enter MySQL password for mycluster:
Error: Unable to connect to the metadata server: Error connecting to MySQL server at eiacdb1test:0: Unknown MySQL server host 'eiacdb1test' (-2) (2005)

HA for MySQL Router in a Windows environment (no replies)

Randall Chilson — Mon, 31 Oct 2022 19:58:34 +0000

I have an environment in which we need an HA Router and DB Cluster setup in a MS Windows environment. We are a paying member of the MySQL Enterprise DB users. In our current environment we do not have access to MS Windows Cluster support, but could use CISCO VRRP as the Virtual IP mechanism to support the configuration as provided by the previous HA MySQL Router setup example, which uses a Linux environment:

HA for MySQL Router:
- https://lefred.be/content/mysql-innodb-cluster-is-the-router-a-single-point-of-failure/
- https://lefred.be/content/mysql-router-ha-with-pacemaker/
- https://lefred.be/content/mysql-router-ha-with-keepalived/

Are there any equivalent guide(s) that would fit my environment? Or any suggested substitutions that would complete a similar implementation in our environment?

MySQL Router - Active Connections not getting cleaned up/removed (no replies)

Dev Dewboy — Wed, 17 Aug 2022 20:18:36 +0000

Hello.
MySQL Routers (MR) are on a VM, load balanced and traffic routed to the physical DB Servers.
On a MR, I have Active Connections that keep increasing and don't get cleaned up. I eventually have to restart the router because it will hit the Max Connections. Sure I can increase the Max Count to a higher limit but the problem still exists and is only a stop gap. I have been able to locate the issue, IMO, to connections from a specific service using JDBC Connection Pool and in Active Connections list it has blank destination IP.
Using the MySQL Router REST API I have been able to get "Connections" to the router which, represents the active connections. I have an example here from 2 points in time. Earlier in the day, the connection has a destination IP. Later, that same record has no destination IP. Here is an example:
With Destination IP, Queried yesterday at around 9:30am yesterday:{"bytesFromServer":17744,"bytesToServer":26847,"sourceAddress":"10.10.10.201:39878","destinationAddress":"10.10.11.13:3306","timeStarted":"2022-08-16T21:11:28.196537Z","timeConnectedToServer":"2022-08-16T21:11:28.198833Z","timeLastSentToServer":"2022-08-16T21:11:30.991969Z","timeLastReceivedFromServer":"2022-08-16T21:11:30.992773Z"}
Without Destination IP, queried this morning:
{"bytesFromServer":17744,"bytesToServer":26847,"sourceAddress":"10.10.10.201:39878","destinationAddress":"","timeStarted":"2022-08-16T21:11:28.196537Z","timeConnectedToServer":"2022-08-16T21:11:28.198833Z","timeLastSentToServer":"2022-08-16T21:11:30.991969Z","timeLastReceivedFromServer":"2022-08-16T21:11:30.992773Z"},
It is these records with blank Destination IPs that are increasing as time passes. Some process, whether at the application side, JDBC Drivers, or on MySQL is blanking the IP out and not removing these "active sessions".
We think it has to do with some code where it is not closing the transaction block some place and it is being looked into. No luck yet.
I was wondering if anyone has experienced this before and can give suggestions at things to look for.

Thanks!
DD

How do i get current number of Connections used by mysqlrouter.? (1 reply)

yeshirow Cheel — Wed, 17 Aug 2022 20:21:11 +0000

I want to warning my application if the number of connections is getting closed to the max_total_connections.
I had read the page about mysqlrouter's commandline, but no idea to do that. please help.

mysqlrouter 8.0.29 next-available config (no replies)

lorenzo tombini — Mon, 18 Jul 2022 13:12:57 +0000

Hi all,
I'm testing mysqlrouter for master/replica environment in ubuntu 20.04 using mysql community packages, but there is something I can'tunderstand about routing_strategy.
This is my routing conf

[routing:MasterReplica]
bind_address = 127.0.0.1
bind_port = 3306
destinations = 192.168.1.181:3306,192.168.1.181:3406
routing_strategy = next-available

I've 2 mysqld instances on same machine.

Reading docs "next-available" means that when a server is unreacheable is marked as unreachable then it gets discarded and is never used again as a destination till mysqlrouter restart.

In my case when I raise up the instance gone dow, the logs show that the server is remouved from quarantine.

"2022-07-18 14:54:02.785 routing DEBUG [7fd09feab700] Destination candidate '192.168.1.181:3306' is available, remove it from quarantine"

Could someone help me to understand which is my fault?

Thanks a lot in advanced

MySQL Router Restful API (no replies)

Edwin Desouza — Fri, 15 Jul 2022 05:37:59 +0000

https://mysqlhk.blogspot.com/2022/07/mysql-router-restful-api.html

mysql stuck on socket write/read with mysqlrouter 8.0.29 if packet is big (no replies)

Liang Cheng — Wed, 01 Jun 2022 01:46:54 +0000

Hi, team
In setup one MySQL Innodb cluster as below
a. host-A
MySQL server 8.0.29 + MySQL rourter 8.0.29 + MySQL shell 8.0.29
b. host-B
MySQL server 8.0.29
c. host-C
MySQL server 8.0.29 + Application

Before running application, I connect to the MySQL Innodb cluster with mysqlrouter url (host-A :6446), everything looks ok. Create db/table and then insert one row, all mysql servers are sychonized.
However, everytime when inserting one big row, around 30M I guess, it always stuck on the socket write with the same thread stack as below, and it's reproducible.
Then I uninstalled mysqlrouter 8.0.29 and mysqlsh 8.0.29, and install both of them with 8.0.28, this issue gone. Reinstalled 8.0.29 back, this issue occurs again.

java.lang.Thread.State: RUNNABLE
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)
at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
at java.io.BufferedOutputStream.write(BufferedOutputStream.java:122)
- locked <0x00000000a4315ac8> (a java.io.BufferedOutputStream)
at com.mysql.cj.protocol.a.SimplePacketSender.send(SimplePacketSender.java:53)
at com.mysql.cj.protocol.a.TimeTrackingPacketSender.send(TimeTrackingPacketSender.java:50)
at com.mysql.cj.protocol.a.NativeProtocol.send(NativeProtocol.java:558)
at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:622)
at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:987)
at com.mysql.cj.NativeSession.execSQL(NativeSession.java:666)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:930)
- locked <0x00000000a4314a50> (a com.mysql.cj.jdbc.ConnectionImpl)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:371)
- locked <0x00000000a4314a50> (a com.mysql.cj.jdbc.ConnectionImpl)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
at org.apache.jackrabbit.core.util.db.ConnectionHelper.execute(ConnectionHelper.java:441)
at org.apache.jackrabbit.core.util.db.ConnectionHelper.reallyUpdate(ConnectionHelper.java:320)
at org.apache.jackrabbit.core.util.db.ConnectionHelper.access$3(ConnectionHelper.java:314)
at org.apache.jackrabbit.core.util.db.ConnectionHelper$2.call(ConnectionHelper.java:308)
at org.apache.jackrabbit.core.util.db.ConnectionHelper$2.call(ConnectionHelper.java:1)
at org.apache.jackrabbit.core.util.db.ConnectionHelper$RetryManager.doTry(ConnectionHelper.java:454)
at org.apache.jackrabbit.core.util.db.ConnectionHelper.update(ConnectionHelper.java:311)
at org.apache.jackrabbit.core.util.db.MySqlConnectionHelper.update(MySqlConnectionHelper.java:165)
at org.apache.jackrabbit.core.persistence.pool.BundleDbPersistenceManager.storeBundle(BundleDbPersistenceManager.java:1087)
- locked <0x00000000a43d6950> (a org.apache.jackrabbit.core.persistence.pool.MySqlPersistenceManager)
at org.apache.jackrabbit.core.persistence.bundle.AbstractBundlePersistenceManager.putBundle(AbstractBundlePersistenceManager.java:684)
at org.apache.jackrabbit.core.persistence.bundle.AbstractBundlePersistenceManager.storeInternal(AbstractBundlePersistenceManager.java:626)
at org.apache.jackrabbit.core.persistence.bundle.AbstractBundlePersistenceManager.store(AbstractBundlePersistenceManager.java:503)
- locked <0x00000000a43d6950> (a org.apache.jackrabbit.core.persistence.pool.MySqlPersistenceManager)
at org.apache.jackrabbit.core.persistence.pool.BundleDbPersistenceManager.store(BundleDbPersistenceManager.java:480)
- locked <0x00000000a43d6950> (a org.apache.jackrabbit.core.persistence.pool.MySqlPersistenceManager)
at org.apache.jackrabbit.core.state.SharedItemStateManager$Update.end(SharedItemStateManager.java:757)
at org.apache.jackrabbit.core.state.XAItemStateManager.commit(XAItemStateManager.java:181)
at org.apache.jackrabbit.core.TransactionContext.commit(TransactionContext.java:209)
- locked <0x00000000fc6ac578> (a org.apache.jackrabbit.core.TransactionContext)
at org.apache.jackrabbit.core.XASessionImpl.commit(XASessionImpl.java:331)
at org.apache.jackrabbit.jca.JCASessionHandle.commit(JCASessionHandle.java:414)
at org.apache.jackrabbit.jca.JCALocalTransaction.commit(JCALocalTransaction.java:84)

Router COnfig (1 reply)

Gan MySQL — Tue, 08 Feb 2022 04:30:29 +0000

How to add multiple bind address without using 0.0.0.0

[routing:1]
bind_address = ip address1
bind_port = 7001
destinations = foo.example.org:3306
routing_strategy = round-robin

[routing:2]
bind_address = ip address2
bind_port = 7002
destinations = foo.example.org:3306
routing_strategy = first-available

How to implement mysql consolidation strategy with multiple instances? (5 replies)

William Frezno — Wed, 19 Jan 2022 22:56:55 +0000

Here's the problem.
When my home setup started, it had several application each having it's own database. But as time goes on my needs grow and suddenly I find myself deploying dozens of sql servers. Now I want to consolidate: i.e. to move every database to a single database server to better manage backups and so on.
The consolidation strategy that I would want to implement is to deploy multiple mysql-server instances on a single server using virtualization technologies such as docker BECAUSE each database is different: e.g. one is psql GIS database. Another is mysql-5, another is mariadb10.4 and so on.
The problem is, to make it consistent I need to have one endpoint to connect to all those database instances. What approach should I use to accomplish the task.
The task is similar to reverse proxy in case of web servers.

mysqlrouter connection error for 127.0.0.1 (no replies)

Sam Tan — Wed, 13 Oct 2021 03:13:53 +0000

Hi Experts,

Basic Info:
MySQL Router v2.1.4 on Linux (64-bit) (Commercial)
MySQL Ver 14.14 Distrib 5.7.20, for Linux (x86_64)

I am new to MySQL and MySQL Router,

Recently our MySQL Router is experiencing an unusual connection problem on our MySQL Router:

[root@pas202]#cat /var/log/mysqlrouter/mysqlrouter.log
2021-10-11 15:26:43 INFO [7f4c7bfa7700] [routing:prodCluster_default_rw] 29 connection errors for 127.0.0.1 (max 100)
2021-10-11 15:36:54 INFO [7f4c80fb1700] [routing:prodCluster_default_rw] 30 connection errors for 127.0.0.1 (max 100)
2021-10-12 10:09:48 INFO [7f4cd17fa700] [routing:prodCluster_default_rw] 31 connection errors for 127.0.0.1 (max 100)
2021-10-12 10:19:57 INFO [7f4c6d78a700] [routing:prodCluster_default_rw] 32 connection errors for 127.0.0.1 (max 100)
2021-10-12 10:29:33 INFO [7f4c72f95700] [routing:prodCluster_default_rw] 33 connection errors for 127.0.0.1 (max 100)
(timestamp above is UTC +8)

I understand that there is a connection error from localhost and I usually clear the error via mysqlrouter restart. But the question is the culprit that is attempting the connection. Upon checking from the MySQL Router logs and matching the timestamp it seems this is the culprit:

[root@pmse01]# cat /var/log/mysqld.log
2021-10-11T05:13:09.524728Z 15758097 [Note] Access denied for user 'ROUTER'@'pas202.presto.local' (using password: YES)
2021-10-11T05:23:45.483704Z 15759262 [Note] Access denied for user 'ROUTER'@'pas202.presto.local' (using password: YES)
2021-10-11T07:16:36.878786Z 15771528 [Note] Access denied for user 'ROUTER'@'pas202.presto.local' (using password: YES)
2021-10-11T07:26:41.565336Z 15772637 [Note] Access denied for user 'ROUTER'@'pas202.presto.local' (using password: YES)
2021-10-11T07:36:52.445713Z 15773751 [Note] Access denied for user 'ROUTER'@'pas202.presto.local' (using password: YES)
2021-10-12T02:09:46.579821Z 15894247 [Note] Access denied for user 'ROUTER'@'pas202.presto.local' (using password: YES)
2021-10-12T02:19:55.397823Z 15895335 [Note] Access denied for user 'ROUTER'@'pas202.presto.local' (using password: YES)
2021-10-12T02:29:31.611252Z 15896393 [Note] Access denied for user 'ROUTER'@'pas202.presto.local' (using password: YES)
(timestamp above is UTC)

I can confirm there is no user 'ROUTER' in the mysql.user.
The MySQL Router connecting to MySQL is using mysql_router9_kjc1lba6sqfb.

Has anyone encountered this phenomena before?

Regards,
Sam Tan