Shulou(Shulou.com)06/01 Report--

Log types in MySQL

MySQL has a variety of logs to record the activity of mysqld. The following are the types in MySQL and their brief descriptions.

Log type description General query log records connections and statements obtained from the client Slow query log records queries that exceed long_query_time time Error log records problems encountered when the mysqld service starts, runs or stops Binary log records statements that modify data Relay log in the replication structure, the data obtained from the library modifies the metadata operations performed by the DDL log (metadata log) DD statement

By default, no logs are available except for Error log on the Windows system.

The following will discuss the situation of these logs according to the common characteristics of logs and the characteristics of each log.

The common characteristics of logs enable the log

By default, logs are not available except Error log on the Windows system. To open these logs, you need to specify the appropriate options when the database starts.

Log type enable option General query log--general_logSlow query log--slow_query_logBinary log--log-binError log--log-errorRelay log does not need to be enabled extra DDL log does not need to be enabled

General query log and slow query log can also be enabled or disabled while the database is running. You can control whether they are enabled and disabled by setting the global variables general_log and slow_query_log, for example:

SET GLOBAL general_log = 'OFF'

SET GLOBAL slow_query_log = 'ON'

Log file name and location

By default, logs are stored in the data directory of the database. You can change the location and name with the appropriate options. You can refer to the following table, where host_name represents the hostname of the database server and nnnnnn represents a sequence of 6-bit integers. File_name and base_name are the specified file names. You can modify the location of the logs by using absolute paths in file_name or base_name.

Log type default name corresponding option General query loghost_name.log--general_log_file=file_nameSlow query loghost_name-slow.log--slow_query_log_file=file_nameBinary logpif-file-bin--log_bin=base_nameError loghost_name.err--log_error=file_nameRelay loghostname-relay-bin.nnnnnn--relay-log=file_nameDDL logddl_log.log cannot be modified

In addition to being recorded in log files, general query log and slow query log can also be recorded in log tables. The log table is in the mysql library. The names are general_log and slow_log, respectively. You can specify the location of these two types of logs through-- log-output, which can be tables, files, or tables and files.

The default name of the Binary log log file is the value of pid-file with the-bin suffix. The default value of pid-file is the hostname of the server. However, the default value is not recommended, and if the hostname of the server changes, the name of the Binary log log file needs to be changed accordingly.

Log refresh

The log refresh closes and then reopens the log file. For binary log and relay log, a new log file is also created.

Log refresh occurs in the following situations:

1. Use the FLUSH LOGS statement

two。 Use the mysqladmin command with the flush-logs or reflush option

3. Use the mysqldump command with the-- flush-logs or-- master-data option

The FLUSH LOGS statement closes and reopens all log files. If you need to refresh only a certain log file, you need to specify the log type, for example:

FLUSH SLOW LOGS

Logging time logging type recording time generl query log is written to the log when the server receives a command from the client. Slow query log is written to the log after the statement is executed and the lock is released. Binary log is written to the log before the statement is executed and the lock is released.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.