ProxySQL: Setup MySQL Replication tools on Kubernetes Cluster

Introduction

ProxySQL is an advanced, high-performance, and GPL licensed MySQL protocol proxy, transforming MySQL into a more robust, scalable, and reliable SQL platform. Providing a layer of control and optimization between your application and the database, it is instrumental in managing and optimizing data queries in a high-traffic environment.

History of ProxySQL

ProxySQL was the brainchild of René Cannaò, a MySQL database administrator with more than a decade of experience in database consulting and management. Cannaò was acutely aware of the unique challenges faced by administrators in managing MySQL databases, particularly in high-demand scenarios that require superior performance and efficiency.

In 2013, Cannaò embarked on the journey to develop ProxySQL with an aspiration to provide a solution for these database management pain points. His goal was to create a tool that allows administrators to manage, control, and optimize MySQL database traffic without altering existing code bases.

ProxySQL was built from scratch, focusing on addressing key issues such as connection handling, routing, and failover mechanisms that were previously hard to manage efficiently. Over the years, it has gone through numerous iterations, each one fine-tuning its capabilities and performance.

Since its inception, ProxySQL has been embraced by the tech community, becoming an integral part of numerous IT infrastructures. Renowned for its high availability and top-tier performance management, it has become a must-have tool for many database administrators.

What is ProxySQL?

ProxySQL stands out as an exceptional MySQL protocol proxy, known for its high performance and a broad array of features. Its unique selling point lies in its multifaceted approach to dealing with MySQL databases. As a proxy, it sits between your application and the database, handling connections and queries, and performing various operations to optimize the interaction.

At its core, ProxySQL is designed to control and optimize database traffic. It handles a multitude of connections from front-end applications, managing these connections, and routing them to the appropriate servers in the back-end. It can also manage a large number of back-end servers, effectively spreading the load to ensure high availability and better performance.

Key features of ProxySQL include, but are not limited to:

• Connection Pooling: ProxySQL can maintain a pool of connections to back-end servers, which reduces the overhead of establishing new connections. This also allows for efficient management of client connections.
• Query Caching: To optimize performance, ProxySQL can cache the result of queries. This means that if the same query is executed again, ProxySQL can serve the result from the cache instead of executing it again on the MySQL server.
• Query Routing and Rewriting: ProxySQL can route queries to different back-end servers based on rules defined by the database administrator. Additionally, it can rewrite queries before they reach the MySQL server, allowing for optimization and control at the query level.
• Traffic Management: ProxySQL offers granular control over traffic, allowing administrators to manage and control how data flows between their application and MySQL servers. It also supports throttling and can limit the number of queries executed per hour.
• High Availability: ProxySQL can manage a pool of MySQL servers and distribute client connections among them. In the event of a server failure, ProxySQL can redirect traffic to other servers, ensuring uninterrupted service.

Introduction to MySQL Replication

MySQL replication is a process that enables data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves). Replication is asynchronous by default - slaves do not need to be connected permanently to receive updates from the master. This technology is widely used for various purposes such as improving the performance of a MySQL database setup, executing backup services on a slave instead of the master, and more.

The key components of MySQL replication include the following:

• Master Server: The source where the database modifications are logged. It writes the changes to its binary log (binlog).
• Slave Server(s): The server or servers where the changes from the master are duplicated. Each slave server keeps a record of the binary log coordinates: the file name and position within the file that it has read and processed from the master.
• Binary Log (Binlog): The record of all changes to the database, both data and structure, made by the MySQL server.

How ProxySQL Fits into MySQL Replication

ProxySQL plays a significant role in managing MySQL replication topologies. It fits well into this scenario due to its advanced capabilities in handling failover mechanisms, load balancing, and ensuring high availability.

When a database application is connected through ProxySQL to a replicated MySQL database cluster, ProxySQL intelligently manages the routing of queries. Reads and writes can be split among servers; write queries are typically directed to the master, while read queries are load-balanced across the available slave servers. This balances the load on the servers and provides a way to scale the read capacity of your application.

One of ProxySQL's key benefits in a replication setup is its capability to handle failover scenarios efficiently. If the master server becomes unavailable, ProxySQL can be configured to promote one of the slave servers to be the new master, ensuring continuity of service.

Additionally, ProxySQL can help with query performance on replicated setups. By caching the results of the read queries, it can reduce the need to execute identical queries on the server side multiple times, providing a significant boost in read performance.

Benefits of Using ProxySQL

ProxySQL offers a wide array of benefits that improve the overall performance, availability, and manageability of MySQL databases. Here are some key benefits:

