Skip to Main Content
Forums

MySQL Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Question about mysqlrouter HA

user619241Jul 28 2020 — edited Jul 30 2020

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
This post has been answered by Dave Stokes-MySQL Community Team-Oracle on Jul 29 2020
Jump to Answer

Comments

Answer

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.

Marked as Answer by user619241 · Sep 27 2020
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,

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

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

1 - 6

Post Details

Added on Jul 28 2020
#mysql-user-groups
6 comments
852 views