본문 바로가기
서버구축 (WEB,DB)

MySQL Proxy 시작하기

by 날으는물고기 2011. 3. 23.

MySQL Proxy 시작하기

제공 : 한빛 네트워크
저자 : Giuseppe Maxia
역자 : 이정목
원문 : Getting Started with MySQL Proxy 

MySQL Proxy가 출시되면서 커뮤니티에 상당한 동요가 일고 있습니다. 그런데 여기엔 그만한 이유가 있습니다. 기능에 굶주려 있는 사람들에게 있어 MySQL Proxy는 MySQL 도구모음에 추가된 기능들 중에서 부정할 수 없는 가장 흥미로운 것이기 때문이죠. 

만약 마지막 문장 때문에 다소 당혹스럽다면 그것은 여러분이 MySQL Proxy의 부가가치를 아직 보지 못했기 때문인데, 걱정할 필요는 없습니다. 이 기사는 여러분에게 Proxy가 무엇을 할 수 있는지에 대해 느낄 수 있도록 하는 것을 목표로 삼고 있기 때문입니다. 

이제 Proxyland로의 굉장한 여행을 할 준비를 해주십시오. 

MySQL Proxy 개관 

MySQL Proxy는 하나 혹은 여러 대의 MySQL 클라이언트들과 한 대의 서버 사이에 위치해 있는 경량 바이너리 애플리케이션(lightweight binary application)입니다. 클라이언트들은 보통 인증정보를 가지고 서버에 접속하는 대신 Proxy에 접속하는데, Proxy는 클라이언트와 서버 사이에서 중간자 역할을 합니다. 

이러한 기본적인 형식에서는 Proxy는 단순히 재지정자(redirector)에 지나지 않습니다. Proxy는 클라이언트(질의문)로부터 비어있는 버킷(bucket)을 하나 받아 그것을 서버로 전달하고, 버킷에 데이터를 채운 다음 다시 클라이언트로 되돌려 줍니다. 

단지 이것이 전부라면 Proxy는 단순히 불필요한 오버헤드일 뿐입니다. 여기엔 제가 아직 말씀드리지 않은 것이 조금 있습니다. Proxy에는 루아 인터프리터(Lua Interpreter)가 내장되어 있습니다. 루아를 이용하면 여러분은 Proxy가 쿼리나 Result Set을 전달해 주기 전에 그것들을 가지고 무엇을 할 것인지를 정의할 수 있습니다. 

그림 1. MySQL Proxy는 쿼리와 Result Set을 변경할 수 있습니다. 

Proxy의 위력은 모두 자체적인 유연함에 있는데, 그러한 유연함은 루아 엔진에 의해 가능한 것입니다. 여러분은 쿼리가 서버로 전달되기 전에 가로챌 수 있으며, 그것들을 가지고 상상할 수 있는 모든 것들을 수행할 수 있습니다:
  • 변경하지 않은 채로 전달(기본값)
  • 철자 오류 수정(CRATE DATAABSE로 적혀있지는 않나?)
  • 필터링, 다시 말해 쿼리를 전체적으로 제거하기
  • 정책에 따른 쿼리 재작성(rewriting) (강력한 비밀번호 요구, 비어있는 내용 전송 금지)
  • 잊어버린 문장 추가(autocommit이 활성화되어 있는 상태에서 사용자가 BEGIN WORK를 보내지는 않았나? 이러한 경우 여러분은 문장 앞에 SET AUTOCOMMIT = 0를 삽입할 수 있습니다)
  • 그 이상의 것들: 만약 여러분이 생각할 수만 있다면 그것은 아마도 이미 가능할 것입니다. 만약 그렇지 않다면 블로그에 올리세요. 누군가가 그것을 만들 수도 있으니까요.
동일한 방법으로 여러분은 Result set을 가로챌 수 있으며, 따라서 여러분은 다음과 같은 것들을 할 수 있습니다:
  • 쿼리 결과에 대한 레코드 제거, 변경, 추가. 비밀번호를 가리고 싶거나, 인증되지 않은 채로 엿보는 시선으로부터 정보를 숨기고 싶지는 않습니까?
  • 컬럼명을 포함하여 여러분만의 Result Set을 만드십시오. 예를 들면 여러분은 사용자가 새로운 SQL 명령을 입력할 수 있게 해놓았다면 여러분은 요청된 result set을 만들어 보여줄 수 있습니다.
  • Result set 무시하기. 즉 result set을 클라이언트로 되돌려 주지 않습니다.
  • 그 이상의 것들을 하길 원하십니까? 아마도 가능할겁니다. 예제를 살펴보시고 실험해 보십시오!
주요 개념 

