Detailed explanation of how to monitor MySQL statements

Quick Reading

Why do we need to monitor SQL statements, how to monitor them, and what are the ways to monitor them.

We know that there is a tool called SQL Profile in SQL Server, which can monitor the SQL statements executed in SQL Server in real time to facilitate debugging bugs or confirm the final generated SQL statements.

Why monitor SQL statements?

1. As the program grows, SQL statements may be called from multiple places. You cannot confirm whether only the statement you need is executed at the current time.
2. Some persistence layer frameworks use LINQ syntax to write SQL, which is not convenient for outputting SQL statements in the program.
3. There is no way to change the program that is running online. But need to confirm where the problem is? Which SQL statements were executed. You can determine where the error occurred based on the sql statement.

How to monitor sql statements in mysql?

By default, MySQL does not enable SQL statement monitoring. When it needs to be enabled, execute the following command.

SHOW VARIABLES LIKE "general_log%"; -- off means off, if it is on, it has been turned on SET GLOBAL general_log = 'ON'; --Turn on log monitoring.

I have it turned on. If it is not turned on, general_log will show off.

As shown in the figure below, you can see the default path where the log is saved.

C:\ProgramData\MySQL\MySQL Server 5.5\Data\hcb-PC.log

Next we execute a few sql statements to check

Execute SQL

SELECT * FROM `platform`
UPDATE `platform` SET platform_type=2 WHERE id=1

Next, let’s look at the log.

You can see that the log contains

Time Id Command Argument

Corresponding to time, id, command, and parameter respectively

Parameters refer to the SQL statements that are executed.

How to display the SQL statement in MySQL in the table

It is inconvenient to view in the log, what if the SQL statement is displayed in a table in the database?

Execute the following sql

SET GLOBAL log_output = 'TABLE'; 
SELECT * FROM mysql.general_log ORDER BY event_time DESC 

Display with processlist

USE `information_schema`;
SELECT * FROM PROCESSLIST WHERE info IS NOT NULL;

It is said that this method can be displayed in real time, but I don’t know why the new SQL cannot be displayed in real time. I will record it first.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.