ProxySQL

May First uses proxysql so databases appear to be on the localhost, while really living on dedicated database servers (origin servers).

The proxysql package is installed on web servers and any server from which a client needs to connect to a database (these servers are referred to as proxy servers).

Database clients (and client configurations) have no knowledge of where their database really lives - they simply attempt to connect via localhost.

Meanwhile, proxysql is listening on localhost and:

  • intercepts all database requests via the default port of 3306 and the unix socket.
  • determines (based on the database username) which server should be queried
  • transparently proxies the connection to the remote database server

What happens on the proxysql server (aka proxy)

The proxysql package maintains it’s own data locally in mysql-like tables. On each proxy server, a /root/.my.cnf file is installed which provides access to the proxysql admin tables via a regular mysql client. So, you can simply run mysql as root and login to the proxysql admin tables.

These tables keep track of which mysql user belongs on which origin server.

We populate these tables the first time proxysql is installed and each time a mysql user is added to any database anywhere (via a trigger in the control panel).

How?

  • We maintain a list of all origin servers in /etc/proxysql-origins.yml, a file generated by ansible.

  • We provide a python script (/usr/local/sbin/proxy-load). It parses this list and connects over ssh to query each origin server (on each origin server it runs the proxysql-get-users script via sudo to get a list of users). Based on the results of the query it updates the proxysql internal tables with the latest user data.

  • The control panel triggers the proxysql-load command every time a new mysql user is added (and also it is run once an hour on a systemd-timer).

What happens on the mariadb origin server (aka origin)

  • We add a script (proxysql-get-users) which queries the local database for a list of users.

  • We add a non-privileged user (proxysql) which is designed to run the proxysql-get-users script via sudo. This user is accessible via ssh to the root user on all servers running proxysql (via ssh public keys).

How do I use it in ansible?

On mysql servers add:

proxysql_profile: origin

On proxy servers add:

proxysql_profile: proxy

Add both servers to the proxysql group.

How do I tune or configure proxysql?

tldr;: Don’t edit /etc/proxysql.cnf - it won’t do anything.

proxysql has a elegantly designed configuration system that has multiple layers, requiring some reading of the manual to fully understand.

By way of example, to change the setting mysql-log_unhealthy_connections to false:

  1. Login to the mysql-like proxysql admin interface: mysql
  2. Switch to the “main” database (aka “memory”, not live): USE main;
  3. Check existing value: SELECT * FROM global_variables WHERE variable_name = 'mysql-log_unhealthy_connections';
  4. Update it in memory: UPDATE global_variables SET variable_value = 'false' where variable_name = 'mysql-log_unhealthy_connections';
  5. Load the change into the live “runtime”: LOAD MYSQL VARIABLES FROM MEMORY; (note, we load “MYSQL” variables because the variable starts with ‘mysql’ - if the variable starts with ‘admin’ we would run: LOAD ADMIN VARIABLES FROM MEMORY)
  6. If you want this setting to persist on a restart, run: SAVE MYSQL VARIABLES TO DISK to save it to the sqlite configuration database.

The /etc/proxysql.cnf file is only consulted when proxysql is initialized or the sqlite database is deleted.

How is tls configured?

Proxysql is configured to support tls encrypted client connections. This doesn’t make a lot of sense (why encrypt a connection via a socket or localhost?), but mariadb client libraries now expect an encrypted connection so we have to provide one.

Fortunately, mariadb doesn’t validate connections from localhost so we use a snake oil certificate, which is generally good for 10 years.

We have a monthly system timer that regenerates it (proxysql-generate-x509.{timer,service}).

Limitations

The primary limitation is that it’s not possible for the connection between proxysql and the origin mysql server to be encrypted (see issue1459).

That means origin servers must be kept in the same cabinet as the proxies they server to avoid sending unencrypted database traffic over the net.

See also

We have documented the steps to convert a MOSH to use proxysql.