Jul 19 2021

How to Access ProxySQL Web Interface on Kubernetes

Published by under Docker,Kubernetes,Mysql

I wrote in a previous post about a ProxySQL Helm chart for Kubernetes, in which you can configure the pod from SQL queries. The chart includes an ingress to reach the stats server on port 6080. Here are a few steps to get this working.


Ingress needs to be activated in the Helm Values file and ProxySQL web server also requires the admin-web_enabled admin variable to be set.

ingress:
  enabled: true
  annotations:
    kubernetes.io/ingress.class: nginx
  hosts:
    - host: proxy.domain.lan
      paths:
        - /

sqlqueries: |
  SET admin-web_enabled='true';
  LOAD ADMIN VARIABLES TO RUNTIME;
  -- other SQL queries


If you leave it like this, HTTP connections to the web interface will throw the following error:
Error: received handshake message out of context
ProxySQL web interface accepts HTTPS connections only – even though examples in the documentation use HTTP – and there’s no workaround it.


Ingress has to connect to the backend using the HTTPS protocol. Nginx lets you specify the protocol used with the backend-protocol annotation. You still have the choice to leave ingress access from outside in HTTP, but don’t on a public interface.

nginx.ingress.kubernetes.io/backend-protocol: "HTTPS"


 

No responses yet

Jul 06 2021

ProxySQL Helm Chart ⎈ Load Rules from SQL Query Set

Published by under Docker,Kubernetes,Mysql

ProxySQL is a powerful tool that relays traffic to multiple Mysql backends. Configuration can be set in proxysql.cnf that is loaded when the daemon starts. This is really nice from a Docker perspective. You change the file and redeploy with the new settings.


Official ProxySQL Helm Chart

I tried to use the ProxySQL team helm chart but quickly ran into some issues.
The docker hub image is a bit outdated. The Helm chart does not seem to be maintained anymore while they had done a great job.

The configuration lies in proxysql.cnf. It’d be nice the file content would load from Values.yaml, making it dynamic for each deployment.
My setup uses mainly mysql_query_rules_fast_routing but here’s the problem: it cannot be configured through proxysql.cnf and ProxySQL developers made it clear they will not change this.


Docker Image with Mysql Client

ProxySQL docker comes without Mysql client probably to make the image size smaller. You will need it though to inject SQL queries locally when the pod starts up. The dockerfile is really easy but it forces you to maintain your own image.

FROM proxysql/proxysql:2.2.0

RUN apt update && apt -y install mysql-client && apt clean all

ENTRYPOINT ["proxysql", "-f", "-D", "/var/lib/proxysql"]

I made an image directly available on Dockerhub. It is referenced in the helm chart but you can build your own if you want to host it on a private repository.


mysql_query_rules_fast_routing Rules

I decided to make some changes on the Helm template so it can read SQL rules from the Value file, and load them into ProxySQL dynamically once the pod is up and running.

sqlqueries: |
   --
   SET mysql-have_ssl='true';
   LOAD MYSQL VARIABLES TO RUNTIME;
   SET admin-web_enabled='true';
   LOAD ADMIN VARIABLES TO RUNTIME;
   --
   INSERT INTO mysql_servers (hostgroup_id,hostname) VALUES (0,'db1.domain.lan');
   INSERT INTO mysql_servers (hostgroup_id,hostname) VALUES (1,'db2.domain.lan');
   --
   INSERT INTO mysql_users (username,password,use_ssl,default_hostgroup) VALUES ('proxy','*9EF51D21B4A3E7BC7A58925308F229CF4AEEC9E1',1,0);
   --
   INSERT INTO mysql_query_rules_fast_routing (username,schemaname,destination_hostgroup, comment) VALUES ('proxy','schema1',0,'');
   INSERT INTO mysql_query_rules_fast_routing (username,schemaname,destination_hostgroup, comment) VALUES ('proxy','schema2',1,'');
   --
   LOAD MYSQL USERS TO RUNTIME;
   LOAD MYSQL SERVERS TO RUNTIME;
   LOAD MYSQL QUERY RULES TO RUNTIME;


SQL queries are added to the Values.yaml file, loaded into the configmap and mounted as a file in /docker-entrypoint-initdb.d/sqlqueries. We can then inject SQL queries once ProxySQL daemon is up and running. It is achieved with a poststart command that is launched right after the docker entrypoint’s been executed.

lifecycle:
   postStart:
     exec:
       command: ["/bin/sh", "-c", "sleep 1 && mysql --show-warnings -uadmin -padmin -h127.0.0.1 -P{{ .Values.pod.adminPort }} < /docker-entrypoint-initdb.d/sqlqueries"]


