Detailed explanation of MySQL log settings and viewing methods

We are all familiar with MySQL logs. This article mainly introduces the MySQL log settings and viewing methods. Friends in need can refer to it. I hope it can help everyone.

MySQL has the following logs:
Error log: -log-err
Query log: -log
Slow query log: -log-slow-queries
Update log: -log-update
Binary log: -log-bin
By default, all logs are created in the mysqld data directory. By flushing the log, you can force mysqld to close and reopen the log file (or in some cases switch to a new log). When you execute a FLUSH LOGS statement or execute mysqladmin flush-logs or mysqladmin refresh, log refresh

1. Error log
Use --log- error[=file_name] option to specify the location where mysqld saves error log files. If no file_name value is given, mysqld uses the error log name host_name.err and writes the log file in the data directory. If you execute FLUSH LOGS, the error log is renamed with an -old suffix and mysqld creates a new empty log file. (If the --log-error option is not given, no renaming will occur).
If --log-error is not specified, or (on Windows) if you use the --console option, errors are written to stderr. Usually standard output is your terminal.

2. General Query Log                                                                                         If no file_name value is given, the default name is host_name.log.

3. Slowly query the log

Use-log-slow-queries [= file_name] option to start, mysqld writes a SQL statement that contains all executions over long_query_time seconds The log file. If the file_name value is not given, the default is not the host name, and the suffix is ​​-slow.log. If a filename is given but not an absolute pathname, the file is written to the data directory.

3. Update log                                                                                                                ;show variables like 'log_%';

How to know the current logmysql> show master status;Show the number of binary logs
mysql> show master logs;

Use to view binary log files mysqlbinlog

shell>mysqlbinlog mail-bin.000001
or shell>mysqlbinlog mail-bin.000001 | tail

Specify the log output location in the configuration file.
Windows: Windows configuration The file is my.ini, usually in the MySQL installation directory or c:\Windows.
Linux: The Linux configuration file is my.cnf, usually under /etc.

Under linux:

Sql code

# 在[mysqld] 中输入
#log
log-error=/usr/local/mysql/log/error.log
log=/usr/local/mysql/log/mysql.log
long_query_time=2
log-slow-queries= /usr/local/mysql/log/slowquery.log
# 在[mysqld] 中输入 #log
log-error=/usr/local/mysql/log/error.log
log=/usr/local/mysql/log/mysql.log
long_query_time=2
log-slow-queries= /usr/local/mysql/log/slowquery.log

Under windows:
Sql code

# 在[mysqld] 中输入
#log
log-error="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/error.log"
log="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/mysql.log"
long_query_time=2
log-slow-queries= "E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/slowquery.log"
# 在[mysqld] 中输入 #log
log-error="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/error.log"
log="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/mysql.log"
long_query_time=2
log-slow-queries= "E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/slowquery.log"

Enable slow query

long_query_time =2 --是指执行超过多久的sql会被log下来，这里是2秒
log-slow-queries= /usr/local/mysql/log/slowquery.log --将查询返回较慢的语句进行记录
log-queries-not-using-indexes = nouseindex.log --就是字面意思，log下来没有使用索引的query
log=mylog.log --对所有执行语句进行记录
windows下开启mysql日志：
在[mysql]下加入这些（基本上等于加在最后面）：
log-error=
#Enter a name for the query log file. Otherwise a default name will be used.
#注:（写成txt文件editplus可以及时重载,不过有时要放在C盘下editplus才可以及时重载）
log= c:/mysql_query.log.txt
#Enter a name for the slow query log file. Otherwise a default name will be used.
log-slow-queries=
#Enter a name for the update log file. Otherwise a default name will be used.
log-update=
#Enter a name for the binary log. Otherwise a default name will be used.
log-bin=

Related recommendations:

MYSQL log and backup and restore details

Sample code sharing that explains the detailed information of MySQL log system

Introduction to mysql log recovery data method

The above is the detailed content of Detailed explanation of MySQL log settings and viewing methods. For more information, please follow other related articles on the PHP Chinese website!