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.
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 firstname.lastname@example.org IDENTIFIED BY 'secret';" mysql -uroot -psecret -h 127.0.0.1 -e "GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO email@example.com;" # 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)/"
- 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.
So yes, it works. Jolly good!
But let me know if you experience any problems not described here.