santrix

Well-Known Member
Nov 30, 2008
225
2
68
Can someone point me in the right direction... I'm trying to find the config options to turn on some logging for mysql in /etc/my.cnf

Currently we have a pretty vanilla my.cnf that is created by cPanel. I would like to be able to at least log connections, and also be able to log queries (preferably independently).

The standard my.cnf already has the following entries (in amongst the rest):

Code:
[safe_mysqld]
err-log=/var/log/mysqld.log
and

Code:
[mysqld]
log_slow_queries=/var/log/mysql.slow-queries.log
but neither of those log files exist in /var/log - is this normal? Do they need touching and chmodding to get them working? This is a production server that has been running for months so I would have expected to see something by now.

Any help?
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
Hi santrix,

Which MySQL version are you using? If you are using MySQL 5.0, then you need to create the log file and you need to use the right /etc/my.cnf syntax:

Code:
log-slow-queries=/var/log/mysql.slow-queries.log
Code:
touch /var/log/mysql.slow-queries.log
chmod 660 /var/log/mysql.slow-queries.log
chown mysql:mysql /var/log/mysql.slow-queries.log
You won't receive any logs in /var/log/mysqld.log unless you restart MySQL in safe mode. The normal error log is at /var/lib/mysql/hostname.err location where hostname will be the fully qualified hostname for the machine (you can see the fully qualified hostname by issuing "hostname -f" command in root SSH).

As for logging connections and queries, you are only entering slow queries, those that take longer than 10 seconds to run, by enabling the slow query log. If you want all queries logged, you'll need to enable the general log. This is the variable to use for it:

Code:
log=/var/log/mysql.general.log
If you wanted to call the log file mysql.general.log and again you would do these commands to create it with the proper permissions and ownership:

Code:
touch /var/log/mysql.general.log
chmod 660 /var/log/mysql.general.log
chown mysql:mysql /var/log/mysql.general.log
The above would not be the case if you are using MySQL 5.1, since to enable the general and slow query logs, you'd simply input these variables into /etc/my.cnf file:

Code:
general_log
slow_query_log
This will automatically create files at /var/lib/mysql called hostname.log and hostname-slow.log where hostname is the short hostname for the machine.

I do want to note that the general log becomes very large very quickly. I would not suggest creating it unless you really need that information. If you do believe that information is necessary to obtain, then I highly suggestion periodically archiving the file using logrotate to do so.

Finally, the above entries being added to /etc/my.cnf file will require a restart of MySQL. Please ensure to create the log files before restarting MySQL so they can write to those log files.