ÒýÓÃ
wIz<Y{HA=
cHs@1R/-s -----------+
YA O,
rh | 152 | root | 192.168.0.100:1808 | atomic_data | Query | 5600 | copy to tmp table | alter table `atomic_data`.`basic_point_beijing_p1`
,5HQHo@ change `valid` `type` int(2) NULL ,
q
[Rqy !, chang |
A1zM$
wDU | 155 | root | 192.168.0.100:1944 | atomic_data | Query | 4702 | Locked | alter table `atomic_data`.`basic_point_beijing_p1`
}$s#H{T! change `valid` `type` int(2) NULL ,
FA<|V!a chang |
j|TcmZGO | 158 | root | localhost | atomic_data | Query | 0 | NULL | show processlist |
\:-#,( .V I3]-$ ^EWkJW,Yc 9{@[l!]W (2) kill 152;
qZACX.Hw 2jV.\C k ÒýÓÃ
6z=h0,Y} zgRZgVj Query OK, 0 rows affected (0.00 sec)
a|rN %hA4 m/RX~,T*v& )Kkw$aQI"d (3) show preocesslist;
"S)2<tV ÒýÓÃ
/8(c^ C4wJSQl_I +-----+------+--------------------+-------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
&<hDl<E | Id | User | Host | db | Command | Time | State | Info |
IlQNo 1 +-----+------+--------------------+-------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
&AM<H}> | 155 | root | 192.168.0.100:1944 | atomic_data | Query | 4840 | copy to tmp table | alter table `atomic_data`.`basic_point_beijing_p1`
kHLpa/A change `valid` `type` int(2) NULL ,
D+edTAQ8 chang |
7{F(NJUO1 | 158 | root | localhost | atomic_data | Query | 0 | NULL | show processlist
r*?rwtFtg GVS-_KP\
s+#|j;V< ÅúÁ¿ kill mysql ÖÐÔËÐÐʱ¼ä³¤µÄsql
FyD^\6/x wkNf[>jX? ÒÔÏÂÄÚÈÝÀ´×ÔmysqlÊֲ᣺
ybnq;0}$ 13.5.5.3. KILLÓï·¨
*;^!FBT KILL [CONNECTION | QUERY] thread_id
P$l-p'U- ÿ¸öÓëmysqldµÄÁ¬½Ó¶¼ÔÚÒ»¸ö¶ÀÁ¢µÄÏß³ÌÀïÔËÐУ¬Äú¿ÉÒÔʹÓÃSHOW PROCESSLISTÓï¾ä²é¿´ÄÄЩÏß³ÌÕýÔÚÔËÐУ¬²¢Ê¹ÓÃKILL thread_idÓï¾äÖÕÖ¹Ò»¸öÏ̡߳£
Twqkd8[ KILLÔÊÐí×ÔÑ¡µÄCONNECTION»òQUERYÐ޸ķû£º
?naPti1GX ¡¤ KILL CONNECTIONÓë²»º¬Ð޸ķûµÄKILLÒ»Ñù£ºËü»áÖÕÖ¹Óë¸ø¶¨µÄthread_idÓйصÄÁ¬½Ó¡£
Hgu:*iYA ¡¤ KILL QUERY»áÖÕÖ¹Á¬½Óµ±Ç°ÕýÔÚÖ´ÐеÄÓï¾ä£¬µ«Êǻᱣ³ÖÁ¬½ÓµÄÔ×´¡£
$xx5+A%, Èç¹ûÄúÓµÓÐPROCESSȨÏÞ£¬ÔòÄú¿ÉÒԲ鿴ËùÓÐÏ̡߳£Èç¹ûÄúÓµÓÐSUPERȨÏÞ£¬Äú¿ÉÒÔÖÕÖ¹ËùÓÐÏ̺߳ÍÓï¾ä¡£·ñÔò£¬ÄúÖ»Äܲ鿴ºÍÖÕÖ¹Äú×Ô¼ºµÄÏ̺߳ÍÓï¾ä¡£
~vf&JH'! ÄúÒ²¿ÉÒÔʹÓÃmysqladmin processlistºÍmysqladmin killÃüÁîÀ´¼ì²éºÍÖÕÖ¹Ï̡߳£
Eu%19s;u ×¢ÊÍ£ºÄú²»ÄÜͬʱʹÓÃKILLºÍEmbedded MySQL Server¿â£¬ÒòΪÄÚÖ²µÄ·þÎñÆ÷Ö»ÔËÐÐÖ÷»úÓ¦ÓóÌÐòµÄÏ̡߳£Ëü²»ÄÜ´´½¨ÈκÎ×ÔÉíµÄÁ¬½ÓÏ̡߳£
`D2wlyqO6 µ±Äú½øÐÐÒ»¸öKILLʱ£¬¶ÔÏß³ÌÉèÖÃÒ»¸öÌØÓеÄÖÕÖ¹±ê¼Ç¡£ÔÚ¶àÊýÇé¿öÏ£¬Ïß³ÌÖÕÖ¹¿ÉÄÜÒª»¨Ò»Ð©Ê±¼ä£¬ÕâÊÇÒòΪÖÕÖ¹±ê¼ÇÖ»»áÔÚÔÚÌض¨µÄ¼ä¸ô±»¼ì²é£º
9a.r(W[9 ¡¤ ÔÚSELECT, ORDER BYºÍGROUP BYÑ»·ÖУ¬ÔÚ¶ÁÈ¡Ò»×éÐкó¼ì²é±ê¼Ç¡£Èç¹ûÉèÖÃÁËÖÕÖ¹±ê¼Ç£¬Ôò¸ÃÓï¾ä±»·ÅÆú¡£
*7L*:g ¡¤ ÔÚALTER TABLE¹ý³ÌÖУ¬ÔÚÿ×éÐдÓÔÀ´µÄ±íÖỶÁÈ¡Ç°£¬¼ì²éÖÕÖ¹±ê¼Ç¡£Èç¹ûÉèÖÃÁËÖÕÖ¹±ê¼Ç£¬ÔòÓï¾ä±»·ÅÆú£¬ÁÙʱ±í±»É¾³ý¡£
}h+_kRQ ¡¤ ÔÚUPDATE»òDELETEÔËÐÐÆڼ䣬ÔÚÿ¸ö×é¶ÁÈ¡Ö®ºóÒÔ¼°Ã¿¸öÒѸüÐлòÒÑɾ³ýµÄÐÐÖ®ºó£¬¼ì²éÖÕÖ¹±ê¼Ç¡£Èç¹ûÖÕÖ¹±ê¼Ç±»ÉèÖã¬Ôò¸ÃÓï¾ä±»·ÅÆú¡£×¢Ò⣬Èç¹ûÄúÕýÔÚʹÓÃÊÂÎñ£¬Ôò±ä¸ü²»»á±» »Ø¹ö¡£
F[aow$",+} ¡¤ GET_LOCK()»á·ÅÆúºÍ·µ»ØNULL¡£
iEDZ\\, ¡¤ INSERT DELAYEDÏ̻߳á¿ìËÙµØˢУ¨²åÈ룩ËüÔÚ´æ´¢Æ÷ÖеÄËùÓеÄÐУ¬È»ºóÖÕÖ¹¡£
WZ.d"EE" ¡¤ Èç¹ûÏß³ÌÔÚ±íËø¶¨¹ÜÀí³ÌÐòÖУ¨×´Ì¬£ºËø¶¨£©£¬Ôò±íËø¶¨±»¿ìËٵطÅÆú¡£
V<} ^n ¡¤ Èç¹ûÔÚдÈëµ÷ÓÃÖУ¬Ïß³ÌÕýÔڵȴý¿ÕÏеĴÅÅ̿ռ䣬ÔòдÈë±»·ÅÆú£¬²¢°éËæ"disk full"´íÎóÏûÏ¢¡£
,gU%%>-_~w ¡¤ ¾¯¸æ£º¶ÔMyISAM±íÖÕÖ¹Ò»¸öREPAIR TABLE»òOPTIMIZE TABLE²Ù×÷»áµ¼Ö³öÏÖÒ»¸ö±»Ë𻵵ÄûÓÐÓÃµÄ±í¡£¶ÔÕâÑùµÄ±íµÄÈκζÁÈ¡»òдÈ붼»áʧ°Ü£¬Ö±µ½ÄúÔÙ´ÎÓÅ»¯»òÐÞ¸´Ëü£¨²»Öжϣ©¡£
4SG22$7 W FV[6">;g 1¡¢Í¨¹ýinformation_schema.processlist±íÖеÄÁ¬½ÓÐÅÏ¢Éú³ÉÐèÒª´¦ÀíµôµÄMySQLÁ¬½ÓµÄÓï¾äÁÙʱÎļþ£¬È»ºóÖ´ÐÐÁÙʱÎļþÖÐÉú³ÉµÄÖ¸Áî
IBwquw+ ¸´ÖÆ´úÂë
(wJtEoB9^ mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root';
kndP?#>
p1 +------------------------+
`1%SXP1 | concat('KILL ',id,';')
&`7~vA&c +------------------------+
nsyg>=j | KILL 3101;
>Kl78w: | KILL 2946;
;WIL?[;w +------------------------+
Q^Ln`zMe 2 rows in set (0.00 sec)
XEqg%f A>L(#lz#ek mysql>select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt';
oB[3?e Query OK, 2 rows affected (0.00 sec)
\h^bOxh FH8?W|
G mysql>source /tmp/a.txt;
DT&[W<oN Query OK, 0 rows affected (0.00 sec)
k?Jzy ¸´ÖÆ´úÂë
SX}GKu 2h&pm 2¡¢
kyJv,!}; ¡¡¡¡É±µôµ±Ç°ËùÓеÄMySQLÁ¬½Ó
{CH\TmSz mysqladmin -uroot -p processlist|awk -F "|" '{print $2}'|xargs -n 1 mysqladmin -uroot -p kill
SU#
S' ¡¡¡¡É±µôÖ¸¶¨Óû§ÔËÐеÄÁ¬½Ó£¬ÕâÀïΪMike
oW]&]*>J mysqladmin -uroot -p processlist|awk -F "|" '{if($3 == "Mike")print $2}'|xargs -n 1 mysqladmin -uroot -p kill
af{;4Cr ¡¡
gb(\c:yg1R 3¡¢Í¨¹ýSHEL½Å±¾ÊµÏÖ
(I.uQP~H #ɱµôËø¶¨µÄMySQLÁ¬½Ó
<O$'3_S"D for id in `mysqladmin processlist|grep -i locked|awk '{print $1}'`
wX 41R]pF do
mKpUEJ<a mysqladmin kill ${id}
qUtVqS done
%n}.E304 aDZLabRu 4¡¢Í¨¹ýMaatkit¹¤¾ß¼¯ÖÐÌṩµÄmk-killÃüÁî½øÐÐ
=r0!-[XCa #ɱµô³¬¹ý60ÃëµÄsql
M~662]Ekk mk-kill -busy-time 60 -kill
!y] Y'j #Èç¹ûÄãÏëÏȲ»É±£¬ÏÈ¿´¿´ÓÐÄÄЩsqlÔËÐг¬¹ý60Ãë
#gN{8Yk> mk-kill -busy-time 60 -print
W>@%d`>o5 #Èç¹ûÄãÏëɱµô£¬Í¬Ê±Êä³öɱµôÁËÄÄЩ½ø³Ì
M2xUs mk-kill -busy-time 60 -print ¨Ckill
}4
$EN ¡¡¡¡mk-kill¸ü¶àÓ÷¨¿É²Î¿¼£º
r kD4}jV ¡¡¡¡
http://www.maatkit.org/doc/mk-kill.html *Mp<4B ¡¡¡¡
http://www.sbear.cn/archives/426 V)(pe #P ¡¡¡¡Maatkit¹¤¾ß¼¯µÄÆäËüÓ÷¨¿É²Î¿¼£º
gdG:
&{|x ¡¡¡¡
http://code.google.com/p/maatkit/wiki/TableOfContents?tm=6 ^cAJCbp7 ¡¡¡¡²Î¿¼Îĵµ£º
mmjB1L ¡¡¡¡
http://www.google.com S i>TG
¡¡¡¡
http://www.orczhou.com/index.PHP/2010/10/kill-mysql-connectio-in-batch/ rRt<kTk!U ¡¡¡¡
http://www.mysqlperformanceblog.com/2009/05/21/mass-killing-of-mysql-connections/