Table of Contents

1. Introduction

2. Why ProxySQL?

   • Benefits

   • Common Use Cases

3. ProxySQL Setup Guide

   • Instance Creation

   • Installing ProxySQL

   • Configuring MySQL Servers in ProxySQL

   • User Setup

   • Query Routing Rules

   • Monitoring & Logging

4. Scenario: Using ProxySQL in Production

   • Challenges & How ProxySQL Helps

   • Implementation Steps

5. [Final Verification & Conclusion

   ](https://chatgpt.com/?temporary-chat=true#5-final-verification--conclusion)

1. Introduction

ProxySQL is a high-performance MySQL proxy designed to enhance efficiency, load balancing, high availability, and query routing.

2. Why ProxySQL?

2.1 Benefits of ProxySQL

• Read-Write Splitting: Directs write queries to the master and read queries to replicas, optimizing database performance.

• High Availability: Supports failover mechanisms to ensure continuous database access during master or replica downtime.

• Connection Pooling: Efficiently manages and reuses database connections, reducing database overhead.

• Query Caching: Caches frequently execute queries to improve response times and reduce database load.

• Query Routing and Filtering: Routes queries to specific backend servers based on rules, enabling better load distribution and resource utilization.

• User-based Access Control: Implements fine-grained access control, restricting users to specific queries or databases.

• Scalability: Allows seamless horizontal scaling by adding database nodes without changing applications.

• Query Insights: Provides real-time monitoring and query statistics to identify performance bottlenecks.

• Support for Private Networks: Works with private IP databases, enhancing security in cloud environments.

2.2 Common Use Cases

• Applications handling high read/write workloads.

• Environments requiring automatic failover.

• Systems needing load balancing across multiple replicas.

• Hybrid cloud/multi-cloud database architectures.

3. ProxySQL Setup

3.1 Instance Creation

Creating a ProxySQL VM Instance

1. Select a machine configuration that meets workload needs.

2. Set Boot Disk Size based on database query load.

3. Attach the VM to a custom VPC for private networking.

Creating Cloud SQL Instances (Master & Replica)

Step 1: Create Master Instance

1. Open Google Cloud Console → Go to Cloud SQL → Click Create Instance → Choose MySQL.

2. Select Edition → Set MySQL Version 8.0.

3. Choose Private IP and attach it to the VPC network.

4. Click Create and wait for provisioning.

Step 2: Create Replica

1. Go to Cloud SQL Instances → Select the Master → Click Create Read Replica.

2. Ensure Private IP is enabled and choose the same VPC Network.

3. Provide a unique name and create the replica.

Step 3: Verify Setup

• Check Replica tab in Cloud SQL.

• Perform write operations on the Master and verify replication on the Replica.

3.2 Installing ProxySQL

Install ProxySQL on a VM

sudo add-apt-repository universe sudo apt update

Add ProxySQL Repository

wget -qO - https://repo.proxysql.com/ProxySQL/repo_pub_key | sudo apt-key add - echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/$(lsb_release -sc)/ ./" | sudo tee /etc/apt/sources.list.d/proxysql.list

Install ProxySQL

sudo apt update sudo apt install proxysql -y

Start ProxySQL Service

sudo systemctl start proxysql sudo systemctl enable proxysql sudo systemctl status proxysql

Verify Installation

proxysql --version

Install MySQL Client

apt install mysql-client-core-8.0 mysql --version

3.3 Configuring ProxySQL

Login to ProxySQL Admin Interface

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt 'ProxySQL Admin> '

Add MySQL Backend Servers (Master & Replica)

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (10, 'master-db-ip', 3306); INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (20, 'replica-db-ip', 3306); LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

3.4 Setting Up Users in ProxySQL

INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) VALUES ('app_user', 'App@123', 10); INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) VALUES ('readonly_user', 'ReadOnly@123', 20); LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;

3.5 Query Routing Rules

Write Queries → Master (Hostgroup 10)

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (2, 1, '^(INSERT|UPDATE|DELETE|CREATE|DROP|RELOAD|SHUTDOWN|PROCESS|REFERENCES|INDEX|ALTER|CREATE TEMPORARY TABLES|LOCK TABLES|EXECUTE|REPLICATION|CREATE VIEW|SHOW VIEW|CREATE ROUTINE|ALTER ROUTINE|CREATE USER|EVENT|TRIGGER|CREATE TABLESPACE).*', 10, 1);

Read Queries → Replica (Hostgroup 20)

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES (1, 1, '^(SELECT|SHOW).*', 20, 1);

LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

MySQL Server Configuration

• Log in to the MySQL server as the root user:

mysql -u root -p

• For full access

CREATE USER 'Demo'@'%' IDENTIFIED BY '*'; GRANT ALL PRIVILEGES ON . TO 'Demo'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES; SHOW GRANTS FOR 'Demo'@'%';

Read-only access

For ex: - ranjith (read-only):

CREATE USER 'ranjith'@'%' IDENTIFIED BY '*';     GRANT SELECT, SHOW VIEW ON . TO 'ranjith'@'%';     FLUSH PRIVILEGES;

Monitor User Configuration:

• Create Monitor User in MySQL:

CREATE USER 'monitor'@'%' IDENTIFIED BY '*';     GRANT USAGE, REPLICATION CLIENT ON . TO 'monitor'@'%';     FLUSH PRIVILEGES;

If already monitor present then alter the password

ALTER USER 'monitor'@'%' IDENTIFIED BY '*'; GRANT USAGE, REPLICATION CLIENT ON . TO 'monitor'@'%';     FLUSH PRIVILEGES;

• Configure Monitor User in ProxySQL:

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';     UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';     UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval', 'mysql-monitor_ping_interval', 'mysql-monitor_read_only_interval');

• Check Monitor Configuration:

SELECT * FROM global_variables WHERE variablename LIKE 'mysql-monitor%';

• Apply Changes:

LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

Check Server Health and Logs in Proxysql:

• Check MySQL Server Connection Logs:

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;

• Check MySQL Server Ping Logs:

SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;

4. Scenario: Using ProxySQL in Production

4.1 Background

A high-traffic web application faces:

• Overloaded Primary DB due to excessive read queries.

• Manual Failover Issues when the master database crashes.

• Inefficient Query Routing where all queries hit the master.

4.2 How ProxySQL Solves These Issues

4.3 ProxySQL Monitoring

SELECT FROM stats_mysql_connection_pool; SELECT FROM stats_mysql_query_rules; SET GLOBAL log_queries_not_using_indexes=1;

5. Final Verification & Conclusion

5.1 Verify ProxySQL Connectivity

SELECT hostgroup, srv_host, srv_port, status FROM stats_mysql_connection_pool;

5.2 View ProxySQL Logs

sudo tail -f /var/lib/proxysql/proxysql.log

5.3 Track Server Load Distribution:

SELECT hostgroup, srv_host, srv_port, Queries, Bytes_data_sent, Bytes_data_recv FROM stats_mysql_connection_pool;

5.4 View Client Statistics with Network Data:

SELECT * FROM stats_mysql_global WHERE Variable_Name LIKE '%bytes%';

5.5 Monitor Runtime Actions:

SELECT * FROM stats_mysql_commands_counters;

5.6 Examine Memory Usage:

SELECT * FROM stats_memory_metrics;

5.7 Time_out

SELECT * FROM global_variables WHERE variable_name LIKE '%timeout%';

5.8 Conclusion

• ProxySQL ensures load balancing & automatic failover.

• Improves query performance through caching & optimization.

• Supports seamless scaling without modifying applications.