Using the Helm Chart

The helm chart is available on github. Clone the repo, edit your own Values file based on example.yaml and deploy the Helm chart

helm install -n my-release -f myconf.yaml ./proxysql


Read this post if you want to enable and reach the stats server through Kubernetes ingress rules.

 

No responses yet

Jun 06 2021

Feedback on Cisco Small Business vs Cisco

Published by under Cisco

Cisco vs Cisco Small Business

I asked my reseller a quote for some Cisco Catalyst switches as usual, and he tells me most of his clients buy Cisco Small Business switches. He argues they are much cheaper, have similar performance and features. Checking on some forums, I get no real answers but vague statements like it is not a real IOS or they are entry level switches. I decided to get a few and install them on remote sites with less traffic for a long term test.


Cisco Small Business Look Great …

Prices are around 4 times cheaper than Cisco Catalysts’ and they are under a lifetime warranty (until the last date of support). That means 3 spares for the same price…

I won’t talk about performance, they’re all pretty much the same.

Before I validate the order, I also made sure of a few essentials:
I need a CLI, it’s handy to connect over slow unstable links, or copy and paste some configuration lines. As mentioned earlier, it is not IOS since it is a different product.
It also provides HTTP(s), telnet and SSH access. Note that unlike Cisco Catalyst, Cisco Small business switches accept only one public SSH key.

Common features like VLANs (up to 256, check the datasheet), port mirroring, SNMP protocol are also available.


… Even though They Don’t Offer as much as Cisco Catalyst

SSH key authentication was not available then but it is now. It seems you can configure only one though.

When switching a port from access to trunk mode, you cannot set the trunk settings before changing to trunk mode. This is particularly annoying if you are configuring a remote switch over that link.

I noticed there is a lot less debug commands and levels but that was expected.

I ran into a bug and only one over 2 years. Interfaces were reporting a lot of packets in error.
This was fixed through a firmware upgrade. I noticed there was very few firmware releases on the download page.
Should I be worried about it? Not necessarily.
Cisco Small Business have a smaller amount of features, meaning no bug on features they don’t implement.
It could also mean less reactivity to fix existing bugs, but you get what you’re paying for, don’t you?


Then What?

Cisco Small Business are a really good, cheap alternative to Cisco Catalyst for access network, and proved to be reliable over time and better than a lot of other switches out there on the market. All depends on the features you need, like advanced QoS functionalities, or if you want to stack them for example. I ended up with a mix keeping Catalyst on the main sites and Cisco Small Business for smaller remote sites equipped with PoE wifi access points. The price is really attractive especially when you need Power over Ethernet (PoE) functionality.

A solution in between is also possible: Cisco Catalyst with the LAN Lite image. They cost halt the price of Cisco Catalyst LAN Base but have restrictions on QoS, ACL, or the number of VLANs among others. It is the same hardware with IOS but note you cannot upgrade from LAN Lite to LAN Base. Such a pity

 

No responses yet

May 16 2021

MySQL / PostgreSQL on iSCSI Fail to Start at Boot

Published by under Linux,Mysql,Postgresql

You are hosting Mysql or PostgreSQL data directory on iSCSI disks but the service fails to start at server’s boot. The service does not find the directory. However, you can start the service manually if you log on the server once SSH is available.

Mysql / PostgreSQL on iSCSI do not start

These are logs for Mariadb but they would be similar for Mysql:

mariadbd[795]: 0 [Note] /usr/sbin/mysqld (mysqld 10.5.9-MariaDB-1:10.5.9+maria~buster-log) starting as process 795 ...
mariadbd[795]: 0 [Warning] Can't create test file /opt/db/data/database_server.lower-test
mariadbd[795]: #007/usr/sbin/mysqld: Cannot change dir to '/var/lib/mysql/data/' (Errcode: 2 "No such file or directory")
mariadbd[795]: 0 [ERROR] Aborting
systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
systemd[1]: mariadb.service: Failed with result 'exit-code'.
systemd[1]: Failed to start MariaDB 10.5.9 database server.


You may also get logs similar to these for PostgreSQL. I stored PostgreSQL on a XFS partition, hosted on LVM for a more flexible disk space management, such as adding space while the filesystem is mounted. This is probably the main reason why iSCSI disks are popular.

systemd: mounting /var/lib/pgsql
starting PostgreSQL database server
sd 2:0:0:0: [sdb] attached SCSI disk
xfs (dm-4): Mounting V4 Filesystem
postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty
postgresql.service: control process exited, code=exited status=1
Failed to start PostgreSQL database server.


