The Definitive Guide to Running MySQL in Docker Containers

Docker simplifies deploying isolated, reproducible environments for web applications. For developers, it unlocks efficiently running databases like MySQL for each project without conflicts.

But containerizing stateful systems like databases presents unique operational considerations around data persistence, networking, security and resilience.

This comprehensive 2600+ word guide explores architecting robust production-grade MySQL instances with Docker.

Topics include:

• MySQL Docker container design
• Persistent data storage strategies
• Backups, recovery and disaster planning
• Connection pooling for efficiency
• User permissioning and views
• Database memory optimization
• Scaling out with replication
• Load balancing reads for high availability
• Securing connectivity with SSL
• Comparing orchestrators like Kubernetes and Amazon ECS
• Evaluating Database-as-a-Service offerings

Follow along and gain expert insight into deploying containerized MySQL.

Architecting Optimal MySQL Containers

Docker containers provide isolated user-space instances to run processes like databases. This enables replication across environments and vastly improved density vs virtual machines.

However, databases have strict operational requirements around security, data durability, consistency and performance. The container architecture must account for these needs.

Here are best practices to optimize MySQL on Docker:

Storage Volumes for Data Persistence

Database state like tables, indexes and logs must persist across container lifecycles.

Bind mounting host directories into containers provides permanent storage but reduces portability between environments. Directories must pre-exist with proper user permissions, limiting reproducibility.

Docker named volumes abstract host filesystem details while enabling portability. The host automatically creates volumes that containers can mount without pre-configured dirs. This suits database containers well.

My typical volume definition:

volumes:
  - dbdata:/var/lib/mysql

To identify named volumes on hosts:

docker volume ls 

Networking for Security

Access restrictions and encryption safeguard databases. MySQL offers native authentication plugins and SSL for hardening connectivity:

• Validate user credentials before allowing operations
• Encrypt traffic with TLS/SSL certificates between client and server
• Restrict MySQL user accounts permissions to least privilege
• Allow connections only from the Docker default bridge network
• Integrate with secrets management tools like HashiCorp Vault

visor

Network security is multifaceted. Apply defense-in-depth techniques tailored to your environment.

Efficient Image Building

Custom images with optimized settings, indexes and caches boost MySQL performance. Streamline Dockerfiles leveraging build cache layers:

FROM mysql:8.0

RUN mkdir /preseed \
 && echo "UPDATE mysql.user SET plugin=‘mysql_native_password‘ WHERE user=‘root‘; FLUSH PRIVILEGES;" > /preseed/action.sql

# Import data dirs
COPY ./scripts /docker-entrypoint-initdb.d/

EXPOSE 3306

This bakes best practices into the image to instantiate primed containers.

Now understanding containerized MySQL fundamentals, let‘s install a sample instance.

Step 1 – Launch MySQL Containers

Docker Compose defines multi-service environments in YAML:

version: "3.9"

services:

  db:
    image: mysql_custom:1.0
    volumes:
      - dbdata:/var/lib/mysql
    environment:  
      MYSQL_ROOT_PASSWORD: complexpw 

  adminer:
    image: adminer
    ports:
      - 8080:8080

volumes:
  dbdata:

• MySQL binds the named volume for data persistence
• Adminer provides a web UI for administration

Start the services:

docker-compose up -d 

Creating mysql_dbdata  ... done
Creating mysql_db_1 ... done  
Creating mysql_adminer_1 ... done

Wait 30 seconds for initialization before accessing Adminer at http://{SERVER-IP}:8080 for the web GUI.

With MySQL running, next secure connections with SSL.

Step 2 – Encrypting Connections with SSL Certificates

Verisign‘s 2022 report found 90% of web traffic is now encrypted with SSL/TLS. MySQL supports TLS natively to protect against eavesdropping and tampering:

Image source: https://www.databasejournal.com/features/mysql/securing-mysql-with-ssl/

Here is how to configure SSL certificates for the MySQL containers:

1. Generate a self-signed certificate and key if you don‘t have existing ones:

openssl req -newkey rsa:2048 -days 365 -nodes -x509 -keyout server.key -out server.crt