MySQL Proxy는 객체 지향적인 기반구조를 토대로 만들어져 있습니다. 주요 클래스는 세 개의 멤버 함수를 외부에 노출합니다. 여러분은 그러한 함수들을 루아 스크립트에서 재정의하여 Proxy의 동작을 변경할 수 있습니다.
  • connect_server(): 연결시에 호출되며 여러분은 이 함수 내에서 작업하여 연결 파라미터를 수정할 수 있습니다. 또한 이 함수는 로드 밸런싱(load balancing)을 제공하는데 사용될 수 있습니다.
  • read_query(packet): 이 함수는 쿼리를 서버로 보내기 전에 호출됩니다. 여러분은 여기에서 원래의 쿼리를 변경하거나 무언가를 큐에 더 집어넣도록 중간에서 조작할 수 있습니다. 게다가 백엔드에 위치한 서버를 완전히 건너뛰고 여러분이 원하는 결과를 클라이언트에게 그대로 돌려보내줄 수도 있습니다(예를 들면 주어진 SELECT * FROM big_table의 경우 여러분은 다음과 같이 되물어볼 수도 있습니다. “big_table은 2천만개의 레코드를 가지고 있습니다. WHERE절을 잊으셨습니까?”)
  • read_query_result(inject_packet): 이 함수는 주입된 쿼리에 대한 응답으로 결과를 되돌려 보내기 전에 호출됩니다. 여러분은 여기에서 Result Set을 가지고 무엇을 할건지를 결정할 수 있습니다.
이러한 서버에 이르는 세 가지 통로들을 조합하여 여러분은 서버에 대한 고수준의 기동성을 달성할 수 있습니다. 

설치 

Proxy를 설치하는 것은 상당히 쉽습니다. 배포 패키지는 단지 하나의 바이너리만을(그리고 0.5.1버전을 비롯한 몇 가지 예제 루아 스크립트) 포함하고 있습니다. 여러분은 배포 패키지의 압축을 풀어 원하는 곳에 복사할 수 있으며 몇몇 운영체제의 경우에는 설치하기가 훨씬 더 쉬운데 왜냐하면 모든 것들을 알아서 해주는 RPM 패키지도 있기 때문입니다. 

만약 여러분의 운영체제가 배포 대상에 포함되어 있지 않거나 아니면 갓 만들어진 최신 기능을 사용해 보고 싶다면 공개 서브버전 트리(public Subversion tree)에서 직접 소스코드를 구해 Proxy를 빌드할 수도 있습니다. 그렇게 하려면 단지 몇 가지 기초적인 작업만 수행하면 됩니다.
 ./autogen.sh
 ./configure && make
 sudo make install 
 # will copy the executable to /usr/local/sbin
간단한 쿼리 가로채기(Simple Query Interception) 

첫 번째 예제로, 일종의 “내가 거기에 있었다”와 같은 여러분이 있고자 하는 곳에 서 있는 듯한 느낌을 주는 동작을 만들어 보도록 합시다.
  1. first_example.lua라는 이름의 Lua 파일을 하나 만들고 다음에 나열되어 있는 코드를 입력합니다.
  2. 여러분의 데이터베이스 서버가 동일한 머신에 있다고 가정하고 프록시 서버를 실행합니다.
  3. 별도의 콘솔에서 일반 서버에 접속하는 것처럼 프록시 서버에 접속하는데, 유일한 차이점은 3306 포트 대신 4040 포트를 사용할 것이라는 점입니다.
 -- first_example.lua 
 function read_query(packet)
   if string.byte(packet) == proxy.COM_QUERY then
     print("Hello world! Seen the query: " .. string.sub(packet, 2))
   end
 end
# starting the proxy
$ mysql-proxy --proxy-lua-script=first_example.lua -D                          
# from another console, accessing the proxy
$ mysql -u USERNAME -pPASSWORD -h 127.0.0.1 -P 4040 -e 'SHOW TABLES FROM test'
여러분이 이전의 터미널 창으로 되돌아 오게 되면 여러분은 Proxy가 여러분을 위해 무언가를 가로챘음을 볼 수 있을 것입니다.
Hello world! Seen the query: select @@version_comment limit 1
Hello world! Seen the query: SHOW TABLES FROM test
첫 번째 쿼리가 MySQL 클라이언트가 접속할 때 전송되었습니다. 두 번째 것은 여러분이 보낸 것입니다. 여러분도 볼 수 있겠지만 여러분은 중간에 끼어들 수 있으며, Proxy가 여러분을 위해 무언가를 하게 만들 수 있습니다. 지금은 이러한 점이 매우 사소한 것에 불과하지만, 다음 문단에서는 좀 더 흥미로운 것을 살펴볼 것입니다. 

사용상 주의할 점 