The database starting manually after the boot indicates there’s most likely a problem with the services boot order. Databases should start after iscsi disks are made available. You can solve this issue adding “After=remote-fs.target” in the service systemd file such as /usr/lib/systemd/system/postgresql-9.5.service for PosgreSQL for instance. This is a way to manage service precedence and dependencies.

Note you may lose these changes next time the package is upgraded. Systemd lets you create an extra file where you can define your own settings. It will never modify it since this is your own file.
Just create /etc/systemd/system/mariadb.service.d/override.conf as follow for Mariadb, it is the same process for Mysql or PostgreSQL:

[Service]
Environment="UMASK_DIR=0750"

[Unit]
After=remote-fs.target


In this file, I also changed the default data directory permissions so users in Mysql group can walk into it.
Run systemctl daemon-reload so it takes the new settings into account and reboot the server. The database service should now start.

 

No responses yet

May 07 2021

Nginx Behind Reverse Proxy 301 https to http Redirect When URL has no Trailing Slash

Published by under Nginx

An Nginx web server hosted on Kubernetes was sending back to me permanent 301 http redirects although I was sending https requests. The web server was behind a reverse proxy that was also running Nginx but it would be the same story with haproxy or another. Worst, the reverse proxy was redirecting http requests to https (this is normal behaviour) but without a trailing slash, creating a loop!

Nginx http redirect behind proxy


Whenever I tried to connect to https://mydomain/app, I got a redirect to http://mydomain/app/. And the other way around… A simple curl confirmed the redirect:

$ curl -v https://mydomain/app
[...]
 > 
 * Connection state changed (MAX_CONCURRENT_STREAMS == 128)!
 < HTTP/2 301 
 < date: Thu, 06 May 2021 08:56:48 GMT
 < content-type: text/html
 < content-length: 170
 < location: http://mydomain/app/
 [...]
 <html>
 <head><title>301 Moved Permanently</title></head>
 <body>
 <center><h1>301 Moved Permanently</h1></center>
 <hr><center>nginx/1.19.10</center>
 </body>
 </html>


Nginx adds a trailing slash because that’s its default behaviour as describe on the Nginx documentation:

“In response to a request with URI equal to this string, but without the trailing slash, a permanent redirect with the code 301 will be returned to the requested URI with the slash appended.” If this is not desired, an exact match of the URI and location could be defined with 2 blocks:

location /app/ {
    [...]
}

location = /app {
    [...]
}


Right… but this cannot be done on the root directory and you don’t want to do this for every single directory. Don’t expect developers to give you a ring every time they create a new directory!


Accept URIs with and without a Trailing Slash

Nginx provides another way to accept URLs with or without a trailing slash, other than duplicating location blocks.
try_files lets you do that by checking the existence of files in the specified order and uses the first found file to process the request.
It is even possible to check a directory’s existence by appending a slash to $uri which gives the following configuration lines:

location / {
    try_files $uri $uri/index.html $uri/ =404;
}


Sounds perfect but there’s a catch. Once I’ve reloaded Nginx, the web browser could fetch no more CSS and Javascript links.
Checking the code returned to the browser, CSS links have been changed from /css/my_file.css to /my_file.css.

The problem is that try_files will source the file $uri/index.html but leaves the URI as /app so any relative URI will be relative to / (or the parent directory) and not /app/.

OK, that works but I need to change all the links to absolute path. From a developer point of view, I would understand this is not something they want.


Relative Redirects

We want something cleaner but we can’t prevent Nginx’s default behaviour from appending a trailing slash.
Let’s focus on the https to http redirect. This is where the main problem lies and most browsers deny that kind of redirection… This is why the problem might be visible on curl but most browsers.

Nginx redirects to the full (or absolute) URL and a trailing slash. The definite solution is to do a relative redirect:

absolute_redirect off;


Reload nginx and run another test with curl:

$ curl -v https://mydomain/app
[...]
 > 
 * Connection state changed (MAX_CONCURRENT_STREAMS == 128)!
 < HTTP/2 301 
 < date: Thu, 06 May 2021 08:56:48 GMT
 < content-type: text/html
 < content-length: 170
 < location: /app/
 [...]
 <html>
 <head><title>301 Moved Permanently</title></head>
 <body>
 <center><h1>301 Moved Permanently</h1></center>
 <hr><center>nginx/1.19.10</center>
 </body>
 </html>


The client will now just reconnect with the protocol he used in the first request.

 

One response so far

Next »