How to configure for log slow queries

[hugoruscitti]

Hi, i want to configure mysql to log the slow queries, but i don't find any example to do it...

I find a note in the readme file that mention the my.cnf file (https://github.com/dokku/dokku-mysql#adding-a-custom-mycnf) but i was wondering how to setup it correctly.

Traditionally [1], i need to set a path value for the log file. Does the mysql-plugin have access to the host filesystem? There is any my.cnf example to explore?

https://confluence.atlassian.com/kb/enabling-mysql-slow-query-logs-772874952.html

Thanks

[josegonzalez]

The mysql plugin does not have access to the host filesystem. We currently only mount the following directories (which you can use to log output to):

/var/lib/mysql
/etc/mysql/conf.d

We can probably also mount /var/lib/log as an enhancement.

[hugoruscitti]

Thanks @josegonzalez, but i don't can setup properly. Did i miss something?:

I create a fille called: /var/lib/dokku/services/mysql/suite-legacy-staging-db/config/my.cnf with this content:

[mysqld]
long_query_time=0.1
slow_query_log_file=/etc/mysql/conf.d/database_slow_query.log
slow-query-log=1

Then, i restart the database and list all logs with:

dokku mysql:restart suite-legacy-staging-db
dokku mysql:logs suite-legacy-staging-db

Then, i see that settings its loaded correctly, but either the paths don't has been mounted or something is missing.

Any advice? I create and allow to all users access permissions to that file too.

This is the database log:

(note the '170513 0:00:42 [ERROR] Could not use /etc/mysql/conf.d/database_slow_query.log for logging ... ' line)

Version: '5.5.54-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
170513  0:00:38 [Note] mysqld: Normal shutdown

170513  0:00:38 [Note] Event Scheduler: Purging the queue. 0 events
170513  0:00:38  InnoDB: Starting shutdown...
170513  0:00:39  InnoDB: Shutdown completed; log sequence number 156350343
170513  0:00:39 [Note] mysqld: Shutdown complete

170513  0:00:41 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
170513  0:00:41 [Note] mysqld (mysqld 5.5.54-log) starting as process 1 ...
170513  0:00:41 [Note] Plugin 'FEDERATED' is disabled.
170513  0:00:41 InnoDB: The InnoDB memory heap is disabled
170513  0:00:41 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170513  0:00:41 InnoDB: Compressed tables use zlib 1.2.3
170513  0:00:41 InnoDB: Using Linux native AIO
170513  0:00:41 InnoDB: Initializing buffer pool, size = 128.0M
170513  0:00:41 InnoDB: Completed initialization of buffer pool
170513  0:00:41 InnoDB: highest supported file format is Barracuda.
170513  0:00:41  InnoDB: Waiting for the background threads to start
170513  0:00:42 InnoDB: 5.5.54 started; log sequence number 156350343
mysqld: File '/etc/mysql/conf.d/database_slow_query.log' not found (Errcode: 13)
170513  0:00:42 [ERROR] Could not use /etc/mysql/conf.d/database_slow_query.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
170513  0:00:42 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
170513  0:00:42 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
170513  0:00:42 [Note] Server socket created on IP: '0.0.0.0'.
170513  0:00:42 [Warning] 'proxies_priv' entry '@ root@e24e82781e2e' ignored in --skip-name-resolve mode.
170513  0:00:42 [Note] Event Scheduler: Loaded 0 events
170513  0:00:42 [Note] mysqld: ready for connections.
Version: '5.5.54-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

Thank !

[josegonzalez]

Its likely that directory is, for whatever reason, not owned or writeable by mysql - or the user that runs it. You can verify this by doing:

dokku mysql:enter SERVICE bash
# in the container

# check for ownership
ls -lah /etc/mysql/conf.d

# check for the process user
ps aux | grep mysql

[hugoruscitti]

Hi @josegonzalez !!, it seems to be related to file permissions... how i can set to be writable by mysql?

root@dokku-1gb-nyc3-01:~# dokku mysql:enter suite-legacy-staging-db bash
-----> Filesystem changes may not persist after container restarts
root@e24e82781e2e:/# ls -lah /etc/mysql/conf.d
total 72K
drwxr-xr-x 2 1000 1000 4.0K May 13 00:16 .
drwxr-xr-x 3 root root 4.0K Mar 21 21:54 ..
-rw-r--r-- 1 1000 1000   72 May  5 14:31 charset_utf8.cnf
-rwxrwxrwx 1 root root  48K May 13 00:38 database_slow_query.log
-rw-r--r-- 1 1000 1000   32 May  5 14:31 disable_performance_schema.cnf
-rw-r--r-- 1 root root  108 May 13 00:00 my.cnf
root@e24e82781e2e:/# ps aux | grep mysql
mysql        1  0.0  6.7 538392 68952 ?        Ssl  00:05   0:49 mysqld
root       134  0.0  0.0  11128   928 ?        S+   14:36   0:00 grep mysql

[hugoruscitti]

any advice?

[josegonzalez]

You can set the perms on those to 1000:1000 I guess?