MySQL SQL 로깅하기

시스템 분석 과정에서 응용 프로그램이 DBMS에 요구하는 여러가지 SQL문장을 분석하는것은 데이터 흐름을 파악할 수 있는 매우 유용한 도구 입니다. 모든 SQL을 로그로 남기도록 DBMS에 지시만 하면 되기 때문에 어렵지도 않습니다. 특히 개별 응용에서 SQL 문장 뒤에 "Call trace"를 SQL 주석 형태로 남기게 되면 해당 SQL을 어떤 코드에서 호출했는지도 알 수 있기 때문에 더욱 더 분석 과정을 효율화 시킬 수 있습니다.

function _execute($sql)
{
    $sql = $this->_prep_query($sql);
    $trc = debug_backtrace();
    $rst = "";
    foreach($trc AS $idx => $ary) {
        if (array_key_exists('file', $ary) && array_key_exists('line', $ary))
            $rst .= $idx.":".$ary['file'].", ".$ary['line']."\n";
    }
    $result = @mysqli_query($this->conn_id, $sql." /* ".$rst."*/");
    return $result;
}

위의 코드는 PHP 프로젝트에서 공통으로 사용하는 DB 함수에 함수 호출 과정을 debug_backtrace()로 추출하여 파일과 라인을 SQL 끝에 주석으로 남기도록 한 것 입니다.

위의 그림은 DB 공통 함수를 수정해서 SQL을 수행한 함수를 간편하게 추적하도록 DB 로깅한 결과입니다.

이렇게 SQL 전체를 로깅하는 방법은 SQL 주석을 남기는 거나 SQL 문장만을 로깅하는 것이므로 프로그램 동작에는 아무런 영향이 없지만 실제 시스템 동작 과정에서는 시스템 성능에 나쁜 영향을 줄 수 있으므로 사용해서는 않되겠죠. 개발 과정이나 분석 과정, 또는 비상 상황에서 사용하면 되겠습니다.

이렇게 SQL 전체에 대해서 로그를 남기는 방법은 예전에는 my.ini에 [mysqld] 섹션에서 

log = sqllog.log

와 같이 설정하고 DBMS를 재가동 시키면 되었습니다. 그런데 최근 버전에서는 이렇게 설정하면 DBMS가 아예 동작하질 않더군요. 그래서 찾아낸 방법

SET GLOBAL general_log = 'ON'

질의 창에서 위와 같이 명령어를 내려주면 이 시점부터 바로 SQL 로깅을(General logging) 시작합니다. 물론 DBMS를 재가동하면 옵션을 자동  OFF되고 로그를 바로 중단 하려면 "SET GLOBAL general_log = 'OFF' 를 내려주면 됩니다.