Docker on Windows 10: mysql:8.0.30-debian log files

Running the Docker Official Image mysql:8.0.30-debian on my Windows 10 Pro host machine, I want to log all queries, slow queries and errors to files on the host machine. In this article, we're discussing how to go about achieving this.

We've previously discussed how to implement a custom config file for MySQL Docker Official Image in Docker on Windows 10: running mysql:8.0.30-debian with a custom config file. This post assumes that we are using this custom config file.

What I would like to do is to log all queries, slow queries and errors to files on the host machine. The official references to each of the log types:

• All queries -- 5.4.3 The General Query Log
• Slow queries --
  • 5.4.5 The Slow Query Log
  • 5.1.7 Server System Variables | log_queries_not_using_indexes
• Errors -- 5.4.2.2 Default Error Log Destination Configuration

Accordingly, the custom config file E:\mysql-config\mysql-docker.cnf gets updated as:

[mysqld]
default_authentication_plugin=mysql_native_password
log_bin_trust_function_creators=1

# Below contents are updated contents.

# General and slow logging.
log_output=FILE

general_log=1
general_log_file="/var/lib/mysql/general_log.log"

slow_query_log=1
slow_query_log_file="/var/lib/mysql/slow_query.log"
long_query_time=10
log_queries_not_using_indexes=1

# Error Logging.
log_error="/var/lib/mysql/error.err"

I updated it directly on Windows, its permissions get changed, and as discussed in Docker on Windows 10: running mysql:8.0.30-debian with a custom config file, we need to change its permissions to the correct ones.

While mysql-docker mysql:8.0.30-debian container is running, start the interactive mode with the Bash process to change config file permissions:

E:\>docker exec -it mysql-docker bash

root@8e6656b15d9a:/# cd /etc/mysql/conf.d/
root@8e6656b15d9a:/# chmod u+rw-x mysql-docker.cnf
root@8e6656b15d9a:/# chmod g+r-wx mysql-docker.cnf
root@8e6656b15d9a:/# chmod o+r-wx mysql-docker.cnf

Permissions should now be correct. To verify:

root@8e6656b15d9a:/# ls -l

total 4
-rw-r--r-- 1 root root 380 Oct 20 11:40 mysql-docker.cnf

Restart mysql-docker container for the new settings to take effect:

E:\>docker stop mysql-docker
E:\>docker start mysql-docker

Please note, for the above two ( 2 ) commands to work, mysql-docker must be started without the --rm flag, that is:

docker run -d -it --name mysql-docker -p 3306:3306 -e MYSQL_ROOT_PASSWORD=pcb.2176310315865259 --mount type=bind,source=//e/mysql-config,target=/etc/mysql/conf.d --mount source=mysqlvol,target=/var/lib/mysql mysql:8.0.30-debian

Please also notice the following option in the above docker run command: --mount source=mysqlvol,target=/var/lib/mysql. We shall come back to this option later on.

Using a client tool such as MySQL Workbench to verify that the new settings are effective:

show variables like 'general_log';
show variables like 'slow_query_log';
show variables like 'log_queries_not_using_indexes';

The above commands should each return ON. Similarly, the below should each return the corresponding values we set in the config file:

show variables like 'general_log_file';
show variables like 'slow_query_log_file';
show variables like 'log_error';

➡️ But where /var/lib/mysql/general_log.log, /var/lib/mysql/slow_query.log and /var/lib/mysql/error.err are?

In Python: Docker volumes -- where is my SQLite database file?, I've discussed where Docker volumes or data files are on Windows 10 Pro host machine: essentially, due to the installation of my Docker Desktop uses Windows Subsystem for Linux ( WSL 2 ) based engine, I can copy and paste this directory:

\\wsl$\docker-desktop-data\version-pack-data\community\docker

to Windows File Explorer, and it should go to the top level Docker desktop data directory; mysql-docker's files are in:

\\wsl$\docker-desktop-data\version-pack-data\community\docker\volumes\mysqlvol\_data

We should find our log files:

Why \wsl$\docker-desktop-data\version-pack-data\community\docker\volumes\mysqlvol?

Recall the docker run option previously mentioned --mount source=mysqlvol,target=/var/lib/mysql? source=mysqlvol is the host machine volume or where the container's data files live.

Logging all queries can slow down the server, and the log file can get very big, it should only be used during development, certainly not in production. Also, these options can be set and reset on the flight without needing to use the config file or server restart. For example, via MySQL Workbench:

SET GLOBAL general_log = 'OFF';
SET GLOBAL general_log = 'ON';

I've tried, and it works: I can turn off general_log, then delete the log file, turn on general_log again, and a new log file is created.

I do hope you find this helpful and useful. Thank you for reading and stay safe as always.