MySQL error log (err log)

Like most relational databases, log files are an important part of the MySQL database. MySQL has several different log files, usually including error log files, binary logs, generic logs, slow query logs, and so on. These logs can help us locate events inside MYSQLD, database performance failures, record data change histories, user recovery databases, and more. This article describes the error log file primarily.

1, the MySQL log file system composition
A, error log: Records the issue that occurs when you start, run, or stop mysqld.
B. General log: Records the established client connections and executed statements.
C, update log: The statement that records the change data. The log is no longer used in MySQL 5.1.
D, binary log: A statement that records all changes to the data. Also used for replication.
E, slow query log: Records all queries that have been executed for more than long_query_time seconds, or queries that do not use indexes.
F, InnoDB logs: InnoDB redo Log

By default, all logs are created in the MYSQLD data directory.
You can force mysqld to close and reopen the log file (or, in some cases, switch to a new log) by refreshing the log.
When you execute a FLUSH logs statement or perform mysqladmin flush-logs or mysqladmin refresh, the log is aged.
In the case of MySQL replication, more log files will be maintained from the replication server, known as the replacement log.

2. Error log
The error log is a text file.
The error log records the details of each startup and shutdown of MySQL server and all the more severe warnings and error messages during the run.
You can use the--log-error[=file_name] option to turn on the MySQL error log, which specifies where mysqld saves the error log file.
For the Specify--log-error[=file_name] option without a given file_name value, MYSQLD uses the error log name Host_name.err and writes the log file in the data directory.
When MYSQLD is writing the error log to a file, the server shuts down and re-opens the log file when flush LOGS or mysqladmin flush-logs is executed.
It is recommended that you manually rename the error log file before flush, and then the MySQL service will open a new file with the original file name.
The following is the error log backup method:
shell> MV Host_name.err Host_name.err-old
Shell> mysqladmin Flush-logs
shell> MV Host_name.err-old Backup-directory

3, Actual combat demo
#启用错误日志, by default the file name is: Hostname.err
#下面2种方式均可进行错误日志的配置
--log-error=file_name #命令行选项 (command option)
Log-error=file_name #配置文件 (Configure file)

#查看当前的错误日志配置, by default in the data directory
mysql> show variables like ' log_error ';
+---------------+-------------------------+
| variable_name | value                    |
+---------------+-------------------------+
| log_error     |/var/lib/mysql/szdb.err |
+---------------+-------------------------+
1 row in Set (0.00 sec)

#查看当前mysql Server error log file
Szdb:/var/lib/mysql # tail szdb.err 
140906 22:06:45 innodb:completed Initialization of buffer pool
140906 22:06:45 innodb:highest supported file format is Barracuda.
140906 22:06:45  innodb:waiting for the background threads to start
140906 22:06:46 innodb:5.5.37 started; log s Equence number 1605345
140906 22:06:47 [Note] Server hostname (bind-address): ' 0.0.0.0 '; port:3306
140906 22:06:47 [note]  -' 0.0.0.0 ' resolves to ' 0.0.0.0 ';
140906 22:06:47 [Note] Server socket created on IP: ' 0.0.0.0 '.
140906 22:06:47 [note] Event scheduler:loaded 0 Events
140906 22:06:47 [note]/usr/sbin/mysqld:ready for CONNECTI Ons.
Version: ' 5.5.37-log '   socket: '/var/lib/mysql/mysql.sock '   port:3306  mysql Community Server (GPL)

#停止mysql服务器
szdb:~ # service MySQL Stop
Shutting down MySQL .... done

#使用配置文件来设置log-error Parameters
Szdb:~ # echo "Log-error=/tmp/szdb.err" >>/etc/my.cnf
Szdb:~ # echo "skip_opt" >>/etc/my.cnf #添加一个异常参数skip_opt
szdb:~ # grep-v ^#/etc/my.cnf
[Mysqld]
Sql_mode=no_engine_substitution,strict_trans_tables
Log-error=/tmp/szdb.err
Skip_opt

#Author: Leshami
#Blog   :  http://blog.csdn.net/leshami
#启动mysql服务器
szdb:~ # mysqld_ Safe--user=mysql &
[1] 7315
szdb:~ # 140907 13:40:33 mysqld_safe Logging to '/tmp/szdb.err '.
140907 13:40:33 Mysqld_safe starting mysqld daemon with databases From/var/lib/mysql
140907 13:40:33 mysqld_safe MySQL D from PID File/var/lib/mysql/szdb.pid ended
[1]+  done                     mysqld_safe--user=mysql
szdb:~ # more/tmp/ Szdb.err
140907 13:40:33 mysqld_safe starting mysqld daemon with databases From/var/lib/mysql
140907 13:40:33 [err OR]/usr/sbin/mysqld:ambiguous option '--skip-opt ' (--skip-optimizer_prune_level)
140907 13:40:33 [ERROR] Aborting            #出现错误提示为有歧义的参数, instance terminated
140907 13:40:33 Mysqld_safe Mysqld from PID File/var/lib/mysql/szdb.pid ended

#修改my. cnf, remove skip-opt option
szdb:~ # VI/ETC/MY.CNF
szdb:~ # grep-v ^#/etc/my.cnf
[Mysqld]
Sql_mode=no_engine_substitution,strict_trans_tables
Log-error=/tmp/szdb.err

#再次启动mysql服务器
szdb:~ # Mysqld_safe--user=mysql &
[1] 7511
szdb:~ # 140907 13:43:23 mysqld_safe Logging to '/tmp/szdb.err '.
140907 13:43:23 Mysqld_safe starting mysqld daemon with databases From/var/lib/mysql
szdb:~ # More/tmp/szdb.err
140907 13:40:33 Mysqld_safe starting mysqld daemon with databases From/var/lib/mysql
140907 13:40:33 [ERROR]/usr/sbin/mysqld:ambiguous option '--skip-opt ' (--skip-optimizer_prune_level)
140907 13:40:33 [ERROR] Aborting
#以下内容为正常启动的相关信息
140907 13:40:33 mysqld_safe mysqld from PID File/var/lib/mysql/szdb.pid ended
140907 13:43:23 Mysqld_safe starting mysqld daemon with databases From/var/lib/mysql
140907 13:43:23 [Note] Plugin ' federated ' is disabled.
140907 13:43:23 innodb:the InnoDB memory heap is disabled
140907 13:43:23 innodb:mutexes and rw_locks use GCC atomic builtins
140907 13:43:23 innodb:compressed tables use zlib 1.2.3
140907 13:43:23 innodb:using Linux native AIO
140907 13:43:23 innodb:initializing buffer pool, size = 128.0M
140907 13:43:23 innodb:completed initialization of buffer pool
140907 13:43:23 innodb:highest Supported file format is Barracuda.
140907 13:43:23 innodb:waiting for the background threads to start
140907 13:43:24 innodb:5.5.37 started; Log sequence Number 1620641
140907 13:43:25 [Note] Server hostname (bind-address): ' 0.0.0.0 '; port:3306
140907 13:43:25 [Note]-' 0.0.0.0 ' resolves to ' 0.0.0.0 ';
140907 13:43:25 [Note] Server socket created on IP: ' 0.0.0.0 '.
140907 13:43:25 [Note] Event scheduler:loaded 0 Events
140907 13:43:25 [Note]/usr/sbin/mysqld:ready for connections.
Version: ' 5.5.37-log ' socket: '/var/lib/mysql/mysql.sock ' port:3306 mysql Community Server (GPL)

As you can see from the error day above, the format of the error log file is usually as follows:
Time [Error level] Error message
Some log information does not necessarily contain an error level

MySQL error log (err log)