Enable logging in docker mysql container

Question

I'm trying to get familiar with the docker ecosystem and tried to setup a mysql database container. With docker-compose this looks like:

version: '2'
services:
  db:
    image: mysql:5.6.33@sha256:31ad2efd094a1336ef1f8efaf40b88a5019778e7d9b8a8579a4f95a6be88eaba
    volumes:
      - "./db/data:/var/lib/mysql"
      - "./db/log:/var/log/mysql"
      - "./db/conf:/etc/mysql/conf.d"
    restart: "yes"
    environment:
      MYSQL_ROOT_PASSWORD: rootpw
      MYSQL_DATABASE: db
      MYSQL_USER: db
      MYSQL_PASSWORD: dbpw

My conf directory contains one file:

[mysqld]
log_error       =/var/log/mysql/mysql_error.log
general_log_file=/var/log/mysql/mysql.log
general_log     =1
slow_query_log  =1
slow_query_log_file=/var/log/mysql/mysql_slow.log
long_query_time =2
log_queries_not_using_indexes = 1

Unfortunately I don't get any log files that way. The setup itself is correct and the cnf file is used. After connecting to the container and creating the 3 files, chown them to mysql and restarting the container, the logging is working as expected.

I'm pretty sure that this is a common scenario, and my current way to get it running seems really stupid. What is the correct way to do it?

I could improve my approach by moving all this stuff in a Dockerfile, but this still seem strange to me.

Answer 1

I was looking for the exact same thing, and now, there is a better way to do it.

The docker mysql writes:

Many configuration options can be passed as flags to mysqld. This will give you the flexibility to customize the container without needing a cnf file. For example, if you want to change the default encoding and collation for all tables to use UTF-8 (utf8mb4) just run the following:

$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

In a docker-compose world, one could pass these arguments through the "command" section of the service:

command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

In my use case I just wanted to turn on the logs and specify the path to the log file :

 command: mysqld --general-log=1 --general-log-file=/var/lib/mysql/general-log.log

With the adequate volumes (e.g. - ./logs/mysql.log:/var/lib/mysql/general-log.log), it becomes easy to reach them.

This is pretty straight forward and avoid dealing with a local configuration. It will works with any MySQL Docker images and will keep the my.cnf as shipped by the image.

Edit: change path from /var/log/mysql/ to /var/lib/mysql/ to ensure a MySQL writable folder.

Answer 2

After connecting to the container and creating the 3 files, chown them to mysql and restarting the container, the logging is working as expected.

That points to a host volume permission issue. When you map from a container to the host, no mappings are made on user id's, and the name attached to the uid inside the container may be very different from outside. You need to initialize the directory permissions with something the container user can write to. One simple method is to create a group that has access to write to the files on both the host and container, and then add the various users to this group on both your image and host OS. Another option is to use a named filesystem that you don't access directly from your host and initialize it with the image's directory permissions.

---

Edit: An example of a named volume with your docker-compose.yml is as simple as:

version: '2'
volumes:
  mysql-data:
    driver: local
  mysql-log:
    driver: local
  mysql-conf:
    driver: local

services:
  db:
    image: mysql:5.6.33
    volumes:
      - "mysql-data:/var/lib/mysql"
      - "mysql-log:/var/log/mysql"
      - "mysql-conf:/etc/mysql/conf.d"
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: rootpw
      MYSQL_DATABASE: db
      MYSQL_USER: db
      MYSQL_PASSWORD: dbpw

Note that I also removed the sha256 from your image name, this reference would block you from being able to pull patched versions of the image. I also prefer the "unless-stopped" restart policy so that Docker does expected things on a reboot.

Answer 3

I needed to temporarily enable logging due to a weird PDO binding issue and I wanted to see the actual query being executed. This question was the top search result and I wasn't satisfied with any of the answers. Assuming you already have volumes setup for the container, I got it working the following way:

1. Run the following queries on the database:

SET global general_log = on;
SET global general_log_file='/var/log/mysql/mysql.log';
SET global log_output = 'file'; 

2. Get the container ID using docker ps.
3. Run docker exec -it <id> /usr/bin/tail -f /var/log/mysql/mysql.log
4. Run the following query once you're done: SET global general_log = off;

If you're having problems setting the general_log_file variable, you probably need to /bin/bash into the container then manually create the log file with the correct permissions.

Answer 4

In addition to ponsfrilus answer you can do the same from docker run command:

docker run --name some-mysql --restart always -v //d/MySqlDockerData:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --log_error=/var/lib/mysql/mysql_error.log --general_log_file=/var/lib/mysql/mysql.log --general_log=1 --slow_query_log=1 --slow_query_log_file=/var/lib/mysql/mysql_slow.log --long_query_time=2 --log_queries_not_using_indexes=1