Monitoring ProxySQL with PMM in Docker

A guy watching himself on a monitor
Monitoring can be an obsession 🙂

ProxySQL is currently the only open source proxy for MySQL and MariaDB that is driven by the community. It is the default proxy for Percona Xtradb Cluster and it also works perfectly with asynchronous replication. And I think that it can make sense to use it even if we have a standalone MySQL server. Let me know if you want me to explore more this topic.

What we want to achieve here is to test a ProxySQL instance monitored by PMM (Percona Monitoring and Management). We want to have it in a Docker container that we can setup in minutes.

ProxySQL meets PMM (in Docker)

If you missed my previous article about Testing PMM with Docker, please read that first. It provides the steps to setup a MySQL container and a MariaDB container, monitored by PMM – as well as the workarounds for some minor problems you will encounter.

Now we are going to add ProxySQL to the MariaDB container. Then we will instruct pmm-admin to monitor ProxySQL.

Important note: Installing ProxySQL in the same machine as the database is not a good choice. Definitely, I wouldn’t do such a thing in production. But our purpose here is to add a proxy and see the metrics appear in PMM, nothing more. I decided to do it in the simplest way. If you dislike this idea, you should find quite easy to change these steps to install ProxySQL in a dedicated Ubuntu container, or in some container that runs your application.

The steps

All these steps need to be executed in MariaDB container. I didn’t try to do it in MySQL container. It should mostly work, but I suspect you may find some minor problems – if you try, please let me know how it went with a comment.

# install ProxySQL
apt-get install -y proxysql
service proxysql start
mysql -uadmin -padmin -h 127.0.0.1 -P6032 -e "INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 3306);"

# create ProxySQL user in MariaDB
mysql -uroot -psecret -h 127.0.0.1 -e "CREATE USER monitor@127.0.0.1 IDENTIFIED BY 'secret';"
mysql -uroot -psecret -h 127.0.0.1 -e "GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO monitor@127.0.0.1;"

# configue ProxySQL (in SQLite)
mysql -uadmin -padmin -h127.0.0.1 -P6032 -e "UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';"
mysql -uadmin -padmin -h127.0.0.1 -P6032 -e "UPDATE global_variables SET variable_value='secret' WHERE variable_name='mysql-monitor_password';"
mysql -uadmin -padmin -h127.0.0.1 -P6032 -e "UPDATE global_variables SET variable_value='1000' WHERE variable_name IN ('mysql-monitor_connect_interval', 'mysql-monitor_ping_interval', 'mysql-monitor_read_only_interval');"
mysql -uadmin -padmin -h127.0.0.1 -P6032 -e "INSERT INTO mysql_replication_hostgroups VALUES (1, 2, 'mariadb');"
mysql -uadmin -padmin -h127.0.0.1 -P6032 -e "LOAD MYSQL VARIABLES TO RUNTIME;"
mysql -uadmin -padmin -h127.0.0.1 -P6032 -e "LOAD MYSQL SERVERS TO RUNTIME;"
mysql -uadmin -padmin -h127.0.0.1 -P6032 -e "SAVE MYSQL VARIABLES TO DISK;"
mysql -uadmin -padmin -h127.0.0.1 -P6032 -e "SAVE MYSQL SERVERS TO DISK;"
mysql -uadmin -padmin -h127.0.0.1 -P6032 -e "INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('root', 'secret', 1);"
mysql -uadmin -padmin -h127.0.0.1 -P6032 -e "LOAD MYSQL USERS TO RUNTIME;"
mysql -uadmin -padmin -h127.0.0.1 -P6032 -e "SAVE MYSQL USERS TO DISK;"

# please test if you can connect MariaDB via ProxySQL.
# if it works you should see mysql, information_schema, performance_schema
mysql -h127.0.0.1 -P6033 -uroot -psecret -e "SHOW SCHEMAS;"

# monitor ProxySQL in PMM
pmm-admin add proxysql:metrics --dsn "admin:admin@tcp(localhost:6032)/"

Note that:

  • We kept the default user and password for ProxySQL superuser, and for everything else the password is secret. Change all these things if you plan to work with sensitive data, even if these containers are running in your laptop.
  • The second-last command is something that you should run manually to check if the connection via ProxySQL works.
  • ProxySQL documentation is stored in SQLite. We edited it using mysql client because it works well. Unfortunately some other clients, like mycli, will not work.

Does it work?

Let’s see. Let’s go to localhost with our browser, then let’s click on HA -> ProxySQL Overview.

ProxySQL overview is in HA menu
Select ProxySQL Overview in PMM
ProxySQL latency in PMM
ProxySQL data is now appearing in PMM!

So yes, it works. Jolly good!

But let me know if you experience any problems not described here.

Toodle pip,
Federico

Leave a Reply

Your email address will not be published. Required fields are marked *