0.5.0 버전까지는 루아 스크립트를 사용하려면 –proxy-profiling 옵션을 사용해야 할 필요가 있는데, 그렇지 않을 경우 read_query와 read_query_result 함수가 작동하지 않습니다. 0.5.1 버전부터는 이 옵션이 더 이상 필요하지 않게 되었습니다. 위에서 언급한 함수가 기본값으로 활성화되는 대신 그러한 사용법을 생략하기 위해 새로운 옵션이 도입되었습니다. 여러분이 프록시를 로드 밸런싱에만 사용하고 있을 경우 지금은 -proxy-skip-profiling 옵션을 지정해야 합니다. 

쿼리 재작성(Query Rewriting) 

좀 더 흥미로운 것을 쿼리 재작성으로 시작해 보도록 합시다. 이 기능을 보여주기 위해 실용적인 작업을 골라보도록 하죠. 우리는 일반적인 입력 오류가 포함되어 있는 쿼리를 받아서 그것을 올바른 키워드로 수정해서 교체하려고 합니다. 가장 자주 손가락이 꼬인 상태에서 입력하는 문장을 SLECT와 CRATE로 간주할 것입니다. 

다음은 second_example.lua입니다.
 function read_query( packet )
   if string.byte(packet) == proxy.COM_QUERY then
     local query = string.sub(packet, 2)
     print ("received " .. query)
     local replacing = false
     -- matches "CRATE" as first word of the query
     if string.match(string.upper(query), '^%s*CRATE') then
         query = string.gsub(query,'^%s*%w+', 'CREATE')
         replacing = true
     -- matches "SLECT" as first word of the query
     elseif string.match(string.upper(query), '^%s*SLECT') then
         query = string.gsub(query,'^%s*%w+', 'SELECT')
         replacing = true
     end
     if (replacing) then
         print("replaced with " .. query )
         proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query )
         return proxy.PROXY_SEND_QUERY
     end
   end
 end
시작하기 전에 –proxy-lua-script=second_example.lua 옵션을 주어 서버를 시작하고 다음과 같이 MySQL 클라이언트에서 접속합니다.
 $ mysql -u USERNAME -pPASSWORD -h 127.0.0.1 -P 4040 
 Welcome to the MySQL monitor.  Commands end with ; or g.
 Your MySQL connection id is 48
 Server version: 5.0.37-log MySQL Community Server (GPL)

 Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

 mysql> use test
 Database changed
 mysql> CRATE TABLE t1 (id int);         # Notice: TYPO!
 Query OK, 0 rows affected (0.04 sec)

 mysql> INSERT INTO t1 VALUES (1), (2);
 Query OK, 2 rows affected (0.01 sec)
 Records: 2  Duplicates: 0  Warnings: 0

 mysql> SLECT * FROM t1;                 # Notice: TYPO!
 +------+
 | id   |
 +------+
 |    1 | 
 |    2 | 
 +------+
 2 rows in set (0.00 sec)
멋지지 않나요? 제가 일상적인 실수들을 저질렀지만, Proxy는 그것들을 수정해줄 만큼 친절합니다. 어떤 내용들이 보고되었는지 살펴봅시다.
 received select @@version_comment limit 1
 received SELECT DATABASE()
 received CRATE TABLE t1 (id int)
 replaced with CREATE TABLE t1 (id int)
 received INSERT INTO t1 VALUES (1), (2)
 received SLECT * FROM t1
 replaced with SELECT * FROM t1
처음의 두 쿼리는 클라이언트가 목적하는 바를 이루기 위해 필요한 것들입니다. 그 다음에는 저의 첫 번째 실수인 CRATE인데, 우아하게CREATE로 수정되었고, 그리고 마지막에는 SLECT를 받았지만 SELECT로 바뀌었습니다. 이 스크립트가 매우 조잡하긴 하지만 여러분에게 가능성에 대한 아이디어는 주었을 것입니다. 

쿼리 주입(Query Injection) 

다음으로는 MySQL Proxy의 특별한 기능 중 하나인 쿼리 주입(query injection)을 이용해 보도록 합시다. 쿼리 주입은 MySQL Proxy의 고유한 기능입니다. 쿼리 주입이 필요할 경우 쿼리의 큐를 생성할 수 있으며 각각의 쿼리에 ID 코드를 할당한 후에 그것들을 서버로 보낼 수 있습니다. 

그림 2. 쿼리 주입(Query Injection) 

