This cheat sheet provides tips for setting up and managing ProxySQL for database connections. ProxySQL is a high-performance MySQL proxy that provides connection pooling, load balancing, query routing, and caching functionalities.
Table of Contents
Connection and Ports
- Admin Interface: 6032
- MySQL Default Port: 3306
- MySQL Interface via ProxySQL: 6033
Connecting to ProxySQL Admin Interface
mysql -uadmin -padmin -h127.0.0.1 -P6032General Configuration Settings
Set Connection Reuse
To enable connection reuse, use the following commands:
UPDATE global_variables SET variable_value='1' WHERE variable_name='mysql-connection_reuse';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;Set Max Connections
Configure the maximum number of connections ProxySQL will open to the backend database:
UPDATE global_variables SET variable_value='100' WHERE variable_name='mysql-max_connections';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;Query Cache Configuration
Set the TTL for query caching:
UPDATE mysql_query_rules set cache_ttl=5000 WHERE active=1 AND destination_hostgroup=1;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;Adding a Backend Server and Users
Add your backend MySQL server to ProxySQL:
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'mysqldb01.example.com',3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;Add users for accessing the MySQL backend:
INSERT INTO mysql_users(username,password,default_hostgroup, default_schema) VALUES ('mysqldb_user','password',1, 'mysqldb_schema');
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;Monitor User Setup
ProxySQL requires a monitor user to check the health of the MySQL backend. Create this user on your MySQL server:
CREATE USER 'monitor'@'%' IDENTIFIED BY 'password';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
FLUSH PRIVILEGES;Configure the monitor user in ProxySQL:
UPDATE global_variables SET variable_value='proxysql_monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='password' 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');
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;Useful Commands and Queries
Query Digest Statistics
Retrieve detailed statistics about query performance:
SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;Connection Pool Stats
Show used connections:
SELECT hostgroup, srv_host, ConnUsed, ConnFree, ConnOK, ConnERR, MaxConnUsed, Queries, Bytes_data_sent, Bytes_data_recv FROM stats_mysql_connection_pool;Show how many times queries were used:
SELECT * FROM stats_mysql_query_digest ORDER BY count_star DESC;
SELECT * FROM stats_mysql_global WHERE Variable_Name LIKE 'Query_Cache%';Check Total Connections to a Database in MySQL
SELECT
IFNULL(DB, 'No Database') AS DatabaseName,
COUNT(*) AS Connections
FROM
information_schema.PROCESSLIST
GROUP BY
DB
ORDER BY
Connections DESC;Notes
- Ensure that you tailor the
INSERT INTO mysql_usersstatements with the correct usernames, passwords, and schema names per your environment. - Always save the configurations to disk after loading them to runtime to make them persistent across restarts.
Wrapping Up the ProxySQL Cheat Sheet
Following these instructions will set up ProxySQL with optimal settings for connection reuse, monitoring, and efficient query handling. Adjust the parameters according to your specific use case and workload requirements.
References
ProxySQL Official Documentation: This is the most authoritative source for ProxySQL information. It covers everything from installation to advanced configuration.
