MySQL is gearing up with its own high-availability Group Replication solution

Do It Yourself

Query Distribution

Query Distribution

Four solutions to the problem of routing queries from the application to the cluster nodes are available, and all four are proxy systems that accept connections from the application and forward them to an appropriate system:

  • ProxySQL [7]: This feature-rich Layer 7 proxy can separate write and read queries and send them to separate servers (see a sample configuration online [8]).
  • HAProxy [9]: A classical Layer 4 proxy (see a sample configuration online [10]).
  • MySQL Router [11]: A routing solution in the scope of the Oracle InnoDB cluster.
  • MariaDB MaxScale [12]: A Layer 7 proxy solution from MariaDB.

Group replication does not solve the problem of how to route queries from the application to the cluster nodes. Particularly in the case of single-primary mode, some additional intelligence is necessary because the details of which node is now accepting writes needs to be stored somewhere (see the "Query Distribution" box).

ProxySQL seems to be the most common solution in what is still a very small market. Ideally, you would install the solution on every application server, rather than a central location on the network. All of the solutions are free software except for MaxScale.

And Galera?

The previous top dog in the field of virtual synchronous replication in the MySQL camp was Galera Cluster, an extension for MySQL released under GPLv2. Galera manages replication in an additional layer that sits on top of the MySQL core. In contrast to Group Replication, it does not use the binary log to handle past transactions; instead, it relies on GCache, a proprietary file that acts as a ring buffer.

Galera relies on MySQL but implements collision detection and distribution of the write set itself. In contrast to Oracle's solution, it does not rely on the modular system of replication features already integrated into the server. Galera has already seen several years of production use in many places and is ahead of Group Replication in this aspect.

Future

With the release of Group Replication, Oracle has given users broad insight into its roadmap [13]. Group replication is the most important component of the MySQL InnoDB cluster, a product that also includes the MySQL shell for simplified cluster installation and administration, and MySQL Router for automatic query distribution. The InnoDB cluster is available as a preview [14]. Oracle's next steps in the evolution of the MySQL platform will be read scale-out (via additional asynchronous read-only slaves) and write scale-out (via sharding).

The Author

Sven Lankes works as a senior consultant at Cocus AG and supports customers with the setup, sizing, and tuning of MySQL deployments.

Buy this article as PDF

Express-Checkout as PDF
Price $2.95
(incl. VAT)

Buy ADMIN Magazine

SINGLE ISSUES
 
SUBSCRIPTIONS
 
TABLET & SMARTPHONE APPS
Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

comments powered by Disqus