Read-only debugging production server with ProxySQL

read-only-debugging-production-server-with-proxysql.md

Read-only debugging production server with ProxySQL

ProxySQL settings

docker-compose.yml

  proxysql:
    image: proxysql/proxysql
    ports:
      - 16032:6032
      - 16033:6033
    volumes:
      - ./config/proxysql.cnf:/etc/proxysql.cnf
      - /var/lib/proxysql

proxysql.cnf

datadir="/var/lib/proxysql"

admin_variables=
{
    admin_credentials="admin:admin;radmin:radmin"
    mysql_ifaces="0.0.0.0:6032"
}

mysql_variables=
{
    threads=4
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:6033"
    default_schema="information_schema"
    stacksize=1048576
    server_version="5.5.30"
    connect_timeout_server=3000
    monitor_username="monitor"
    monitor_password="monitor"
    monitor_history=600000
    monitor_connect_interval=60000
    monitor_ping_interval=10000
    monitor_read_only_interval=1500
    monitor_read_only_timeout=500
    ping_interval_server_msec=120000
    ping_timeout_server=500
    commands_stats=true
    sessions_sort=true
    connect_retries_on_failure=10
}

Admin access

mysql -h127.0.0.1 -P16032 -uradmin -pradmin --prompt "ProxySQL Admin> "

Mysql client access

mysql -u root -pdev -h 127.0.0.1 -P16033

ProxySQL setting via Admin access

# see all settings
SELECT * FROM mysql_users;
SELECT * FROM mysql_servers;
SELECT * FROM mysql_replication_hostgroups;
SELECT * FROM mysql_query_rules;
# and variables
SHOW VARIABLES;

# clean up
DELETE FROM mysql_users;
LOAD MYSQL USERS TO RUNTIME;
DELETE FROM mysql_servers;
DELETE FROM mysql_replication_hostgroups;
LOAD MYSQL SERVERS TO RUNTIME;
DELETE FROM mysql_query_rules;
LOAD MYSQL QUERY RULES TO RUNTIME;

# turn of monitoring
SET mysql-monitor_enabled = 0;
LOAD MYSQL VARIABLES TO RUNTIME;
SHOW VARIABLES;

# create write and read host groups
INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (0,1,'dev');
# add write only server
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, 'mysql', '3306');
# add read only server
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, 'mysql_read', '3306');
LOAD MYSQL SERVERS TO RUNTIME;
SELECT * FROM mysql_servers;
SELECT * FROM mysql_replication_hostgroups;
# eliminate auto added write from read server, if added
UPDATE mysql_servers SET weight=0 WHERE hostgroup_id=1 AND hostname='mysql';
LOAD MYSQL SERVERS TO RUNTIME;
SELECT * FROM mysql_servers;

# add query filter
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT',1,1),  (2,1,'^UPDATE',0,1),  (3,1,'^INSERT',0,1);
LOAD MYSQL QUERY RULES TO RUNTIME;

# add user, what is also used as client to remote mysql servers
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','dev', 1);
LOAD MYSQL USERS TO RUNTIME;

# debugging

SELECT rule_id, hits, destination_hostgroup hg FROM mysql_query_rules NATURAL JOIN stats_mysql_query_rules;
SELECT hostgroup,srv_host,status,Queries,Bytes_data_sent,Latency_us FROM stats_mysql_connection_pool where hostgroup in (0,1);