MySQL Error Log)

MySQL Error Log)

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.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

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: