Downloads

Blog

Setting up a ProxySQL Sidecar Container

March 23, 2020

Author

Jake Davis

Insight for DBAs

ProxySQL

Share this Post:

Recently, a client of ours, Duolingo, was using Aurora was reaching the max connection limit of 16,000 (that is Aurora’s hard limit for all instance classes). In this case, Percona recommended implementing ProxySQL to manage connections (now their max connections only peak to 6000!).

Duolingo’s main application is run in AWS ECS (Elastic Container Service) so they decided to attach ProxySQL as a sidecar container to the main application container.

What is a Sidecar?

The sidecar design concept consists of a main application container plus a second container running a service to support, enhance, or extend the main container. The second container is “attached” to the main container, much like a sidecar attaches to a motorcycle.

The most common example you’ll likely find is a web server plus a log forwarding service (pictured above). The web server is the main container, the log forwarder is the sidecar, and in this case they actually share data (the volume is mounted to both containers). The web server writes the data, while the log forwarder just reads and sends it along (perhaps to some aggregation service like Splunk).

Sidecars don’t have to just be logs. It could be for monitoring or even a MySQL proxy…

Launch A ProxySQL Sidecar

Let’s set up a WordPress container (our main container) and a ProxySQL 2 sidecar with an Aurora cluster backend.

Prerequisites

Docker and Docker Compose installed.

An Aurora cluster (I used 2.07.0) with at least one writer and one reader instance.

A ProxySQL “monitor” user and “wordpress” user created on Aurora.

mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'MonPass123^';<br>mysql> GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'%';<br><br>mysql> CREATE USER 'wordpress'@'%' IDENTIFIED BY 'WPPass123^';<br>mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON wordpress.* TO 'wordpress'@'%';

1	mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'MonPass123^';<br>mysql> GRANT REPLICATION CLIENT ON . TO 'monitor'@'%';<br><br>mysql> CREATE USER 'wordpress'@'%' IDENTIFIED BY 'WPPass123^';<br>mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON wordpress.* TO 'wordpress'@'%';

A “wordpress” database created on Aurora.

mysql> CREATE DATABASE wordpress;

1	mysql> CREATE DATABASE wordpress;

Configuration

1. Create a project directory and change into it.

$ mkdir wordpress ; cd $_

1	$ mkdir wordpress ; cd $_

2. Create a Dockerfile to build our sidecar.

This will install ProxySQL2 from ProxySQL repositories and copy our (soon to be created) ProxySQL config file from our host into the image.

Note: ProxySQL v1.4.x cannot detect Aurora failovers so v2.0.x is required.

$ vim Dockerfile<br><br>FROM debian:buster-slim<br><br>RUN apt-get update ; apt-get -y install lsb-release gnupg2 curl<br><br>RUN curl -L 'https://repo.proxysql.com/ProxySQL/repo_pub_key' | apt-key add - && echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/$(lsb_release -sc)/ ./ | tee /etc/apt/sources.list.d/proxysql.list<br><br>RUN apt-get update ; apt-get -y install proxysql<br><br>COPY proxysql.cnf /etc/proxysql.cnf

$ vim Dockerfile FROM debian:buster-slim RUN apt-get update ; apt-get -y install lsb-release gnupg2 curl RUN curl -L 'https://repo.proxysql.com/ProxySQL/repo_pub_key' | apt-key add - && echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/$(lsb_release -sc)/ ./ | tee /etc/apt/sources.list.d/proxysql.list RUN apt-get update ; apt-get -y install proxysql COPY proxysql.cnf /etc/proxysql.cnf

3. Create our ProxySQL configuration file.

When adding your nodes to the mysql_servers, you must use the instance endpoint and not the Aurora reader/writer VIPs.

The reason is after a failover ProxySQL will move the individual instances around within the hostgroups to direct read/write traffic. It determines an instance’s role itself based on innodb_read_only and does not need to wait for Aurora to update the VIPs.

This is advantageous. Our client reported failovers taking as little as 15 seconds verse 30 – 60 seconds for Aurora’s DNS failovers.

$ vim proxysql.cnf<br><br>datadir="/var/lib/proxysql"<br>errorlog="/var/lib/proxysql/proxysql.log"<br><br>admin_variables={<br>admin_credentials="admin:admin"<br>mysql_ifaces="0.0.0.0:6032"<br>}<br><br>mysql_variables={<br>monitor_username="monitor"<br>monitor_password="MonPass123^"<br>monitor_read_only_timeout=60000<br>monitor_connect_timeout=60000<br>monitor_ping_timeout=60000<br>mysql_ping_timeout_server=500<br>}<br><br>mysql_servers=({<br>hostname="my-aurora-instance-endpoint-1.zzzzg1gfxyo9.us-west-2.rds.amazonaws.com"<br>port=3306<br>hostgroup=1<br>},{<br>hostname="my-aurora-instance-endpoint-2.zzzzg1gfxyo9.us-west-2.rds.amazonaws.com"<br>port=3306<br>hostgroup=2<br>})<br><br>mysql_users=({<br>username="wordpress"<br>password="WPPass123^"<br>default_hostgroup=1<br>})<br><br>mysql_replication_hostgroups=({<br>writer_hostgroup=1<br>reader_hostgroup=2<br>check_type="innodb_read_only"<br>comment="Aurora Wordpress"<br>})