• Enhanced Performance and Scalability: ProxySQL significantly improves MySQL performance by managing connections more efficiently. By pooling connections, limiting redundancy, caching queries, and enabling intelligent load balancing, ProxySQL can scale to handle high volumes of traffic with minimal overhead.
• Advanced Query Routing and Rewriting: ProxySQL allows for complex rule-based query routing. Queries can be routed to different backend servers based on their content. Additionally, queries can be automatically rewritten for optimization or other purposes, providing a powerful tool for managing how data is accessed and manipulated.
• Effective Traffic Management: By managing and controlling how data flows between applications and MySQL servers, ProxySQL offers granular control over database traffic. It can limit the number of queries executed per hour, ensuring that your database servers remain performant under heavy load.
• High Availability: By managing a pool of MySQL servers and effectively distributing client connections among them, ProxySQL ensures high availability. In the event of server failure, traffic can be rerouted to other servers, minimizing downtime.

Use Cases of ProxySQL

ProxySQL is used extensively in many industry sectors and varied applications. Here are some typical use cases:

• Load Balancing in High Traffic Applications: In applications that handle a large volume of simultaneous requests, ProxySQL can distribute the traffic load across multiple MySQL servers, preventing any single server from becoming a bottleneck.
• Database Replication Management: ProxySQL excels at managing MySQL replication setups. It can intelligently route queries to the appropriate server (master for writes, slaves for reads) and handle failovers effectively.
• Query Optimization: By caching the results of read queries and allowing for query rewriting, ProxySQL can significantly improve the performance of database operations.

Comparing ProxySQL with Other Proxy Servers

There are several proxy servers available for MySQL, such as HAProxy and MySQL Router. Each of these tools has its own strengths and weaknesses, and your choice may depend on your specific use case.

• HAProxy: Known for its high performance and reliability, HAProxy has been a popular choice for years. However, it lacks some of the MySQL-specific features that ProxySQL offers, such as query routing and rewriting, and connection pooling.
• MySQL Router: As a product from the developers of MySQL, MySQL Router offers tight integration with MySQL and its Group Replication feature. However, it does not offer the same level of control and optimization as ProxySQL, particularly in terms of query routing and rewriting.

In a replication setup, ProxySQL often stands out due to its advanced feature set tailored specifically for MySQL databases. Its robust handling of MySQL replication topologies, coupled with its high performance, flexibility, and granular control, makes it an excellent choice.

The Future of ProxySQL

As database requirements continue to evolve, ProxySQL is expected to keep up with the pace, enhancing its feature set and optimizing performance. While it is difficult to predict precise future developments, one can expect more advanced traffic management features, more efficient connection handling, and improved integration with different types of database systems.

Additionally, as distributed database systems become more prevalent, ProxySQL is likely to improve its capabilities in managing these complex setups, offering efficient, scalable solutions for managing and optimizing distributed databases.

Installing ProxySQL on Kubernetes Dashboard

Prerequisites

The highly recommended method for deploying ProxySQL on Kubernetes involves leveraging Helm Charts. ProxySQL provides an official Helm Chart to streamline the installation process on a Kubernetes Cluster. To get started, we need to download ProxySQL Helm Chart:

~$ git clone git@github.com:ProxySQL/kubernetes.git proxysql-k8s

~$ cd proxysql-k8s/proxy-cluster

The directory structure contains multiple folders, each tailored for various scenarios. For general use, we can utilize the Helm Chart located within the proxy-cluster folder.

Create dedicated MySQL User for Monitoring

ProxySQL recommends having a specific MySQL user allocated solely for monitoring purposes. Accordingly, we'll proceed to create this dedicated user.

# CREATE USER 'monitoring' IDENTIFIED WITH mysql_native_password BY 'passwordsecret';
# GRANT ALL PRIVILEGES ON *.* TO 'monitoring'@'%' WITH GRANT OPTION;

Configure ProxySQL

To configure ProxySQL to work with your setup, we'll make changes to the files/proxysql.cnf file. Assuming we have two databases, one acting as Master (IP: 10.58.0.29) and the other acting as Slave (Read Replica, IP: 10.58.0.30), we will route all SQL queries for write operations to the Master, and all SQL queries for read operations to the Slave. Below is the configuration for ProxySQL to achieve this setup:

Install to Kubernetes Cluster

Once all the prerequisites are met, we can proceed with the installation onto the Kubernetes Cluster.

~$ helm install proxysql .

Verify installation

~$ kubectl get pods
NAME           READY   STATUS    RESTARTS   AGE
proxysql-xxx   1/1     Running   1          1m
proxysql-xxx   1/1     Running   1          1m

Done, now you have scalable and reliable MySQL Proxy, installed on your Kubernetes Cluster.

About 8grams

We are a small DevOps Consulting Firm that has a mission to empower businesses with modern DevOps practices and technologies, enabling them to achieve digital transformation, improve efficiency, and drive growth.

Ready to transform your IT Operations and Software Development processes? Let's join forces and create innovative solutions that drive your business forward.

Subscribe to our newsletter for cutting-edge DevOps practices, tips, and insights delivered straight to your inbox!