이미지에는 서버가 세 개의 쿼리를 전달받는데, 물론 서버는 세 개의 Result Set을 되돌려 줍니다. 쿼리 주입이 일어날 때 Result Set은 다른 함수인 read_query_result에 의해 처리되는데, 이 함수에서 여러분은 각각의 ID에 따라 Result Set을 처리할 수 있습니다. 예제에서는 ID 2와 3에 대해 여러분은 SHOW STATUS로부터 반환되는 것을 받아 그것들의 값을 비교하여 메인 쿼리가 서버에 미치는 영향을 측정할 수 있습니다. 여러분은 SHOW STATUS 값을 내부 연산에 대해서만 사용하기 때문에 그러한 Result Set을 클라이언트에 보내지는 않으며(단순히 이렇게 해도 괜찮은 것은 클라이언트가 그것을 전달받지 않는 것으로 예상하고 있기 때문입니다), 그냥 버리면 됩니다. 

그림 3. 주입된 쿼리 처리 

클라이언트에 의해 전달된 쿼리의 Result Set은 알맞게 반환됩니다. 이는 클라이언트에 대해서 투명하며, 틈틈이 여러분은 프록시 콘솔에 보여지는 통계적인 수치를 수집할 수도 있습니다. 완전한 예제를 보려면 Forge에 있는 쿼리 주입 튜토리얼(the query injection tutorial)을 참고하십시오. 

매크로(Macro) 

매크로는 단지 쿼리 재작성 기능을 사용하는 또 다른 방법일 뿐입니다. 매크로는 프록시를 사용하는 방법에 있어 가장 인상적인 것 중의 하나입니다. 여러분은 SQL 언어를 재작성하거나 혹은 좀 더 여러분의 구미에 맞게 만들 수 있습니다. 예를 들면 MySQL 명령행 클라이언트를 사용하는 사람들은 use와 show tables를 사용하는 대신 cd와 ls를 입력하곤 합니다. MySQL Proxy를 이용하면 cd와 ls를 이용하여 기대하는 결과를 얻을 수 있습니다. 이러한 쓸만한 매크로 생성 및 사용에 관한 예제는 이전 블로그 포스팅에서 찾아볼 수 있습니다. 여기서 그것들을 모두 반복하는 것 보다는 여러분이 직접 your first macros with MySQL Proxy를 방문하여 살펴보시길 바랍니다. 

Result Set 생성: MySQL 쉘 명령어 

Proxy는 클라이언트로부터 요청을 받아 Result Set을 클라이언트에게 되돌려 주어야 합니다. 대부분 이렇게 하는 것은 매우 쉽습니다. 쿼리를 서버로 전달하고, Result Set을 가져오고, Result Set을 클라이언트에게 전달하면 됩니다. 그렇지만 서버가 제공할 수 없는 무언가를 되돌려 주어야 할 필요가 있다면 어떻게 해야 될까요? 그럴 경우 우리는 컬럼명과 데이터의 이차원 배열로 구성되어 있는 Result Set을 만들 필요가 있을 것입니다. 

데이터셋(Dataset) 생성 기초 

예를 들어 비추천 기능에 관한 경고를 반환하기를 원할 경우 저는 Result Set을 아래와 같이 만들 수 있습니다:
 proxy.response.resultset = {
     fields = {
         { 
            type = proxy.MYSQL_TYPE_STRING, 
            name = "deprecated feature", 
         },
         { 
            type = proxy.MYSQL_TYPE_STRING, 
            name = "suggested replacement", 
         },
     },
     rows = {
          { 
             "SHOW DATABASES", 
             "SHOW SCHEMAS" 
          }
     }
 }
 --
그리고 나서 이것을 클라이언트에게 보냅니다.
return proxy.PROXY_SEND_RESULT
위의 구조를 클라이언트가 받게 되면 다음과 같이 보여질 것입니다:
+---------------------+-----------------------+
| deprecated feature  | suggested replacement |
+---------------------+-----------------------+
| SHOW DATABASES      | SHOW SCHEMAS          |
+---------------------+-----------------------+
즉, 여러분의 필요에 맞는 모든 Result Set을 만들어 낼 수 있다는 얘깁니다. 좀 더 자세한 내용을 보려면 Jan Kneschke의 예제를 확인해 보십시오.

MySQL 클라이언트 쉘 명령어 

그럼 이번에는 완전히 다른 것을 위하여, 새롭게 받아들인 지식을 이용하여 Proxy를 통해 쉘 명령어를 수행하는 방법에 대해 알아보기로 합시다. 저는 이미 Proxy의 행위를 루아 스크립트를 통해 변경할 수 있다고 말씀드렸습니다. 그리고 루아는 하나의 완전한 언어인데, 이 말은 여러분이 루아를 가지고 쉘 명령어를 수행하는 것을 포함한 거의 모든 것들을 할 수 있다는 것을 의미합니다. 이러한 지식을 데이터셋을 생성하는 가능성과 결부시켜 보면 우리는 MySQL 로부터 쉘 명령어를 요청하여 Proxy로 하여금 쉘 명령어의 결과가 평범한 데이터베이스 레코드일 경우 그것을 반환하도록 하는 것을 생각해낼 수 있습니다. 