$ vim proxysql.cnf datadir="/var/lib/proxysql" errorlog="/var/lib/proxysql/proxysql.log" admin_variables={ admin_credentials="admin:admin" mysql_ifaces="0.0.0.0:6032" } mysql_variables={ monitor_username="monitor" monitor_password="MonPass123^" monitor_read_only_timeout=60000 monitor_connect_timeout=60000 monitor_ping_timeout=60000 mysql_ping_timeout_server=500 } mysql_servers=({ hostname="my-aurora-instance-endpoint-1.zzzzg1gfxyo9.us-west-2.rds.amazonaws.com" port=3306 hostgroup=1 },{ hostname="my-aurora-instance-endpoint-2.zzzzg1gfxyo9.us-west-2.rds.amazonaws.com" port=3306 hostgroup=2 }) mysql_users=({ username="wordpress" password="WPPass123^" default_hostgroup=1 }) mysql_replication_hostgroups=({ writer_hostgroup=1 reader_hostgroup=2 check_type="innodb_read_only" comment="Aurora Wordpress" })

4. Create our Docker Compose file to bring it all together.

This calls the “Dockerfile” to build our ProxySQL image, download the latest WordPress image, and start the containers.

WordPress depends_on ProxySQL. This instructs Docker Compose to start ProxySQL first. If WordPress launched first, it could not connect to Aurora.

We are using Docker’s internal naming to define the database endpoint. It’s simply the ProxySQL container name – WORDPRESS_DB_HOST: proxysql:6033

Take a look at the volumes mounted to each container. It is not required in this case, but I mounted the ProxySQL volume to the WordPress container as well.

$ vim docker-compose.yml<br><br>version: '3'<br><br>services:<br>   wordpress:<br>     image: wordpress:latest<br>     container_name: wordpress<br>     depends_on:<br>       - proxysql<br>     ports:<br>       - "8080:80"<br>     volumes:<br>       - wordpress:/var/www/html<br>       - proxysql:/var/lib/proxysql<br>     environment:<br>       WORDPRESS_DB_HOST: proxysql:6033<br>       WORDPRESS_DB_USER: wordpress<br>       WORDPRESS_DB_PASSWORD: WPPass123^<br>       WORDPRESS_DB_NAME: wordpress<br>     restart: always<br><br>   proxysql:<br>     build: .<br>     container_name: proxysql<br>     volumes:<br>       - proxysql:/var/lib/proxysql<br>     restart: always<br>     command: ["proxysql", "-f", "-D", "/var/lib/proxysql", "-c", "/etc/proxysql.cnf"]<br><br>volumes:<br>    wordpress:<br>    proxysql:

$ vim docker-compose.yml version: '3' services: wordpress: image: wordpress:latest container_name: wordpress depends_on: - proxysql ports: - "8080:80" volumes: - wordpress:/var/www/html - proxysql:/var/lib/proxysql environment: WORDPRESS_DB_HOST: proxysql:6033 WORDPRESS_DB_USER: wordpress WORDPRESS_DB_PASSWORD: WPPass123^ WORDPRESS_DB_NAME: wordpress restart: always proxysql: build: . container_name: proxysql volumes: - proxysql:/var/lib/proxysql restart: always command: ["proxysql", "-f", "-D", "/var/lib/proxysql", "-c", "/etc/proxysql.cnf"] volumes: wordpress: proxysql:

5. Now we’re ready to bring up the containers. This will take several minutes as Docker needs to download and build our images.

$ docker-compose up -d<br>...<br>Creating network "wordpress_default" with the default driver<br>Creating proxysql ... done<br>Creating wordpress ... done

1	$ docker-compose up -d<br>...<br>Creating network "wordpress_default" with the default driver<br>Creating proxysql ... done<br>Creating wordpress ... done

Review

Once finished, check that the containers are up.

$ docker ps -a<br>CONTAINER ID        IMAGE                COMMAND                  CREATED             STATUS              PORTS                  NAMES<br>79babc5c1c0e        wordpress:latest     "docker-entrypoint.s…"   23 seconds ago      Up 21 seconds       0.0.0.0:8080->80/tcp   wordpress<br>d8ea54cc02e5        wordpress_proxysql   "proxysql -f -D /var…"   24 seconds ago      Up 22 seconds                              proxysql

1	$ docker ps -a<br>CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES<br>79babc5c1c0e wordpress:latest "docker-entrypoint.s…" 23 seconds ago Up 21 seconds 0.0.0.0:8080->80/tcp wordpress<br>d8ea54cc02e5 wordpress_proxysql "proxysql -f -D /var…" 24 seconds ago Up 22 seconds proxysql

Then visit your WordPress site! http://127.0.0.1:8080/

Summary

The sidecar design pattern is an easy solution to SPOF (single point of failure) because each application container gets its very own ProxySQL. For applications that are already containerized this is a simple and effective approach. Using Kubernetes? You can launch a sidecar container into the same pod as your application container.

Lastly, there is an official ProxySQL Docker image on Docker Hub you can pull instead of building your own!

Duolingo is the most popular language-learning platform worldwide, with over 300 million total learners. Their mission is to make education free and accessible to everyone. They offer 94 total courses across 38 distinct languages, from the world’s top most-spoken languages to endangered languages like Hawaiian, Navajo, and Scottish Gaelic. Duolingo is available on iOS, Android and web at www.duolingo.com.