Today, a colleague asked me to help me read a report. This report was generated by an open-source system and the background database uses MySQL. the problem is that the data in one report is incorrect. At that time, the other report was correct. Therefore, we hope to find the background logic and modify the report.
This is my first time in contact with MySQL, so it takes time to study it. At that time, the idea was to view the running SQL statements like SQL Server or Oracle trace.
Start to connect to MySQL. Check help and log on with the root Super User. The system keeps prompting that the error cannot be connected to localhost. Then we can see that the parameter is the port specified by P, add the port to connect to the instance.
After logon, run show processlist, but only some spids are displayed, without detailed SQL statements.
Then I thought about finding a GUI tool to capture the trace. I saw that MySQL provided some logs that could be used to record the executed SQL and allowed slow SQL. The command is as follows:
-- Enable the SQL Execution record Function
Set global log_output = 'table'; -- output to table
Set global log = on; -- enable the general_log function for all command execution records. All statements: successful and unsuccessful.
Set global log_slow_queries = on; -- open the slow query SQL record slow_log and run the slow query statement and the statement without using the index successfully.
Set global long_query_time = 0.1; -- slow query time limit (seconds)
Set global log_queries_not_using_indexes = on; -- Records SQL statements that do not use indexes.
-- Query SQL Execution records
Select * From mysql. slow_log order by 1; -- executed successfully: Slow query statement, and statement without using Index
Select * From mysql. general_log order by 1; -- all statements: successful and unsuccessful.
-- Disable SQL Execution records
Set global log = off;
Set global log_slow_queries = off;
But I directly reported an error when running, saying "unknown system variable logoutput ".
Use show variables to view the current configurable options. The configuration items corresponding to global log_output are not displayed. MySQL configuration documents cannot be found at night on the server.
Let's look at the help documentation for version 5.1. Is it because of version issues? With this question, I checked @ version. Our version is 4.1. Then I checked the Help File of 4.1, so there would be no configuration items above.
Although the trace is not caught, the corresponding information is found because the column to be queried is known, and the report is correct after the error data is changed. It seems that you have to take a moment to know about mysql. After all, many open-source software uses this database as the background.