ÒýÓÃ
;=)k<6 \A 2r] -----------+
P{kur} T | 152 | root | 192.168.0.100:1808 | atomic_data | Query | 5600 | copy to tmp table | alter table `atomic_data`.`basic_point_beijing_p1`
N.,X<G.H change `valid` `type` int(2) NULL ,
fInb[ chang |
x-WmMfcz& | 155 | root | 192.168.0.100:1944 | atomic_data | Query | 4702 | Locked | alter table `atomic_data`.`basic_point_beijing_p1`
lA|
5E? change `valid` `type` int(2) NULL ,
[+2[`K
c] chang |
<=zQ NBtx | 158 | root | localhost | atomic_data | Query | 0 | NULL | show processlist |
X9v.1s, xI #9 @Q)OGjaq \FQRNj?'_ (2) kill 152;
! E<[JM D=q:*x ÒýÓÃ
$O&b`` cvn@/qBq*t Query OK, 0 rows affected (0.00 sec)
b6c Bg 5kc/Y/4o Zi *2nv' (3) show preocesslist;
x,=&JtKVc ÒýÓÃ
|kyX3~ F|SXn\ +-----+------+--------------------+-------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
3+)J
@(a | Id | User | Host | db | Command | Time | State | Info |
G?L HmTHg +-----+------+--------------------+-------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
[T8WThs | 155 | root | 192.168.0.100:1944 | atomic_data | Query | 4840 | copy to tmp table | alter table `atomic_data`.`basic_point_beijing_p1`
lEH65;Nh* change `valid` `type` int(2) NULL ,
$njUXSQ; chang |
8r*E-akuyr | 158 | root | localhost | atomic_data | Query | 0 | NULL | show processlist
\{Y 7FC~ J?*1*h F6|TP.VY_. ÅúÁ¿ kill mysql ÖÐÔËÐÐʱ¼ä³¤µÄsql
!S$:*5=& OSRp0G20k\ ÒÔÏÂÄÚÈÝÀ´×ÔmysqlÊֲ᣺
_zR+i]9 13.5.5.3. KILLÓï·¨
"Bh}}!13 KILL [CONNECTION | QUERY] thread_id
w^\52 ÿ¸öÓëmysqldµÄÁ¬½Ó¶¼ÔÚÒ»¸ö¶ÀÁ¢µÄÏß³ÌÀïÔËÐУ¬Äú¿ÉÒÔʹÓÃSHOW PROCESSLISTÓï¾ä²é¿´ÄÄЩÏß³ÌÕýÔÚÔËÐУ¬²¢Ê¹ÓÃKILL thread_idÓï¾äÖÕÖ¹Ò»¸öÏ̡߳£
57'*w]4f KILLÔÊÐí×ÔÑ¡µÄCONNECTION»òQUERYÐ޸ķû£º
[@&m4 7 ¡¤ KILL CONNECTIONÓë²»º¬Ð޸ķûµÄKILLÒ»Ñù£ºËü»áÖÕÖ¹Óë¸ø¶¨µÄthread_idÓйصÄÁ¬½Ó¡£
ts,ZvY] ¡¤ KILL QUERY»áÖÕÖ¹Á¬½Óµ±Ç°ÕýÔÚÖ´ÐеÄÓï¾ä£¬µ«Êǻᱣ³ÖÁ¬½ÓµÄÔ×´¡£
>"??!|XG^ Èç¹ûÄúÓµÓÐPROCESSȨÏÞ£¬ÔòÄú¿ÉÒԲ鿴ËùÓÐÏ̡߳£Èç¹ûÄúÓµÓÐSUPERȨÏÞ£¬Äú¿ÉÒÔÖÕÖ¹ËùÓÐÏ̺߳ÍÓï¾ä¡£·ñÔò£¬ÄúÖ»Äܲ鿴ºÍÖÕÖ¹Äú×Ô¼ºµÄÏ̺߳ÍÓï¾ä¡£
0.\/\V:H6 ÄúÒ²¿ÉÒÔʹÓÃmysqladmin processlistºÍmysqladmin killÃüÁîÀ´¼ì²éºÍÖÕÖ¹Ï̡߳£
*^\Ef4Lh ×¢ÊÍ£ºÄú²»ÄÜͬʱʹÓÃKILLºÍEmbedded MySQL Server¿â£¬ÒòΪÄÚÖ²µÄ·þÎñÆ÷Ö»ÔËÐÐÖ÷»úÓ¦ÓóÌÐòµÄÏ̡߳£Ëü²»ÄÜ´´½¨ÈκÎ×ÔÉíµÄÁ¬½ÓÏ̡߳£
TEP,Dq µ±Äú½øÐÐÒ»¸öKILLʱ£¬¶ÔÏß³ÌÉèÖÃÒ»¸öÌØÓеÄÖÕÖ¹±ê¼Ç¡£ÔÚ¶àÊýÇé¿öÏ£¬Ïß³ÌÖÕÖ¹¿ÉÄÜÒª»¨Ò»Ð©Ê±¼ä£¬ÕâÊÇÒòΪÖÕÖ¹±ê¼ÇÖ»»áÔÚÔÚÌض¨µÄ¼ä¸ô±»¼ì²é£º
Fe# 1 ¡¤ ÔÚSELECT, ORDER BYºÍGROUP BYÑ»·ÖУ¬ÔÚ¶ÁÈ¡Ò»×éÐкó¼ì²é±ê¼Ç¡£Èç¹ûÉèÖÃÁËÖÕÖ¹±ê¼Ç£¬Ôò¸ÃÓï¾ä±»·ÅÆú¡£
+G$4pt|= ¡¤ ÔÚALTER TABLE¹ý³ÌÖУ¬ÔÚÿ×éÐдÓÔÀ´µÄ±íÖỶÁÈ¡Ç°£¬¼ì²éÖÕÖ¹±ê¼Ç¡£Èç¹ûÉèÖÃÁËÖÕÖ¹±ê¼Ç£¬ÔòÓï¾ä±»·ÅÆú£¬ÁÙʱ±í±»É¾³ý¡£
`}D,5^9] ¡¤ ÔÚUPDATE»òDELETEÔËÐÐÆڼ䣬ÔÚÿ¸ö×é¶ÁÈ¡Ö®ºóÒÔ¼°Ã¿¸öÒѸüÐлòÒÑɾ³ýµÄÐÐÖ®ºó£¬¼ì²éÖÕÖ¹±ê¼Ç¡£Èç¹ûÖÕÖ¹±ê¼Ç±»ÉèÖã¬Ôò¸ÃÓï¾ä±»·ÅÆú¡£×¢Ò⣬Èç¹ûÄúÕýÔÚʹÓÃÊÂÎñ£¬Ôò±ä¸ü²»»á±» »Ø¹ö¡£
Giid~e33 ¡¤ GET_LOCK()»á·ÅÆúºÍ·µ»ØNULL¡£
1gE [v ¡¤ INSERT DELAYEDÏ̻߳á¿ìËÙµØˢУ¨²åÈ룩ËüÔÚ´æ´¢Æ÷ÖеÄËùÓеÄÐУ¬È»ºóÖÕÖ¹¡£
Q1T$k$n ¡¤ Èç¹ûÏß³ÌÔÚ±íËø¶¨¹ÜÀí³ÌÐòÖУ¨×´Ì¬£ºËø¶¨£©£¬Ôò±íËø¶¨±»¿ìËٵطÅÆú¡£
fJ=0HNmX ¡¤ Èç¹ûÔÚдÈëµ÷ÓÃÖУ¬Ïß³ÌÕýÔڵȴý¿ÕÏеĴÅÅ̿ռ䣬ÔòдÈë±»·ÅÆú£¬²¢°éËæ"disk full"´íÎóÏûÏ¢¡£
Z|&MKG24 ¡¤ ¾¯¸æ£º¶ÔMyISAM±íÖÕÖ¹Ò»¸öREPAIR TABLE»òOPTIMIZE TABLE²Ù×÷»áµ¼Ö³öÏÖÒ»¸ö±»Ë𻵵ÄûÓÐÓÃµÄ±í¡£¶ÔÕâÑùµÄ±íµÄÈκζÁÈ¡»òдÈ붼»áʧ°Ü£¬Ö±µ½ÄúÔÙ´ÎÓÅ»¯»òÐÞ¸´Ëü£¨²»Öжϣ©¡£
|O{m2Fi
YC*"Thuu 1¡¢Í¨¹ýinformation_schema.processlist±íÖеÄÁ¬½ÓÐÅÏ¢Éú³ÉÐèÒª´¦ÀíµôµÄMySQLÁ¬½ÓµÄÓï¾äÁÙʱÎļþ£¬È»ºóÖ´ÐÐÁÙʱÎļþÖÐÉú³ÉµÄÖ¸Áî
aEBu *`-j ¸´ÖÆ´úÂë
FXpJqlhNv mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root';
TeMHm?1^ +------------------------+
kq?Ms|h | concat('KILL ',id,';')
aacpM[{f +------------------------+
/RG:W0=K | KILL 3101;
O24m;oHM | KILL 2946;
kAKK bmE +------------------------+
b\ X@gq
2 rows in set (0.00 sec)
\|n-
O=}=2 OP;v bZ mysql>select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt';
q'+)t7! Query OK, 2 rows affected (0.00 sec)
]q\b,)4
e w3N[9w?1 mysql>source /tmp/a.txt;
BfXgh'Z~ Query OK, 0 rows affected (0.00 sec)
x!hh"x ¸´ÖÆ´úÂë
uY0lR:| !zBhbmlKt 2¡¢
Yh7rU?Gj ¡¡¡¡É±µôµ±Ç°ËùÓеÄMySQLÁ¬½Ó
V[mT<Lc mysqladmin -uroot -p processlist|awk -F "|" '{print $2}'|xargs -n 1 mysqladmin -uroot -p kill
@<,X0S ¡¡¡¡É±µôÖ¸¶¨Óû§ÔËÐеÄÁ¬½Ó£¬ÕâÀïΪMike
-7SAK1c$ mysqladmin -uroot -p processlist|awk -F "|" '{if($3 == "Mike")print $2}'|xargs -n 1 mysqladmin -uroot -p kill
fAF1"4f ¡¡
65L6:}# 3¡¢Í¨¹ýSHEL½Å±¾ÊµÏÖ
S&e0u%8mc #ɱµôËø¶¨µÄMySQLÁ¬½Ó
9X[kEl for id in `mysqladmin processlist|grep -i locked|awk '{print $1}'`
eJ,/:=QQ{ do
Y=%tn8< mysqladmin kill ${id}
Y$o<6[7 done
FSP+?(( >f^r^P 4¡¢Í¨¹ýMaatkit¹¤¾ß¼¯ÖÐÌṩµÄmk-killÃüÁî½øÐÐ
Q_1:tW
& #ɱµô³¬¹ý60ÃëµÄsql
N1Ng^aY0 mk-kill -busy-time 60 -kill
#r$cyV!k #Èç¹ûÄãÏëÏȲ»É±£¬ÏÈ¿´¿´ÓÐÄÄЩsqlÔËÐг¬¹ý60Ãë
Ac96
[ mk-kill -busy-time 60 -print
tI]Q%S, #Èç¹ûÄãÏëɱµô£¬Í¬Ê±Êä³öɱµôÁËÄÄЩ½ø³Ì
&]g}u5J!= mk-kill -busy-time 60 -print ¨Ckill
,bXe<L) ¡¡¡¡mk-kill¸ü¶àÓ÷¨¿É²Î¿¼£º
kd>hhiz| ¡¡¡¡
http://www.maatkit.org/doc/mk-kill.html VX].3=T8 ¡¡¡¡
http://www.sbear.cn/archives/426 R}'bP ¡¡¡¡Maatkit¹¤¾ß¼¯µÄÆäËüÓ÷¨¿É²Î¿¼£º
B+Rm>^CBm ¡¡¡¡
http://code.google.com/p/maatkit/wiki/TableOfContents?tm=6 WeJl4wF ¡¡¡¡²Î¿¼Îĵµ£º
^b/ Z)3 ¡¡¡¡
http://www.google.com G(Lzf( ¡¡¡¡
http://www.orczhou.com/index.PHP/2010/10/kill-mysql-connectio-in-batch/ DD7h^-x ¡¡¡¡
http://www.mysqlperformanceblog.com/2009/05/21/mass-killing-of-mysql-connections/