Scaling ProxySQL Rapidly in Kubernetes

1. There will be a request to your cloud service provider for a new VM instance.
2. Once the instance is up and running as part of the group, it will need to install ProxySQL along with supporting packages such as consul (agent) and consul-template.
3. Once the packages are installed, they'll need to be configured to work with the consul server nodes as well as the ProxySQL nodes that are participating in the ProxySQL cluster.
4. The new ProxySQL host will announce to Consul that it’s available, which in turn will update all the other participating nodes in the ProxySQL cluster.

1. Each container should run only a single process. We know we’re working with ProxySQL, consul (agent), and consul-template, so these will all need to be in their own containers.
2. The primary process running in each container should run as PID 1.
3. The primary process running in each container should not run as root.
4. Log output from the primary process in the container should be sent to STDOUT so that it can be collected by Docker logs.
5. Containers should be as deterministic as possible — meaning they should run the same (or at least as much as possible) regardless of what environment they are deployed in.

Consul (Agent) Container

FROM centos:7
 RUN yum install -q -y unzip wget && \
  yum clean all
 RUN groupadd consul && \
  useradd -r -g consul -d /var/lib/consul consul
 RUN mkdir /opt/consul && \
  mkdir /etc/consul && \
  mkdir /var/log/consul && \
  mkdir /var/lib/consul && \
  chown -R consul:consul /opt/consul && \
  chown -R consul:consul /etc/consul && \
  chown -R consul:consul /var/log/consul && \
  chown -R consul:consul /var/lib/consul
 RUN wget -q -O /opt/consul/consul.zip https://releases.hashicorp.com/consul/1.6.1/consul_1.6.1_linux_amd64.zip && \
  unzip /opt/consul/consul.zip -d /opt/consul/ && \
  rm -f /opt/consul/consul.zip && \
  ln -s /opt/consul/consul /usr/local/bin/consul
 COPY supportfiles/consul.conf.json /etc/consul/
 USER consul
 ENTRYPOINT ["/usr/local/bin/consul", "agent", "--config-file=/etc/consul/consul.conf.json"]

1. Start from CentOS 7. This is a personal preference of mine. There are probably more lightweight distributions that can be considered, such as Alpine as recommended by Google, but I’m not the best OS nerd out there so I wanted to stick with what I know.
2. Install our dependencies, which in this case are unzip and wget.
3. Create our consul user, group and directory structure.
4. Install consul.
5. Copy over the consul config file from the host where the Docker build is being performed.
6. Switch to the consul user.
7. Start consul (agent).

• Container runs a single process:
  • The ENTRYPOINT runs Consul directly, meaning nothing else is being run. Keep in mind that ENTRYPOINT specifies what should be run when the container starts. This means when the container starts it won’t have to install anything because the packages come with the image as designated by the Dockerfile, but we still need to launch Consul when the container starts.
• Process should be PID 1:
  • Any process run by ENTRYPOINT will run as PID 1.
• Process should not be run as root:
  • We switched to the Consul user prior to starting the ENTRYPOINT.
• Log output should go to STDOUT:
  • If you run Consul using the command noted in the ENTRYPOINT, you’ll see log output goes to STDOUT.
• Should be as deterministic as possible:
  • We’ve copied the configuration file into the container, meaning the container doesn’t have to get support files from anywhere else before Consul starts. The only way the nature of Consul will change is if we recreate the container image with a new configuration file.

ProxySQL Container

