How to configure MySQL log management

Generally, MySQL log files are stored in the database file storage directory by default (usually /usr/local/mysql/data/). You can also modify the configuration file to customize the storage location of the log files.

When I was compiling and installing, the database file storage directory was set to /home/mysql.

1. Introduction to four types of logs

1.1 Error log

The error log is used to record error information that occurs when MySQL starts, stops or runs. It is enabled by default.

vim /etc/my.cnf

[mysqld]

log-error=/home/mysql/mysql_error.log #Specify the storage location of the log

1.2 General query log

The general query log is used to record all connections and statements of MySQL. It is closed by default.

vim /etc/my.cnf
 
 [mysqld]
 general_log=ON
 general_log_file=/home/mysql/mysql_general.log

1.3 Binary log

Binary log (binlog) is used to record all statements that have updated data or have potentially updated data. It records data changes and can be used for data recovery. Default Turned on.

vim /etc/my.cnf
 
 [mysqld]
 log-bin=mysql-bin      #也可以log_bin=mysql-bin
 #使用相对路径，则文件存储在默认目录/usr/local/mysql/data/中

1.4 Slow query log

The slow query log is used to record all statements whose execution time exceeds long_query_time seconds. You can find which query statements take a long time to execute for optimization. It is turned off by default. .

 vim /etc/my.cnf
 
 [mysqld]
 slow_query_log=ON
 slow_query_log_file=/home/mysql/mysql_slow_query.log 
 long_query_time=5    #慢查询时间，设置超过5秒执行的语句被记录，缺省时为10秒

2. Configure four types of logs:

Step 1: Modify the configuration file /etc/my.cnf

vim /etc/my.cnf
 
 [mysqld]
 ##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
 log-error=/home/mysql/mysql_error.log
 
 
 ##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的
 general_log=ON
 general_log_file=/home/mysql/mysql_general.log
 
 
 ##二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
 log-bin=mysql-bin      #也可以log_bin=mysql-bin
 #使用相对路径，则文件存储在默认目录/home/mysql/中
 
 
 ##慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的
 slow_query_log=ON
 slow_query_log_file=/home/mysql/mysql_slow_query.log 
 long_query_time=5    #慢查询时间，设置超过5秒执行的语句被记录，缺省时为10秒

Step 2: Restart the service

systemctl restart mysqld

3. Query whether the log is enabled

mysql -u root -p[密码]
 
 #查看错误日志存放位置
 show variables like 'log_error';    
  
 #查看通用查询日志是否开启
 show variables like 'general%'; 
  
 #查看二进制日志是否开启
 show variables like 'log_bin%';     
 
 #查看慢查询日功能是否开启
 show variables like '%slow%';   
 
 #查看慢查询时间设置
 show variables like 'long_query_time';                      
 
 
 #在数据库中设置开启慢查询的方法，即以修改变量值的方式开启。但重启服务后会失效。
 set global slow_query_log=ON;

4. Split the binary log

# The ##mysql-bin.index file will split the binary log.

Every time mysql is restarted or after "flush logs" refreshes the log in the database, a new binary log will be generated.

The above is the detailed content of How to configure MySQL log management. For more information, please follow other related articles on the PHP Chinese website!