ProxySQL commands

proxysql.md

ProxySQL Commands

Servers

Add

The server require fill two tables; mysql_replication_hostgroups and mysql_servers. The first table define who is use for write and who is for reads, and the id number for each, for example any master: [10, 20, 30, 40] and all slave: [11, 12, 13, 21, 22, 23]. The second table is for the server list associated with mysql_replication_hostgroups.

INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment)
VALUES (10, 11, 'Events');

INSERT INTO mysql_servers (hostgroup_id,hostname,port, max_connections, weight, comment)
VALUES
(10, 'mysql-events-master.prd.sb' ,3306, 100, 1, 'Master'),
(11, 'mysql-events-slave.prd.sb' ,3306, 100, 1, 'Slave');

Change

UPDATE mysql_servers SET status = 'OFFLINE_SOFT' WHERE hostname = 'mysql-events-slave.prd.sb';
UPDATE mysql_servers SET status = 'OFFLINE_HARD' WHERE hostname = 'mysql-events-slave.prd.sb';
UPDATE mysql_servers SET status = 'SHUNNED' WHERE hostname = 'mysql-events-slave.prd.sb';
UPDATE mysql_servers SET status = 'ONLINE' WHERE hostname = 'mysql-events-slave.prd.sb';

Remove

DELETE FROM mysql_servers WHERE hostname = 'mysql-events-slave.prd.sb';

Apply changes

Any change on servers require execute the follow commands to apply:

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Users

Add

INSERT INTO mysql_users (
	username, password, active, default_hostgroup, default_schema, max_connections
) VALUES (
	'thn_<app>_rw', '<pass>', 1, 140, 'thn_<app>', 100);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Stadistic

This section have the command to verify the status for many components: servers, conections, queries.

Check the connection pool

SELECT * FROM stats.stats_mysql_connection_pool;

To reset the values, execute the follow query:

SELECT * FROM stats.stats_mysql_connection_pool_reset;

Queries

SELECT * FROM stats_mysql_query_digest;

Commands

SELECT * FROM stats_mysql_commands_counters;