MySQL log file management for learning notes

The MySQL log file is divided into 4 types: error log, universal query log, slow query log, and binary log.

1. Error log: Log The problem that occurs when the MySQL server starts, runs, or stops.

(1) Start and set error log

The error log feature is turned on by default, and the error log cannot be disabled. Open the My.ini file in the data installation directory to find the Log-error option to view, as follows:

# Error Logging.

Log-error= "Ws.err"

The LOG-ERROR option specifies the location where the error log file mysqld saved, with a simple syntax structure as follows:

Log-erroe [=dir/fire_name];

In the above syntax, dir specifies the path to the error log, Fire_name is the name of the error log, and the default is the hostname when there is no parameter.

(2) Query error log

The error log is a text file that records when the MySQL service is turned on and off, as well as errors, warnings, and exception information during the operation of the service. If the MySQL service has an exception, you can open the error log to see why.

(3) Delete error log

The MySQL database can open new error logs in two ways: the Mysqladmin command and the FLUSH LOGS statement. The syntax structure of the Mysqladmin command is as follows:

Mysqladmin-u root-p Flash-logs

Executing the above command will automatically create a new error log, but the old error log will remain, just change its name and add the-old content after the name.

You must first log in to the MySQL database before you execute the Flush LOGS statement, so that after you create a new error log, the database administrator can back up the old error log to another hard disk, and you can delete the old error log if it is not already present.

2. General Query log: Record user login and query information.

The general query log is often referred to as a general query log or a normal query log, which is used to record all actions of the user, including starting and shutting down the MySQL service, updating statements, and query statements. By default, the General query log feature is turned off. You can query whether it is enabled through the SHOW GLOBAL VARIABLES statement. The statements and output are as follows:

Mysql> SHOW GLOBAL VARIABLES like '%general% ';

+---------------------------------------------+--------+

| variable_name | Value |

+---------------------------------------------+--------+

| General_log | OFF |

| General_log | WS.log |

+---------------------------------------------+--------+

2 rows in Set (0.00 sec)

3. Slow query log: Records all queries that have been executed for more than a specified time, or queries that do not use an index.

(1) Start slow query log

By default, the slow query log feature is turned off. You can view the slow query configuration directly from the show VARIABLES statement. Execute the statement and output the result as follows:

Mysql> Show variables like '%slow% ';

+------------------------------------------------+------------------------+

| variable_name | Value |

+------------------------------------------------+------------------------+

| log_show_admin_statements | OFF |

| log_slow_slave_statements | OFF |

| Slow_launch_time | 2 |

| Slow_query_log | OFF |

| Slow_query_log_fire | Ws-slow.log |

+------------------------------------------------+------------------------+

5 rows in Set (0.00 sec)

4. Binary log: The operation in the database is recorded as a binary file, but the query statement is not logged.

(1) Enable binary logging

The Log-bin option in the My.ini file can turn binary logs on or off. The code in the My.ini file is as follows:

# Binary Logging.

# Log-bin

Unlike the error log, the binary logging feature is turned off by default, and if you want to turn it on, you can remove the "#" before the Log-bin option, and then specify the parameter value for the Log-bin option. The syntax structure is as follows:

Log-bin [= Dir\[firename]]

(2) Viewing binary logs

Binary logs are stored in binary mode, so it cannot be read directly. If you want to view the contents of a binary log, you must use the Mysqlbinlog command tool that comes with MySQL to view it. You must specify the path to view the binary log file when you execute the command, or you will not find the specified binary file.

(3) Clean up the binary log

A few common ways to clean up binary log files:

1. Delete all binaries with the following syntax:

RESET MASTER;

2. Delete According to the number, the syntax is as follows:

PURGE MASTER LOGS to ' firename.number ';

3. Delete According to the creation time, the syntax is as follows:

PURGE MASTER LOGS before ' yyyy-mm-dd hh:mm:ss ';

(4) Restore the binary log with the following syntax:

Mysqlbinlog Firename.number | Mysql-u root-p

MySQL log file management for learning notes