Logging MySQL queries in table

Just came around a handy (i.e. Docker environment) logging feature of MariaDB.

Enable logging:

SET global general_log = 1;
SET global log_output = 'table';

 

View query log:

SELECT * FROM mysql.general_log
-- OR --
SELECT * FROM mysql.general_log ORDER BY event_time DESC

 

Disable logging:

SET global general_log = 0;
Advertisements

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

 

Purging mysql-bin files, a.k.a. saving GBs of SSD space

My SSD was filling up and while searching for the cause I ended at MySQL binary log files. They used almost 40GB :-O while I don’t use the logs so often and especially not so backdated

$ sudo ls -lSh /var/lib/mysql |head 
total 7.8G
-rw-rw---- 1 mysql mysql 1.1G May 18 13:34 mysql-bin.000521
-rw-rw---- 1 mysql mysql 796M May 14 20:36 mysql-bin.000517
-rw-rw---- 1 mysql mysql 528M May 21 20:18 mysql-bin.000525
-rw-rw---- 1 mysql mysql 370M May 22 20:18 mysql-bin.000526
-rw-rw---- 1 mysql mysql 367M May 17 19:44 mysql-bin.000520
-rw-rw---- 1 mysql mysql 354M May 13 20:38 mysql-bin.000516
-rw-rw---- 1 mysql mysql 348M May 2 20:25 mysql-bin.000503
-rw-rw---- 1 mysql mysql 297M May 18 20:13 mysql-bin.000522
-rw-rw---- 1 mysql mysql 285M May 16 20:29 mysql-bin.000519

After some googling I found how to purge the files.

PURGE BINARY LOGS BEFORE '2017-05-01 00:00:00';

Because I don’t want to be bothered with this same issue a set expire_log_days to the 60 days in my.cnf, because the default is 0, so no automatic removal.

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.