Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Question about mysqlrouter HA

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
-
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.
Dave Stokes
MySQL Community Manager
Answers
-
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.
Dave Stokes
MySQL Community Manager
-
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/
Dave Stokes
MySQL Community Manager
-
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.
Dave Stokes
MySQL Community Manager
-
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