Shulou(Shulou.com)05/31 Report--

This article will explain in detail several kinds of logs about MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

The main log files for MySQL are as follows.

Error log

Record problems encountered when MySQL starts, runs, and shuts down.

You can specify the path by adding the parameter-- log-error [= file_name] after mysqld, or you can add log-error [= file_name] to the configuration file to specify the path.

General query log

Record client connections and SQL statements received from the client, which is useful when checking for client errors. Query logs are turned off by default. The query log can be recorded in a table in the database or in a log file

Use the parameter-- log-output=TABLE,FILE. Use-- general_log [= {0 | 1}] to activate or close the query log. You can specify the name of the log through-- general_log_file=file_name.

Binary log

Record changes to the database, such as the creation of tables, changes or deletions of data in tables. This log has two main uses:

(1) during replication, the primary node transmits the binary log to the standby node for application by the standby node.

(2) binary logs are required for some data recovery.

Binary logs do not record statements such as SELECT or SHOW that do not involve data changes. If you want to record all the SQL statements, you can use the query log. To open the binary log, add the-- log-bin [= base_name] parameter when you start the service.

Slow query log

Record queries whose execution time exceeds the long_query_time threshold. Also work with another parameter, min_examined_row_limit, which means that queries with fewer than this number of rows will not be logged to the slow query log. The unit of long_query_time is microseconds.

By default, administrative statements and queries that do not use indexes are not logged. To enable the slow log, you can add the parameter-- slow_query_log [= {0 | 1}] when starting the service. 1 means to open the log and 0 means to close the log.

You can specify the name of the slow query log through the-- slow_query_log_file=file_name parameter. You can specify the path to the slow query log through-- log-output. If you do not specify the name of the slow log, mysql will specify the name of the slow log as host_name-slow.log.

Log_queries_not_using_indexes decides whether queries that do not use indexes are recorded in the slow query log

Mysql > show variables like 'log_queries_not_using_indexes'

+-+ +

| | Variable_name | Value |

+-+ +

| | log_queries_not_using_indexes | OFF |

+-+ +

1 row in set (0.00 sec)

DDL log

Record the metadata of the DDL operation, such as DROP TABLE, ALTER TABLE statements. MySQL uses DDL logs to recover interrupted metadata operations. The DDL log is stored in the data directory, the file name is ddl_log.log, it is a binary log, do not edit this log artificially.

When using logs, you can use the FLUSH LOGS statement to refresh and restart all log files; back up the corresponding logs before refreshing.

Shell > mv host_name.log host_name-old.log

Shell > mysqladmin flush-logs

Shell > mv host_name-old.log backup-directory

The setting method of the log

[root@T400-kelong log] # vim / etc/my.cnf

..

# error log

Log-error=/log/err.log

# general query log

Log-output=FILE

General_log=1

General_log_file=/log/general_query.log

# bin log

Server_id=100

Log-bin=/log/product-bin

# slow query log

Slow_query_log=1

Slow_query_log_file=/log/slow_query.log

After setting up the parameter file, start the database

View the parameter files set in the database

Mysql > show variables like'% query_log%'

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_rows_query_log_events | OFF |

| | slow_query_log | ON |

| | slow_query_log_file | / log/slow_query.log |

+-+ +

3 rows in set (0.00 sec)

Mysql > show variables like'% general_log%'

+-+

| | Variable_name | Value |

+-+

| | general_log | ON |

| | general_log_file | / log/general_query.log |

+-+

2 rows in set (0.00 sec)

Mysql > show variables like'% log_bin%'

+-+

| | Variable_name | Value |

+-+

| | log_bin | ON |

| | log_bin_basename | / log/product-bin |

| | log_bin_index | / log/product-bin.index |

| | log_bin_trust_function_creators | OFF |

| | log_bin_use_v1_row_events | OFF |

| | sql_log_bin | ON |

+-+

6 rows in set (0.00 sec)

Mysql > show variables like'% error%'

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_error_action | ABORT_SERVER |

| | error_count | 0 | |

| | log_error | / log/err.log |

| | log_error_verbosity | 3 | |

| | max_connect_errors | 100 | |

| | max_error_count | 64 | |

| | slave_skip_errors | OFF |

+-+ +

7 rows in set (0.00 sec)

View the generated log file

[root@T400-kelong log] # ls

Err.log general_query.log product-bin.000001 product-bin.000002 product-bin.index slow_query.log

This is the end of this article on "there are several kinds of MySQL logs". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

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.