CODEWITHSUNDEEP
mysql
sof
sof

Reputation: 9649

How to log queries to stdout on MySQL?

MySQL 5.6.26

Trying to log queries to stdout doesn't work below,

$ mysqld --general_log=1 --general_log_file=/dev/stdout

Upvotes: 13

Views: 10074

Answers (2)

sam2426679
sam2426679

Reputation: 3857

I am very surprised at how difficult it is to do this in MySQL vs. PostgreSQL.

Following below is the config that allowed me to send all MySQL logs to the Docker container's stdout.

Contents of docker-compose.yml:

---
version: "3.9"
services:
  your_db:
    image: mysql:5.7-debian
    command:
      - /usr/local/bin/mysqld.sh
    environment:
      MYSQL_DATABASE: "$your_db"
      MYSQL_PASSWORD: "$your_pass"
      MYSQL_ROOT_PASSWORD: "$root_pass"
      MYSQL_USER: "$your_user"
    volumes:
      - ./my.cnf:/etc/mysql/conf.d/my.cnf:ro
      - ./mysqld.sh:/usr/local/bin/mysqld.sh:ro
    networks:
      - your_net

Contents of my.cnf:

[mysqld]
# Log General
general_log = 1
general_log_file = /var/log/mysql_general.log

# Log Error
log_error = /var/log/mysql_error.log

# Log Slow
slow_query_log = 1
slow_query_log_file = /var/log/mysql_slow_query.log
long_query_time = 0  # 0 has the effect of logging all queries
log_queries_not_using_indexes = 1

Contents of mysqld.sh:

#!/usr/bin/env bash
set -euo pipefail

##
# This script exists to work-around the fact that
# mysql does not support logging to stdout
#
# This will tail the file logs configured in ./my.cnf
##

LOG_PATHS=(
  '/var/log/mysql_general.log'
  '/var/log/mysql_error.log'
  '/var/log/mysql_slow_query.log'
)
for LOG_PATH in "${LOG_PATHS[@]}"; do
  # https://serverfault.com/a/599209
  ( umask 0 && truncate -s0 "$LOG_PATH" )
  tail --pid $$ -n0 -F "$LOG_PATH" &
done

docker-entrypoint.sh mysqld

Upvotes: 10

Schwern
Schwern

Reputation: 165536

You can't, not directly. The query logs only go to files, tables or nowhere. They don't seem to go to /dev/stdout nor honor the - convention. Don't worry, there's a much better way.

If all you want to do is see the query log as it happens, you can stream the log file using various Unix utilities. tail -f is one option.

tail -f /path/to/query.log

I prefer opening the log with less and using the F command to keep reading as the file is added to. It's like tail -f but you can also scroll around and search the file.

$ less /path/to/query.log
~
~
/opt/local/lib/mysql56/bin/mysqld, Version: 5.6.27-log (Source distribution). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
151004 16:10:51     1 Connect   schwern@localhost as  on 
                    1 Query     select @@version_comment limit 1
151004 16:10:53     1 Query     select 1
151004 16:10:54     1 Quit      
Waiting for data... (interrupt to abort)

Upvotes: 2

Related Questions