그림 4. Proxy를 통한 쉘 명령어 실행 

MySQL Forget에 있는 튜토리얼을 이용하여 직접 해보도록 합시다. 

쉘 튜토리얼은 쉘 명령어를 요청하는 간단한 문법을 구현하고 있습니다.
 SHELL command
예를 들면:
 SHELL ls -lh /usr/local/mysql/data
  1. 쉘 튜토리얼 스크립트를 가져와 shell.lua라는 이름으로 저장합니다.
  2. 프록시를 실행합니다.
  3. 프록시에 접속합니다.
$ /usr/local/sbin/mysql-proxy --proxy-lua-script=shell.lua -D

# from a different console
$ mysql -U USERNAME -pPASSWORD -h 127.0.0.1 -P 4040
데이터베이스 서버에 대한 일반 프록시로 작동함을 보장합니다.
 Welcome to the MySQL monitor.  Commands end with ; or g.
 Your MySQL connection id is 49
 Server version: 5.0.37-log MySQL Community Server (GPL)

 Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

 mysql> use test
 Database changed
 mysql> show tables;
 +----------------+
 | Tables_in_test |
 +----------------+
 | t1             |
 +----------------+
 1 row in set (0.00 sec)

 mysql> select * from t1;
 +------+
 | id   |
 +------+
 |    1 |
 |    2 |
 +------+
 2 rows in set (0.00 sec)
좋습니다. 일반적인 연산들이 예상대로 작동합니다. 이제 강화된 기능을 테스트 해보겠습니다.
 mysql> shell df -h;
 +--------------------------------------------------------+
 | df -h                                                  |
 +--------------------------------------------------------+
 | Filesystem            Size  Used Avail Use% Mounted on |
 | /dev/md1               15G  3.9G  9.7G  29% /          |
 | /dev/md4              452G  116G  313G  27% /app       |
 | tmpfs                 1.7G     0  1.7G   0% /dev/shm   |
 | /dev/md3              253G  159G   82G  67% /home      |
 | /dev/md0               15G  710M   13G   6% /var       |
 +--------------------------------------------------------+
 6 rows in set (0.00 sec)
반갑다 쉘! 이것은 정말로 고급 사용자에게는 큰 기쁨을 줄 것입니다. 여러분이 외부 명령어로 접근하는 수단만 취할 수 있다면 여러분은 완전히 창조적인 작업들을 해낼 수 있을 것입니다.
 mysql> shell grep key_buffer /usr/local/mysql/my.cnf;
 +-----------------------------------------+
 | grep key_buffer /usr/local/mysql/my.cnf |
 +-----------------------------------------+
 | key_buffer=2000M                        |
 +-----------------------------------------+
 1 row in set (0.00 sec)
저는 동일한 내용을 SHOW VARIABLES로 확인해 볼 수 있다는 것을 알고 있긴 하지만 이것은 온라인에서도 설정할 수 있는 값이므로 저는 그러한 내용이 설정 파일에도 들어있다는 것을 확인해보고 싶었습니다. 그러면 메모리 상태는 어떻게 되어 있을까요?
 mysql> shell free -m;
 +---------------------------------------------------------------------------+
 | free -m                                                                   |
 +---------------------------------------------------------------------------+
 |              total       used       free     shared    buffers     cached |
 | Mem:          3280       1720       1560          0          9       1006 |
 | -/+ buffers/cache:        704       2575                                  |
 | Swap:         8189          2       8186                                  |
 +---------------------------------------------------------------------------+
 4 rows in set (0.08 sec)
나쁘진 않군요. 이제 서버 상태를 볼 수 있어서 기쁜데, 한번 재미있는 걸 해볼까요? 예를 들면 Planet MySQL의 마지막 항목을 확인해 볼 수도 있습니다. 여러분께서는 제가 농담하는 거라 생각하세요? 전혀 아닙니다. 명령어는 꽤 길지만 분명히 작동합니다.
   wget -q -O - http://www.planetmysql.org/rss20.xml  
      | perl -nle 'print $1 if m{}' 
      |head -n 21 | tail -n 20;
