인커밍(incoming) MySQL 커넥션을 테이블에…

- 원문은 http://www.futhark.ch/mysql/143.html 에 있음

MySQL 5.1에서는 일반적인 쿼리 로그를 테이블에 저장할 수가 있다. 하지만 만약에 모든 단일 명령문을 로그하는 대신에 단지 로깅을 하는 사용자 및 접속을 하는 호스트만 로그 하고자 할 경우에는 어떻게 해야 하는가? 이번 컬럼에서는 위의 문제를 간단하게 해결할 수 있는 방법을 설명하기로 한다. 이 해법은 MySQL 5.0에서도 실행할 수 있으며, 약간의 변경만 하면 이전 버전에서도 실행 시킬 수가 있다.

이에 대한 해결책은 바로 로깅 루틴을 설치하는 도구로 init_connect 서버 변수를 사용하는 것이다. init_connect 변수는 커넥션되는 각 클라이언트(SUPER 사용자는 제외)를 위해 실행되는 SQL 명령문을 가지고 있다.

우선, 커넥션 로그(connecting log)를 보관하기 위한 데이터 베이스와 테이블을 하나씩 생성한다. 새로운 데이터 베이스 sys를 아래와 같이 생성한다.

CREATE DATABASE IF NOT EXISTS sys;

CREATE TABLE sys.connection_log (
log_id INT UNSIGNED NOT NULL auto_increment,
login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
connection_id INT(10) NOT NULL,
user_id VARCHAR(255) NOT NULL,
PRIMARY KEY (log_id),
KEY (user_id)
);

로그 엔트리를 주문(order)하기 위해서는 auto_increment 컬럼이 필요한데, 그 이유는 TIMESTAMP만으로는 불충분하고 CONNECTION_ID()는 서버가 재 시작(restart)되면 리셋(reset)되어 버리기 때문이다.

다음으로는, 커넥션 정보를 로그에 기록하는 간단한 루틴을 설치한다. CREATE 명령어는 루트(root)로 실행하거나 또는 sys.connection_log 테이블에 INSERT 권한을 가진 사용자로 실행하는 것이 좋다.

delimiter //

CREATE PROCEDURE sys.init_connect()
SQL SECURITY DEFINER
MODIFIES SQL DATA DETERMINISTIC
BEGIN
INSERT INTO connection_log
(connection_id, user_id)
VALUES (CONNECTION_ID(), USER());
END //

delimiter ;

SQL SECURITY를 DEFINER에 설정함으로써, 일반 사용자로부터 오는 로깅 시스템의 모든 내부 정보는 숨길 수가 있게 된다. 우리가 원하는 것은 단지 모든 사용자에게 새로운 데이터 베이스 sys상에서의 글로벌 실행 권한만을 주는 것이다.

REPLACE INTO mysql.db
(Host, Db, User, Execute_priv)
VALUES ('%', 'sys', '', 'Y');

FLUSH PRIVILEGES;

로깅을 활성화 시키기 위해서는, 우리가 사용한 루틴을 init_connect 변수에 연결(hook)하기만 하면 된다:

SET GLOBAL init_connect = 'CALL sys.init_connect()';

만일 서버가 리부팅(rebooting)될 때에도 커넥션 로깅 정보가 남아 있도록 하고자 한다면, 이 설정 내용을 my.cnf 파일에 추가하면 된다:

[mysqld]
init_connect = 'CALL sys.init_connect()'

이와 같이 하면, 모든 커넥션(connection)은 로그가 된다.
실무에 많은 도움이 되시길……….