MSYQL log classification, storage, startup, slow query log (i)

classification of MySQL logs:

error log: (log_error) This log records the startup and shutdown of the server, and also records information about the failure or abnormal condition.

After we have installed the database, start the database if an exception occurs, we should first check this log file.

General Query log:(log) This log includes records of customer connections, SQL queries from customers, and various other events. generally we can use it to monitor the user's activities , who is connected, from which connection, what to do

Slow query log:(slow_query_log) This log is used to improve query performance, slow is time defined, with variables (long_query_time) to configure, The default value is 2.0, the unit is seconds, this slow also depends on your server, such as the server load is high, may query speed will drop, is not a slow query statement, will also be written to the slow query log.

Binary log (binary log): (Log_bin) This log is composed of multiple files, which are recorded by Update,delete,insert,create table, drop table, Grant and other statements completed data modification, binary log is a number of binary encoded data modification "event", it has a set of matching index files, which lists the existing binary log files on the server

Binary Log index file:(Log_bin_index) records the existing binary log files on the server, which can match the database backup file to restore the data table after the system crash, restore the database from the backup file, The Mysqlbinlog tool is then used to convert the contents of the binary log into a text statement, followed by the execution of each data modification statement executed after the last backup, to the state before the database crash was restored.

MySQL replication mechanism: It is through the binary log to the main server on the data modification events occurred from the server up

Relay log:(Relay_log) in the master-slave architecture, the general maintenance of a relay log from the server, which is recorded from the primary server, currently not executed data modification events, relay log and binary log format is the same, and also has a matching index file That lists the existing relay log files from the server.

Relay Log index files:(relay_log_index) lists the existing relay log files from the server.

Storage of logs:

After the 5.1.20 version: The log can also be stored as follows in addition to the file being written:

Error log: Can be sent to Syslog

General log: Can be written to the database table in the MySQL database

Slow query log: You can write to the database table in the MySQL database

Note: If we do not configure, the default MySQL server does not create any logs , except for two exceptions:

1. In the UNIX operating system: If you start the server with Mysqld_safe, the script creates an error log and tells the server to use it

2. In the Windows operating system: If the--console option is not used to indicate that the error message was sent to the console instead of being sent to the file, the server creates an error log

Note: If the error log file already exists, but the account used to log on to the server does not have write permission, the MySQL service will fail to start and no error messages are written to the log.

When you start the database server using Mysqld_safe, how to send the error log information to the syslog instead of to a log file:

Use the--syslog parameter instead of the--LOG_ERROR--MYSQL Technical Insider, but the parameter is not found in 5.6

error Log on Windows:

On Windows, MySQL writes the error log to the Hostname.err file in the data directory by default, but if you use the parameter--consoleat startup, MySQL will print the error message to the console and Will no longer be written to the error file.

But: If your MySQL is installed as a Windows service, then using--console will have no effect, because it can not find the console to output ...

How logging is enabled:

1. The way to use parameters: The following is the name of the parameter I can use in mysql5.6:

Parameters	Meaning	Value	Meaning
--log_error:	(Error log)	Path+file	Log file storage path
--log	General Log	Path+file
--slow_query_log	Whether to turn on slow query log	On/off	Switch
--slow_query_log_file	Slow query log File Store name	Path+file	Slow query File Store path and name
--log_bin	Whether to turn on binary log	On/off	Switch
--log_bin_basename	File name of binary log	Path+file
--log_bin_index	Binary Log index files	Path+file
--relay_log	Whether to turn on the relay log	On/off
--relay_log_basename	Relay log file name	Path+file
--relay_log_index	Relay Log Index File	Path+file

More commands can be used: show variables like "%log%"; to query

Note: When configuring the log, if there is no file, then MySQL will automatically create the file, but if there is no corresponding subdirectory, MySQL will not automatically create subdirectories