현재 접속중인 클라이언트(자식 쓰레드)를 KILL문이나 mysqladmin kill 을 사용해서 강제종료시킬 수 있다.
기동중에 있는 자식 쓰레드를 확인하기 위해서는 다음과 같은 명령어를 사용할 수 있다.
SHOW PROCESSLIST
또는
SELECT * FROM information_schema.PROCESSLIST
또는
mysqladmin processlist
실행하기 위해서는 PROCESS권한이 필요한다.
실행결과에 표시되는 내용은 다음과 같다.
- Id: 쓰레드 ID이다. MySQL서버가 작성한 클라이언트용 쓰레드에 고유 번호가 할당된다. KILL문을 사용해서 쓰레드를 죽일 경우에 이 번호를 지정한다.
- User, Host: 접속유저, 클라이언트 호스트를 나타낸다.
- db:현재 데이터베이스를 나타낸다.
- Command:실행되고 있는 명령어 종류를 나타낸다. 다음과 같은 것이 있다. Binlog Dump, Change user, Close stmt, Connect Connect Out, Create DB, Daemon, Debug, Delayed insert, Drop DB, End, Error, Execute, Fetch, Field List, Init DB, Kill, Long Data, Ping, Prepare, Processlist, Query, Quit, Refresh, Register Slave, Reset stmt, Set option, Shutdown, Sleep, Statistics, Table Dump, Time
- Time: 명령어의 실행시간(sec.)이다.
- State:상태의 설명이다. 다음과 같은 것이 있다. After create, allocating local table, Analyzing, Changing master, Checking master version, Checking table, cleansing up, closing tables, Connecting to master, converting HEAP to MyISAM, copy to tmp table, Copying to group table, Copying to tmp table, Copying to tmp table on disk, Creating delayed handler, Creating sort index, creating table, Creating table from master dump, Creating tmp table, deleting from main table, deleting from reference tables, discard_or_import_tablespace, Execution of init_command, Finished reading one binlog; switching to next binlog, Flushing tables, freeing items, FULLTEXT initialization, got handler lock, got old table, Has read all relay log; waiting for the slave I/O thread to update it, Has sent all binlog to slave; waiting for binlog to be updated, init, insert, Killed, Killing slave, Locked, logging slow query, login, Making temp file, Opening master dump table, Opening table, Opening tables, preparing, Purging old relay logs, query end, Queueing master event to the relay log, Reading event from the relay log, Reading from net, Reading master dump table data, Rebuilding the index on master dump table, Reconnecting after a failed binlog dump request, Reconnecting after a failed master event read, Registering slave on master, Removing duplicates, removing tmp table, rename, rename result table, Reopen tables, Repair by sorting, Repair with keycache, Requesting binlog dump, reschedule, Searching rows for update, Sending binlog event to slave, Sending data, setup, Sorting for group, Sorting for order, Sorting result, starting slave, statistics, storing row into queue, System lock, Table lock, unauthenticated user, update, Updating, updating main table, updating reference tables, upgrading lock, User lock, waiting for delay_list, Waiting for event from ndbcluster, waiting for handler insert, waiting for handler lock, waiting for handler open, Waiting for INSERT, Waiting master to send event, Waiting for master update, Waiting for the slave SQL thread to free enough relay log space, Waiting for slave mutex on exit, Waiting for table, Waiting for tables, Waiting for the next event in relay log, Waiting on cond, Waiting to finalize termination, Waiting to reconnect after a failed binlog dump request, Waiting to reconnect after a failed master event read, Writing to net
- Info: 쓰레드가 실행되고 있는 문장을 나타낸다. NULL인 경우는 문장을 실행하고 있지 않다는 뜻이다.
