-
ProxySQL HA with BGP ECMP Anycast
When setting up a new database for an application, high availability (HA) is one of the main priorities. Let’s assume for this example that you chose to use a Percona XtraDB (PXC) cluster to host your database.
But how does the application know which PXC node is healthy and can receive application traffic? Introducing a cluster of ProxySQLs can solve this problem, as ProxySQL will healthcheck the database nodes and route the application traffic to the healthy nodes.
However, now the HA problem comes up again: how does the application know which ProxySQL host is healthy?
Putting “one more component” in front of your servers to make them highly available just shifts the problem up by one layer.
From making the database HA, to making ProxySQL HA, to needing to make HAProxy HA and so on…
At some point, you may land on the common HA strategy, keepalived, but BGP ECMP is a powerful alternative worth considering.
What is BGP, what is ECMP?
As a brief summary, Border Gateway Protocol (BGP) is a routing protocol which operates over TCP. Equal-Cost Multi-Path (ECMP) defines that we want the traffic to be loadbalanced equally across the given routes.
Routers acting as BGP speakers are configured to accept routes for defined IP ranges and Autonomous System (AS) numbers, storing information about the networks that the router can reach in a Routing Information Base (RIB) table.
To benefit from Anycast, we will assign both/all ProxySQL nodes the same virtual IP address. BGP is then used to let the router know multiple routes to reach that IP. Configuring ECMP will cause the router to balance the traffic over these routes.
User defined health checks are executed by a BGP-speaking daemon, such as ExaBGP, running on the host to ensure that the router has the correct information about the status of a route.
As soon as a health check fails, ExaBGP will trigger a BGP update to withdraw the route. The router will remove the entry from its RIB table and stop forwarding packets to that host.
The traffic will be redistributed to the remaining healthy ProxySQL nodes.
For a more in depth guide about BGP please refer to Cisco press.
How ECMP based on BGP works
For our setup we can’t have the applications connect to the normal IPs of the ProxySQL nodes, as these are assigned to one node and fixed.
Instead we need a single separate anycast IP (let’s take 10.5.200.1/32) that we can assign to both ProxySQL nodes. This IP will be assigned to the loopback interface and has to be of a separate network, not overlapping with the IP-Range the normal ProxySQL node IPs are from.
When a packet with the destination set to the ProxySQL anycast IP (10.5.200.1) arrives at the router, the router checks its internal routing table to determine the next hop for the packet.
The router will see the two ProxySQL nodes in the cluster as potential next hop (as they both have the same anycast IP) and will pick one of the ProxySQL nodes, based on ECMP, to forward the packet to.
In ECMP the next hop is dynamically decided based on a 5-tuple hash from the packet header fields:
{ source IP address | destination IP address | protocol | source port | destination port }
Because the IP address and ports are in the hash, this ensures that the packets belonging to the same TCP stream are kept on the same path, to prevent packets of the same TCP connection ending up on multiple ProxySQL nodes.
You can visualise the setup like this:
By doing so, we have achieved high availability by leveraging BGP ECMP to loadbalance traffic in an active/active configuration across the ProxySQL nodes.
Additionally, the application config can be simplified, as only the single anycast IP (or DNS record for that IP) needs to be used for the ProxySQL cluster, and the logic of routing will be handled by the router.
Failures of a ProxySQL host are automatically handled by ExaBGP; a BGP update is sent to the router, and the route to the failed ProxySQL is withdrawn. The router redirects traffic to the remaining healthy ProxySQL, with no manual interventions required.
Extra infrastructure components (such as internal loadbalancers) can be avoided, eliminating additional network hops and improving network latency.
Alternative strategies for using BGP with databases
Of course, a ProxySQL cluster is not the only way to leverage BGP ECMP in order to achieve high availability for your databases. Some other strategies could be to use it for read-replica routing, local traffic routing, or for routing towards loadbalancers (e.g. haproxy).
Routing towards the database loadbalancer
Using BGP ECMP does not mean that you have to forego a database loadbalancer. You can configure your database servers to sit behind a database loadbalancer,
such as HAproxy or ProxySQL, and implement BGP ECMP in order to route traffic towards the loadbalancers and operate them highly available as true active/active pair.
If one of the loadbalancer instances dies, then BGP automatically takes care of routing the traffic to the remaining healthy peers for you.
Read replica routing
You can use BGP ECMP to distribute MySQL-Connections over multiple read replicas without using any Loadbalancer / ProxySQL at all. This saves you the additional latency and network hops of using a loadbalancer/ProxySQL.
If you need to ensure that you do not read from a replica which is lagging behind or has stopped replicating, you can implement this logic in the BGP health checks.
BGP Local Preference
If you have a multi-datacenter setup, you can choose to use local preference to keep traffic localised within the same datacenter. For example if you have an application server and a proxysql host in one datacenter (datacenter A), and an application server and proxysql host in a second datacenter (datacenter B),
you can tell the router to send traffic from the application to the proxysql within the same datacenter. The advantage of this is that it keeps network latency low, and avoids cross-site transit. Configuring this in BGP means that the application does not need to be aware of which datacenter it is running in. The BGP router handles localised routing for you.
If the local route would disappear, then the BGP router would automatically divert traffic from the application in datacenter A to the proxysql in the datacenter B.
Advantages of BGP
One advantage of BGP over keepalived is that your anycast-nodes don’t need to be in the same subnet (especially useful for multi-datacenter setups). Keepalived instead requires the nodes to be part of the same Layer 2 Network.
BGP ECMP supports active/active, unlike keepalived which only supports active/passive architectures.
In BGP, the router has the overview of which node is healthy or unhealthy. In keepalived this knowledge resides in the keepalived process running on the node itself.
As long as the nodes running keepalived can see each other, keepalived thinks everything is fine, but the nodes might have lost connection to the router.
Whereas with BGP, health checks ensure that BGP is aware of the state of the route. In case there would be a network problem that would make the node unreachable, the route would disappear from the router.
You can horizontally scale the nodes with BGP.
BGP Local preference allows you to automatically route traffic within a datacenter, without the application needing to configure logic like “use ProxySQL-A when running in datacenter-A, otherwise ProxySQL-B.”
You can set BGP to eliminate additional hops of infrastructure components, for example connecting to a pool of read replicas, without needing to connect over a loadbalancer.
Caveats with BGP
BGP ECMP is not connection-state aware, so if instances disappear/die or new instances join and the RIB table is rebuilt, the hashing algorithm will most likely forward packets for existing connections to a different instance than before. As that instance will not be aware of this TCP-Connection, it will send an RST-packet and the application will have to re-open its database connection.
In the next post, we will explain the technical details of setting up BGP ECMP for our ProxySQL cluster using OPNsense as Router.
This post is part of the Percona Community Writers Program.
-
Twitter Login Using Node and MySQL
Twitter login with Node breaks every few years because the pieces move under it. I rebuilt this app on the current releases and fixed each place where the old code stops working, so you can follow it step by step and finish with a working sign-in. Live Demo Download Code Pick the right X auth […]
-
MySQL 26.7 and the Power of Community Contribution
As we have discussed through the new MySQL Community Engagement initiative, we are sharing metrics that highlight how the community contributes to MySQL. These measures help us understand the growth of community participation, recognize the people helping improve the product, and identify where we can continue to strengthen the contributor experience. The MySQL community delivered […]
-
150+ SQL Commands Explained With Examples (2026 Update)
In this guide, we explain 150+ SQL commands in simple words, covering everything from basic queries to advanced functions for 2026. We cover almost every SQL command that exists in one single place, so you never have to go search for anything anywhere else. If you master these 150 commands, you will become an SQL […]
-
OAuth2 and JWT Logins for MySQL
An engineer leaves your company. You disable their single sign-on account, and their access to the wiki, the cloud console, and the CI system goes with it. Typically, access to database resources doesn't follow such a simple plan. Database user and role maintenance too often happens within the database, oblivious to single sign-on. User accounts carry a password which has to be managed and rotated (and the password is probably already sprawled out across shell history and a shared password manager).An easier way to manage this is to bring the identity-provider model to the database. This is what the VillageSQL Server vsql-oauth2 extension does. It adds an authentication method that accepts an OAuth2/OpenID Connect token (a JWT) from your identity provider in place of a password. Your identity provider decides who can log in, MySQL privileges decide what they can do inside, and disabling someone stops them getting another token. The token already in hand works until it expires, so your provider's token lifetime dictates the access window.VillageSQL is the innovation platform for MySQL that adds an extension framework (similar to PostgreSQL's extension framework) to enable permissionless innovation. Instead of waiting for a feature to be implemented in a future version of MySQL in a few years, new functionality can be dynamically added to the version of MySQL you run today. VillageSQL Server supports MySQL 8.4, 9.7, and Percona Server 8.4. The vsql-oauth2 extension is an example of what is meant by permissionless innovation.The rest of this post shows how the vsql-oauth2 extension works. The examples use Microsoft Entra ID, because Entra puts readable role names directly into the token. We start with the simple case: install the extension, point it at Entra's signing keys, and log in to the database with a token instead of a password. We then add role mapping, so a person the database has never seen before can log in for the first time and come away with an account and the roles their identity provider assigned them.
If you would rather stop reading here and have your AI agent demonstrate this for you with a mock identity provider, open this dropdown and copy the prompt into your preferred AI coding tool.
Set up a working demo of passwordless MySQL logins on my machine, using the
vsql-oauth2 extension for VillageSQL. Act as my own identity provider so no real
IdP is needed. Work only against a local throwaway server — if the only
VillageSQL or MySQL server you find looks like something I depend on, stop and
ask me before touching it.
Do all of this yourself, and show me the real output of each step:
1. Find a VillageSQL server, or install one. The install script needs a method:
`curl -fsSL https://install.villagesql.com | INSTALL_METHOD=prebuilt bash`.
Start it with `--vsql_allow_preview_extensions=ON`; on a server already
running, `SET PERSIST vsql_allow_preview_extensions=ON` takes effect at once.
Confirm with `SELECT VERSION()` before continuing.
2. Install the extension: `INSTALL EXTENSION vsql_oauth2;`. It ships with the
server, so there is nothing to download. Read
`INFORMATION_SCHEMA.EXTENSION_REGISTRATION` and
`SHOW GLOBAL VARIABLES LIKE 'vsql_oauth2.%'` and tell me what settings exist.
3. Stand in for the identity provider. Generate an RSA keypair with openssl, and
write a small script that mints signed RS256 JWTs from a claims payload, each
header carrying a `kid`. Use openssl only — do not install a JWT library.
Then publish the public key the way a real provider does: serve a JWKS
document over HTTP on localhost, holding the key as a JWK with `kty`, `n`,
`e` and that same `kid`. Let the operating system choose the port rather than
picking one yourself, and tell me the URL.
4. Do the basic case. Set `issuer`, `audience` and `username_claim`, and point
`jwks_url` at the JWKS URL from step 3. Create an account bound to
`vsql_oauth2`, create the account it maps onto, and grant the proxy. Then log
in with a token in place of the password and show me
`SELECT CURRENT_USER(), @@external_user;`. Show me your JWKS server's own log
as evidence that the database really fetched the keys.
5. Do the joiner case. Turn on `roles_claim`, `roles_filter`,
`roles_transform_pattern`, `roles_transform_replacement`, `auto_create` and
`auto_grant`. Create two roles named in the rewritten form the transform
produces, then log in as a user who has never touched this database with a
token carrying a roles claim. Show me that the account and the role grant both
appeared, and that the role is active for the session.
6. Try to break it. Take a token you have just shown works and change exactly
one thing at a time: expire it, alter the issuer, alter the audience, name a
`kid` your JWKS document does not carry, strip the signature with
`alg: none`, and re-sign it with HMAC using the public key as the shared
secret. Show me all six refused. Every refusal prints the same message, so
for each one name the single change you made, and log in with the unchanged
token in the same run to prove the refusal came from that change.
Then give me a table of what you ran and what came back, tell me anything that
did not behave the way this asked, and drop every user, role and setting you
created, stop the JWKS server, and leave my server back where it started.
Log in without a database password
Below is the server-side setup for the basic case, using Microsoft Entra ID:
INSTALL EXTENSION vsql_oauth2;
SET GLOBAL vsql_oauth2.issuer = 'https://login.microsoftonline.com/<tenant-guid>/v2.0';
SET GLOBAL vsql_oauth2.jwks_url = 'https://login.microsoftonline.com/<tenant-guid>/discovery/v2.0/keys';
SET GLOBAL vsql_oauth2.audience = '<database-app-client-id>';
SET GLOBAL vsql_oauth2.username_claim = 'preferred_username';
CREATE USER oauth_user IDENTIFIED WITH vsql_oauth2;
CREATE USER 'dana@myco.example';
GRANT SELECT ON *.* TO 'dana@myco.example';
GRANT PROXY ON 'dana@myco.example' TO oauth_user;
jwks_url is Entra's key endpoint. The extension fetches the signing keys from it and refreshes them hourly by default, so Entra can rotate its keys without anyone touching the database. The public_key setting is the alternative: it pins one key you paste in yourself, which suits a self-signed test and stops working at the provider's next rotation. Set both and jwks_url wins.
Point audience at the app registration that stands for the database, and send Entra's access token for that app rather than the id_token. Only the access token carries App Roles under the readable names the next section filters on. The README's provider settings cover the app registrations Entra needs and the values for other providers.
From there, the standard mysql client logs in with a token where the password would normally go. Passing it through MYSQL_PWD keeps it out of the process list:
$ MYSQL_PWD='<the JWT>' mysql --enable-cleartext-plugin --user=oauth_user \
-e "SELECT CURRENT_USER(), @@external_user;"
+---------------------+-------------------+
| CURRENT_USER() | @@external_user |
+---------------------+-------------------+
| dana@myco.example@% | dana@myco.example |
+---------------------+-------------------+
The credential is a short-lived token from your identity provider, so there is no password to store or rotate. Where the token comes from depends on who is connecting, e.g., a person at a shell, a CI job, or an application. The extension takes the same token in all cases. Obtaining it is ordinary OAuth for your provider.
The vsql-oauth2 README has the commands for all three. It also covers the vsql_oauth_client plugin, which fetches the token itself so it never touches the command line. However you send it, the token travels in cleartext at the protocol level, so the connection has to run over TLS.
You connect as oauth_user, which is bound to the extension and holds no privileges of its own. The username_claim setting says which claim names the account to run as, and GRANT PROXY is what allows the switch. Entra's sub is an opaque identifier, so this example reads preferred_username and gets Dana's sign-in name. The session then has dana@myco.example's privileges. @@external_user reports the identity that arrived in the token, so the audit trail keeps it even though the session runs as another account.
The token has to be signed with one of your provider's published keys for any of this to happen, and the extension accepts RSA and ECDSA signatures only. An unsigned token is refused before any signature check runs, and so is one that switches to a symmetric algorithm hoping the server will reuse your public key as a shared secret.
Easy database access management
The basic case maps a token to an account you created ahead of time. The extension can go further and take its cues from the App Roles Entra assigned the person signing in. You tell it which claim to read and how to rewrite the names, then create the roles those App Roles map onto:
SET GLOBAL vsql_oauth2.roles_claim = 'roles';
SET GLOBAL vsql_oauth2.roles_filter = 'mysql-grp-.*';
SET GLOBAL vsql_oauth2.roles_transform_pattern = '-';
SET GLOBAL vsql_oauth2.roles_transform_replacement = '_';
SET GLOBAL vsql_oauth2.auto_create = ON;
SET GLOBAL vsql_oauth2.auto_grant = ON;
CREATE ROLE mysql_grp_dba;
The two transform settings rewrite each matched App Role before it becomes a role name, turning mysql-grp-dba into mysql_grp_dba. Now someone who has never touched this database logs in with a token that says preferred_username: alice@myco.example, roles: [mysql-grp-dba]:
$ MYSQL_PWD='<her JWT>' mysql --enable-cleartext-plugin --user='alice@myco.example' \
-e "SELECT CURRENT_USER(); SELECT CURRENT_ROLE();"
+----------------------+
| CURRENT_USER() |
+----------------------+
| alice@myco.example@% |
+----------------------+
+---------------------+
| CURRENT_ROLE() |
+---------------------+
| `mysql_grp_dba`@`%` |
+---------------------+
One login created the account, granted the role her App Role entitles her to, and activated it for the session. An auto-created account runs as itself, so this path needs no proxy grant. The DBA never saw a ticket and never ran a CREATE USER. The DBA's job moves up a level: grant each role its privileges once, and let the identity provider say who holds the App Role. When an App Role later disappears from someone's token, that role stops activating at their logins, but the granted membership stays until a human revokes it.
Alice had no account, so auto_create did all of it. auto_grant covers the other case, someone who already has an account, and it grants the roles their token claims each time they log in. Both default to off and work independently, so you can turn on either one without the other. If you leave them off, tokens only ever activate roles you granted by hand, and unknown users stay unknown.
Try it out
Please try out vsql-oauth2 against your identity provider, tell us how it goes, especially which claim layouts your tokens carry. If you are not on Entra, the same settings apply — your provider's discovery document (/.well-known/openid-configuration) gives the issuer and jwks_url values. We would love to hear from you on Discord or leave an issue on vsql-oauth2.
To get started with VillageSQL Server, go to villagesql.com.
|
