Introduction to the six log types in MySQL

MySQL mainly has the following log types:

Error log - information during the startup and shutdown process of the MySQL service as well as other error and warning information. The default is in the data directory.

General query log - a log used to record select query statements. general_log and general_log_file are closed by default and are recommended to be closed.

Slow query log - log-slow-queries records all SQL statements that exceed long_query_time,

Binary log - records any operations that cause data changes, used for backup and restore. Stored in the data directory by default, the binary log will be rolled during refreshes and service restarts.

Relay log - Events copied from the binary file of the main server and saved as a binary file in the same format as the binary log.

Transaction log - ensure transaction consistency.

Slow query log:

mysql> show variables like "long%";                    #查看慢查询日志信息
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.13 sec)

mysql> show variables like "slow%";                    #查看慢查询日志设置
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_launch_time    | 2                           |
| slow_query_log      | OFF                         |
| slow_query_log_file | /data/mysql/slave2-slow.log |
+---------------------+-----------------------------+

3 rows in set (0.06 sec)
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log #查看访问最多的10个SQL
mysql> set long_query_time=5;                           #慢查询时间设置，永久改变需要更改
Query OK, 0 rows affected (0.25 sec)                    配置文件

    二进制日志：
mysql> show global variables like "%log%";              #查看日志相关变量
mysql> show variables like "%log_bin%";                 #查看二进制变量
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
[root@slave02 mysql]# vim /etc/my.cnf
log_bin                                                #去掉注释，开启二进制日志
[root@slave02 mysql]# service mysqld restart
mysql> show binary logs;                               #查看所有二进制日志文件
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| slave2-bin.000001 |       168 |
| slave2-bin.000002 |       120 |
+-------------------+-----------+
mysql> show master status;                             #查看当前使用的二进制日志
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| slave2-bin.000002 |      120 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.03 sec)
mysql> flush logs;                                     #刷新二进制日志
Query OK, 0 rows affected (0.17 sec)
[root@slave02 mysql]# mysqlbinlog mysql-bin.00001;     #显示二进制文件内容
mysql> show global status like '%Slow_queries%';       #查看慢查询日志数目

[Related recommendations]

1. Free mysql online video tutorial

2. MySQL latest manual tutorial

3. Those things about database design

The above is the detailed content of Introduction to the six log types in MySQL. For more information, please follow other related articles on the PHP Chinese website!