How does MySQL often lock the dead? MySQL table lock resolution method

On the server command line login, Mysql-uroot-upassword, write the password to the command line, do not need to input each time, press ctrl+p and then enter, try N times, and finally logged in; The number of connections, after all, can still find a loophole in. Quickly view and retain important parameters for the running state:

The code is as follows	Copy Code
Show variables; Show status; Show Processlist;

Boy, so many connections:

| 10520 | Unauthenticated user | 192.168.0.109:41063 | NULL | Connect | NULL | Login | NULL |
| 10521 | Unauthenticated user | 192.168.0.109:41065 | NULL | Connect | NULL | Login | NULL |
| 10522 | Unauthenticated user | 192.168.0.109:41067 | NULL | Connect | NULL | Login | NULL |
| 10523 | Unauthenticated user | 192.168.0.109:41068 | NULL | Connect | NULL | Login | NULL |
| 10524 | Unauthenticated user | 192.168.0.109:41069 | NULL | Connect | NULL | Login | NULL |
| 10525 | Unauthenticated user | 192.168.0.109:41070 | NULL | Connect | NULL | Login | NULL |
| 10526 | Unauthenticated user | 192.168.0.109:41071 | NULL | Connect | NULL | Login | NULL |
Like some thousand lines. First of all, this is definitely a problem.

The code is as follows	Copy Code
Set global max_connections=4000;

Increase the maximum number of connections allowed, so that the front desk site can work properly.

Back to Google:mysql unauthenticated user

Sure enough, a lot of people encounter such problems, the problem is MySQL reverse IP address resolution, configuration parameters plus skip-name-resolve can be.

Add

First, view process performance (Session 1)

The code is as follows

Copy Code

Mysql> Select Id,user,host,db,command,time,state from Processlist A; +--+--+ ————— –+ —————— –+ ——— +--+ ——— – + | ID | user | Host | db | Command | Time | State | +--+--+ ————— –+ —————— –+ ——— +--+ ——— – + | 40 | Root | localhost:14046 | Information_schema | Query | 0 | Executing | | 39 | Root | localhost:13992 | CHF | Sleep | 251 | | | 38 | Root | localhost:13991 | CHF | Sleep | 251 | | +--+--+ ————— –+ —————— –+ ——— +--+ ——— – + 3 Rows in Set (0.00 sec)

Second, the construction table is locked phenomenon
1) Lock table (Session 1)

The code is as follows	Copy Code
Mysql>lock tables Chf.disc02 READ; or –lock tables CHF.DISC02 WRITE;

2) performing DML operations (Session 2)

The code is as follows	Copy Code
Mysql>delete from CHF.DISC02 limit 1;– session is stuck in a dead state

3 Query Process operation (Session 1)

The code is as follows

Copy Code

Mysql> Select Id,user,host,db,command,time,state from Processlist A; +--+--+ ————— –+ —————— –+ ——— +--+ ——— – + | ID | user | Host | db | Command | Time | State | +--+--+ ————— –+ —————— –+ ——— +--+ ——— – + | 41 | Root | localhost:14358 | CHF | Query | 5 | Locked | | 40 | Root | localhost:14046 | Information_schema | Query | 0 | Executing | | 39 | Root | localhost:13992 | CHF | Sleep | 343 | | | 38 | Root | localhost:13991 | CHF | Sleep | 343 | | +--+--+ ————— –+ —————— –+ ——— +--+ ——— – +

4 rows in Set (0.01 sec)
Note: The process status with process ID 41 is found to be locked
Third, unlock operation
1) Delete the locked process (session 1)

The code is as follows	Copy Code
Mysql> Kill 41;

Symptoms (Session 2)
ERROR 2013 (HY000): Lost connection to MySQL server during query
2 View process (Session 1)

The code is as follows

Copy Code

Mysql> Select Id,user,host,db,command,time,state from Processlist A; +--+--+ ————— –+ —————— –+ ——— +--+ ——— – + | ID | user | Host | db | Command | Time | State | +--+--+ ————— –+ —————— –+ ——— +--+ ——— – + | 40 | Root | localhost:14046 | Information_schema | Query | 0 | Executing | | 39 | Root | localhost:13992 | CHF | Sleep | 298 | | | 38 | Root | localhost:13991 | CHF | Sleep | 298 | | +--+--+ ————— –+ —————— –+ ——— +--+ ——— – + 3 rows in Set (0.01 sec)

Four, bulk unlock

The code is as follows	Copy Code
Mysql> Select Concat (' Kill ', id, '; ') kill_process from Processlist a where a.state= ' Locked '; + ———— –+ | kill_process | + ———— –+ | Kill 43; | | Kill 42; | + ———— –+ 2 rows in Set (0.01 sec)

Note:
1 can use show processlist to view current user connections
If it is the root account, you can see the current connection for all users. If it's another normal account, you can only see the connection you're using. Show Processlist only list the first 100, if you want to list all please use show full processlist;
2 in the conversation of constructing the lock, use unlock tables; you can also unlock

Summarize the reasons, probably as follows:

Because MySQL defaults to the client's IP address in reverse resolution, for user login authorization. But normally, few people use this. IP address reverse resolution is very slow, in particular, the high load of MySQL, hundreds of times per second, or even higher requests, this request to the local DNS server, the DNS server may suspect you in a malicious request, and then ignore you, and then these login requests are hanging there, the subsequent connection is continued, Then the more accumulated, and then the MySQL maximum connection data limit, and then the new connection will be directly rejected, get the number of connections too many messages.

Because the MySQL profile uses the previous configuration file, then with the Web server, so there is no this problem.

This also explains why phpMyAdmin in the mysqld state, there are many failed connections, they should be rejected because of the failure to resolve.