Log mysql queries in Docker container

To log mariadb (mysql) SQL queries in a Docker container add - ./docker/mysql/conf.d:/etc/mysql/conf.d to volumes of the database service.

version: '3'

     container_name: db
     image: mariadb:10.1
       - db_data:/var/lib/mysql
       - ./docker/mysql/conf.d:/etc/mysql/conf.d


The contents of docker/mysql/conf.d/my.cnf

# docker/mysql/conf.d/my.cnf

general_log = 1
general_log_file = /var/log/mysql/query.log

slow_query_log = 1
long_query_time = 1 # seconds
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 0



Log navigator a.k.a. lnav

Getting to love lnav. Just specify a directory path as argument and it will parse the logs.

lnav /var/log/mysql

A list of supported log formats is at http://lnav.readthedocs.io/en/latest/formats.html

New path for MySQL query log on OSX

Sometimes during development I need to see what SQL queries are executed and listing them in Console.app is maybe the most convenient solution on OSX.


To see queries in Console

mysqld query log OSX

I just created /etc/mysql/my.cnf with:

general_log = 1
# general_log_file = /usr/local/mysql/data/mysqld.query.log
general_log_file = /var/log/mysql/mysqld.query.log

slow_query_log = 1
long_query_time = 1 # second
# slow_query_log_file = /usr/local/mysql/data/mysqld.slow.log
slow_query_log_file = /var/log/mysql/mysqld.slow.log
log_queries_not_using_indexes = 0

MySQL server needs own permissions on the parent log dir so:

sudo mkdir /var/log/mysql
sudo chown -R _mysql:_mysql /var/log/mysql

Last step is server restart.

sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

On a Linux machine I use multitail as an alternative.

Enabling MySQL 5.7 query logging on OSX

I got used to it that sometimes I need to check the queries run by MySQL. After clean OSX upgrade I forgot to enable it.

So I added the following to /etc/mysql/my.cnf:



general_log = 1
general_log_file = /usr/local/mysql/data/mysqld.query.log

slow_query_log = 1
long_query_time = 1 # second
slow_query_log_file = /usr/local/mysql/data/mysqld.slow.log
log_queries_not_using_indexes = 0

The default one that comes with the package is at /usr/local/mysql/support-files/my-default.cnf

There’s also an option to set logging via SQL queries.

Enable MySQL file logging

Wanted to know what queries are executed so I had to enable it for MariaDB.

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql.log';
SET GLOBAL slow_query_log = 'On';
SET GLOBAL slow_query_log_file = '/var/log/mysql-slow.log';

To see the changes:

SHOW VARIABLES LIKE 'general_log';
SHOW VARIABLES LIKE 'slow_query_log';

logrotate error: stat of /var/log/xferlog failed

In Arch Linux the logrotate service was failing:

user@host# systemctl start logrotate.service 
Job for logrotate.service failed. See "systemctl status logrotate.service" and "journalctl -xe" for details.

So I ran it by hand to debug:

user@host# logrotate /etc/logrotate.conf

And the following error appeared:

logrotate error: stat of /var/log/xferlog failed

I fired up a grep for xferlog in the /etc directory:

user@host:/etc# grep -r xferlog *

Solution was commenting the xferlog rule in logrotate.d/proftpd. I don’t need the transfer log.