PHP 프로그래밍

Web Programming >> PHP Programming

제6장 PHP와 웹 데이터베이스의 연동

3. MySQL 클라이언트 사용법

mysql 디렉토리 밑의 bin 디렉토리를 보면 mysql 이라는 파일이 존재한다. 이 파일은 MySQL 데이터베이스 서버에 접속하여 SQL 질의어를 통해 데이터베이스와 테이블을 생성 및 삭제하거나 테이블 내의 데이터를 삽입, 수정, 삭제, 검색할 수 있는 MySQL 데이터베이스 서버의 대화형 SQL 클라이언트(client) 프로그램이다.

MySQL 내에 저장된 데이터를 웹상에서 PHP로 연동하기 전에는 이 mysql 클라이언트 프로그램을 이용하여 앞의 제2절에서 배운 SQL문으로 직접 데이터베이스를 조작해야 한다.

가. mysql 클라이언트의 실행 및 종료
앞에서 mysql 실행 파일이 존재하는 bin 디렉토리를 경로명에 추가하였으므로 어느 디렉토리에서든지 mysql을 실행시키면 된다.

[root@www root]#mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version : 3.33.34

Type 'help' for help.

mysql>

그러면 위와 같이 mysql 클라이언트 프로그램이 실행되면서 SQL문을 통해 데이터베이스내의 데이터를 조작할 수 있는 상태가 된다. "mysql>" 다음에 제2절에서 배웠던 SQL 문을 입력하면 그 SQL문의 실행 결과가 출력된다.

mysql 클라이언트에서 빠져나오려면 아래와 같이 "\q"나 "exit" 명령어를 준다.

[root@www root]#mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version : 3.33.34

Type 'help' for help.

mysql> \q
Bye
[root@www root]#

나. 데이터베이스와 테이블 정보 출력

MySQL 데이터베이스를 설치하면 기본적으로 mysql이라는 이름을 갖는 데이터베이스와 test 라는 데이터베이스 두 개가 MySQL 서버에 생성된다.

[root@www root]#mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version : 3.33.34

Type 'help' for help.

mysql>show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rwos in set (0.01 sec)

mysql> \q
Bye
[root@www root]#

위에서 show databases 명령은 현재 MySQL 서버에 생성되어 있는 데이터베이스의 목록(이름)을 출력한다.
이때 "mysql>" 다음에 내리는 모든 명령은 반드시 명령문의 끝이 세미콜론(;) 또는 "\g"로 끝나야 한다.

mysql> show databases; // 모든 명령문은 세미콜론(;) 이나 "\q"로 끝나야 한다.

MySQL을 설치한 후에는 mysql 과 test 라는 데이터베이스만이 생성되어 있다. 여기서 mysql 데이터베이스의 테이블 리스트를 보려면 mysql 클라이언트 프로그램을 실행할 때 다음과 같이 연결하고자 하는 데이터베이스를 지정해주어야 한다.

첫 번째 줄의 "mysql mysql"에서 첫 번째 mysql은 클라이언트 프로그램을 실행하기 위한 파일명이고 두 번째 msyql은 생성되어 있는 mysql 데이터베이스를 의미한다. 즉, 이는 mysql 클라이언트 프로그램을 통해 MySQL 서버에 설치된 mysql 이라는 이름을 갖는 데이터베이스로 접속한다는 의미이다. 따라서 이제 여기서 내리는 SQL 등의 모든 명령문은 mysql 데이터베이스에만 적용된다. 만일 test 데이터베이스에 대한 조작을 수행하려면 "mysql test"와 같이 test 데이터베이스를 지정해주어야 한다.
위에서 "show tables" 명령문은 현재 선택된 mysql 데이터베이스에는 6개의 테이블이 있는 것을 알 수 있다.
"show columns from db"는 mysql 데이터베이스 내에 생성되어 있는 6갱의 테이블 중 하나인 유 테이블의 구조 정보를 출력한다. 여기에는 CREATE TABLE 문으로 db 테이블을 생성할 때 정의한 필드명과 데이터 타입 등의 정보가 출력된다. 이는 "desc[테이블명]"과 동일한 명령이다.