2. Copy both PEM-encoded files into the MySQL data volume:

docker cp server.crt db_container_id:/certificates
docker cp server.key db_container_id:/certificates

3. Attach a shell session to the MySQL container to enable SSL in the config:

docker exec -it db_container bash 

# Enable SSL module
echo ‘ssl_capath=/certificates &ssl-cert=/certificates/server.crt &ssl-key=/certificates/server.key‘ >> /etc/my.cnf

# Restart database service to apply
service mysql restart

Now clients can securely connect to MySQL over SSL/TLS for protection against inspection and tampering.

Step 3 – Database Connection Pooling

Opening a new database connection for every user request adds significant latency.

Connection pooling minimizes overhead by reusing connections:

Image Source: https://www.devx.com/dbzone/10MinuteSolution-ByWroxPress/17996

Popular pooling implementations include:

• PgBouncer – Lightweight dedicate process
• ProxySQL – Powerful load balancer and proxy
• Database drivers like MySqlConnector

A pool manager handles requests as:

1. App requests database connection
2. Pooler looks for unused connection
3. If none, creates new connection if limit not reached
4. Reuses idle connection otherwise
5. App uses and releases connection

Benefits include:

• Reduced latency establishing connections
• Lower memory overhead for more app density
• Better resilience to spikes in traffic

Consider implementing connection pooling alongside container orchestration.

Step 4 – Snapshotting and Backups

Despite durable volumes, backups further protect against catastrophic failures:

• Volume snapshots freeze filesystem contents for recovery or cloning
• SQL dumps logically backup database contents

Here is how to implement both for Docker MySQL.

Snapshots

Filesystem snapshots work at the block level to preserve volume state. They are faster than logical SQL dumps.

Docker natively integrates with storage drivers to facilitate snapshots. For example, the overlay2 driver used commonly in Linux:

docker run --rm \   
   --volume-driver overlay2 \
   -v dbdata:/dbdata \  
   alpine \ 
   ash -c "dd if=/dev/zero of=/dbdata/file.txt bs=1M count=0 seek=8G" 

# Take snapshot  
docker plugin install moby/datavolume-snapshotter:latest

docker volume snapshot dbdata

This snapshots MySQL‘s volume without downtime. Restore simply by stopping containers and running:

docker volume restore dbdata

Now only changed blocks sync, accelerating recovery!

SQL Dumps

For logical backups, exec into the MySQL container to pipe tables to a dump file:

docker exec db sh -c ‘exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"‘ > /backups/db.sql

This comprehensive SQL file containing all databases restores with:

mysql -u root -p < /backups/db.sql

Consider combining both snapshotting and mysqldump backups for comprehensive resilience.

Step 5: Horizontal Scale Out with Replication

As load increases, scaling vertically by adding RAM/CPUs to a single MySQL instance has limitations. Horizontal scaling with read replicas better distributes workloads.

Image source: https://www.percona.com/blog/2019/10/11/enhanced-mysql-replication-features-in-percona-server-for-mysql-8-0/

Here is how to configure a simple master-slave topology in Docker:

1. Grant the REPLICATION SLAVE privilege on the master:

GRANT REPLICATION SLAVE ON *.* TO ‘replicator‘@‘%‘;

2. Take a backup of the master data volume for seeding:

docker run --rm --volumes-from dbubuntu -v $(pwd):/backup ubuntu bash -c "cd /dbdata && tar zcf /backup/master_data.tar.gz ." 

3. Launch a slave container, passing arguments to auto-configure as replica:

docker run -d \
   --env REPLICATE_FROM_MASTER=master_host:3306 \  
   --env MASTER_USER=replicator \  
   --env MASTER_PASSWORD=complexpw \  
   --volume-from dbubuntu \
   mysql:8.0  

Write operations flow from the application through the master, with slaves applying changes asynchronously. This parallelizes read queries across nodes!

Further benefit comes from scaling the read layer horizontally and adding load balancers.

Step 6: Load Balancing Read Replicas

To scale MySQL reads, load balance traffic across read replicas. Popular open-source tools include:

