mysql Operations and Maintenance Server Log. (east_sun Reference Document)

Summary:

MySQL logs are classified into 1. server_log and 2. transaction_log. In the actual operation and maintenance, server logs are often used: server error logs, slow query logs and comprehensive query logs. Transaction logs are commonly used: storage engine transaction logs, binary logs.

1.1 Server Log Server Error Log:

Server error log mainly records Error, Warning, Note and other information in the process of MySQL running. Error log can be viewed if system error or a record problem occurs.

Mysql > SHOW GLOBAL VARIABLES LIKE'log_error'; This sentence SQL gives the specific location of the server error log:
+---------------+----------------------------------------+
| Variable_name | Value                                  |
+---------------+----------------------------------------+
| log_error     | /usr/local/mysql/data/mysqld.local.err |
+---------------+----------------------------------------+
1 row in set (0.00 sec)

Then enter the log_error file and read the content with less, you can see Error, Warning, Note and other information. It needs to be emphasized that the log_error is full of error information. At the same time, users use the important information and configuration parameter log_error document information in the start-up of the record instance. When a DBA encounters a failure to start mysql, it needs to think about checking log_error for the first time.

1.2 Server Log Slow Query Log (log_slow):
The main purpose of slow log query under server log classification is to record statements whose response time exceeds the threshold value in MySQL. Specifically, it refers to statements whose run time exceeds the long_query_time setting value. The default value is 10 seconds. At the same time, this parameter can be precise to microseconds.
MySQL official documentation( https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html Slow query log explanations are as follows:

The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. The minimum and default values of long_query_time are 0 and 10, respectively. The value can be specified to a resolution of microseconds. For logging to a file, times are written including the microseconds part. For logging to tables, only integer times are written; the microseconds part is ignored.

//Main parameters of slow log:

mysql> SHOW GLOBAL VARIABLES LIKE '%slow%'; **##View slow_log related logs**
+---------------------------+--------------------------------------------------------+
| Variable_name             | Value                                                  |
+---------------------------+--------------------------------------------------------+
| log_slow_admin_statements | OFF                                                    |
| log_slow_slave_statements | OFF                                                    |
| slow_launch_time          | 2                                                      |
| slow_query_log            | OFF                                                    |
| slow_query_log_file       | /usr/local/mysql/data/louhaomiaodeMacBook-Pro-slow.log |
+---------------------------+--------------------------------------------------------+
5 rows in set (0.01 sec)

mysql> SET GLOBAL slow_query_log = 1 ; **##Turn on Error Log Query (normally turned off by default)**
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%slow%'; **##View slow_log related logs**
+---------------------------+--------------------------------------------------------+
| Variable_name             | Value                                                  |
+---------------------------+--------------------------------------------------------+
| log_slow_admin_statements | OFF                                                    |
| log_slow_slave_statements | OFF                                                    |
| slow_launch_time          | 2                                                      |
| slow_query_log            | ON                                                     |
| slow_query_log_file       | /usr/local/mysql/data/louhaomiaodeMacBook-Pro-slow.log |
+---------------------------+--------------------------------------------------------+
5 rows in set (0.01 sec)
mysql> SHOW GLOBAL VARIABLES LIKE'%long%'; ##Default to 10 seconds
+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |
| performance_schema_events_stages_history_long_size       | 1000      |
| performance_schema_events_statements_history_long_size   | 1000      |
| performance_schema_events_transactions_history_long_size | 1000      |
| performance_schema_events_waits_history_long_size        | 1000      |
+----------------------------------------------------------+-----------+
5 rows in set (0.00 sec)

mysql> SET GLOBAL long_query_time = 5 ;##Generally, the online service is set to five seconds.
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE'%long%';
+----------------------------------------------------------+----------+
| Variable_name                                            | Value    |
+----------------------------------------------------------+----------+
| long_query_time                                          | 5.000000 |
| performance_schema_events_stages_history_long_size       | 1000     |
| performance_schema_events_statements_history_long_size   | 1000     |
| performance_schema_events_transactions_history_long_size | 1000     |
| performance_schema_events_waits_history_long_size        | 1000     |
+----------------------------------------------------------+----------+
5 rows in set (0.01 sec)