General logging won't work in MySQL

Question

I saw on SF that there's an option in MySQL to log all queries. So, in my version (mysql-server-5.0.45-7.el5 on CentOS 5.2) this appears to be a case of enabling the 'log' option, so I edited /etc/my.cnf to add this:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=
log=/var/log/mysql-general.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I then created the file and set permissions:

# touch /var/log/mysql-general.log
# chown mysql. /var/log/mysql-general.log
# ls -l /var/log/mysql-general.log
-rw-r--r-- 1 mysql mysql 0 Jan 18 15:22 /var/log/mysql-general.log

But when I start mysqld I get:

120118 15:24:18  mysqld started
^G/usr/libexec/mysqld: File '/var/log/mysql-general.log' not found (Errcode: 13)
120118 15:24:18 [ERROR] Could not use /var/log/mysql-general.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.
120118 15:24:18  InnoDB: Started; log sequence number 0 182917764
120118 15:24:18 [Note] /usr/libexec/mysqld: ready for connections.

Can anyone suggest why this isn't working?

Answer 1

"Not found" sounds like a permission problem, even though you have given mysql permission to touch that specific file, it might not have access to /var/log. Try putting the log file in the mysql data folder, /var/lib/mysql/. I am trying to remember a case of ever seeing the mysql log files outside of that path..

Answer 2

If you're on Ubuntu, you may hit this if you're trying to put the log file outside of a mysql directory (e.g. /var/log/mysql). To fix this, add your path to /etc/apparmor.d/usr.sbin.mysqld.

Answer 3

This is not an unusual problem when services write to /var/log. The usual process when installing a new app as root is to touch the new file by name, and then chown that file over to the user/group that will be writing to that file.
(syslog services will typically refuse to create a new log file in /var/log because the directory itself is owned by root, so services that aren't running as root can't create new files. Once you manually create the file, the log rotation process will maintain owership and permissions for you.)

IE:

root$ touch /var/log/mysql_general.log

root$ chown mysql.mysql /var/log/mysql_general.log

This will allow the mysqld service to write to the log file even though the effective userid/gid of mysqld is not root. You may want to check logrotate to setup a rotation schedule if yours doesn't handle it by default. For more details on permissions and best practices, you'll want to investigate syslog configuration & maintenance.

(I just fixed this same issue on an old server I inherited.)

(edited to fix a typo and clarify a point)

Answer 4

In /var/lib/mysql/mysql there is a file named general_log.csv created by default. You can also use this file to specify (e.g log=/var/lib/mysql/mysql/general_log.csv)

Answer 5

One other thing you might check if you are sure your file permissions are correct is if SELinux is interfering with anything.

We threw SELinux into permissive and all of a sudden MySQL was able to create the log file on a separate mount we had designated for logging. We ended up having to tweak the contexts of the directory path where we wanted to place the log.

Answer 6

mkdir -p /var/log/mysqld/
touch /var/log/mysqld/mysql-general.log
# eventually setup file permissions for it

should be the sufficient way to go. For the search term / sentence "Could not use for logging (error 2). 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."