그런데 나열되는 목록이 너무 길어 아무도 그것을 기억하지는 않을 것이므로 그것을 쉘 스크립트로 붙여넣은 다음 last_planet.sh와 같이 호출해야 할 것입니다. 그렇게 하는 방법이 아래에 있습니다!
 mysql> shell last_planet.sh;
 +-------------------------------------------------------------------------------------+
 | last_planet.sh                                                                      |
 +-------------------------------------------------------------------------------------+
 | Top 5 Wishes for MySQL                                                              |
 | Open Source ETL tools.                                                              |
 | MySQL Congratulates FSF on GPLv3                                                    |
 | Query cache is slow to the point of being unusable - what is being done about that. |
 | About 'semi-unicode' And 'quasi Moon Stone'                                         |
 | My top 5 MySQL wishes                                                               |
 | Four more open source startups to watch                                             |
 | More on queue... Possible Solution...                                               |
 | MySQL as universal server                                                           |
 | MySQL Proxy. Playing with the tutorials                                             |
 | Open source @ Oracle: Mike Olson speaks                                             |
 | Quick musing on the "Queue" engine.                                       |
 | Distributed business organization                                                   |
 | Ideas for a MySQL queuing storage engine                                            |
 | MySQL Test Creation Tool Design Change                                              |
 | Queue Engine, and why this won' likely happen...                                    |
 | What?s your disk I/O thoughtput?                                                    |
 | My 5+ Wish List?                                                                    |
 | Top 5 best MySql practices                                                          |
 | Packaging and Installing the MySQL Proxy with RPM                                   |
 +-------------------------------------------------------------------------------------+
 20 rows in set (1.48 sec)
쉘에 접근해서 MySQL 클라이언트로부터 웹 컨텐츠를 가져왔습니다! 

당부의 말씀 

앞에서 보여주었듯이 여러분은 MySQL 연결로부터 쉘에 접근할 수 있는데, 그렇다고 해서 이러한 사실이 그대로 여러분이 그렇게 해야 한다는 것을 의미하지는 않습니다. 쉘 접근은 보안상 취약점이며, 그리고 여러분이 이 기능을 여러분의 서버에서 사용하고 싶을지라도 내부적인 목적으로만 사용하십시오. 애플리케이션에 열려있는 일반 사용자가 쉘 접근을 할 수 있도록 하지 마십시오. 그렇게 할 경우 화를 자초할 수 있습니다(그리고 그런 문제는 정말로 빨리 나타납니다) 여러분은 쉘을 이용하여 무언가를 볼 수도 있는 동시에 그것을 지울 수도 있습니다.
 mysql> shell ls *.lua*;
 +---------------------+
 | ls *.lua*           |
 +---------------------+
 | first_example.lua   |
 | first_example.lua~  |
 | second_example.lua  |
 | second_example.lua~ |
 +---------------------+
 4 rows in set (0.03 sec)

 mysql> shell rm *~;
 Empty set (0.00 sec)

 mysql> shell ls *.lua*;
 +--------------------+
 | ls *.lua*          |
 +--------------------+
 | first_example.lua  |
 | second_example.lua |
 +--------------------+
 2 rows in set (0.01 sec)
쉘 접근시에는 매우 조심해야 합니다! 

Proxy를 통해 쉘에 접근할 때 Proxy가 실행되고 있는 호스트임을 명심하십시오. 만약 여러분이 Proxy를 동일한 호스트에 설치할 경우 데이터베이스와 동일한 곳에 위치할 것입니다만 그걸 당연한 걸로 생각하시면 안됩니다. 

로깅 커스터마이즈하기(Customized Logging) 

저는 이 예제를 가장 마지막에 두었는데 왜냐하면 제 경험상 로깅은 가장 흥미로우면서도 실무적이고, 또 즉각적으로 사용할 수 있는 것이기 때문입니다. Logs on demand는 MySQL 5.1에서 사용할 수 있긴 하지만 만약 여러분이 MySQL 5.0이라면 Proxy가 도움이 될 것입니다.

단순 로깅(Simple Logging) 

일반적인 로그처럼 보이는 쿼리 로깅을 활성화하는 것은 매우 쉽습니다. 다음의 코드를 simple_logs.lua 파일에 작성합니다(아니면 MySQL Forge의 snippet에서 다운로드 하십시오).
 local log_file = 'mysql.log'
 local fh = io.open(log_file, "a+")

 function read_query( packet )
   if string.byte(packet) == proxy.COM_QUERY then
     local query = string.sub(packet, 2)
     fh:write( string.format("%s %6d -- %s n", 
         os.date('%Y-%m-%d %H:%M:%S'), 
         proxy.connection["thread_id"], 
         query)) 
     fh:flush()
   end
 end
