2

I use the mysql:5.7 official docker image and setup the general_log_file and logs_error logs on mysql using custom .cnf file but here when i setup error log then getting below error:

$ sudo docker-compose up
db_1   | Initializing database
db_1   | Initializing database
db_1   | Initializing database
....

^CGracefully stopping... (press Ctrl+C again to force)

$ cat mysql-custom.cnf
[mysqld]
general_log_file        = /var/log/mysql/mysqld.log
general_log             = 1
log_error               = /var/log/mysql/error.log  # When i put this then getting above error.

Here I getting error then I put the log_error or slow_query_log_file, Anyone know that why this simple thing not work on docker ?

2
  • Can you please elaborate bcoz I'm not getting you. Apr 19, 2017 at 9:17
  • 1
    Added an answer. Apr 19, 2017 at 9:32

2 Answers 2

2

When in a container, you might not have the full support of the functionalities of the underlying operating system; such behaviour is by design as you want to have a more secure and restricted environment for a specific purpose.

As such, for MySQL logs, you might want to log to MYSQL DB/tables.

For version 5.x onwards (5.1, 5.2, cannot remember) SQL supports logging to MySQL DB/tables, instead of files, and comes by default with two tables already created, mysql.general_log and mysql.slow_log.

To setup their use, you do:

Adding to /etc/mysql/my.cnf:

[mysqld]
slow-query-log
general_log=1
log-output=TABLE

And then restart mysql.

To backup them every day, you can run in a system with access to query the database:

mysqldump mysql general_log  --lock-tables=0 > general_log_errors.db
mysqldump mysql slow_log  --lock-tables=0 > slow_log_errors.db

(You need to use lock-tables=0, otherwise you will get the error message:

mysqldump: Got error: 1556: You can't use locks with log tables. when doing LOCK TABLES

)

And then run the following queries to clean the tables.

TRUNCATE TABLE mysql.general_log;
TRUNCATE TABLE mysql.slow_log;

This is the general idea. You might want to keep logs of the last few days, and as such the MySQL TRUNCATE/DELETE queries would be more elaborated.

The tables should be there by default, maybe it was scrubbed in that docker image.

As such, for v5.7, you can create them with:

CREATE TABLE IF NOT EXISTS `general_log` (
  `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON    UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumblob NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';

CREATE TABLE IF NOT EXISTS `slow_log` (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumblob NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';

Further reading: Writing Logs into Tables

As for creating the error_file: /var/log might not even exist in your docker file. If those two logging options above are not enough, you might create a file for testing at a directory owned by the MySQL user; one of such directories where MySQL resides is usually /var/lib/mysql.

10
  • what location of logs because I've to mount volume into host. Apr 19, 2017 at 9:32
  • Edited the answer. It is logging to MySQL DB tables...not files. Apr 19, 2017 at 9:33
  • Is there any way to store logs in file or any other better option, here my plan is to backup logs every day. Apr 19, 2017 at 9:41
  • Due to the nature of containers, I would advise tables. Added to the answer. Apr 19, 2017 at 9:47
  • But error_logs table not found on mysql db, how do i get the errors logs ? Apr 19, 2017 at 10:04
0

I ran into similar problem yesterday but mine was on setting the path for error log in my.cnf. I am used to using log-error=/var/log/mysql/mysql.err (which works fine in other non-docker environment), but kept when used in docker, I realized that it is not accepting .err extension. Then I used .log extension and it worked fine. Although I am running the base mariadb:10.2 docker image but it should be the same when running mysql:5.7 image. Just fyi: although error file is a log, it is not affected by the variable log-output=TABLE as it cannot be viewed in a mysql table. I also think logging to TABLE creates unnecessary burden on the server and this should be avoided if possible.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .