한빛출판네트워크

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

[이전 기사 보기]
MySQL Proxy 시작하기(1)

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에게 감사의 말씀을 전하고 싶습니다.

• 카툰 작업: Cartoon Studio Limited의 Richard Duszczak
• 다이어그램: Patrizio Tassone

---

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