Payload Website Template

Why?

Running a single instance of a database is easy but it does not provide high availability. From the applications standpoint we don't want to manage multiple databases and mange their roles. Running a single instance also becomes hard to scale horizontally after a point, you can t just keep adding RAM and CPU.

Therefore using multiple databases is advised. This is where proxySQL comes in handy. Since proxySQL balances the databases and can manage routing to read/write replicas.

How?

As a service proxySQL runs as a single or multiple instances and connects straight to your backend databases. There are a few users we have to setup to be able to pass data through the proxy service.

Example deployment below

1apiVersion: apps/v1
2kind: Deployment
3metadata:
4  name: proxysql
5  labels:
6    app.kubernetes.io/name: proxysql
7    app.kubernetes.io/part-of: authentication
8spec:
9  selector:
10    matchLabels:
11      app.kubernetes.io/name: proxysql
12  strategy:
13    type: RollingUpdate
14  template:
15    metadata:
16      labels:
17        app.kubernetes.io/name: proxysql
18    spec:
19      serviceAccountName: proxysql
20      containers:
21      - image: proxysql/proxysql
22        name: proxysql
23        imagePullPolicy: IfNotPresent
24        resources:
25          limits:
26            memory: 200Mi            
27          requests:
28            cpu: 100m
29            memory: 100Mi
30        ports:
31        - containerPort: 6033
32          name: proxysql-mysql
33        - containerPort: 6032
34          name: proxysql-admin
35        - containerPort: 6070
36          name: proxysql-metric
37        - containerPort: 6080
38          name: proxysql-web
39        env:
40          - name: PROXYSQL_ADMIN_PASSWORD
41            valueFrom:
42              secretKeyRef:
43                name: proxysql-admin-password
44                key: proxysql-admin-password
45          - name: PROXYSQL_STATS_PASSWORD
46            valueFrom:
47              secretKeyRef:
48                name: proxysql-stats-password
49                key: proxysql-stats-password
50          - name: MYSQL_MONITOR_PASSWORD
51            valueFrom:
52              secretKeyRef:
53                name: mysql-monitor-password
54                key: mysql-monitor-password
55          - name: MYSQL_PASSWORD
56            valueFrom:
57              secretKeyRef:
58                name: mysql-password
59                key: mysql-password
60        envFrom:
61          - configMapRef:
62              name: proxysql-config
63        securityContext:
64          capabilities:
65            drop:
66              - ALL
67          runAsNonRoot: true
68          runAsUser: 6001
69          allowPrivilegeEscalation: false
70          seccompProfile:
71            type: RuntimeDefault

Upon starting the service for the first time a config file is generated based on the ENV variables. Inspect the configuration file located in: `/etc/proxysql.cnf` and you will see the that user:password pairs have been created. These users are used in the following way:

ProxySQL:

admin_credentials: padmin - used for logging into proxySQL mysql

stats_credentials: sadmin - used for viewing the stats on the SSL https://localhost:6080 address

There are some users that have to be created manually, or should already exist as:

Mariadb:

root - default user on mariadb

Shared users - These users have to be setup on both proxySQL and Mariadb with MATCHING CREDENTIALS !!!

monitor_user - used for monitoring the database, this user is set as an ENV variable, and has to be created in the backend databases.

mysql_users - these are the users which will be able to access the backend databases through proxysql. They have to be created in both systems. In proxysql you have to create them as part of the proxysql.cnf or as ENV variables. In the backend system they have to be created.

ProxySQL runs its own SQL database

Use the mysql-client package using mysql to connect

1LOAD ADMIN VARIABLES TO RUNTIME;
2SAVE ADMIN VARIABLES TO DISK;
3
4
5Table: mysql_group_replication_hostgroup
6writer_hostgroup - will contain all the responsible writer hosts
7backup_writer_hostgroup - used for failover scenarios, as hot spares
8reader_hostgroup - will contain all the responsible writer hosts 
9offline_hostgroup - intentionally marked and taken offline
10active - is currently active or inactive, active=1 is active, 0 is inactive, useful to take servers out of service
11max_writers - defines how many nodes can act as writers
12writer_is_also_reader - sets so the readers can also be used for writing
13max_transacitons_behind - sets how many transactions behind before we mark the node is considered unhealthy
14