FROM centos:7
 RUN groupadd proxysql && \
  useradd -r -g proxysql proxysql
 RUN yum install -q -y https://github.com/sysown/proxysql/releases/download/v2.0.6/proxysql-2.0.6-1-centos67.x86_64.rpm mysql curl && \
  yum clean all
 COPY supportfiles/* /opt/supportfiles/
 COPY startstop/* /opt/
 RUN chmod +x /opt/entrypoint.sh
 RUN chown proxysql:proxysql /etc/proxysql.cnf
 USER proxysql
 ENTRYPOINT ["/opt/entrypoint.sh"]

1. Start from CentOS 7.
2. Create our ProxySQL user and group.
3. Install ProxySQL and dependencies, which in this case is curl, which will be used to poll the GCP API in order to determine what region the ProxySQL cluster is in. We’ll cover this in more detail below.
4. Move our configuration files and ENTRYPOINT script to the container.
5. Make sure the ProxySQL config file is readable by ProxySQL.
6. Switch to the ProxySQL user.
7. Start ProxySQL via the ENTRYPOINT script provided with the container.

#!/bin/bash
 dataCenter=$(curl https://metadata.google.internal/computeMetadata/v1/instance/zone -H "Metadata-Flavor: Google" | awk -F "/" '{print $NF}' | cut -d- -f1,2)
 ...
 case $dataCenter in
  us-central1)
  cp -f /opt/supportfiles/proxysql-us-central1.cnf /etc/proxysql.cnf
  ;;
  us-east1)
  cp -f /opt/supportfiles/proxysql-us-east1.cnf /etc/proxysql.cnf
  ;;
 esac
 ...
 exec proxysql -c /etc/proxysql.cnf -f -D /var/lib/proxysql

• Container runs a single process:
  • ENTRYPOINT calls the entrypoint.sh script, which does some conditional logic based on the regional location of the container, then ends by running ProxySQL. This means at the end of the process ProxySQL will be the only process running.
• Process should be PID 1:
  • The command “exec” at the end of the ENTRYPOINT script will start ProxySQL as PID 1.
• Process should not be run as root:
  • We switched to the ProxySQL user prior to starting the ENTRYPOINT.
• Log output should go to STDOUT:
  • If you run ProxySQL using the command noted at the end of the ENTRYPOINT script you’ll see that log output goes to STDOUT.
• Should be as deterministic as possible:
  • We’ve copied the potential configuration files into the container. Unlike Consul, there are multiple configuration files and we need to determine which will be used based on the region the container lives in, but the configuration files themselves will not change unless the container image itself is updated. This ensures that all containers running within the same region will behave the same.

Consul-template container

FROM centos:7
 RUN yum install -q -y unzip wget mysql nmap-ncat curl && \
  yum clean all
 RUN groupadd consul && \
  useradd -r -g consul -d /var/lib/consul consul
 RUN mkdir /opt/consul-template && \
  mkdir /etc/consul-template && \
  mkdir /etc/consul-template/templates && \
  mkdir /etc/consul-template/config && \
  mkdir /opt/supportfiles && \
  mkdir /var/log/consul/ && \
  chown -R consul:consul /etc/consul-template && \
  chown -R consul:consul /etc/consul-template/templates && \
  chown -R consul:consul /etc/consul-template/config && \
  chown -R consul:consul /var/log/consul
 RUN wget -q -O /opt/consul-template/consul-template.zip https://releases.hashicorp.com/consul-template/0.22.0/consul-template_0.22.0_linux_amd64.zip && \
  unzip /opt/consul-template/consul-template.zip -d /opt/consul-template/ && \
  rm -f /opt/consul-template/consul-template.zip && \
  ln -s /opt/consul-template/consul-template /usr/local/bin/consul-template
 RUN chown -R consul:consul /opt/consul-template
 COPY supportfiles/* /opt/supportfiles/
 COPY startstop/* /opt/
 RUN chmod +x /opt/entrypoint.sh
 USER consul
 ENTRYPOINT ["/opt/entrypoint.sh"]

1. Start from CentOS 7.
2. Install our dependencies which are unzip, wget, mysql (client), nmap-ncat and curl.
3. Create our Consul user and group.
4. Create the consul-template directory structure.
5. Download and install consul-template.
6. Copy the configuration file, template files and ENTRYPOINT script to the container.
7. Make the ENTRYPOINT script executable.
8. Switch to the Consul user.
9. Start consul-template via the ENTRYPOINT script that’s provided with the container.

#!/bin/bash
 dataCenter=$(curl https://metadata.google.internal/computeMetadata/v1/instance/zone -H "Metadata-Flavor: Google" | awk -F "/" '{print $NF}' | cut -d- -f1,2)
 ...
 cp /opt/supportfiles/consul-template-config /etc/consul-template/config/consul-template.conf.json
 case $dataCenter in
  us-central1)
  cp /opt/supportfiles/template-mysql-servers-us-central1 /etc/consul-template/templates/mysql_servers.tpl
  ;;
  us-east1)
  cp /opt/supportfiles/template-mysql-servers-us-east1 /etc/consul-template/templates/mysql_servers.tpl
  ;;
 esac
 cp /opt/supportfiles/template-mysql-users /etc/consul-template/templates/mysql_users.tpl
 ### Ensure that proxysql has started
 while ! nc -z localhost 6032; do
  sleep 1;
 done
 ### Ensure that consul agent has started
 while ! nc -z localhost 8500; do
  sleep 1;
 done
 exec /usr/local/bin/consul-template --config=/etc/consul-template/config/consul-template.conf.json

• Container runs a single process:
  • ENTRYPOINT calls the entrypoint.sh script, which does some conditional logic based on the regional location of the container, then ends by running consul-template. This means at the end of the process consul-template will be the only process running.
• Process should be PID 1:
  • The command “exec” at the end of the ENTRYPOINT script will start consul-template as PID 1.
• Process should not be run as root:
  • We switched to the consul user prior to starting the ENTRYPOINT.
• Log output should go to STDOUT:
  • If you run Consul using the command noted at the end of the ENTRYPOINT script, you’ll see log output goes to STDOUT.
• Should be as deterministic as possible:
  • Just like ProxySQL and consul (agent), all the supporting files are packaged with the container. Yes, there is logic to determine what files should be used, but you have the assurance that the files won’t change unless you create a new version of the container image.

Putting it all together

1. Create a Kubernetes cluster if you don’t already have one. This is what provisions the Cloud Compute VMs the pods will run on.
2. Push your three Docker images to the Google container registry. This makes the images available for use by the Kubernetes engine.
3. Create your Kubernetes workload, which can be done simply via the user interface in the GCP console. All that’s required is selecting the latest version of the three containers you’ve pushed up to the registry, optionally applying some metadata like an application name, Kubernetes namespace, and labels, then selecting which cluster you want to run the workload on.

1. The pod is started.
2. The three containers will start. In Kubernetes, pods are fully atomic. All the containers start without error or the pod will not consider itself started.
3. The consul-template container will poll consul (agent) and ProxySQL on their respective ports until it’s confirmed those processes have started, then consul-template will start.
4. Consul-template will create the new SQL files meant to configure ProxySQL based on the contents of the Consul key / value store.
5. Consul-template will run the newly created SQL files against ProxySQL via its admin interface.
6. The pod is now ready to receive traffic.

The YAML