ProxySQL is a high-performance SQL proxy that sits between applications and MySQL databases. It helps boost performance and gives database administrators more control over traffic, load balancing, and real-time monitoring.
This tool can really streamline database connections. Setting up ProxySQL on Ubuntu 24.04 takes a few specific steps and some careful configuration.
Installing ProxySQL on Ubuntu 24.04 means grabbing the right package, setting up the repository, and configuring initial database connections in the admin interface. You’ll need to prep your system, install a few dependencies, and tweak ProxySQL so it works with your MySQL servers.
Table of Contents
ProxySQL brings handy features like query caching, connection pooling, and failover. These make it a solid choice for production. The setup process on Ubuntu 24.04 sticks to standard package management, but you’ll want to pay attention to database connectivity and user permissions.
Key Takeaways
- ProxySQL installation on Ubuntu 24.04 requires proper repository configuration and package management setup
- The configuration process involves setting up MySQL backend servers and defining user access through the admin interface
- Advanced features like connection pooling and query routing provide significant performance benefits for database operations
Preparing the Ubuntu 24.04 Environment
Before you dive in, check your system resources and make sure you have the right packages and install files. It’s important to confirm your system meets the minimum requirements and install dependencies first.
System Requirements
ProxySQL runs smoothly on Ubuntu 24.04 and doesn’t need much hardware. You’ll want at least 1 GB of RAM and 100 MB of free disk space.
Stick with a 64-bit processor for best results. To check your architecture, just run uname -m.
You’ll need root or sudo privileges to install everything. Don’t forget, network access is a must for downloading packages and dependencies.
ProxySQL works on both physical servers and virtual machines. If you prefer containers, it gets along with proxysql docker images too.
Installing Dependencies
Before setting up ProxySQL, install a few essentials. You’ll need wget for downloads and some basic development tools.
Start by updating your package repository:
sudo apt updateThen install the required dependencies:
sudo apt install wget curl gnupg2 software-properties-commonWant to test database connections? The mysql client package comes in handy. Install it with:
sudo apt install mysql-clientThese tools help you check if ProxySQL works after you set it up. Curl lets you securely grab files from remote repositories.
Downloading ProxySQL Packages
You can get ProxySQL packages from the official repository. First, add the ProxySQL repository to your system.
Import the GPG key to verify packages:
wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-3.x/repo_pub_key' | apt-key add -Add the official ProxySQL repository:
echo deb https://repo.proxysql.com/ProxySQL/proxysql-3.x/ubuntu jammy main | sudo tee /etc/apt/sources.list.d/proxysql.listUpdate your package list so Ubuntu knows about ProxySQL:
sudo apt updateNow you’re ready to download ProxySQL. You can double-check the repository by listing available ProxySQL packages.
Installing and Running ProxySQL
To install ProxySQL on Ubuntu 24.04, update your package repositories and use the package manager. You’ll want the service to start now and on every reboot.
ProxySQL Installation Steps
Update your system packages before you install ProxySQL. This keeps things compatible.
sudo apt update
sudo apt upgrade -yInstall ProxySQL from the Ubuntu repositories. The package manager sorts out dependencies automatically.
sudo apt install proxysql -yProxySQL v3.0.2 and up come with better MySQL compatibility. The install process creates config files in /etc/proxysql/.
Check your installation with:
dpkg -l | grep proxysqlStarting and Enabling the ProxySQL Service
You’ll need to start ProxySQL manually after installing. Use systemctl to kick it off:
sudo systemctl start proxysqlEnable the service so it starts on boot:
sudo systemctl enable proxysqlCheck if it’s running with:
sudo systemctl status proxysqlProxySQL listens on two ports: 6032 for admin connections, and 6033 for client MySQL connections.
It creates log files in /var/lib/proxysql/. These logs are useful if you run into connection issues.
Verifying Installation and Checking ProxySQL Version
Test if ProxySQL responds on the admin port. Use the MySQL client:
mysql -u admin -padmin -h 127.0.0.1 -P 6032The default admin username and password are both admin. You’ll probably want to change these right away.
Once inside the admin interface, check the version:
SELECT @@version;You can also see runtime stats:
SELECT * FROM stats_mysql_global;To confirm ProxySQL is listening on both ports, run:
netstat -tlnp | grep proxysqlThis shows active ports and tells you if the install worked.
Configuring ProxySQL for MySQL
ProxySQL needs some configuration to handle MySQL connections the right way. You’ll set up the admin interface, add backend servers, create users, and save your changes.
Accessing the Admin Interface
The admin interface lets you control everything with SQL commands. By default, it runs on port 6032.
Connect using the MySQL client:
mysql -h 127.0.0.1 -P 6032 -u admin -pThe password is admin unless you changed it. Please, for your own sake, swap it out for something stronger.
Once connected, you’ll see “ProxySQL Admin>” at the prompt. You can use regular MySQL queries to tweak settings.
To check status, run:
SELECT * FROM stats_mysql_global;Adding MySQL Backend Servers
Backend servers do the heavy lifting for database queries. Add each MySQL server to the mysql_servers table.
Here’s how to add a backend server:
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight) VALUES (0, '192.168.1.100', 3306, 1000);Use hostgroup_id to organize servers. Group 0 is usually for writes. Group 1 is for read-only servers.
Add more servers if you want load balancing:
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight) VALUES
(0, '192.168.1.100', 3306, 1000),
(1, '192.168.1.101', 3306, 900),
(1, '192.168.1.102', 3306, 900);See all your configured servers:
SELECT * FROM mysql_servers;Setting Up MySQL Users
ProxySQL needs user accounts for backend connections and client access. Add them to the mysql_users table.
Here’s how to add a MySQL user:
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('appuser', 'password123', 0);Set default_hostgroup to 0 if this user needs write access.
For health checks, create a monitoring user:
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('monitor', 'monitorpass', 0);Set global variables for monitoring:
SET mysql-monitor_username='monitor';
SET mysql-monitor_password='monitorpass';Saving and Loading Configuration Changes
Changes stick in memory until you save them to disk and load them at runtime. This helps prevent mistakes from becoming permanent right away.
Save your changes:
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL VARIABLES TO DISK;Load the config to runtime:
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL VARIABLES TO RUNTIME;You can also load variables from disk if needed:
LOAD MYSQL VARIABLES FROM DISK;Double-check that your changes are live:
SELECT * FROM runtime_mysql_servers;
SELECT * FROM runtime_mysql_users;Advanced Features and Operational Best Practices
ProxySQL gives you powerful tools for query routing with mysql_replication_hostgroups. It also supports automatic database failover and connection pooling for handling high database traffic and keeping things reliable.
Configuring Query Routing and Rules
Query routing decides where database traffic goes, based on rules and patterns. ProxySQL lets you set these decisions in the mysql_query_rules table.
Basic routing rules send read queries to slaves and write queries to masters. The match_pattern column uses regular expressions to spot query types.
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*', 2, 1);Advanced routing can use user, database name, or specific table patterns to steer queries. The mysql_replication_hostgroups table handles read-write splits for MySQL replication setups.
You can set query timeouts, connection limits, and retry policies right in the rules table. Use LOAD MYSQL QUERY RULES TO RUNTIME to activate new rules instantly—no need to restart ProxySQL.
Setting Up Database Failover and High Availability
Database failover keeps services running when primary servers go down. ProxySQL checks server health with regular connection and query tests.
Health monitoring runs at set intervals to check if servers are alive. If a server fails, ProxySQL pulls it from active hostgroups until it recovers.
The mysql_servers table lists servers, along with weight and status. Servers with higher weight get more connections during normal times.
Automatic failover shifts traffic from failed masters to available slaves or backups. The mysql_replication_hostgroups config takes care of this in replication environments.
Recovery brings servers back online after they pass health checks. If you need to, you can manually force a server’s status change.
Monitoring and Scaling ProxySQL
Monitoring tracks connection counts, query speeds, and server health. The stats database gives you a live look at ProxySQL’s operations.
Key monitoring tables include stats_mysql_connection_pool for connection data and stats_mysql_query_rules for routing stats. These tables update as ProxySQL runs.
Connection pooling cuts overhead by reusing connections. The mysql_users table lets you set per-user connection limits and authentication.
Scaling strategies mean adding backend servers to hostgroups or tweaking pool sizes. High-traffic databases usually need higher max_connections and better query caching.
Tuning performance might involve adjusting thread counts, memory buffers, or query timeouts to fit your workload.
Managing ProxySQL Clusters
ProxySQL clusters offer redundancy and spread the load across several proxy instances. Each cluster member keeps its config in sync with the others.
Cluster setup starts by listing members in the proxysql_clusters table. Members share a cluster name and the same sync credentials.
When you change the config, ProxySQL pushes updates to all nodes. Use LOAD MYSQL SERVERS TO RUNTIME to update every member at once.
Load balancing spreads client connections across ProxySQL instances. You can use an external load balancer or DNS round-robin for initial connections.
Cluster monitoring keeps an eye on the health of each ProxySQL node and backend connection. If a member fails, ProxySQL bypasses it until it’s healthy again.
Frequently Asked Questions
To install ProxySQL, update your system packages and enable the service after. Before you manage database connections and user privileges, you’ll need to set up connection pools, the admin interface, and security settings.
What are the necessary steps to install ProxySQL on Ubuntu?
First, update your Ubuntu packages to keep everything compatible. Then install ProxySQL:
sudo apt-get update
sudo apt-get install proxysqlThe installer grabs ProxySQL from the official repository. Double-check that installation finishes without errors.
After that, start and enable the ProxySQL service:
sudo systemctl start proxysql
sudo systemctl enable proxysqlHow do you configure ProxySQL after installation?
Configure ProxySQL through its admin interface on port 6032. Connect with the default admin credentials to get started.
The main config file lives at /etc/proxysql.cnf. You’ll find server definitions and pool settings there.
Add your MySQL backend servers to the config. Define server groups and set up routing rules for connections.
Tweak connection pooling settings for your app’s needs. These control how ProxySQL manages connections.
What methods are available to ensure ProxySQL is running correctly on Ubuntu?
Check ProxySQL’s status using systemctl. This tells you if the service is active and running as expected.
sudo systemctl status proxysqlThe admin interface shows real-time backend server stats. You can track connections and server health there.
Log files in /var/log/proxysql.log give you detailed info about what’s happening. Use these to troubleshoot issues.
Commands like ps and netstat confirm ProxySQL is listening on the right ports.
How can you update ProxySQL to the latest version?
Update ProxySQL using Ubuntu’s package manager. The system checks your repositories for updates.
sudo apt-get update
sudo apt-get upgrade proxysqlBack up your config files first. This saves your custom settings in case something goes sideways during the upgrade.
After updating, restart ProxySQL. That way, it loads the new version with your existing config.
What is the best practice for securing ProxySQL on an Ubuntu server?
Change the default admin passwords right after you install ProxySQL. Strong passwords keep your config safe from unwanted visitors.
Bind the admin interface to localhost only. This blocks outside access to ProxySQL’s config.
Set firewall rules so only authorized servers can connect to ProxySQL ports. Don’t leave them wide open.
Use SSL to encrypt data between your app and ProxySQL. Set up SSL certs for secure database connections.
How do you manage user accounts and privileges within ProxySQL?
ProxySQL keeps user accounts in its own internal database tables. You can add users using the admin interface or by editing configuration files.
Every user account needs specific privileges for database access. These privileges control which databases and operations each user can use.
You can integrate user authentication with existing MySQL user systems. ProxySQL checks credentials directly with the backend database servers.
Password policies and account restrictions help boost security. You can also set connection limits or access timeouts for user accounts if needed.
