Skip to Main Content

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Processing

Post Details

Added on Jul 28 2020
6 comments
827 views