Mysql Error log in Unix systems

By default, Mysql logs are disabled, but if you started mysqld with mysqld_safe script log-error will be enabled by this script to a standard location. Some of then can be dynamically enabled or disabled and other not as you can see below:

Error log --> Problems encountered starting, running, or stopping mysqld
General query log--> Established client connections and statements received from clients --> Can be enable or disabled without instance restart
Binary log--> Statements that change data (also used for replication)
Relay log --> Data changes received from a replication master server
Slow query log--> Queries that took more than long_query_time seconds to execute --> Can be enable or disabled without instance restart
DDL log (metadata log)--> Metadata operations performed by DDL statements



Error log


To enable fisrt one (Error log) it is needed to restart Mysql instance and startup it with this parameter --log-error={path to error file}, if not Mysqld will write this output to console, for example in my system:

[root@quickstart log]# ps -ef | grep mysqld
root      9848  6269  0 23:07 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql     9962  9848  0 23:07 pts/0    00:00:01 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root     10739  6269  0 23:17 pts/0    00:00:00 grep mysqld

As you can see below, i started mysqld daemon with mysqld_safe script, so this script enables Error log to a default location who is setup on /etc/my.cnf or /etc/mysql/my.cnf, if you want to set it to a non standard location you can modify this file or do this:

Fisrt i stop mysqld daemon by doing:

[root@quickstart tmp]# /usr/bin/mysqladmin -u root -ptemporal2 shutdown
[root@quickstart tmp]#

And now, i can start it with Error log redirected to a non-standard location as you can see:

[root@quickstart tmp]# /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --log-error=/tmp/mysqld.log --basedir=/usr --user=mysql&
[2] 11081
[root@quickstart tmp]# 171015 23:22:07 mysqld_safe Logging to '/tmp/mysqld.log'.
171015 23:22:07 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

-log-error parameter and others are configured in /etc/my.cnf or /etc/mysql/my.cnf (depends on your system), for example in my system:

[root@quickstart /]# cat /etc/my.cnf
# UTF-8 is used as the default encoding to support other locales (such as Japanese)

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
default-storage-engine=innodb

You can also manage log verbosity with this parameter: --log-error-verbosity=[1|2|3]
1 (errors only), 2 (errors and warnings), 3 (errors, warnings, and notes). Default of 3.


And finally to archive Error log you should user flush-logs command this way:

[root@quickstart log]# mv mysqld.log mysqld.log.old
[root@quickstart log]# /usr/bin/mysqladmin -u root -ptemporal2 flush-logs
[root@quickstart log]# ls -ltr mysqld.log
-rw-rw---- 1 mysql mysql 0 Oct 16 00:18 mysqld.log

You can see i archive mysql.log with mv unix-command and after executing flush-logs, mysqld creates another mysqld.log file with proper permissions (be carefull your directory has proper permissions allowing mysql user to create this new file, if not this command will fail with error 1105)
Example of flush log fail

[root@quickstart log]# /usr/bin/mysqladmin -u root -ptemporal2 flush-logs
/usr/bin/mysqladmin: refresh failed; error: 'Unknown error'

or

mysql> flush logs;
ERROR 1105 (HY000): Unknown error

Another log that is commonly used is


Slow query log

Slow query log feature writes on a log file (slow_query_log_file) SQL statements launched on Mysql database that took more than long_query_time seconds to execute, and required at least min_examined_row_limit rows to be examined. The minimum and default values of long_query_time are 0 and 10, respectively

This log can be dinamically set. This is a my.cnf, i put in red color 3 parameters needed to setup this feature:

[root@quickstart log]# cat /etc/my.cnf
# UTF-8 is used as the default encoding to support other locales (such as Japanese)

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
slow_query_log=1
slow_query_log_file=/var/log/slow.log
long_query_time=2

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
default-storage-engine=innodb

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

Once i set my my.cnf file with this tree parameters i restart mysqld this way:

[root@quickstart var]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

And now, a file called /var/log/slow.log is created (be caraluffy with directory permissions)

[root@quickstart log]# ls -ltr /var/log/slow.log
-rw-rw---- 1 mysql mysql 174 Oct 16 00:47 /var/log/slow.log