[MySQL] Routine과 Definer와의 관계 및 모니터링

MySQL에서 Function과 Procedure를 합쳐서 Routine이라고 부릅니다.

이 Routine들은 MySQL내에서 좀 특별히 취급됩니다. 아래 내용에서 좀더 자세하게 확인해 봅니다.

 

1. Definer

Routine은 생성될 때 Definer라는 특별한 내용이 추가가 됩니다. 예제를 통해 확인해 봅니다.

[예제]
보통 아래와 같이 프로시저를 만듭니다.
CREATE  PROCEDURE `sbtest`.`proc1`()
BEGIN
    select * from sbtest.sbtest1;
    select * from sbtest.sbtest2;
END

그리고 그 소스를 확인해 보면 다음과 같이 Definer라는 내용이 추가됩니다.
CREATE DEFINER=`jhlee`@`%` PROCEDURE `sbtest`.`proc1`()
BEGIN
    select * from sbtest.sbtest1;
    select * from sbtest.sbtest2;
END

이 Definer는 Routine이 생성될 그 시점에 로그인 IP와 User의 정보로 작성됩니다.

 

2. Routine 실행방법

Routine 실행방법은 Call Procedure명을 이용하여 호출합니다. 이때 특정 Routine이 definer는 user1@% 유저로 되어있다고 가정하고, user2@% 유저로 호출한다고 가정합니다. 프로시저명은 Proc1이라고 합니다. 이때 Routine이 호출되는 프로세스는 다음과 같습니다.

- user2유저에서 Call 명령어로 Procedure나 Function호출. [여기서는 Proc1]

- 해당 Routine의 Defner를 확인하여 해당 유저로 Routine을 실행합니다. 여기서는 user1입니다.

위에서 설명했듯이 Routine을 실행할 때 다른 user가 실행을 한다 하더라도 해당 Routine의 definfer의 유저가 Routine을 실행시킵니다.

이 얘기는 만약 Definer의 User가 삭제되게 된다면 그 Routine은 실행할 수가 없게 됩니다.

그리고 이 Routine을 실행하게 되면 error log에서 Routine을 실행할 수 없다는 에러가 떨어지게 됩니다.

이것은 Routine뿐만이 아니라 Event, Trigger, View등 definer가 붙는 모든 객체에 해당되게 됩니다.

 

3. Routine 모니터링

show processlist로도 확인이 간으하나 sys.session으로 보시면 좀더 정확한 모니터링이 가능합니다.

show processlist는 Routine안의 SQL이 실행이 되어도 state컬럼이 Sleep으로 나오게 됩니다.

그러나 sys.session으로 보게 되면 SQL이 전송중 이라고 표시가 되어 쿼리가 실행중임을 확인하기가 더 쉽습니다.