루아 파일을 작성한 다음 Proxy를 시작하고 동일한(concurrent) 세션으로부터 Proxy에 접속합니다. 이 스크립트는 모든 쿼리를 mysql.log라는 이름의 텍스트 파일로 로그로 남길 것입니다. 몇몇 세션이 활동한 뒤의 로그 파일은 다음과 같을 것입니다:
 2007-06-29 11:04:28     50 -- select @@version_comment limit 1 
 2007-06-29 11:04:31     50 -- SELECT DATABASE() 
 2007-06-29 11:04:35     51 -- select @@version_comment limit 1 
 2007-06-29 11:04:42     51 -- select USER() 
 2007-06-29 11:05:03     51 -- SELECT DATABASE() 
 2007-06-29 11:05:08     50 -- show tables 
 2007-06-29 11:05:22     50 -- select * from t1 
 2007-06-29 11:05:30     51 -- show databases 
 2007-06-29 11:05:30     51 -- show tables 
 2007-06-29 11:05:33     52 -- select count(*) from user 
 2007-06-29 11:05:39     51 -- select count(*) from columns
로그는 날짜, 시간, 연결 ID, 쿼리를 담고 있습니다. 이러한 짧은 스크립트는 단순하고 효과적입니다. 로그에는 세 개의 세션이 있는데 각 세션의 명령어는 세션별로 정렬되지는 않고 실행된 시간 순으로 정렬된다는 것을 명심하십시오. 

긍정적인 측면은 여러분이 일반 로그 기능을 활성화하기 위해 서버를 재시작하지 않아도 된다는 점입니다. 여러분이 해야할 것은 단지 여러분의 애플리케이션이 3306 포트 대신 4040 포트를 가리키도록 하게 하고, 간단하지만 기능적인 로깅을 활성하는 것뿐입니다. 생각해 보면 여러분은 애플리케이션 또한 수정하거나 재시작할 필요가 없습니다. 여러분은 동일한 결과를 서버나 애플리케이션을 손대지 않고도 얻을 수 있습니다. 단순히 서버가 위치해 있는 동일 머신상의 Proxy를 시작하는 것만으로도 iptable 규칙을 활성화하여 3306 포트로부터의 트래픽을 4040 포트로 재지정할 수 있게 됩니다(이 내용을 알려주신 Patrizio Tassone씨에게 감사드립니다).
sudo iptables -t nat -I PREROUTING 
   -s ! 127.0.0.1 -p tcp 
   --dport 3306 -j 
   REDIRECT --to-ports 4040

그림 5. 3306 포트의 트래픽을 4040포트로 재지정 

이제 여러분은 로깅을 활성화하였으며 서버를 재시작하거나 여러분의 애플리케이션을 건드릴 필요가 없습니다! 작업을 완료했고, 더 이상 로그가 필요하지 않다면 규칙을 제거하고(-I 대신 -D) 프록시를 종료하면 됩니다.
sudo iptables -t nat -D PREROUTING 
   -s ! 127.0.0.1 -p tcp 
   --dport 3306 -j 
   REDIRECT --to-ports 4040
로깅 커스터마이즈에 관해 좀 더 알아보기 

이전 섹션에서 알아본 간단하고 효과적인 로깅 스크립트에 혹하더라도 그것은 정말 기초적인 것에 불과합니다. 우리는 앞서 Proxy 내부를 잠깐이나마 살펴보았으며, 그리고 좀 더 풍부한 정보를 얻을 수 있음을 알아보았고, 그리고 이러한 로그들이 단순히 쿼리문의 목록보다는 훨씬 더 흥미롭습니다. 예를 들면, 전송한 쿼리가 성공했거나 문법 오류로 거부되었는지, 얼마만큼의 행을 전달받았는지, 얼마만큼의 행이 영향을 받았는지를 알고 싶습니다. 

우리는 이미 목적을 달성하기 위한 모든 것들을 알고 있습니다. 스크립트는 조금 더 길어지겠지만, 그리 많이 길어지지는 않을 것입니다.
 -- logs.lua
 assert(proxy.PROXY_VERSION >= 0x00600,
  "you need at least mysql-proxy 0.6.0 to run this module")

 local log_file = os.getenv("PROXY_LOG_FILE")
 if (log_file == nil) then
   log_file = "mysql.log"

 end

 local fh = io.open(log_file, "a+")
 local query = "";
스크립트의 대부분이 우리가 적절한 버전의 Proxy를 사용하고 있는지를 확인하는 것인데, 왜냐하면 0.5.0 버전에서는 사용할 수 없는 기능을 사용하고 있기 때문입니다. 그리고 나서 파일명을 지정하는데, 환경변수에서 가져오거나 아니면 기본값을 할당합니다.
 function read_query( packet )
   if string.byte(packet) == proxy.COM_QUERY then
     query = string.sub(packet, 2)
     proxy.queries:append(1, packet )
     return proxy.PROXY_SEND_QUERY
   else
       query = ""

   end
 end
