Mysql--mysql Log

In MySQL, there are 4 different logs, namely error log, binary log (BINLOG log), query log, and slow query log.

1. Error log

The error log is one of the most important logs in MySQL, and it records any critical information that occurs when mysqld starts and stops, and when the server is running. You can view this log first when any failure in the database causes it to work correctly.

You can use the--log-error[=file_name] option to specify the location of the mysqld (MySQL server) to save the error log file. If no file_name value is given, MYSQLD uses the error log name Host_name.err (host_name as the hostname) and writes the log file by default in the directory specified by the parameter DataDir (data directory).

2. binary files

The binary log (BINLOG) records all DDL (data definition Language) statements and DML (data manipulation language) statements, but does not include data query statements. Statements are saved as "events," which describe the process of changing the data. This log plays an extremely important role in recovering data from disasters.

2.1 Location and format of the log

When started with the--log-bin[=file_name] option, MYSQLD begins writing data changes to the log file. If the file_name value is not given, the default name is hostname followed by "-bin". If the file name is given, but the path is not included, the files are written to the directory specified by the parameter DataDir (data directory) by default.

In MySQL 5.5, the binary log format is divided into 3 types: STATEMENT, ROW, MIXED, can be set at startup by parameter--binlog_format, the difference between the 3 formats is as follows.

• STATEMENT

This is the way the previous versions of MySQL 5.1 are used. As the name implies, the log is recorded in the statement is the statement (a), each of the data caused by the modification of the SQL statement will be recorded in the log, through the Mysqlbinlog tool, you can clearly see the text of each statement. When master-slave replication occurs, the log is parsed from the library (slave) to the original text and executed again from the library.

The advantage of this format is that the log records are legible, the log volume is small, and the impact on I/O is small.

The disadvantage is that in some cases slave log replication will be faulted.

• ROW

After MySQL 5.1.11, this new log format appears. It records the changes in each row to the log instead of logging the SQL statement.

The advantage of this format is that the details of the changes in each row of data are recorded, and there are no cases where replication cannot occur.

The disadvantage is that the log volume is large and the impact on I/O is large.

• MIXED

This is the current MySQL default log format, which is a mix of STATEMENT and ROW two logs. By default, STATEMENT is used, but in some special cases the row is used for logging, such as using the NDB storage engine, at which time the DML statements on the table are all ROW, the client uses the temporary table, the client uses the indeterminate function, such as current_user (), etc. Because this indeterminate function may have different values from the master, it results in inconsistent master-slave data. The MIXED format makes the best use of the advantages of both modes and avoids their drawbacks.

Note: You can set the log format for Binlog_format at the global and session levels, but be careful to ensure that replication from the library is working correctly.

2.2 Reading of the log

Since the log is stored in binary mode and cannot be read directly, it needs to be viewed with the Mysqlbinlog tool, with the following syntax:

1 Shell > Mysqlbinlog log-file;

If the log is in ROW format, then Mysqlbinlog parsing is a bunch of unreadable characters. You can add the-V or the-VV parameter to read at this time.

2.3 Deletion of logs

Method 1

Perform

RESET MASTER;

command, the command deletes all BINLOG logs and the new log number starts at 000001.

Method 2

Perform

PURGE MASTER LOGS to ' mysql-bin.****** '

command, the command will delete all logs before the "******" number.

Method 3

Perform

PURGE MASTER LOGS before ' Yyyy-mm-dd hh24:mi:ss '

command, which deletes all logs that are generated before the specified log.

Method 4

Set the parameter--expire_logs_days=#, which means to set the number of days that the log expires, and the log will be automatically deleted after a specified number of days, which will help reduce the amount of DBA administration logs.

Trigger log file Update:

Mysqladmin Flush-log

2.4 Other options

Binary logging is very important for data integrity and security because it records the process of data change. As a result, MySQL also provides some additional parameter options for smaller-grained management.

--binlog-do-db=db_name: This option tells the primary server that if the current database (that is, use selected database) is db_name, the record should be updated into the binary log. All other database updates that are not explicitly specified are ignored and are not recorded in the log.

--binlog-ignore-db=db_name: This option tells the server that if the current database (that is, use selected database) is db_name, updates should not be saved to the binary log, and other databases that are not explicitly ignored will be logged.

If you want to record or ignore multiple databases, you can use the above two options several times, specifying the appropriate options for each database.

--innodb-safe-binlog: This option is often used in conjunction with--sync-binlog=n (each write N log synchronous disk), making transaction logging in logs more secure.

SET sql_log_bin=0: A client with SUPER privileges can prevent its own statements from being credited to a binary record by this statement. This option is useful in some environments, but be careful when used because it is likely to cause incomplete logging or inconsistent master-slave data in the replication environment.

Mysql--mysql Log