Forum Stats

  • 3,768,299 Users
  • 2,252,772 Discussions
  • 7,874,520 Comments

Discussions

Question about mysqlrouter HA

user619241
user619241 Member Posts: 17 Blue Ribbon
edited Jul 30, 2020 10:41AM in MySQL Community Space

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

Best Answer

Answers