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