"desc"는 describe의 약자로 해당 테이블에 대한 구조 정보를 출력하는 명령문이다.

다. 데이터베이스의 사용 권한 설정

MySQL 서버는 새로운 데이터베이스를 생성할 때 이 데이터베이스에 대한 사용 권한을 사용자에 따라 다르게 부여함으로써 허가된 사용자만이 해당 데이터베이스를 전체 또는 부분적으로 사용할 수 있도록 하고 있다.

데이터베이스를 사용할 수 있는 권한은 접속하는 호스트에 따라, 또는 등록되어 있는 사용자에 따라 달라질 수 있는데 그러한 권한은 앞에서 본 mysql이라는 데이터베이스에 설정한다. 즉, mysql 데이터베이스는 특정 데이터를 저장하기 위한 일반적인 데이터베이스의 하나가 아니라 MySQL 서버에 저장되어 있는 모든 데이터베이스에 대한 권한 설정을 담고 있는 데이터베이스 즉, 마스터 데이터베이스라고 할 수 있다.

현재 우리가 설치한 mysql 데이터베이스에는 columns_priv, db, func, host, tables_priv, user의 여섯 개의 테이블이 있다. 이 테이블의 종류는 MySQL 의 버전에 따라서 약간씩 다르게 나타난다.

user 테이블은 MySQL 서버에 생성되어 있는 데이터베이스를 사용할 권한을 갖는 사용자, 즉 각각의 데이터베이스 소유자에 대한 정보를 담고 있는 테이블이다.

user 테이블은 Host, User, Password 등 사용자 정보와 해당 사용자가 데이터베이스에 대해 가질 수 있는 권한에 대한 정보로 구성되어 있다. 여기서 Select_priv, Insert_priv, Update_priv, Delete_priv의 필드값이 'Y'라면 해당 사용자는 네 개의 SQL 문(SELECT, INSERT, UPDATE, DELETE)을 사용하여 데이터베이스를 조작할 수 있는 권한을 가진다는 것을 의미한다. 만일 Reload_priv, Shutdown_priv 등이 'Y' 이면 해당 사용자는 MySQL 서버를 재 기동하거나 종료시킬 수 있다.

중요한 점은 여기 user 테이블에서 설정한 권한은 MySQL 서버에 저장되어 있는 모든 데이터베이스에 적용된다는 것이다. 따라서 새로운 사용자를 user 테이블에 등록할 때에는 Host, User, Password를 제외한 나머지 권한은 허가하지 않음('N')으로 설정하는 것이 안전하다.

앞의 제2절에서 배운 SELECT SQL 명령문으로 현재 user 테이블에 저장되어 있는 사용자 데이터를 보면 기본적으로 root 만이 등록되어 있다.

mysql> select host,user,password from user;

+------------------+-----------+------------------+

| host | user | password |

+------------------+-----------+------------------+

| localhost | root | |

| www.kornux.co.kr | root | |

| localhost | | |

| www.kornux.co.kr | | |

+------------------+-----------+------------------+

16 rows in set (0.00 sec)

mysql>

이 root 사용자는 mysql 데이터베이스에 대한 소유자로 결국 MySQL 내에 생성되는 모든 데이터베이스에 대한 권한을 설정할 수 있는 관리자이다. 따라서 이 관리자는 user 테이블에서 host, user, password를 제외한 나머지 필드의 값이 모두 'Y'이다. 즉, root 사용자는 데이터베이스 서버를 종료 또는 재 기동시킬 수 있고 새로운 데이터베이스와 테이블을 생성 할 수도 있으며 생성된 테이블에 대해 삽입, 수정, 삭제, 검색 등의 조작을 할 수 있다.

