Question about mysqlrouter HA

user619241

Based on this link: https://lefred.be/content/mysql-innodb-cluster-is-the-router-a-single-point-of-failure/ , mysqlrouter should be deployed with application server. However, if number of application servers is many, we want to deploy mysqlrouter outside application sever. So I choose to deploy mysqlrouter with InnoDB Cluster nodes. I have deployed (3) mysqlrouter instances, each instance is on one cluster node. To achieve HA, I set up load balancing virtual IP to load balance the (3) mysqlrouter instances. This setup works fine. However, I found a problem:

• I got a lot of "error 1129: Too many connection errors" during a simple sysbench load testing. One of cluster node status will show this error: "shellConnectError": "MySQL Error 1129 (HY000): Host '<node-ip>' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'",

Below is my simple sysbench load run script:

#!/bin/bash

sysbench \

/usr/share/sysbench/oltp_read_write.lua \

--report-interval=2 \

--threads=8 \

--time=300 \

--db-driver=mysql \

--mysql-user=innotestusr \

--mysql-db=innotest_rw \

--mysql-password=**** \

--mysql-port=6446 \

--mysql-host=<mysqlrouter-virtual-ip> \

--tables=20 \

--table-size=100000 \

run

This is the error I got:

FATAL: unable to connect to MySQL server on host '<mysqlrouter-virutal-ip>', port 6446, aborting...

FATAL: error 1129: Too many connection errors from <lb-ip>

FATAL: `thread_init' function failed: /usr/share/sysbench/oltp_common.lua:349: connection creation failed

FATAL: unable to connect to MySQL server on host '<mysqlrouter-virutal-ip>', port 6446, aborting...

FATAL: error 1129: Too many connection errors from <lb-ip>

FATAL: `thread_init' function failed: /usr/share/sysbench/oltp_common.lua:349: connection creation failed

I'm not familiar with peacemaker or keepalived, so I choose to use our own load balancer.

How do we achieve mysqlrouter HA setup using the load balancer we want to choose?

Thanks,

- Xinhuan

Dave Stokes-MySQL Community Team-Oracle

You want to use MySQL Router as the load balancer for the cluster.  If you are running a load balancer to get to the application that is fine but bot for InnoDb Cluster traffic.

Dave Stokes-MySQL Community Team-Oracle

You want to use MySQL Router as the load balancer for the cluster.  If you are running a load balancer to get to the application that is fine but bot for InnoDb Cluster traffic.

lefred-MySQL-Oracle

Hello,

Putting MySQL Router on a dedicated server (or more) is OK, but I really don't understand the benetifs to put it on cluster nodes !

Maybe your loadbalancer doesn't close connections ? Like that it's difficult to answer. Check the connectio status and variables in MySQL and check the connections in the router.

Regards,

Dave Stokes-MySQL Community Team-Oracle

Place Router on the application nodes not on the InnoDB cluster nodes.  See https://dev.mysql.com/doc/mysql-router/8.0/en/

user619241

Hello,

I agree with all the answers. I still don't understand this statement:

"You want to use MySQL Router as the load balancer for the cluster."

Is MySQL Router a load balancer by itself? Then does the Router itself have HA capability?

Thanks,

- Xinhuan

Dave Stokes-MySQL Community Team-Oracle

MySQL Router will load balance the queries to innoDB Cluster or ReplciaSet.

user619241

In my test environment, I installed multiple mysqlrouter and start them. I'm testing if one of mysqlrouter process stop, can client connection be failed over to another mysqlrouter instance automatically. I tested with Keepalived. So far it wasn't successful. It looks like mysqlrouter is Single-Point-of-Failure.

- Xinhuan