MySQL Error Log and mysqllog

MySQL Error Log and mysqllog

Like most relational databases, log files are an important part of MySQL databases. MySQL has several different log files, such as error log files, binary logs, common logs, and slow query logs. These logs help us to define what happens inside mysqld, database performance faults, records data change history, and user recovery database. This document describes error log files.

 

1. Composition of the MySQL Log File System
A. Error Log: records the problems that occur when mysqld is started, running, or stopped.
B. General logs: records established client connections and executed statements.
C. Update logs: statements used to record and change data. This log is no longer used in MySQL 5.1.
D. binary log: records all statements for changing data. It is also used for replication.
E. Slow query log: records all queries whose execution time exceeds long_query_time seconds or where no index is used.
F. Innodb log: 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 by refreshing the log (or switch to a new log in some cases ).
When you execute a flush logs statement or mysqladmin flush-logs or mysqladmin refresh, the log is aging.
In the case of MySQL replication, the slave replication server maintains more log files, which are called replacement logs.

 

2. Error Log
The error log is a text file.
The error log records detailed information about each MySQL Server startup and shutdown, as well as all serious warnings and error messages during the running process.
You can use the -- log-error [= file_name] Option to enable mysql error logs. This option specifies the location where mysqld stores error log files.
If the file_name value is not specified for the -- log-error [= file_name] Option, mysqld uses the error log name host_name.err and writes the log file to the data directory.
When mysqld is writing an error log to a file and executing flush logs or mysqladmin flush-logs, the server will close and re-open the log file.
We recommend that you manually rename the error log file before flushing, and mysql will use the original file name to open a new file.
The following describes how to back up error logs:
Shell> mv host_name.err host_name.err-old
Shell> mysqladmin flush-logs
Shell> mv host_name.err-old backup-directory

 

3. Practical demonstration
# Enable error logs. The default file name is hostname. err.
# The following two methods can be used to configure error logs
-- Log-error = file_name # command option)
Log-error = file_Name # configuration file (configure file)

# View the current error log configuration, which is located in the data directory by default
Mysql> show variables like 'Log _ error ';
+ --------------- + ------------------------- +
| Variable_name | Value |
+ --------------- + ------------------------- +
| Log_error |/var/lib/mysql/SZDB. err |
+ --------------- + ------------------------- +
1 row in set (0.00 sec)

# Viewing the current 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 sequence 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 schedents: Loaded 0 events
140906 22:06:47 [Note]/usr/sbin/mysqld: ready for connections.
Version: '5. 5.37-log' socket: '/var/lib/mysql. sock' port: 3306 MySQL Community Server (GPL)

# Stopping a mysql Server
SZDB :~ # Service mysql stop
Shutting down MySQL... done

# Use the configuration file to set the log-error parameter
SZDB :~ # Echo "log-error =/tmp/SZDB. err">/etc/my. cnf
SZDB :~ # Echo "skip_opt">/etc/my. cnf # Add an exception parameter 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
# Start the mysql server
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 mysqld 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 [ERROR]/usr/sbin/mysqld: ambiguous option '-- skip-opt' (-- skip-optimizer_prune_level)
140907 13:40:33 [ERROR] Aborting # ERROR message: ambiguous parameter, instance termination
140907 13:40:33 mysqld_safe mysqld from pid file/var/lib/mysql/SZDB. pid ended

# Modify my. cnf and delete the 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

# Start the mysql server again
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
# The following content is related to normal startup
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.0 M
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 schedents: Loaded 0 events
140907 13:43:25 [Note]/usr/sbin/mysqld: ready for connections.
Version: '5. 5.37-log' socket: '/var/lib/mysql. sock' port: 3306 MySQL Community Server (GPL)

The error log file format is as follows:
Time [error level] error message
Some log information does not necessarily contain the error level

 

 

How does ubuntu view mysql error logs?

Whether logs are enabled
Mysql> show variables like 'Log _ % ';
How to know the current log
Mysql> show master status;
Display the number of binary logs
Mysql> show master logs;
Use mysqlbinlog to view binary log files
Shell> mysqlbinlog mail-bin.000001
Or shell> mysqlbinlog mail-bin.000001 | tail
Specify the log output location in the configuration file.
The configuration file for Linux is my. cnf, which is usually in/etc.
In linux:
SQL code
# Input in [mysqld]
# Log
Log-error =/usr/local/mysql/log/error. log
Log =/usr/local/mysql/log/mysql. log
Long_query_time = 2
Log-slow-queries =/usr/local/mysql/log/slowquery. log

# Input # log in [mysqld]
Log-error =/usr/local/mysql/log/error. log
Log =/usr/local/mysql/log/mysql. log
Long_query_time = 2
Log-slow-queries =/usr/local/mysql/log/slowquery. log

Where is the mysql error log?

If you haven't changed anything,
In windows, the file extension under the data directory under the installation directory is. err. You can open the my. ini file under the installation directory and check
In linux, it is usually/var/log/mysqld. log. You 'd better use cat/etc/my. cnf