MySQL에서 수행 중인 Process를 확인하는 방법
1. mysql 명령어 실행 상태에서 show processlist를 수행하면 된다.
mysql> show processlist ;
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------+
| 11 | repl | Smysql:41648 | NULL | Binlog Dump | 14706 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 16 | root | localhost | mysql | Query | 0 | starting | show processlist |
| 21 | root | 192.168.120.31:52616 | NULL | Sleep | 195 | | NULL |
| 22 | root | 192.168.120.31:52617 | NULL | Sleep | 195 | | NULL |
| 33 | root | 192.168.122.7:35439 | information_schema | Sleep | 0 | | NULL |
| 34 | root | 192.168.122.7:35440 | information_schema | Sleep | 5 | | NULL |
| 35 | root | 192.168.122.7:35442 | information_schema | Sleep | 0 | | NULL |
| 36 | root | 192.168.122.7:35446 | information_schema | Sleep | 0 | | NULL |
| 37 | root | 192.168.122.7:35450 | information_schema | Sleep | 0 | | NULL |
| 38 | root | 192.168.122.7:35453 | information_schema | Sleep | 0 | | NULL |
| 39 | root | 192.168.122.7:35455 | information_schema | Sleep | 5 | | NULL |
| 40 | root | 192.168.122.7:35460 | information_schema | Sleep | 5 | | NULL |
| 44 | root | 192.168.10.1:50685 | tpcc | Query | 0 | NULL | COMMIT |
| 45 | root | 192.168.10.1:50686 | tpcc | Sleep | 0 | | NULL |
| 46 | root | 192.168.10.1:50687 | tpcc | Sleep | 0 | | NULL |
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------+
15 rows in set (0.00 sec)
2. mysql 명령어 실행 상태에서 INFORMATION_SCHEMA.PROCESSLIST 뷰를 조회 한다.
mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep';
+----+------+--------------------+-------+-------------+-------+---------------------------------------------------------------+-----------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+------+--------------------+-------+-------------+-------+---------------------------------------------------------------+-----------------------------------------------------------------------+
| 11 | repl | Smysql:41648 | NULL | Binlog Dump | 15479 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 46 | root | 192.168.10.1:50687 | tpcc | Query | 0 | closing tables | COMMIT |
| 58 | root | localhost | mysql | Query | 0 | executing | SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep' |
+----+------+--------------------+-------+-------------+-------+---------------------------------------------------------------+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)
3. 일정한 간격을 가지고 모니터링을 하고 싶다면 ???
[root@mmysql ~]# mysqladmin -u root -p -i 2 processlist 명령을 사용하면 된다.
2초마다 processlist를 수행하라는 명령어 이다.
[root@mmysql ~]# mysqladmin -u root -p -i 2 processlist
Enter password:
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------+
| 11 | repl | Smysql:41648 | | Binlog Dump | 15041 | Master has sent all binlog to slave; waiting for more updates | |
| 21 | root | 192.168.120.31:52616 | | Sleep | 530 | | |
| 22 | root | 192.168.120.31:52617 | | Sleep | 530 | | |
| 33 | root | 192.168.122.7:35439 | information_schema | Sleep | 0 | | |
| 34 | root | 192.168.122.7:35440 | information_schema | Sleep | 5 | | |
| 35 | root | 192.168.122.7:35442 | information_schema | Sleep | 0 | | |
| 36 | root | 192.168.122.7:35446 | information_schema | Sleep | 0 | | |
| 37 | root | 192.168.122.7:35450 | information_schema | Sleep | 0 | | |
| 38 | root | 192.168.122.7:35453 | information_schema | Sleep | 5 | | |
| 39 | root | 192.168.122.7:35455 | information_schema | Sleep | 5 | | |
| 40 | root | 192.168.122.7:35460 | information_schema | Sleep | 0 | | |
| 44 | root | 192.168.10.1:50685 | tpcc | Query | 0 | closing tables | COMMIT |
| 45 | root | 192.168.10.1:50686 | tpcc | Query | 0 | | COMMIT |
| 46 | root | 192.168.10.1:50687 | tpcc | Query | 0 | closing tables | COMMIT |
| 51 | root | localhost | | Query | 0 | starting | show processlist |
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------+
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------+
| 11 | repl | Smysql:41648 | | Binlog Dump | 15043 | Master has sent all binlog to slave; waiting for more updates | |
| 21 | root | 192.168.120.31:52616 | | Sleep | 532 | | |
| 22 | root | 192.168.120.31:52617 | | Sleep | 532 | | |
| 33 | root | 192.168.122.7:35439 | information_schema | Sleep | 2 | | |
| 34 | root | 192.168.122.7:35440 | information_schema | Sleep | 7 | | |
| 35 | root | 192.168.122.7:35442 | information_schema | Sleep | 2 | | |
| 36 | root | 192.168.122.7:35446 | information_schema | Sleep | 2 | | |
| 37 | root | 192.168.122.7:35450 | information_schema | Sleep | 2 | | |
| 38 | root | 192.168.122.7:35453 | information_schema | Sleep | 7 | | |
| 39 | root | 192.168.122.7:35455 | information_schema | Sleep | 7 | | |
| 40 | root | 192.168.122.7:35460 | information_schema | Sleep | 2 | | |
| 44 | root | 192.168.10.1:50685 | tpcc | Query | 0 | closing tables | COMMIT |
| 45 | root | 192.168.10.1:50686 | tpcc | Query | 0 | | COMMIT |
| 46 | root | 192.168.10.1:50687 | tpcc | Query | 0 | | COMMIT |
| 51 | root | localhost | | Query | 0 | starting | show processlist |
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------+
사람의 욕심이 끝이 없는지라 ….
실제 일을 하는 Process만 보고 싶다면 … (Commnad 에 Sleep이 아닌 것만 보고자 하는 의도 임)
[root@mmysql ~]# mysqladmin -u root -p -i 2 processlist | grep -v Sleep
Enter password:
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------+
| 11 | repl | Smysql:41648 | | Binlog Dump | 15227 | Master has sent all binlog to slave; waiting for more updates | |
| 45 | root | 192.168.10.1:50686 | tpcc | Query | 0 | | COMMIT |
| 46 | root | 192.168.10.1:50687 | tpcc | Query | 0 | closing tables | COMMIT |
| 54 | root | localhost | | Query | 0 | starting | show processlist |
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+------------------+
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+---------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+---------------------------------------------------------------------+
| 11 | repl | Smysql:41648 | | Binlog Dump | 15229 | Master has sent all binlog to slave; waiting for more updates | |
| 44 | root | 192.168.10.1:50685 | tpcc | Query | 0 | closing tables | COMMIT |
| 45 | root | 192.168.10.1:50686 | tpcc | Query | 0 | updating | UPDATE warehouse SET w_ytd = w_ytd + p_h_amount
WHERE w_id = p_w_id |
| 46 | root | 192.168.10.1:50687 | tpcc | Query | 0 | closing tables | COMMIT |
| 54 | root | localhost | | Query | 0 | starting | show processlist |
+----+------+----------------------+--------------------+-------------+-------+---------------------------------------------------------------+---------------------------------------------------------------------+
4. 특정 세션(프로세스)를 Kill 하고 싶다면 ?
Kill Id 또는 call mysql.rds_kill(id) 명령을 사용.
mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep';
+----+------+--------------------+-------+-------------+-------+---------------------------------------------------------------+-----------------------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+------+--------------------+-------+-------------+-------+---------------------------------------------------------------+-----------------------------------------------------------------------+
| 11 | repl | Smysql:41648 | NULL | Binlog Dump | 15479 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 46 | root | 192.168.10.1:50687 | tpcc | Query | 0 | closing tables | COMMIT |
| 58 | root | localhost | mysql | Query | 0 | executing | SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep' |
+----+------+--------------------+-------+-------------+-------+---------------------------------------------------------------+-----------------------------------------------------------------------+
mysql> kill 58 ;
mysql>call mysql.rds_kill(58) ;
또는
mysqladmin -u root -p kill 58