위에서 보면 root 사용자는 password가 비어 있다. 그래서 앞에서 mysql 클라이언트로 mysql 데이터베이스에 접속할 때 아무런 패스워드도 요구받지 않았던 것이다. 이제 데이터베이스의 보안을 위해 먼저 root 사용자의 패스워드를 설정하자. 여기서는 root 사용자의 패스워드는 "php99"로 하기로 한다.

mysql>update user set password = password('php99') where user = 'root' ;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 0

mysql> select host,user,password from user;

----------------------------------------------------

host user password

----------------------------------------------------

localhost root 257995000ed3c7e1

www.kornux.co.kr root 257995000ed3c7e1

localhost

www.kornux.co.kr

----------------------------------------------------

4 rows in set (0.01 sec)

mysql>

UPDATE 명령문으로 user 테이블에서 root 사용자의 password 필드값을 password('php99')로 수정하였다. 여기서 password() 라는 함수는 인자로 주어진 문자열을 위와 같이 암호화하는 MySQL 함수로 패스워드의 특성상 암호를 단순 문자열로 입력하지 않고 password() 함수로 암호화하여 저장한 것이다.

이제부터 mysql 클라이언트 프로그램으로 mysql 데이터베이스로 접속하려면 앞에서 변경한 암호를 입력해야 한다. 변경된 암호를 사용해야 하는 시점은 MySQL 서버가 재 기동되는 순간부터이다. 즉, INSERTS나 UPDATE 명령으로 user 테이블의 사용자 정보를 변경시킨 후에는 아래와 같이 반드시 MySQL 서버를 한번 재기동시켜주어야 한다.

[root@www /root]# mysqladmin -u root reload // MySQL 서버 재기동

[root@www /root]# mysql -u root mysql // 암호 입력 없으면 실패

ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

[root@www /root]# mysql -u root -p mysql // -p 옵션을 주어

Enter password: // 암호를 입력한다.

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 23279 to server version: 3.22.24

Type 'help' for help.

mysql>

여기서는 "mysqladmin -u root reload"로 MySQL 서버를 재기동시켜주었다. 이제 mysql 클라이언트로 mysql 데이터베이스에 접속할 때 이전과 같이 "mysql -u root mysql"을 입력하면 mysql 데이터베이스로의 연결이 거부된다. 암호를 입력하지 않았기 때문이다. 암호를 입력하기 위해서는 아래와 같이 -p 옵션을 주어야 한다.

[root@www mysql]#mysql -u root -p mysql

Enter password:

-p는 사용자가 root가 mysql 데이터베이스에 연결할 때 필요한 암호를 입력하겠다는 뜻이다. 따라서 -p 옵션을 주면 다음 줄에 암호를 입력하기 위한 입력란이 나타난다. 여기에 앞에서 설정한 "php99"를 입력하면 mysql 데이터베이스에 연결이 된다.

이제 root 사용자는 패스워드를 가지므로 root가 내리는 모든 명령은 -p 옵션으로 암호를 같이 입력해야만 실행된다. 예를 들면, 다음과 같이 서버를 재 기동할 때에도 -p로 암호를 입력해야 한다. 그렇지 않을 경우 "Access denied"라는 거부 메시지가 출력된다.

[root@www /root]# mysqladmin -u root mysql

mysqladmin: connect to server at 'localhost' failed

error: 'Access denied for user: 'root@localhost' (Using password: NO)'

[root@www /root]# mysqladmin -u root -p mysql

Enter password:

[root@www /root]#

암호를 입력하는 또 다른 방법은 -p 다음에 암호를 공백 없이 바로 붙여서 입력하는 방법이다. 이 경우 암호가 화면에 그대로 출력되므로 안전한 방법은 아니다.

[root@www /root]# mysql -u root -pphp99 mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 23284 to server version: 3.22.24

Type 'help' for help.

mysql>

위와 같이 root의 암호가 "php99"이므로 -p 다음에 바로 php99를 붙여서 입력하여도 된다.

라. 새로운 데이터베이스 생성

