This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,738 Users
  • 2,269,776 Discussions
  • 7,916,827 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