logging mysql queries from docker container

user_9378427 0
Viewed 8285

i am new to docker and I am still figuring out how it works. I have setup through docker-compose a wordpress site and the yaml file i am using is the following.

version: '3.3'

services:
   db:
     image: mysql:5.7
     volumes:
       - ./db_data/db/backup.sql:/docker-entrypoint-initdb.d/backup.sql #it loads an already existing database
       #- ./db_data/all.log:/var/log/mysql/all.log # i want here to have the logs of mysql
     restart: always
     environment:
       MYSQL_ROOT_PASSWORD: somewordpress
       MYSQL_DATABASE: wordpress
       MYSQL_USER: wordpress
       MYSQL_PASSWORD: wordpress

   wordpress:
     depends_on:
       - db
     image: wordpress:latest
     ports:
       - "8000:80"
     restart: always
     environment:
       WORDPRESS_DB_HOST: db:3306
       WORDPRESS_DB_USER: wordpress
       WORDPRESS_DB_PASSWORD: wordpress
     volumes:
       - ./db_data/wp-content:/var/www/html/wp-content # loads the files from an existing installation of wordpress
volumes:
    db_data:

as you can see by this line

- ./db_data/all.log:/var/log/mysql/all.log

i am trying to somehow make it so i can log all the queries made. I have managed to do that by doing the following:

1) docker-compose up, while having commented out the line with all.log from yaml file.

2) logging to the container of the mysql with bash and running the following

mysql -u root -psomewordpress -e "SET global log_output = 'FILE'; SET global general_log_file='/var/log/mysql/all.log'; SET global general_log = 1;"

3) Going to the /var/lib/docker/volumes and getting the file created.

What i am trying to achieve is to make somehow this command part of the yaml file so I can also have the all.log file mounted at the location where the yaml file is.

docker-compose docker mysql
1 Answers

What I did to solve my problem was using the following:

version: '3.3'

services:
   db:
     image: mysql:5.7
     volumes:
       - ./db_data/db/backup.sql:/docker-entrypoint-initdb.d/backup.sql #it loads an already existing database
       -./logs/mysql:/var/log/mysql # to get the folder of mysql in my logs/mysql
     restart: always
     environment:
       MYSQL_ROOT_PASSWORD: somewordpress
       MYSQL_DATABASE: wordpress
       MYSQL_USER: wordpress
       MYSQL_PASSWORD: wordpress

   wordpress:
     depends_on:
       - db
     image: wordpress:latest
     ports:
       - "8000:80"
     restart: always
     environment:
       WORDPRESS_DB_HOST: db:3306
       WORDPRESS_DB_USER: wordpress
       WORDPRESS_DB_PASSWORD: wordpress
     volumes:
       - ./db_data/wp-content:/var/www/html/wp-content

This code would sync what is inside the docker container of mysql with my folder logs/mysql. So I put a little shell script in there.

#!/bin/bash

if [ ! -f /var/log/mysql/all.log ]; then
    touch /var/log/mysql/all.log
fi

chmod 777 /var/log/mysql/all.log
mysql -u root -psomewordpress -e "SET global log_output = 'FILE'; SET global general_log_file='/var/log/mysql/all.log'; SET global general_log = 1;"

It checks if the all.log file is there, if not it creates it and then starts mysql logging. Thats it, after that all queries are logged directly to all.log in which i have direct access.
One more thing, to execute that shell script you might want to use

docker exec 5.7-mysql "./var/log/mysql/initlogs.sh"

where initlogs.sh is the name of the shell script.

I really hope it would help someone!

user_93784270
Related