My SQL 강좌 1
My SQL 강좌 1
허정수
1997년 겨울, 필자는 SQL을 배우기 위해 DB 서버를 찾고 있었다. 여러 잡지와 책을 찾아 보았는데, 그때만 해도 mSQL과 PostgreSQL에 대한 내용밖에 없었다. 평소 일반 사람들과 다른 삶을 살고 싶었던 필자는 일반 사람들이 많이 쓰지 않는 DB 서버를 찾다가 우연히 MySQL을 알게 되었고, MySQL에 반해 버렸다. 1997년 만해도 MySQL은 mSQL이나, PostgreSQL보다는 사용자가 적었다(우리 나라에서만 그런지는 모르지만). 몇 달 전 리눅스 저널에서 각 분야별로 어떤 응용 프로그램이 가장 많은 사용자를 확보했나 조사를 한 적이 있다. DB 분야에서는 MySQL이 1등을 했다. 아이러니컬 하게도 다른 사람이 많이 안 쓰는 DB 서버를 사용하기 위해 MySQL을 썼었는데, 지금은 가장 많은 사용자를 확보하였다. 이 글에서는 MySQL의 특징과 설치 방법, 설정 방법, CAPI를 이용하여 응용 프로그램과 연동하기, PHP3를 이용하여 연동하는 방법에 대해서 알아 본다. 이번 기사에서는 MySQL의 특징과 설치 방법, 설정 방법에 대해서 알아 본다.
MySQL의 메뉴얼을 보면 MySQL의 특징이 약 30가지 정도 나와 있다. 요약해 보면 다음과 같다.
* 커널 thread를 이용한 Multi thread를 지원하므로,
CPU가 여러 개일 경우 이들 CPU를 잘 활용할 수 있다.
* 다양한 플랫폼을 지원한다.( Windows NT도 지원한다. )
* 아주 큰 데이타 베이스도 다룰 수 있다. MySQL을
만든 회사는 5천만개의 레코드를 가진 데이타 베이스도 사용하고 있다.
* 다양한 나라말로 에러 메시지를 표시한다.
* 최적화된 라이브러리를 사용한다.
하지만, MySQL의 가장 큰 특징을 들라고 하면, 빠른 속도를 들 수 있다. 아래에 MySQL과 오라클의 속도를 비교해 놓은 벤치 마크 결과가 있다.
위 그림에서 알 수 있듯이, MySQL은 매우 빠르다. http://www.tcx.se/benchmark.html 에 가면 다른 제품과도 비교해 놓은 자료가 많이 있으니, 관심있는 독자는 직접 확인하기 바란다.
그렇다면, 왜 MySQL이 다른 제품에 비해서 빠른지 궁금하지 않는가? MySQL은 속도 향상을 위해서 일부러 몇 가지 ANSI SQL을 구현하지 않다. 바로, Transaction과 Trigger이다. Transaction과 Trigger는 시스템의 자원을 많이 소모함으로 속도를 떨어지게 한다. MySQL은 Transaction과 Trigger를 일부러 구현하지 않았다.
다음은 아직 MySQL이 지원하는 않는 기능들이다.
Sub-select
SELECT * FROM table1 WHERE id IN( SELECT id FROM table2) ; 와 같은 것은 아직
MySQL에서 지원하지 않는다. 다만, INSERT .. SELECT .. 와 REPLACE .. SELECT 만이
지원된다. sub-select는 3.23.0 버전에서 사용가능할 것이라고 한다. *
SELECT INTO TABLE
MySQL은 SELECT .. INTO TABLE을 지원하지 않는다. SELECT .. INTO OUTFILE 만을
지원한다.
Transaction
앞에서도 말했다시피 MySQL은 Transaction을 지원하지 않는다. Transaction은 시스템의
자원을 많이 소모함으로 전체적인 속도를 느리게 한다. 따라서, MySQL은 Transaction을
구현하지 않고, 대신에 LOCK TABLES/UNLOCK TABLES를 이용하여 Transaction과 같은
일을 할 수 있도록 하였다.
Stored Procedure와 Trigger
Stored Procedure는 앞으로 지원할 계획이지만, Trigger는
속도를 떨어드리고, 별로 필요 없는 쿼리라서 앞으로도 지원할 계획이 없다.
Foreign Key
View
아쉽게도 MySQL의 라이센스 방식 때문에 레드햇 같은 리눅스 배포판에는 MySQL이 포함되어 있지 않다. MySQL의 라이센스 방식은 비상업용으로 MySQL이 쓰일 경우 무료이고, 상업적으로 쓰일 경우, 라이센스를 구입해야 한다. Win 95, Win 98, Win NT용 MySQL은 상업적이든 비상업적이든 라이센스를 구입해야 한다. http://www.tcx.se/download.html 에서 MySQL을 구할 수 있다.
배포되는 형식은 소스 코드, 바이너리, RPM이다. 이 글이 쓰여 지고 있는 지금의 최신 버전은 3.22.20a이다.
초보자들은 컴파일을 두려워 하는 경향이 있다. 하지만 대부분의 프로그램은 autoconf와 automake를 이용하여 배포되므로 컴파일하기가 쉽다. 메뉴얼에 나온대로 잘 따라간다면 실패없이 단 한번에 설치를 마칠 수 있다. 가장 기본적인 과정은 다음과 같다.
% gzip -d mysql-Version.tar.gz % tar xvf mysql-Version.tar % cd mysql-Version
이제 실제 설치 단계이다.
% configure % make % make check % make install % cd scripts % mysql_install_db
몇 줄만 치면 설치가 다 끝난다. 각 단계는 다음과 같은 일을 한다.
configure : 각자의 환경에 맞게 Makefile을 생성한다.
make : 컴파일을 한다.
make check : 컴파일이 제대로 되었는지 검사하는 단계이다. (꼭 할 필요는 없다.)
make install : 만들어진 실행 파일을 원하는 디렉토리에 설치한다.
mysql_install_db : MySQL의 권한에 대한 테이블을 생성한다.
MySQL을 처음 설치했을 때만 필요한 과정이고, 이전 버전의 MySQL에서 업그레이드를 하고 싶다면 mysql_fix_ privilege_tables를 실행시키면 된다.
따로 MySQL이 설치될 디렉토리를 지정하지 않았다면, 기본적인 디렉토리는 /usr/local/이 된다. MySQL을 설치할 시스템이 자신이 슈퍼 유저인 시스템이라면 상관없겠으나, 학교 서버에 설치한다면, 경우는 대부분 /usr/local/에 설치할 권한이 없을 것이다. 이때, MySQL을 설치할 디렉토리를 지정해 줄 수 있다.
% configure --prefix=원하는 디렉토리
이렇게 하면 MySQL이 자신이 지정한 디렉토리에 설치된다. MySQL이 설치된 디렉토리의 bin 디렉토리에 실행 파일이 설치되고, include/mysql에 헤더 파일이 설치된다. lib/mysql에 라이브러리들이 설치되고, libexec에 MySQL의 데몬인 mysqld가 설치된다. var에는 실제 데이타들이 설치된다.
일단 바이너리 배포판을 위의 홈페이지에서 받은 후에
% gzip -d mysql-Version-OS.tar.gz % tar xvf mysql-Version-OS % ln -s mysql-Version-OS mysql % cd mysql% scripts/mysql_install_db
이렇게 하면 설치가 끝난다.
rpm은 소스 코드 rpm과 바이너리 rpm이 있다. rpm이 소스 코드를 담고 있다면,
% rpm -i MySQL-Version.src.rpm % cd /usr/src/redhat/SOURCES
이후에는 소스 코드로 설치하기 과정을 하면 된다.
바이너리의 경우 MySQL-Version.i386.rpm, MySQL-client-Version.i386.rpm MySQL-bench-Version.i386.rpm, MySQL-devel-Version.i386.rpm 의 4 개의 파일이 필요하다. rpm을 이용하여 위의 4 개 파일을 설치하면 된다. 이때 /var/lib/mysql에 실제 데이타들이 저장된다. 부팅시에 자동으로 데몬을 실행시키는 파일을 자동으로 만들어 준다.
설치가 완료됐으니, 이제 MySQL을 사용해 보자. 먼저 어떤 실행 파일이 만들어 졌는지 살펴 보자. 많은 실행 파일이 있지만,많이 쓰이는 실행 파일만 정리하면 다음과 같다.
1) mysql
SQL 쉘로서, client 프로그램이다. MySQL에 접속하여, 쿼리를 수행하는 프로그램이다. 앞으로 많이 사용하게 될 것이다.
2) mysqladmin
MySQL을 관리하는 프로그램이다. 데이타 베이스를 만들고, 삭제하고, 권한에 대한 테이블을 다시 읽어 들이는 일을 한다.
3) mysqld
MySQL 데몬이다. MySQL을 사용하기 위해서 항상 실행 중이어야 한다.
4) mysqlshow
MySQL안에 어떤 데이타 베이스들이 있는지, 그 데이타 베이스들에는 어떤 테이블이 있는지, 테이블에는 어떤 컬럼이 있는지를 보여 준다.
5) safe_mysqld
mysqld를 좀더 안전하게 실행시키는 스크립트 파일이다.
6) isamchk
테이블에 문제가 있을 때 문제점을 고치기도 하고, 테이블을 최적화한다.
MySQL의 사용하기 위해서 데몬이 실행 중이어야 한다고, 설명을 했다. 데몬을 실행시키는 방법은
% safe_mysqld 혹은 % mysqld
이다. MySQL은 3306번 포트를 사용한다. 혹시 다른 프로그램에서 먼저 3306번을 쓰고 있으면 데몬이 에러를 내면서 실행이 안 된다. 이때는
% mysqld -P 3333
처럼 사용할 포트 번호를 지정해 줄 수 있다.
매번 부팅시마다 데몬을 손수 실행시키기 귀찮다면, safe_mysqld를 /etc/rc.d/rc.local에
한줄 추가해주면 부팅시마다 자동으로 실행된다. (rpm으로 설치한 사람은 자동으로
되어 있다.)
MySQL 설치 단계에서 mysql_install_db 단계가 권한에 대한 테이블을 설치하는 단계라고 하였다. MySQL을 처음 설치하면 root만이 MySQL을 사용하도록 권한이 주어여 있다. 일단 어떤 데이타 베이스들이 있는지 보자.
% mysqlshow -u root +-----------+ | Databases | +-----------+ | mysql | | test | +-----------+
‘-u root’가 바로 root로 사용을 한다는
옵션이다.
MySQL을 처음 설치하면 mysql, test라는 두 개의 데이타 베이스가 설치된다. mysql에
어떤 테이블이 있는지 보자.
% mysqlshow -u root mysql Database: mysql +--------------+ | columns_priv | | db | | func | | host | | tables_priv | | user | +--------------+
6 개의 테이블이 있다. 이 중 user 테이블이 사용자에 관한 테이블이다. 이제 mysql이라는 클라이언트 프로그램을 이용해서 MySQL을 사용해 보자.
% mysql -u root mysql>
mysql 데이타 베이스를 사용하기 위해서 다음과 같이 한다.
mysql> use mysql
그럼 다음과 같이 하여, mysql 데이타 베이스의 user 테이블에 어떤 레코드가 있는지 보자.
mysql> select * from user ; +-------------+-------+----------------+-------------+- | Host | User | password | Select_priv | +-------------+-------+----------------+-------------+- | localhost | root | | Y | | inos.ml.org | root | | Y | | localhost | | | | N | | inos.ml.org | | | N | +-------------+-------+----------------+-------------+- 7 rows in set (0.00 sec)
위와 비슷하게 나왔을 것이다. SQL 언어를 잘 모르는 독자를 위해 설명하자면 ‘SELECT ... ’는 테이블로 부터 컬럼을 리턴하라는 쿼리이다. 모든 쿼리의 끝은 ‘;’ 혹은 ‘\g’로 끝난다. XXX_priv는 XXX에 대한 권한을 나타낸다. 즉 Select_priv가 ‘Y’이면 그 사용자는 select 쿼리를 수행할 수 있음을 나타낸다.
| Host | User | password | Select_priv | +-------------+-------+------------------+-------------+- | localhost | root | | Y |
위는 localhost의 root가 패스워드는 없고, 모든 권한이 주어져 있다는 것을 나타낸다. 하지만 어떠한 유저라도
% mysql -u root
만 하면 MySQL에 대한 모든 권한을 가질 수 있으므로 매우 위험하다. 암호를 지정하는 방법은 다음과 같다.
mysql> UPDATE user SET password = password(‘새로운암호’) > WHERE user=’root’ ;
쿼리가 길다면 두 줄에 나눠서 써도 된다. 쿼리의 끝을 나타내는’;’ 만 있으면 된다.
UPDATE는 컬럼의 값을 변경시키는 쿼리이다. (다음 기사에 SQL 언어에 대한 설명이 이어 진다.)MySQL은 password를 암호화 한다. 따라서 암호를 지정할 때는 password(‘암호’) 처럼 password() 함수를 이용해야 한다.
이제 mysql을 잠시 종료하고, 암호가 제대로 되었는지 확인해 보자.
mysql> quit
권한에 대한 테이블을 변경하였을 경우에는 mysqladmin을 이용하여 꼭 변경된 권한을 다시 읽어 들여야 한다. 다음과 같이 한다.
% mysqladmin -u reload
위는 권한을 다시 읽어 들이란 명령이다. 위를 안하면, 변경되지 않은 내용을 가지고 있게 된다. 다시 mysql을 실행시켜 보자. 이제 암호가 있으므로 -p 옵션을 붙여야 한다.
% mysql -u root -p
Enter password:
이제 새로운 사용자를 추가해 보자. 사용자가 root만 있는 경우는 없고, 모든 사용자가 모든 권한을 갖는 경우도 없다. 사용자에 따라서는 SELECT만 할 수 있는 권한을 줄 때도 있다. 사용자를 추가하려면, INSERT .. INTO를 이용해서 MySQL의 user 테이블에 추가를 하면 된다.
% mysql -u root -p mysql Enter password: mysql> INSERT INTO user VALUES(‘localhost’, ‘new-user’, password(‘newpasswd’), ‘Y’,’Y’,’ Y’,’Y’,‘Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’) ; Query OK, 1 row affected (0.00 sec)
localhost의 new-user라는 사용자가 newpasswd라는 암호와 모든 권한으로 추가되었다. 모든 권한을 주고 싶지 않다면, 주고 싶지 않은 권한 부분을 ‘Y’으로 하면 된다.
만약 SELECT, INSERT, DELETE에 대한 권한만을 주고 싶다면
mysql> INSERT INTO user (host, user,password, Select_priv,
Insert_priv, Delete_priv)
-> VALUES (‘localhost’, ‘new-user’,password(‘newpasswd’),
’Y’,’Y’,’Y’) ;
처럼 하면 다른 권한은 모두 자동으로 ‘N’이 된다. 사용자 추가는 INSERT외에 GRANT를 이용할 수도 있다.
mysql> GRANT ALL PRIVILEGES ON *.* TO new-user@localhostI DENTIFIED BY ‘newpasswd’ WITH GRANT OPTION ;
위는 localhost의 new-user가 newpasswd라는 암호로 모든 권한을 갖는다는 것을 나타낸다.
mysql> GRANT RELOAD, PROCESS ON *.* TO new-user@localhost ;
는 localhost의 new-user가 RELOAD와 PROCESS의 권한을 갖는다는 나타낸다.
INSERT와 GRANT 중 어느 것을 써도 상관없다.
위에서 권한이 바뀌면 mysqladmin reload를 이용해서 바뀐 권한을 적용해야 한다고 했다. 하지만, 다음과 같이 할 수도 있다.
mysql> FLUSH PRIVILEGES ;
지금까지 MySQL의 특징과 설치법, 설정 방법에 대해 알아 보았다. 다음 시간에는 SQL 언어에 대해서 공부를 한다. 글을 읽으면서 모르는 점이 있다면 서슴치 말고 필자에게 메일을 보내주기 바란다.
마지막으로 암호를 잊어 버렸을 경우, 어떻게 해결할 지에 대해서 설명을 하겠다. 암호를 잊어버리고서, 난감해 하는 사람들을 많이 봤다. 게 중에는 아예 MySQL을 지우고 새로 설치하는 사람도 있다. 시간이 남는 사람이라면 새로 설치해도 되겠지만, 시간이 남아도 중요한 데이타가 있을 경우는 새로 깔지 못할 것이다. 이런 경우 다음과 같이 하면 된다.
먼저 MySQL 데몬이 실행 중이라면, 데몬의 실행을 중지한다. 그후, 데몬을 다시 실행시키는데, 권한에 대한 검사를 하지 않고 실행을 시킨다.
% safe_mysqld -Sg &
-Sg가 바로 권한 검사를 하지 않는다는 옵션이다. 이제 mysql을 이용해서 암호를 제거해 주면 된다. 그후 mysqladmin reload를 이용해서 바뀐 권한을 적용해주고 다시 데몬을 실행시키면 된다.
[ 필자소개 ] 허정수님은 숭실대학교 컴퓨터학부에 재학중이며 리눅스 포커스 자원봉사자로도 활동하고 있다. 리눅스 커널과 데이타베이스에 관심이 많다고 한다.