이제 새로운 데이터베이스를 생성하고 이 데이터베이스 내에 제2절에서 사용했던 userdb라는 테이블을 생성해보자. MySQL에서 데이터베이스를 생성하는 방법은 두 가지가 있다.

첫 번째는 mysqladmin 실행 파일을 이용하는 방법으로 여기서는 "sample"이라는 이름을 갖는 데이터베이스를 생성한다.

[root@www /root]# mysqladmin -u root -p create sample
Enter password:
Database "sample" created.
[root@www /root]#

mysqladmin으로 데이터베이스를 생성하려면 "create [생성할 데이터베이스명]" 구문을 사용해야 한다. 반대로 생성된 데이터베이스를 mysqladmin으로 삭제하려면 다음과 같이 "drop [생성된 데이터베이스명]" 구문을 사용한다.

[root@www /root]# mysqladmin -u root -p drop sample
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'sample' database [y/N]
y
Database "sample" dropped
[root@www /root]#

[root@www data]# ls -l
drwx------   2 root     root         4096 Mar 4 15:33 mysql/
-rw-r--r--   1 root     root         5245 Jul 24 17:14 www.kornux.co.kr.err
-rw-r--r--   1 root     root            3 Jul 24 17:14 www.kornux.co.kr.pid
drwx------   2 root     root         4096 Mar 4 15:33 test/
drwx------   2 root     root          024 Jun 28 11:53 sample/
[root@www data]#

그리고 이 sample 데이터베이스 내에 테이블을 생성할 경우 해당 테이블에 대한 정의를 담은 파일(테이블이름.frm)과 인덱스 파일(테이블이름.ism), 데이터 파일(테이블이름.isd)들이 테이블과 동일한 이름으로 이 디렉토리 내에 각각 생성된다.

두 번째 방법은 mysql 클라이언트로 mysql 데이터베이스에 연결하여 데이터베이스를 생성하는 방법이다. 물론 mysql 데이터베이스에 연결하기 위해선 root 사용자로 접속해야 한다.
mysql 클라이언트에서 데이터베이스를 생성하려면 "create database [생성할 데이터베이스명]" 구문을 사용한다. "sample" 데이터베이스를 생성한 후 show databases 명령으로 "sample" 데이터베이스가 생성된 것을 확인할 수 있다. 마찬가지로 mysql 클라이언트에서 기존의 데이터베이스를 삭제하려면 create 대신 drop을 사용하면 된다.

[root@www mysql]#mysql -u root -p mysql
Enter password:
mysql> drop database sample;
Query OK, 0 rows affected (0.00 sec)

mysql> create database sample;
Query OK, 0 rows affected (0.00 sec)

일단 데이터베이스가 생성되면 다음과 같이 mysql 디렉토리 밑의 data 디렉토리 밑에 생성된 데이터 베이스와 동일한 이름의 디렉토리(sample)가 만들어진다.

마. 생성된 데이터베이스의 등록

앞에서 생성한 "sample"이라는 이름의 데이터베이스를 사용하기 위해서는 이 데이터베이스를 어느 사용자에게 사용 허가할 것이지를 mysql 데이터베이스에 등록시켜주어야 하는데 mysql 데이터베이스의 유 테이블이 이러한 역할을 한다.
db 테이블은 db명(db)과 이 db를 사용할 권한을 갖는 사용자(user) 및 호스트(host) 와 이 db를 조작할 수 있는 여러 가지 권한에 대한 설정 항목을 갖는다.

여기서는 "sample" 데이터베이스의 소유자를 "php"라는 사용자로 정하고 호스트는 모든 호스트 상에서 접근이 가능하도록 설정한다.

db 테이블의 구조를 보면 host, db, user를 제외한 나머지 권한에 관한 항목은 기본 값이 'N'인데 우리는 "php"라는 사용자에 한하여 "sample" 데이터베이스를 사용할 수 있도록 하고자 하므로 모든 권한을 'Y' 로 설정하여 부여한다.

