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'

services:
   db:
     container_name: db
     image: mariadb:10.1
     volumes:
       - db_data:/var/lib/mysql
       - ./docker/mysql/conf.d:/etc/mysql/conf.d
     environment:
       MYSQL_ALLOW_EMPTY_PASSWORD: 'yes'

volumes:
    db_data:

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

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

[mysqld]
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

 

Advertisements

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.

console.app

To see queries in Console

mysqld query log OSX

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

[mysqld]
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:

[mysqld]

sql_mode=

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 *
logrotate.d/proftpd:/var/log/xferlog

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