mysql经常锁死怎么办?mysql表锁死解决方法

mysql 表锁死通常是我们配置没配置好的同时网站流量也不小这样导致服务器或mysql处理不过来出现mysql 表锁死问题了,下面我就自己的问题找了一些相关mysql 表锁死问题的解决办法,下面来给大家分享一下.

在服务器上命令行登录，mysql -uroot -upassword,把密码写到命令行里,不用每次再输入,按ctrl+p 然后回车,尝试N次,终于登录进去了,连接数过多,终究还是可以找到空子进去的,赶快查看并保留运行状态的重要参数,代码如下:

show variables;show status;show processlist;

好家伙,这么多连接,代码如下:

1. |10520|unauthenticateduser|192.168.0.109:41063|NULL|Connect|NULL|login|NULL|
2. |10521|unauthenticateduser|192.168.0.109:41065|NULL|Connect|NULL|login|NULL|
3. |10522|unauthenticateduser|192.168.0.109:41067|NULL|Connect|NULL|login|NULL|
4. |10523|unauthenticateduser|192.168.0.109:41068|NULL|Connect|NULL|login|NULL|
5. |10524|unauthenticateduser|192.168.0.109:41069|NULL|Connect|NULL|login|NULL|
6. |10525|unauthenticateduser|192.168.0.109:41070|NULL|Connect|NULL|login|NULL|
7. |10526|unauthenticateduser|192.168.0.109:41071|NULL|Connect|NULL|login|NULL|

上千行,先不管,这里肯定是问题突破口,代码如下:

set global max_connections=4000;

增加允许的最大连接数,先让前台网站可以正常工作,回过头google:mysql unauthenticated user,果然,遇到此类问题的人很多,问题在于mysql的反向ip地址解析,配置参数里加上skip-name-resolve就可以.

一、查看进程运行情况,会话1,代码如下:

1. mysql>selectid,user,host,db,command,time,statefromprocesslista;
2. +—-+——+—————–+——————–+———+——+———–
3. +
4. |id|user|host|db|command|time|state
5. |
6. +—-+——+—————–+——————–+———+——+———–
7. +
8. |40|root|localhost:14046|information_schema|Query|0|executing
9. |
10. |39|root|localhost:13992|chf|Sleep|251|
11. |
12. |38|root|localhost:13991|chf|Sleep|251|
13. |
14. +—-+——+—————–+——————–+———+——+———–
15. +
16. 3rowsinset(0.00sec)

二、构造表被锁现象

1）锁住表（会话1）,代码如下:

mysql>LOCK TABLES chf.disc02 READ;或者–LOCK TABLES chf.disc02 WRITE;

2）执行dml操作（会话2）,代码如下:

mysql>delete from chf.disc02 limit 1;–会话处于卡死状态

3）查询进程运行情况（会话1）,代码如下:

1. mysql>selectid,user,host,db,command,time,statefromprocesslista;
2. +—-+——+—————–+——————–+———+——+———–
3. +
4. |id|user|host|db|command|time|state
5. |
6. +—-+——+—————–+——————–+———+——+———–
7. +
8. |41|root|localhost:14358|chf|Query|5|Locked
9. |
10. |40|root|localhost:14046|information_schema|Query|0|executing
11. |
12. |39|root|localhost:13992|chf|Sleep|343|
13. |
14. |38|root|localhost:13991|chf|Sleep|343|
15. |
16. +—-+——+—————–+——————–+———+——+———–
17. +
20. 4rowsinset(0.01sec)

说明:发现进程id为41的进程状态为Locked

三、解锁操作

1）删掉被锁进程（会话1）,代码如下:

mysql> kill 41;

出现现象（会话2）:ERROR 2013 (HY000): Lost connection to MySQL server during query

2）查看进程（会话1）,代码如下:

1. mysql>selectid,user,host,db,command,time,statefromprocesslista;
2. +—-+——+—————–+——————–+———+——+———–
3. +
4. |id|user|host|db|command|time|state
5. |
6. +—-+——+—————–+——————–+———+——+———–
7. +
8. |40|root|localhost:14046|information_schema|Query|0|executing
9. |
10. |39|root|localhost:13992|chf|Sleep|298|
11. |
12. |38|root|localhost:13991|chf|Sleep|298|
13. |
14. +—-+——+—————–+——————–+———+——+———–
15. +
16. 3rowsinset(0.01sec)

四、批量解锁,代码如下:

1. mysql>selectconcat(‘kill‘,id,’;')kill_processfromprocesslistawherea.state=’Locked’;//开源代码phpfensi.com
2. +————–+
3. |kill_process|
4. +————–+
5. |kill43;|
6. |kill42;|
7. +————–+
8. 2rowsinset(0.01sec)

Note:

1）可以使用show processlist查看当前用户连接

如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。show processlist;只列出前100条，如果想全列出请使用show full processlist;

2）在构造锁的会话中,使用unlock tables;也可以解锁

总结一下原因,大概如下:

因为mysql默认会根据客户端的ip地址反向解析,用于用户登录授权之用,不过正常情况下，很少会有人这样用,ip地址反向解析是很慢的，尤其是高负荷的mysql,每秒种几百次甚至更高的请求,这个请求压到本地的dns服务器上,dns服务器说不定会怀疑你在恶意请求，然后不理你了,然后这些登录请求就挂在那里,后面的连接还持续,然后越积越多,然后就达到mysql的最大连接数据限制了,然后新的连接就直接被拒,得到连接数过多的消息.

因为mysql配置文件使用的之前的配置文件,当时跟web同服务器,所以不存在这个问题.

这也正好解释了为什么phpMyAdmin里看mysqld状态时,有很多失败的连,它们应该就是因反解析失败而被拒的.