MySQL error log and general query example analysis

Composition of MySQL log file system

a. Error log: records problems that occur when starting, running or stopping mysqld.

b. General log: records the established client connection and executed statements.

c. Update log: records statements that change data. This log is deprecated in MySQL 5.1.

d. Binary log: records all statements that change data. Also used for copying.

e. Slow query log: records all queries whose execution time exceeds long_query_time seconds or queries that do not use indexes.

f. Innodb log: innodb redo log

Start error log

• Under different circumstances, the error log will be recorded in different locations. If no file name is specified in the configuration file, the default name of the file is hostname.err.

• In the RPM publishing mode of MySQL5.6, the error log is placed in /var/log/mysqld.log by default.

• Can be configured through the my.cnf file:

 [mysqld_safe]
 log-error=/var/log/mysqld.log

Use vi /etc/my.cnf to open the configuration file

Note: Only after modifying the parameter file and restarting the MySQl service can the modification take effect.

View error log

MySQL error log is stored in text form. It can be viewed directly using various text-related commands.

Theperror command can be used to query the meaning of error codes. For example:

perror 1006

Use tail -n 15 /var/log/mysqld.log to view the error log

You need to pay attention to the time and level of the log generation. Usually the log level is divided into the following types:

debug, info, note, warning, error, critical

Delete error log

The error log may continue to grow. Therefore periodic maintenance is necessary.

Recommended deletion method:

#echo > /var/log/mysqld.log

General query log

Record queries executed by MySQL

Enable general query log

MySQL The general query log is not enabled by default.

Manually enable it when sampling analysis is required.

It is recommended to use method 1, because no persistent settings are required, and modifying the configuration file requires restarting the MySQL service

开启方法1：SET GLOBAL general_log=1；
关闭：SET GLOBAL general_log=0；
开启方法2：编辑配置文件 /etc/my.cnf
[mysqld]
general-log-file[=path/[filename]]
general-log=1

Note: If no general is specified When querying the location and file name of the log file, it is placed in the MySQl data file directory by default, that is, /var/lib/mysql, and the file name is hostname.log.

Delete the general query log

The general query log may continue to grow. It is usually turned on when doing sampling analysis. Remember to delete unused general query logs.

Recommended deletion method:

#set global general_log=0;删除前先关闭
#rm -rf /var/lib/mysql/hostname.log

Backup of Universal Query Log

In Linux or Unix, you can rename the file through the following command

And create a new file:

   shell> mv hostname.log hostname-old.log
   shell> mysqladmin flush-logs
   shell> cp hostname-old.log to-backup-directory
   shell> rm hostname-old.log

When a log file is opened by a Windows server, renaming of the log file is not allowed. You must first stop the server and then rename the log files. Then restart the server to create the new log file.

The above is the detailed content of MySQL error log and general query example analysis. For more information, please follow other related articles on the PHP Chinese website!