MySQL Slow Query Configuration for Docker

MySQL-Slow-Query-Configuration-for-Docker.md

Check running mysql container:

$ sudo docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
26137efa979f        mysql               "docker-entrypoint..."   8 months ago        Up 10 minutes       0.0.0.0:3306->3306/tcp   container-mysql

Check docker container mysql version:

$ sudo docker exec <container-name> mysqld --version`
mysqld  Ver 5.7.15 for Linux on x86_64 (MySQL Community Server (GPL))

Check current mysqld.cnf:

$ sudo docker exec <container-name> cat /etc/mysql/mysql.conf.d/mysqld.cnf

Copy mysqld.cnf to local:

$ sudo docker cp <container-name>:/etc/mysql/mysql.conf.d/mysqld.cnf ~/mysqld.cnf

Edit config:

$ sudo nano ~/mysqld.cnf`

[mysqld]
skip-host-cache
skip-name-resolve
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Slow query settings:
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=0.1

character-set-server=utf8mb4

[client]
default-character-set=utf8mb4

Where long_query_time takes seconds as its value. (0.1 = 100 miliseconds)

Note: In MySQL 5.7 and MariaDB 10.X, the variable log-slow-queries is deprecated. Use slow_query_log_file instead.

Copy new mysqld.cnf to the container:

$ sudo docker cp ~/mysqld.cnf <container-name>:/etc/mysql/mysql.conf.d/mysqld.cnf

Restart mysql container:

$ sudo docker restart <container-name>

Check slow query log:

$ sudo docker exec <container-name> cat /var/log/mysql/slow.log

OR

$ sudo docker exec <container-name> mysqldumpslow /var/log/mysql/slow.log