첫 번째 함수는 하는 일이 적은데, 쿼리를 프록시 큐에 추가하여 다음 함수가 결과가 준비되었을 때 트리거되도록 합니다.
 function read_query_result (inj)
   local row_count = 0
   local res = assert(inj.resultset)
   local num_cols = string.byte(res.raw, 1)
   if num_cols > 0 and num_cols < 255 then
     for row in inj.resultset.rows do
       row_count = row_count + 1
     end
   end
   local error_status =""
   if res.query_status and (res.query_status < 0 ) then
       error_status = "[ERR]"

   end
   if (res.affected_rows) then
       row_count = res.affected_rows
   end
   --
   -- write the query, adding the number of retrieved rows
   --
   fh:write( string.format("%s %6d -- %s {%d} %sn", 
     os.date('%Y-%m-%d %H:%M:%S'), 
     proxy.connection["thread_id"], 
     query, 
     row_count,
     error_status))
   fh:flush()
 end
이 함수에서는 우리가 현재 데이터를 조작하는 쿼리나 select 쿼리를 다루고 있는지를 확인해 볼 수 있습니다. 만약 행(row)이 있으면 함수는 그것들의 개수를 센 다음 그 결과가 로그파일의 중괄호 안에 출력됩니다. 영향을 받은 행이 있으면 로그파일에는 영향을 받은 개수가 기록된다. 게다가 우리는 오류가 있었는지를 확인할 수 있는데, 정보가 중괄호 안에 반환된 경우로서, 결론적으로 말해서 모든 것들이 로그 파일로 기록됩니다. 아래는 이러한 로그파일의 예입니다:
 2007-06-29 16:41:10     33 -- show databases {5} 
 2007-06-29 16:41:10     33 -- show tables {2} 
 2007-06-29 16:41:12     33 -- Xhow tables {0} [ERR]
 2007-06-29 16:44:27     34 -- select * from t1 {6} 
 2007-06-29 16:44:50     34 -- update t1 set id = id * 100 where c = 'a' {2} 
 2007-06-29 16:45:53     34 -- insert into t1 values (10,'aa') {1} 
 2007-06-29 16:46:07     34 -- insert into t1 values (20,'aa'),(30,'bb') {2} 
 2007-06-29 16:46:22     34 -- delete from t1 {9}
첫 번째와 두 번째, 그리고 네 번째 줄은 상대적으로 다섯 번째, 두 번째, 여섯 번째 줄로부터 반환된 쿼리임을 말해줍니다. 세 번째 줄은 쿼리가 오류를 반환하였음을 말해줍니다. 다섯 번째 줄은 2개의 행이 UPDATE 명령에 의해 영향을 받았음을 말해줍니다. 그 다음의 라인은 모두 INSERT와 DELETE문에 의해 영향을 받은 행의 개수를 말해줍니다. 

예제에서 알아두어야 할 사항 

이 예제에서 제공하는 예제는 몇몇 운영체제에서 테스트하였습니다. 알파 버전 단계의 코드는 아직 작동하긴 하지만, 기능집합이 안정화될 때까지는 자료 구조나, 옵션, 인터페이스에 변화가 있을 수 있습니다. 

이 다음은 무엇입니까? 

긴 여담의 끝에서 제가 겨우 수박 겉핥기만 했음을 느낍니다. MySQL Proxy는 바로 이런 것이며, 훨씬 그 이상입니다. 아직 제가 다루지 않았던 기능들도 있고 몇 가지 벤치마크를 통해 적절한 커버리지를 필요로 하는 것도 있습니다. 또한 저는 아키텍처에 대해 상세히 알아보지도 않았습니다. 누군가가 언젠가 그러한 것들에 관해 다루겠지요. 

커뮤니티에서 이름값을 할 정도로 충분한 내용들을 모으는 대로 로드 밸런싱(load balancing), 특정 기능 복제(replication specific features), 벤치마크(benchmark), 그리고 특히 MySQL Proxy 에 대해 상세히 다루고 있는 더 많은 기사들이 나오기를 기대하십시오. 

마지막으로 MySQL Proxy를 만들어준 Jan Kenshke에게 감사의 말씀을 전하고 싶습니다.저자 Giuseppe Maxia는 MySQL 커뮤니티 팀의 QA 개발자입니다. 그는 IT 분야의 20년의 경험을 지닌 시스템 분석가로서 수년동안 데이터베이스 컨설턴트 및 설계자로서 일했으며 자주 오픈소스 행사에서 연설하기도 하고 수많은 아티클을 작성하기도 하였습니다. 지금은 이탈리아의 Sardinia에서 살고 있습니다.


출처 :  http://www.hanb.co.kr/
728x90
저작자표시

관련글

댓글

티스토리툴바