mysql> insert into db values('%','sample','php','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
Query OK, 1 row affected (0.03 sec)

mysql> select host,db,user from db;
-------------------------------
host   db              user
-------------------------------
%     test
%     test\_%
%     sample          php
-------------------------------
3 rows in set (0.00 sec)

mysql>

이제 "php"라는 사용자는 "sample"이라는 데이터베이스에 대하여 db 테이블에 정의된 권한을 갖는다.

바. 새로운 사용자(데이터베이스 소유자)등록
이제 생성된 "sample" 데이터베이스를 사용할 "php"라는 사용자를 등록해야 한다. 새로운 사용자는 user 테이블에 등록한다.
앞에서 root 사용자의 패스워드를 변경하였듯이 새로운 사용자 "php"를 user 테이블에 등록시키면 된다.

mysql> insert into user (host,user,password) values('localhost','php',password('php99'));
Query OK, 1 row affected (0.00 sec)

mysql> select host,user,password from user;
-------------------------------------------
host            user     password
-------------------------------------------
localhost         root     25799500ed3c7e1
www.kornux.co.kr root     25799500ed3c7e1
localhost
www.kornux.co.kr
localhost         php      25799500ed3c7e1
--------------------------------------------
5 rows in set (0.00 sec)

mysql> flush privileges;

사용자가 "php"의 암호는 편의상 root 사용자와 동일하게 "php99"로 하였다. 여기서는 host, user, password를 제외한 나머지 권한 설정 관련 항목의 기본 값이 모두 'N' 이므로 데이터 삽입시 host와 user, password 값만 입력하였다.

데이터베이스의 사용 권한 설정 부분에 강조하여 이야기한 것처럼 여기 user 테이블에서 설정한 권한은 MySQL 서버에 생성되는 모든 데이터베이스에 적용되므로 "php" 같은 일반 사용자를 등록할 때에는 "php"가 다른 데이터베이스를 건드리지 못하도록 권한 설정 항목을 모두 'N'으로 해야 한다.

그리고 나서 각각의 사용자가 사용할 데이터베이스는 db 테이블에서 데이터베이스를 등록할 때 설정해 준다. 앞에서 "sample" 데이터베이스를 db 테이블에 등록할 때 user를 "php"로 하였고 나머지 권한은 모두 'Y'로 한 것을 기억할 것이다. 이는 해당 데이터베이스(sample)에 대해서는 소유자인 "php"가 db 테이블에서 설정한 모든 권한을 갖는다는 것을 의미한다.

따라서 일반적으로 새로운 사용자를 등록할 때에는 user 테이블의 권한은 모두 'N'으로 하고 각 데이터베이스 소유자의 권한은 db 테이블에서 따로 설정해주는 것이 안전하다.
db 테이블과 user 테이블의 등록 및 권한 설정은 보안 측면에서 상당히 중요한 부분이므로 잘 이해하고 있어야 한다.

새로운 사용자 "php"를 user 테이블에 등록하였으므로 이 경우 역시 MySQL 서버를 재 기동시켜야 한다.

사. 테이블의 생성과 수정 및 삭제

이제 sample 데이터베이스와 데이터베이스의 소유자 "php"를 mysql 데이터베이스의 db 테이블과 user 테이블에 각각 등록하였으므로 아래와 같이 "php"라는 일반 사용자 계정으로 sample 데이터베이스에 연결할 수 있다.

[root@www /root]#mysql -u php -p sample // php 사용자계정으로 연결
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23284 to server version: 3.22.24

Type 'help' for help.

mysql>show tables;
Empty set (0.00 sec)

mysql>

php 사용자로 sample이라는 데이터베이스에 연결하였다. 여러 가지 SQL 문을 이용하여 sample 데이터베이스 내에 새로운 테이블을 생성하거나 삭제, 변경할 수 있고 테이블에 데이터를 삽입, 수정, 삭제, 검색할 수도 있다.

이 sample 데이터베이스에 userdb 테이블을 생성하려면 앞에서 다루었던 CREATE TABLE 명령문을 입력하면 된다.

create table userdb (
name CHAR(8)
id VARCHAR(10) NOT NULL,
email VARCHAR(40),
sex CHAR(1),
milage INT,
level CHAR(1),
PRIMARY KEY(id)
);

그 결과 sample 데이터베이스에는 userdb라는 테이블이 하나 생성된다. desc 명령으로 userdb 테이블의 구조 명세를 보면 다음과 같다.

mysql>desc userdb;
-----------------------------------------------------------------
Field   Type            Null     Key     Defalut  Extra
-----------------------------------------------------------------
name  varchar(8)        YES            NULL
id     varchar(10)               PRI
email   varchar(40)       YES            NULL
sex    char(1)          YES            NULL
milage  int(11)           YES            NULL
level   char(1)          YES            NULL
-----------------------------------------------------------------
6 rows in set (0.00 sec)

mysql>

만일 생성된 userdb 테이블을 수정하려면 ALTER TABLE문을 이용한다. 다음의 명령문은 userdb 테이블에서 id 필드의 데이터 타입을 varchar(10)에서 varchar(12)로 수정한다.

mysql>alter table userdb change column id id varchar(12) NOT NULL;
Query OK, 0 rows affected (0/02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>desc userdb;
-----------------------------------------------------------------
Field   Type            Null     Key     Defalut  Extra
-----------------------------------------------------------------
name  varchar(8)        YES            NULL
id     varchar(10)               PRI
email   varchar(40)       YES            NULL
sex    char(1)          YES            NULL
milage  int(11)           YES            NULL
level   char(1)          YES            NULL
-----------------------------------------------------------------
6 rows in set (0.00 sec)

mysql>

userdb 테이블을 삭제하려면 DROP TABLE문을 사용하면 된다.

mysql>drop table userdb;
Query OK, 0 rows affected (0.01 sec)

mysql>show tables;
Empty set (0.00 sec)

mysql>

한편 mysql 클라이언트를 이용하지 않고 데이터베이스 내에 테이블을 생성하는 또 다른 방법이 있다. 먼저 다음의 SQL 문을 만들어 userdb.sql이라는 파일로 저장한다.

create table userdb (
name CHAR(8)
id VARCHAR(10) NOT NULL,
email VARCHAR(40),
sex CHAR(1),
milage INT,
level CHAR(1),
PRIMARY KEY(id)
);

이제 계정의 커맨드 라인에서 다음과 같은 명령을 내리면 mysql 클라이언트를 통해 sample 데이터베이스에 연결하지 않고도 데이터베이스 내에 userdb 테이블을 생성할 수 있다.

[root@www mysql]#mysql -uphp -pphp99 sample < userdb.sql // 테이블 생성문
[root@www mysql]#mysql -u php -p sample
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23284 to server version: 3.22.24

Type 'help' for help.

mysql>show tables;
--------------
Tables in sample
--------------
userdb
--------------
1 row in set (0.00 sec)

mysql>

첫 줄을 보면 다음과 같다.

[root@www mysql]#mysql -uphp -pphp99 sample < userdb.sql

이 명령은 php라는 사용자로 sample 데이터베이스에 연결하여 userdb.sql 파일에 담긴 sql문을 실행한다는 의미이다. 따라서 이 한 줄의 명령은 mysql 클라이언트를 이용하여 sample 데이터베이스에 연결한 후 userdb.sql 파일의 SQL문을 직접 입력하는 것과 동일한 결과를 갖는다. 이때 주의할 것은 사용자를 가리키는 -u 옆에 공백 없이 사용자 계정을 바로 입력하고 패스워드 역시 -p 옆에 바로 붙여서 입력해야 한다는 점이다.
위 명령문을 실행한 후 msyql 클라이언트로 sample 데이터베이스에 들어가서 show tables 명령을 내리면 userdb라는 테이블이 생성되어 있는 것을 확인할 수 있다.

[목차]