User Gabriel Ciciliani talks about setting up a MySQL architecture using ProxySQL, an Open Source and high-performance proxy for MySQL, on their FreeBSD system. See the link below to read more about Ciciliani’s use case for a highly-available architecture that deals with metadata replication, devd, and CARP.
When designing a highly-available MySQL architecture, having a proxy to route the traffic to the appropriate instances is crucial to achieve transparent (or almost) failovers/switchovers. ProxySQL, a popular open source, SQL-level proxy is a great choice for this, and even though an experimental clustering feature is available, we could follow a simpler active-passive approach too, based on Common Address Redundancy Protocol (CARP), which will also provide a highly available IP for the applications to connect.
Solution overview …
Obtaining an Active-Passive ProxySQL on FreeBSD: https://blog.pythian.com/obtaining-an-active-passive-proxysql-on-freebsd/