title: “ProxySQL”
date: 2022-12-08T09:15:17
slug: proxysql
apiVersion: monitoring.coreos.com/v1
kind: ServiceMonitor
metadata:
name: proxysqlcluster
namespace: squad-rtlplus-podcast
labels:
prometheus: r5s-shared
spec:
endpoints:
– path: /metrics
port: proxysql-exp
jobLabel: app
namespaceSelector:
matchNames:
– squad-rtlplus-podcast
selector:
matchLabels:
app: proxysql-exporter
Configmap:
apiVersion: v1
data:
proxysql.cnf: |
datadir=”/var/lib/proxysql”
errorlog=”/var/lib/proxysql/proxysql.log”
admin_variables=
{
admin_credentials=”admin:admin;cluster:secret”
mysql_ifaces=”0.0.0.0:6032″
refresh_interval=2000
cluster_username=”cluster”
cluster_password=”secret”
}
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;/tmp/proxysql.sock”
default_schema=”information_schema”
stacksize=1048576
server_version=”8.0.23″
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
}
proxysql_servers =
(
{ hostname = “proxysql-0.proxysqlcluster”, port = 6032, weight = 1 },
{ hostname = “proxysql-1.proxysqlcluster”, port = 6032, weight = 1 },
{ hostname = “proxysql-2.proxysqlcluster”, port = 6032, weight = 1 }
)
fluent-bit.conf: |
[SERVICE]
Flush 1
Parsers_File /etc/parsers.conf
Log_Level info
Daemon Off
[INPUT]
Name tail
Buffer_Max_Size 5MB
Buffer_Chunk_Size 256k
path /var/lib/proxysql/queries.log.*
DB /var/lib/proxysql/fluentd.db
Parser JSON
[OUTPUT]
Name stdout
Format json_lines
json_date_key timestamp
json_date_format iso8601
Match *
[FILTER]
Name modify
Match *
Rename client source
Rename duration_us duration_query
parsers.conf: |
[PARSER]
Name JSON
Format json
time_key starttime
time_format %Y-%m-%d %H:%M:%S
kind: ConfigMap
metadata:
creationTimestamp: null
name: proxysql-configmap
Terraform:
Create ProxySQL User and Password with Connections from two Subnets
resource “random_password” “password_mysql_proxysql” {
length = 16
special = false
}
resource “aws_secretsmanager_secret” “mysql-podcast_proxysql” {
name = “rtl-plus-podcast/mysql-proxysql-{{environment}}”
description = “ProxySQL Credentials {{environment}} Stage”
}
resource “aws_secretsmanager_secret_version” “mysql-podcast_proxysql” {
secret_id = aws_secretsmanager_secret.mysql-podcast_proxysql.id
secret_string = jsonencode({“user”=”mysql_proxysql_{{environment}}”, “password”=random_password.password_mysql_proxysql.result})
}
resource “mysql_user” “podcast-proxysql” {
provider = mysql.aurora-sl
user = “mysql_proxysql_{{ environment }}”
host = “{{ inventory.parameters.mysql_user_proxysql_host }}”
plaintext_password = random_password.password_mysql_proxysql.result
}
resource “mysql_grant” “podcast-proxysql_user” {
provider = mysql.aurora-sl
user = mysql_user.podcast-proxysql.user
host = mysql_user.podcast-proxysql.host
database = “*”
table = “*”
privileges = [“replication client”]
}
resource “kubernetes_config_map” “proxysql-config-sql” {
metadata {
name = “proxysql-config-sql”
namespace = “squad-rtlplus-podcast”
}
data = {
sql = templatefile(“./proxysql.sql”, {
aurora_domain = “{{ inventory.parameters.aurora_domain }}”
podcast_user_password = random_password.password_mysql_aurora.result
podcast_user_name = mysql_user.podcast-serverless-db.user
proxysql_user_password = random_password.password_mysql_proxysql.result
proxysql_user_name = mysql_user.podcast-proxysql.user
instances = aws_rds_cluster_instance.podcast-serverless-db
})
}
}
resource “null_resource” “restart_proxysql_statefulset” {
provisioner “local-exec” {
command = “kubectl rollout restart statefulset proxysql”
}
lifecycle {
replace_triggered_by = [
kubernetes_config_map.proxysql-config-sql
]
}
}
proxysql.sql
DELETE FROM mysql_aws_aurora_hostgroups;
INSERT INTO mysql_aws_aurora_hostgroups values (0,1,1,3306,'{{inventory.parameters.aurora_domain}}’,600000,1000,3000,0,1,30,30,1,'{{ environment }}’);
SELECT * FROM mysql_aws_aurora_hostgroups;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
DELETE FROM mysql_users;
INSERT INTO mysql_users(active,username,password,default_hostgroup,transaction_persistent,use_ssl) VALUES (1,’${podcast_user_name}’,’${podcast_user_password}’,0,0,1);
INSERT INTO mysql_users(active,username,password,default_hostgroup,transaction_persistent,use_ssl) VALUES (1,’root’,’FUiv8yXi7buM0KoD’,0,0,0);
SELECT * FROM mysql_users;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
DELETE FROM mysql_servers;
{% raw %}
%{ for instance in instances ~}
%{ if instance.writer == true ~}
INSERT INTO mysql_servers(hostgroup_id,hostname,port,use_ssl) VALUES (0,’${instance.endpoint}’,3306,1);
%{ else ~}
INSERT INTO mysql_servers(hostgroup_id,hostname,port,use_ssl) VALUES (1,’${instance.endpoint}’,3306,1);
%{ endif ~}
%{ endfor ~}
{% endraw %}
SELECT * FROM mysql_servers;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
UPDATE global_variables SET variable_value=’${proxysql_user_name}’ WHERE variable_name=’mysql-monitor_username’;
UPDATE global_variables SET variable_value=’${proxysql_user_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’);
UPDATE global_variables SET variable_value=’true’ WHERE variable_name=’mysql-have_ssl’;
UPDATE global_variables SET variable_value=’0′ WHERE variable_name=’mysql-set_query_lock_on_hostgroup’;
UPDATE global_variables SET variable_value=’5000′ WHERE variable_name=’mysql-monitor_ping_timeout’;
UPDATE global_variables SET variable_value=’500′ WHERE variable_name=’mysql-throttle_connections_per_sec_to_hostgroup’;
UPDATE global_variables SET variable_value=’5000′ WHERE variable_name=’mysql-default_max_latency_ms’;
UPDATE global_variables SET variable_value=’0′ WHERE variable_name=’mysql-set_query_lock_on_hostgroup’;
UPDATE global_variables SET variable_value=’queries.log’ WHERE variable_name=’mysql-eventslog_filename’;
UPDATE global_variables SET variable_value=’2′ WHERE variable_name=’mysql-eventslog_format’;
UPDATE global_variables SET variable_value=’512′ WHERE variable_name=’mysql-query_cache_size_MB’;
SELECT * FROM global_variables;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
DELETE from mysql_query_rules;
INSERT INTO mysql_query_rules (active,match_digest,apply,cache_ttl) VALUES (1,’^SELECT’,1,60000);
select t0.*,t0.id from submissions as t0 where (t0.feed\_url = ?) limit ?
INSERT INTO mysql_query_rules (active,digest,destination_hostgroup,apply,cache_ttl) VALUES (1,’0x0C80CCC93E4A6477′,1,1,60000);
select t0.id from episodes as t0 where (t0.uid = ?) limit ?
INSERT INTO mysql_query_rules (active,digest,destination_hostgroup,apply,cache_ttl) VALUES (1,’0x78DDF3A07A7B6E97′,1,1,60000);
select count(*) as count from episodes as t0 limit ?
INSERT INTO mysql_query_rules (active,digest,destination_hostgroup,apply,cache_ttl) VALUES (1,’0x4E91709EE1214CA5′,1,1,60000);
select count(*) as count from podcasts as t0 limit ?
INSERT INTO mysql_query_rules (active,digest,destination_hostgroup,apply,cache_ttl) VALUES (1,’0xB8ED2A71067C2EE5′,1,1,60000);
SELECT ?
INSERT INTO mysql_query_rules (active,digest,destination_hostgroup,apply,cache_ttl) VALUES (1,’0x1C46AE529DD5A40E’,1,1,60000);
select distinct t0.*,t1.podcast\_id from episodes as t0 left join episodes\_podcast\_links as t1 on t0.id = t1.episode\_id where (t1.podcast\_id in (?))
INSERT INTO mysql_query_rules (active,digest,destination_hostgroup,apply,cache_ttl) VALUES (1,’0xA8CF5BFA3D088DFA’,1,1,60000);
INSERT INTO mysql_query_rules (active,digest,destination_hostgroup,apply) VALUES (1,’0x970F45504162F173′,0,1);
INSERT INTO mysql_query_rules (active,digest,destination_hostgroup,apply) VALUES (1,’0x23C7F2C66F50F4A0′,0,1);
INSERT INTO mysql_query_rules (active,digest,destination_hostgroup,apply) VALUES (1,’0xD2247BD720196139′,0,1);
INSERT INTO mysql_query_rules (active,digest,destination_hostgroup,apply) VALUES (1,’0x3465337B476BD70F’,0,1);
INSERT INTO mysql_query_rules (active,digest,destination_hostgroup,apply) VALUES (1,’0xCDC03F9ECEFE25F0′,0,1);
INSERT INTO mysql_query_rules (active,match_digest,destination_hostgroup,apply) VALUES (1,’^SELECT.*FOR UPDATE’,0,1), (1,’^SELECT’,1,1);
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply,cache_ttl) VALUES (1,1,’0x357FE2F04F7B1185′,1,1,6000);
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply,cache_ttl) VALUES (2,1,’0x7C0DB66C3A8F048D’,1,1,6000);
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply,cache_ttl) VALUES (3,1,’0x7346A6D7423B7B87′,1,1,6000);
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply,cache_ttl) VALUES (4,1,’0x9210A11FA3CFB6C7′,1,1,6000);
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply,cache_ttl) VALUES (5,1,’0x35A086C5312A7AA9′,1,1,6000);
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply,cache_ttl) VALUES (6,1,’0xD5B76CB799A8EB07′,1,1,6000);
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply,cache_ttl) VALUES (7,1,’0xCECC5BDAB513EB4A’,1,1,6000);
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply,cache_ttl) VALUES (8,1,’0xD0DA41F6615CBD24′,1,1,6000);
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply,cache_ttl) VALUES (9,1,’0xF06765D077F9D71B’,1,1,6000);
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply,cache_ttl) VALUES (10,1,’0x7631E5190F85279E’,1,1,6000);
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply,cache_ttl) VALUES (11,1,’0x55F97DEBD03DFBBD’,1,1,6000);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
select * from mysql_query_rules;
SELECT * FROM stats.stats_mysql_connection_pool;
SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;
“`