Mysql logs

[capoinfra]

Hello,

I can not see any mysql log, and the logs are enabled in /etc/my.cnf

Here's the copy of the file:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
local-infile=0
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=600
interactive_timeout=20
wait_timeout=10
connect_timeout=10
thread_cache_size=128
#key_buffer=16M
key_buffer=200M
join_buffer=1M
max_allowed_packet=16M
table_cache=1536
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=1M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=8
myisam_sort_buffer_size=64M

[mysql.server]
user=mysql
#basedir=/var/lib

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

Any idea?

Thanks in advance.-

[Infopro]

bin files? Are they recent or old? Is it possible you had this in your my.cnf at one time? log-bin=mysql-bin
If so you might clear them with this:
expire_logs_days = 0

restart MySQL.

Just guessing here of course. I've done this one myself.

[Miraenda]

There are quite a few really old settings in your /etc/my.cnf file. When you mention you don't see any MySQL logs, which logs are you talking about? The general and slow query logs don't automatically log and only the error logs do.

You don't need several of the settings you have in your /etc/my.cnf due to the reason they are already set by default in MySQL for these:

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking

skip-locking is now called skip-external-locking and it's already set by default (see this location for a discussion). The datadir is already /var/lib/mysql and the socket file is already /var/lib/mysql/mysql.sock

Also, thread_concurrency doesn't exist on Linux MySQL so that variable serves no purpose in /etc/my.cnf file (see this location for a discussion on how thread_concurrency only exists on Solaris systems).

Next, this err-log isn't even a variable for mysql itself for logging errors, this is only when running with mysqld_safe:

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

The actual MySQL error log itself automatically logs to /var/lib/mysql and should be saving in /var/lib/mysql/hostname.err where hostname is your server's fully qualified domain name hostname. If you wanted to specify a different name or location for the error log, under the [mysqld] section (not the [mysqld_safe] section), you would put:

log-error=/var/lib/mysql/error.log

Or whatever you wanted for the name of the error log. You would have to create the file with the right permissions and ownership:

cd /var/lib/mysql
touch error.log
chmod 660 error.log
chown mysql:mysql error.log

Again, though, it automatically saves in /var/lib/mysql/hostname.err anyway without setting it in /etc/my.cnf so there's no point to add log-error line unless you really want to change the log's name or location for some reason.

If you want the general or slow query logs to save as well, you can enable them using the following depending on what MySQL version you are using:

MySQL 4.1 and MySQL 5.0

In /etc/my.cnf put the following for the general log:

log=/var/lib/mysql/general.log

In /etc/my.cnf put the following for the slow log:

log-slow-queries=/var/lib/mysql/slow.log

Now, you'd have to create the log files:

cd /var/lib/mysql
touch general.log
touch slow.log
chmod 660 general.log
chmod 660 slow.log
chown mysql:mysql general.log
chown mysql:mysql slow.log

Restart MySQL

/etc/init.d/mysql restart

MySQL 5.1

In /etc/my.cnf put the following for the general log:

general_log

In /etc/my.cnf put the following for the slow log:

slow_query_log

Restart MySQL:

/etc/init.d/mysql restart

Under MySQL 5.1, if you have the general_log and slow_query_log lines in /etc/my.cnf file, then the log files create automatically without having to create them in /var/lib/mysql

They will have the names /var/lib/mysql/hostname.log (general log) and /var/lib/mysql/hostname-slow.log (slow query log) where hostname is your machine's fully qualified domain hostname.

The general logs can grow quite large as they will log all database activity, so a cron to clear the log periodically might be needed. There is a log rotation script at /usr/share/mysql/mysql-log-rotate on most systems that you could modify to put the correct paths to the log (it defaults to /var/lib/mysql/mysqld.log as the general log name so you'd need to change it to /var/lib/mysql/general.log or /var/lib/mysql/hostname.log depending on what MySQL version you are using).

Of note, I would advise being careful about getting variables online from guides. I've seen a lot of people with these incorrect variables on their MySQL 4.1, 5.0 and 5.1 machines all due to using guides that haven't been updated since 2004 or 2005.