How to make MySQL inside Docker Production Ready?

It is easy to simply spin a new MySQL and assume that it is ready for the production. This can’t be further from the preparation of disaster.

Few days back I wrote about having a infrastructure docker file to do your development and I got a comment was it ready for production. Today that ‘No’ is about to change to ‘Yes’.

When application grows, the table grows and we need more in-memory, more innodb instances and more threads. However, default MySQL configuration is not changed for last 8-10 years. Machine has got faster, MySQL default config didn’t catch up to that. Thus it is important to override configurations.

This is one example:

docker-compose.yml

version: '3.2'
networks:
  dual-localhost:
    driver: bridge
services:
  mysql:
    image: mysql:5.8
    restart: always
    volumes:
        - type: bind
          source: ./mysql
          target: /var/lib/mysql
        - type: bind
          source: /var/log/mysql/
          target: /var/log/mysql/
        - type: bind
          source: ./mysql.cnf
          target: /etc/mysql/mysql.conf.d/mysql.cnf
    environment:
        - MYSQL_ROOT_PASSWORD=some_weird_password
    networks:
      - dual-localhost
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address		= 0.0.0.0
#skip-networking #insecure
#bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size		= 16M
max_allowed_packet	= 512M
thread_stack		= 192K
thread_cache_size       = 64
innodb_read_io_threads = 2
innodb_write_io_threads = 2
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit	= 8M
tmp_table_size      = 32M
query_cache_size        = 32M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
# slow-query-log=1
# slow-query-log-file=/var/log/mysql/mysql-slow.log

#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id		= 10
log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
binlog_row_image=minimal
max_binlog_size   = 256M
binlog_cache_size = 2M
binlog_rows_query_log_events = on

relay-log               = /var/log/mysql/mysql-relay-bin.log

innodb_log_file_size = 512M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
collation_server=utf8mb4_unicode_ci
character_set_server=utf8mb4

wait_timeout = 500
interactive_timeout = 28800

open_files_limit = 1024000
skip-name-resolve

join_buffer_size = 512K

innodb_buffer_pool_size = 3174M
innodb_buffer_pool_instances = 2
innodb_stats_persistent_sample_pages = 100
innodb_stats_transient_sample_pages = 24
innodb_rollback_on_timeout = on

How to use docker to generate wildcard SSL certificates for your website?

Google Chrome has started giving a warning for a non-SSL website and hence it has become more important than ever to generate SSL certificate for your website today!

When it comes to “docker” idea is simple, you mount a volume to share certificates with other containers. There are many docker images which have ‘in-built’ SSL generator. However, if you want it to be scalable, then this is a pretty bad way to do it. You would want to keep a track of all subdomains and their certificates along with where they have been generated. Load-balancers need not to be pointing to the “right” container during validation. So problems are many.

Docker image

I am using adferrand/letsencrypt-dns for this and it comes with ‘auto-restarting’ a docker container if a matching certificate has been renewed. It supports for 50+ dns managers and I am sure yours is covered 😉 . I am a fan of Linode, if you are serious about your business growth, give them a shot.
Docker Compose content:

cat docker-compose.yml

version: '3.2'
services:
  letsencrypt-dns:
    image: adferrand/letsencrypt-dns
    restart: always
    volumes:
        - "/etc/passwd:/etc/passwd:ro"
        - "/etc/group:/etc/group:ro"
        - "/var/run/docker.sock:/var/run/docker.sock"
        - "./letsencrypt:/etc/letsencrypt"
    environment:
        - CERTS_USER_OWNER=
        - CERTS_GROUP_OWNER=
        - CERTS_DIRS_MODE=0755
        - CERTS_FILES_MODE=0644
        - LETSENCRYPT_USER_MAIL=@.com
        - LEXICON_SLEEP_TIME=1500
        - LEXICON_PROVIDER=linode
        - LEXICON_LINODE_TOKEN=

Explaining the docker-compose.yml

We are mounting passwd and group as read-only to enable host user and group respectively.

Adding docker.sock ensures that it can restart related docker containers OR execute a command inside the targetted container. If you don’t mount it, containers will have old certificates even after certificates have been renewed and thus, it is very important that you mount it.

Since, dns server is using Linode’s DNS manager, we are adding LEXICON for linode and token. Sleep timing is 1500 seconds that means 25 mins, making each domain to be validated after 25 mins of adding the verification code in dns. If you are in USA, it will probably work with much lesser like 500 seconds.

Example content of domains.conf


cat letsencrypt/domains.conf

webapplicationconsultant.com *.webapplicationconsultant.com autorestart-containers=nginx_nginx_1,nginx_nginx_2
varunbatra.com *.varunbatra.com autocmd-containers=varunbatra_static_1:service nginx reload
  1. webapplicationconsultant.com *.webapplicationconsultant.com autorestart-containers=nginx_nginx_1,nginx_nginx_2 will restart containers by the name nginx_nginx_1 and nginx_nginx_2 once certificates of webapplicationconsultant.com has been renewed
  2. varunbatra.com *.varunbatra.com autocmd-containers=varunbatra_static_1:service nginx reload will execute the command service nginx reload once certificates of varunbatra.com have been renewed.

Generated SSL locations

  1. ./letsencrypt/live/varunbatra.com/fullchain.pem
  2. ./letsencrypt/live/webapplicationconsultant.com/fullchain.pem

Now you can use these certificates in NGINX or APACHE or ‘Whatever’ 🙂 Just make sure whatever you do, you don’t forget to add a proper autorestart and autocmd lines for their respective containers.