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

Today, I will talk to you about how to view links in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

1. View database links

The most common statement to view database links is show processlist, which can view the thread status that exists in the database. Ordinary users can only view links initiated by the current user, while users with PROCESS global privileges can view links for all users.

The Info field in the show processlist result displays only the first 100 characters of each statement, and if you need to display more information, you can use show full processlist. Similarly, if you look at the information_schema.processlist table, you can see the database link status information.

# ordinary users can only see the link mysql > select user () initiated by the current user; +-+ | user () | +-+ | testuser@localhost | +-+ 1 row in set (0.00 sec) mysql > show grants +-- + | Grants for testuser@% | +- -+ | GRANT USAGE ON *. * TO 'testuser'@'%' | | GRANT SELECT INSERT, UPDATE, DELETE ON `testdb`. * TO 'testuser'@'%' | +-+ 2 rows in set (0.00 sec) mysql > show processlist +-+ | Id | User | Host | db | Command | Time | State | Info | | +-+-+ | 769386 | testuser | localhost | NULL | Sleep | 769390 | | NULL | | 769390 | | | testuser | localhost | testdb | Query | 0 | starting | show processlist | +-+-+ 2 rows in set (0.00 sec) mysql > select * from information_schema.processlist | +-+-+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | + -- + | 769386 | testuser | localhost | NULL | Sleep | | NULL | | 769390 | testuser | localhost | testdb | Query | 0 | executing | select * from information_schema.processlist | +- -+-+ 2 rows in set (0.00 sec) # after PROCESS permission is granted You can see all users' links mysql > grant process on *. * to 'testuser'@'%' Query OK, 0 rows affected (0.01 sec) mysql > flush privileges; Query OK, 0 rows affected (0.00 sec) mysql > show grants +-- + | Grants for testuser@% | +- -+ | GRANT PROCESS ON *. * TO 'testuser'@'%' | | GRANT SELECT INSERT, UPDATE, DELETE ON `testdb`. * TO 'testuser'@'%' | +-+ 2 rows in set (0.00 sec) mysql > show processlist +-+ | Id | User | Host | db | Command | Time | State | Info | +-+-+ | 769347 | root | Localhost | testdb | Sleep | 53 | NULL | 769357 | root | 192.168.85.0 Sleep 61709 | NULL | Sleep | 521 | NULL | | 769386 | testuser | localhost | Sleep | 406 | NULL | 769473 | testuser | localhost | testdb | Query | 0 | starting | show processlist | | +-+-+ 4 rows in set (0.00 sec) |

Through the results of show processlist, we can clearly understand the details of each thread link. The specific meaning of the field is relatively easy to understand. Let's explain what each field represents:

Id: this is the unique identifier of this link, which can be killed by adding this ID value to the kill command.

User: refers to the user name that initiates this link.

Host: records the IP and port number of the client that sent the request, and you can locate which process of which client sent the request.

Db: which database is the currently executing command on? If no database is specified, the value is NULL.

Command: refers to the command that is being executed by the thread link at the moment.

Time: indicates the time that the thread link is in the current state.

State: the state of the thread, corresponding to Command.

Info: records the specific statements executed by the thread.

When there are too many database links, filtering useful information becomes a hassle, such as we only want to check links for a certain user or status. At this time, using show processlist will find some information we don't need. It will be much easier to filter with information_schema.processlist. Here are some common filtering requirements:

# only view the link information of an ID select * from information_schema.processlist where id = 705207; # filter out a user's link select * from information_schema.processlist where user = 'testuser'; # filter out all non-idle links select * from information_schema.processlist where command! =' Sleep'; # filter out links with an idle time of more than 600 seconds select * from information_schema.processlist where command = 'Sleep' and time > 600 # filter out links in a certain state select * from information_schema.processlist where state = 'Sending data'; # filter links to a client IP select * from information_schema.processlist where host like' 192.168.85.0%'

two。 Kill the database link

If a database link is abnormal, we can kill the link through the kill statement. The standard syntax for kill is: KILL [CONNECTION | QUERY] processlist_id

KILL allows the use of optional CONNECTION or QUERY modifiers:

KILL CONNECTION, like KILL without modifiers, terminates the process-related link.

KILL QUERY terminates the currently executing statement of the link, but leaves the link itself unchanged.

The ability to kill links depends on SUPER permissions:

If you do not have SUPER permission, you can only kill links initiated by the current user.

Users with SUPER privileges can kill all links.

In case of an emergency, when you need to kill links in batches, you can get the kill statement by stitching SQL, and then execute it. This will be much more convenient. Share several SQL that may be used to kill links:

# kill the link whose idle time is more than 600s, and splice the kill statement select concat ('KILL', id,';') from information_ schema.`processlist` where command = 'Sleep' and time > 600; # kill the link in a certain state, and splice the kill statement select concat (' KILL', id,';') from information_ schema.`processlist`where state = 'Sending data'; select concat (' KILL', id,'' ') from information_ schema.`processlist`where state =' Waiting for table metadata lock'; # kill the link initiated by a user, and splice the kill statement select concat ('KILL', id,';') from information_ schema.`processlist` user = 'testuser'

As a reminder here, the kill statement must be used with caution! Especially when this link executes an update statement or a table structure change statement, killing the link may take a long time to roll back.

After reading the above, do you have any further understanding of how to view links in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.