Proxysql

1mysql -u padmin -h 127.0.0.1 -P 6032 -p

Writer

1mysql -h aalrdsstack-rds-databse.url -u aaladmin --skip-ssl -pReader mysql -h we --skip-ssl -u aaladmin -p

proxySQL Check backends

1SELECT * FROM mysql_servers;
2## Create monitoring 
3MYSQL users CREATE USER 'proxysql'@'%' IDENTIFIED BY 'xxxx';GRANT SELECT on sys.* to 'proxysql'@'%';
4GRANT SELECT on performance_schema.* to 'proxysql'@'%';
5GRANT USAGE, REPLICATION CLIENT ON *.* TO 'proxysql'@'%';
6FLUSH PRIVILEGES;
7## Create user for accessing the data
8CREATE USER 'proxyadmin'@'%' IDENTIFIED BY 'password123';
9GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, CREATE VIEW, SHOW VIEW, TRIGGER ON metrics.* TO 'proxyadmin'@'%';
10CREATE DATABASE metrics;
11

Needed to only setup on the writer, since the reader is a replica in my AWS RDS case and the user got replicated.

Need to setup proxySQL to constantly monitor the health of the serversProxySQL stats page is accessible on 6080 and accessing it over HTTPS as https://localhost:6080

to test we can run the following:

1mysql -h proxysql -P6033 --skip-ssl -u proxyadmin -p -e "SELECT @@hostname"

1admin_variables=
2{
3    admin_credentials="padmin:password"
4    stats_credentials="sadmin:password"
5    mysql_ifaces="0.0.0.0:6032"
6    refresh_interval=2000
7    web_enabled=true
8    restapi_enabled=true
9    restapi_port=6070
10}
11mysql_variables=
12{
13    threads=4
14    max_connections=2048
15    default_query_delay=0
16    default_query_timeout=36000000
17    have_compress=true
18    poll_timeout=2000
19    interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
20    default_schema="information_schema"
21    stacksize=1048576
22    server_version="8.0.17"
23    connect_timeout_server=10000
24    monitor_history=60000
25    monitor_connect_interval=200000
26    monitor_ping_interval=200000
27    ping_interval_server_msec=10000
28    ping_timeout_server=200
29    commands_stats=true
30    sessions_sort=true
31    monitor_username="proxysql"
32    monitor_password="password"
33}
34mysql_replication_hostgroups =
35(
36    { writer_hostgroup=10, reader_hostgroup=20, comment="MySQL Replication 8.0" }
37)
38mysql_servers =
39(
40    { address="hostname1" , port=3306 , hostgroup=10, max_connections=100 },
41    { address="hostname2" , port=3306 , hostgroup=20, max_connections=100 }
42)
43mysql_users =
44(
45    { username = "mysql_user" , password = "password" , default_hostgroup = 10 , active = 1 }
46)
47mysql_query_rules =
48(
49{
50    rule_id=100
51    active=1
52    match_pattern="^SELECT .* FOR UPDATE"
53    destination_hostgroup=10
54    apply=1
55},
56{
57    rule_id=200
58    active=1
59    match_pattern="^SELECT .*"
60    destination_hostgroup=10
61    apply=1
62},
63{
64    rule_id=300
65    active=1
66    match_pattern=".*"
67    destination_hostgroup=10
68    apply=1
69}

Add a user for mysqld-exporter which we will use in prometheus

1CREATE USER 'exporter'@'%' IDENTIFIED BY 'password';
2GRANT PROCESS, REPLICATION CLIENT, SLAVE MONITOR, SELECT ON *.* TO 'exporter'@'%';

Load-balancing mariadb with ProxySQL

Why?

How?

Join the Discussion on github