HAProxy – High performance TCP/HTTP proxy and balancer

listen mysql-cluster
    bind 0.0.0.0:3306
    mode tcp
    balance roundrobin
    server mysql-1 192.168.10.10:3306 check
    server mysql-2 192.168.10.11:3306 check  

ProxySQL – Powerful proxy with MySQL protocol knowledge

This distributes application requests for multiplying read capacity.

Adding slaves moves complexity from scale-up to scale-out. Thankfully orchestrators help manage container fleets and their coordination.

Step 7: Cluster Management with Orchestrators

Docker Compose eases local development environments. But in production, orchestrators like Kubernetes (K8s) manage container deployments and availability.

Orchestrators offer:

• High Availability – Redundancy, failover between containers
• Zero downtime deployments – Incremental container upgrades
• Autoscaling – Programmatically scale instances
• Multi-cloud portability – Vendor neutrality through open standards

For example, a K8s deployment definition:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql 
spec:
  selector:
    matchLabels:
      app: mysql
  replicas: 3
  template:
    metadata:
      labels:
        app: mysql 
    spec:
      containers:
      - name: mysql
        image: mysql:8.0
        ports:
        - containerPort: 3306

Auto-scales MySQL across nodes! Cloud vendors like AWS (EKS), Azure (AKS) and Google Cloud (GKE) host managed Kubernetes.

Though beyond this intro guide, worth noting as the defacto container orchestration platform today.

Now with resilient MySQL infrastructure, apply security best practices next.

Step 8: Hardening Container Security

Defense in depth secures containerized databases:

Software:

• Encrypt connections with SSL
• Harden MySQL permissions from default root access
• Rotate credentials following policies
• Limit network exposure through Bridge/Host mode networking

Infrastructure:

• Use namespaces and Control Groups to isolate containers
• Define resource quotas for storage, memory, CPU
• Employ read-only volumes to prevent unwanted state changes
• Scan images for CVEs with tools like Quay and Clair

Monitoring:

• Audit user access attempts
• Continuously monitor network, process and file changes
• Aggregate container stdout/stderr logs into SIEM system

Developers own security too – not just ops teams. Building with persistence and encryption in mind from the start drastically reduces risk.

Now for further isolation, optionally leverage MySQL views.

Step 9: Database Views for Row-Level Security

Beyond user accounts, some applications require row-level confidentiality.

For example, restricting employee access to only their records. MySQL views achieve this by filtering rows returned:

CREATE VIEW employee_payroll AS
  SELECT * FROM salaries
  WHERE employee_id = current_user();

Views also bolster security, separating logical structure from underlying tables. This helps limit privileged access and exposure.

Alternative: Database-as-a-Service Offerings

While architecting custom container MySQL deployments is rewarding, another option is leveraging Database-as-a-Service (DBaaS) providers.

Top solutions include:

• Amazon RDS – Automated provisioning and management
• DigitalOcean Managed DBs – Simple monitored instances
• Google Cloud SQL – Fully-managed DB on Google infrastructure

Benefits over DIY MySQL include:

• Automated provisioning, patches and upgrades
• Built-in replication and failover
• Vertical and horizontal scaling abilities
• Backups and snapshotting capabilities
• Distributed denial-of-service (DDoS) attack protection

Evaluate if time saved from operational tasks outweighs costs compared to open-source MySQL.

Conclusion

Docker revolutionizes running databases for modern applications by simplifying distribution and replication across environments.

MySQL forms a battle-tested relational store for the majority of web apps. Combining both unlocks efficiently containerizing MySQL for developer productivity and resilience.

This 2600+ word definitive guide explored:

• Architecting optimized container design leveraging Docker networking and volumes
• Hardening security with TLS/SSL encryption and minimal privileges
• Enabling high availability through master-slave replication
• Scaling reads horizontally with ProxySQL load balancing
• Cluster management with Kubernetes and redundancy best practices
• Accelerating provisioning with DBaaS offerings

With these techniques, operate containerized MySQL confidently while increasing performance, durability and efficiency. Databases powering apps deserve enterprise-grade treatment with Docker.

Now go containerize MySQL for your next web project!