Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to view the SQL statements being executed by mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In project development, you always have to check the sql statements executed in the background, and the mysql database is no exception, the way to view it is generally like to use the show processlist; command to solve the problem. Through the field interpretation of the output results, you can analyze the type of sql statements that have been executed, but it is not very suitable for general primary users, so viewing sql statements through log files is the most direct way.

First, you need to create a log file log.txt. For example, I saved it directly in the middle of the data directory under the mysql directory, and then you only need to add it to the configuration file my.ini of mysql.

Log=d:/mysql/data/log.txt

This records all sql statements executed by mysql!

Of course, for those who like to try show processlist;, the following article provides a detailed explanation of the mysql show processlist command for readers to have more reference!

The following is the original text:

The output of the processlist command shows which threads are running and can help identify problematic query statements. Use this command in two ways.

1. Enter mysqladmin processlist under the mysql/bin directory

two。 Start mysql and enter show processlist

If you have SUPER permission, you can see all the threads; otherwise, you can only see the threads that you initiated (that is, the threads running under the corresponding MySQL account).

The form of the data is as follows (only three pieces have been intercepted):

Mysql > show processlist

+-

| | Id | User | Host | db | Command | Time | State | Info |

+-

| | 207 | root | 192.168.0.20 Sleep 51718 | mytest | Sleep | 5 | | NULL |

| | 208 | root | 192.168.0.20 Sleep 51719 | mytest | Sleep | 5 | | NULL |

| | 220 | root | 192.168.0.20 Query 51731 | mytest | Query | 84 | Locked |

Select bookname,culture,value,type from book where id=001

First briefly talk about the meaning and purpose of each column, the first column, id, needless to say, a logo, when you want to kill a statement is very useful. The user column shows the pre-order user. If it is not root, this command only shows the sql statements within your permissions. The host column, which shows which port of the ip this statement is issued from. Hehe, it can be used to track the user who has the problem statement. The db column that shows which database the process is currently connected to. The command column, which shows the commands executed by the current connection, which are generally sleep, query, connect. Time column, the duration of this state, in seconds. State column, shows the status of the sql statement using the current connection, very important column, there will be a description of all the states later, please note that state is only a certain state in the execution of the statement, a sql statement, which has been queried as an example, may need to be completed through copying to tmp table, Sorting result, Sending data, etc. Info column shows this sql statement, because the length is limited, so the long sql statement is not fully displayed But it is an important basis to judge the problem sentence.

The most important thing in this command is the state column. Mysql lists the following states:

Checking table is checking the data table (this is automatic). Closing tables is flushing modified data from the table to disk while closing the table that has been used up. This is a quick operation, and if not, you should make sure that the disk space is full or that the disk is under a heavy load. The Connect Out replication slave server is connecting to the master server. Copying to tmp table on disk is saving memory by changing temporary tables from memory storage to disk storage because the temporary result set is larger than tmp_table_size. Creating tmp table is creating a temporary table to hold some of the query results. The deleting from main table server is performing the first part of a multi-table delete, and the first table has just been deleted. The deleting from reference tables server is performing the second part of the multi-table deletion and is deleting records for other tables. Flushing tables is executing FLUSH TABLES, waiting for another thread to close the data table. If Killed sends a kill request to a thread, the thread will check the kill flag bit and abandon the next kill request. MySQL checks the kill flag bit in each main loop, but in some cases the thread may take a short time to die. If the thread is locked by another thread, the kill request takes effect as soon as the lock is released. Locked is locked by other queries. Sending data is processing the records of the SELECT query and is sending the results to the client. Sorting for group is sorting for GROUP BY. Sorting for order is sorting for ORDER BY. The process of Opening tables should be very fast, unless disturbed by other factors. For example, a data table cannot be opened by another thread until the execution of an ALTER TABLE or LOCK TABLE statement is complete. Trying to open a table. Removing duplicates is executing a query in SELECT DISTINCT mode, but MySQL cannot optimize those duplicate records in the previous stage. Therefore, MySQL needs to remove the duplicate records again and then send the results to the client. Reopen table acquires a lock on a table, but the lock cannot be acquired until the table structure has been modified. The lock has been released, the datasheet has been closed, and an attempt is being made to reopen the datasheet. The Repair by sorting repair directive is sorting to create an index. The Repair with keycache repair instruction is using the index cache to create new indexes one by one. It will be slower than Repair by sorting. Searching rows for update is talking about finding qualified records for updating. It must be done before UPDATE modifies the relevant records. Sleeping is waiting for the client to send a new request. System lock is waiting to acquire an external system lock. If you are not currently running multiple mysqld servers requesting the same table at the same time, you can disable external system locks by adding the-- skip-external-locking parameter. Upgrading lock INSERT DELAYED is trying to get a lock table to insert a new record. Updating is searching for matching records and modifying them. User Lock is waiting for GET_LOCK (). Waiting for tables the thread is informed that the data table structure has been modified and needs to be reopened to get the new structure. Then, in order to reopen the table, you must wait until all other threads close the table. This notification occurs in the following situations: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE. Waiting for handler insert INSERT DELAYED has processed all pending insert operations and is waiting for a new request. Most states correspond to fast operations, and as long as one thread stays in the same state for a few seconds, there may be a problem that needs to be checked. There are other states not listed above, but most of them are only useful to see if there are any errors on the server.

There are descriptions of all states in the mysql manual, and the link is as follows: http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html

The Chinese description is taken from http://www.linuxpk.com/5747.html

This is the end of the content of "how to view the SQL statements being executed by mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.