MySQL log file _ MySQL

MySQL log file bitsCN.com

MySQL log file

I,

MySQL has different types of log files, from which you can query what MYSQL has done, which is indispensable for MYSQL management.

1. error log: records errors during database startup, operation, and stop;

-- Log-error = [file-name] is used to specify the location where error logs are stored.

If [file-name] is not specified, the default hostname. err is used as the file name, which is stored in the DATADIR directory by default.

Error log file format:

Time [error level] error message

The MySQL client can obtain the detailed location of the error log using the following methods:

1. mysql> show variables like 'log _ error ';

2. + --------------- + --------------------------------------------- +

3. | Variable_name | Value |

4. + --------------- + --------------------------------------------- +

5. | log_error |/usr/local/mysql/var/51ososdb. err |

6. + --------------- + --------------------------------------------- +

0.00 row in set (sec)

Run the show variables like 'log _ error' command to obtain the value of the log_error variable, which is the detailed location of the error log file.

Error log file record level:

The error log has three levels: error, warning, and information. I am using a mysql-5.1.51 installed under/usr/local/mysql.

In/usr/local/mysql/include/my_sys.h

# Cat my_sys.h you will find the following definition

Enum loglevel {

ERROR_LEVEL, // error level

WARNING_LEVEL, // warning level

INFORMATION_LEVEL // information level

};

The following is an error log sample:

2. Error message template

080313 05:21:55 mysqld started

080313 5:21:55 InnoDB: Started; log sequence number 0 43655

080313 5:21:55 [Note]/usr/local/mysql/bin/mysqld: ready for connections.

Version: '5. 0.26-standard-log' socket: '/var/lib/mysql. sock' port: 3306 MySQL Community Edition-Standard (GPL)

080313 5:24:13 [Note]/usr/local/mysql/bin/mysqld: Normal shutdown

080313 5:24:13 InnoDB: Starting shutdown...

080313 5:24:16 InnoDB: Shutdown completed; log sequence number 0 43655

080313 5:24:16 [Note]/usr/local/mysql/bin/mysqld: Shutdown complete

080313 05:24:16 mysqld ended

080313 05:24:47 mysqld started

080313 5:24:47 InnoDB: Started; log sequence number 0 43655

080313 5:24:47 [Note]/usr/local/mysql/bin/mysqld: ready for connections.

Version: '5. 0.26-standard-log' socket: '/var/lib/mysql. sock' port: 3306 MySQL Community Edition-Standard (GPL)

2. ISAM (index sequential access method) operation log (The isam log): records all changes to The ISAM table. This log is only used to debug The ISAM mode;

3. SQL execution log (the query log): records the client connection and the executed SQL statements;

4. update log: the statement that records data changes. it is not recommended to use it, instead of binary logs;

5. binary log: records all statements for modifying database data;

6. time-out log (the slow log): records all statements whose execution time exceeds the maximum SQL execution time (long_query_time) or which are not indexed;

If you are using mysql's copy and backup functions, the server also provides a log file called relay log;

II,

By default, all log files are recorded in the MYSQL data directory. you can force mysql to close and re-open a file to record the logs. The system will automatically add a suffix (such. 00001). you can run the mysql> flush logs statement in the mysql environment or run the mysqladmin management program # mysqladmin flush-logs or # mysqladmin refresh.

The startup method of these logs can be followed by the option parameters when starting the database in mysql_safe mode, or configured in the configuration file. The second method is recommended. the configuration method is very simple.

I only configured three types of logs:

[Mysqld]

Log =/var/log/mysqld_common.log

Log-error =/var/log/mysqld_err.log

Log-bin =/var/log/mysqld_bin.bin

Log viewing is very simple. most of the logs are text. you can directly view the logs using tools such as vim, less, and more. it is worth noting that you can view the binary files:

1) First, determine whether the binary file record function is enabled.

Mysql> show variables like 'log _ bin ';

2) If you want to know the details of the file that records binary data, you can use the following statement to see which file is being recorded and the current location of the record:

Mysql> show master status;

3) to view binary data, you need to use the program mysqlbinlog to see which options are supported and use them as needed.

Mysqlbinlog/var/log/mysql/mysql-bin.000040;

4) you can execute the following statements to query a certain time range. if there are many records, you can direct the results to a file and read them :-):

Mysql> mysqlbinlog -- start-datetime = '2017-01-01 00:00:00 '-- stop-datetime = '2017-08-08 00:00:00'/var/log/mysql/mysql-bin.000040>. /tmp. log

BitsCN.com