// 제 목: MySQL 설치후 DB,Table,User 만들고 PHP로 접속하기
// 작성자: 김영대( http://www.howto.pe.kr )
설치환경:
Redhat 7.3 (Kernel 2.4.18)
mysql-3.23.51
MySQL 을 설치하지 않으셨다면 이곳 사이트의
"Unix/Linux" -> "Apache + MySQL + PHP4 + Zend Optimizer 설치" 문서를 참고한다
1.MySQL 서버의 구동 확인하기
root 권한으로 실행된 safe_mysql 데몬과 mysql 권한으로 실행된 몇개의 mysqld 데몬을 확인한다
[root@howto root]# ps -ef|grep mysql
root 558 1 0 21:06 ? 00:00:00 /bin/sh /usr/local/mysql/bin/safe_mysql
mysql 603 558 0 21:06 ? 00:00:00 /usr/local/mysql/libexec/mysqld
mysql 605 603 0 21:06 ? 00:00:00 /usr/local/mysql/libexec/mysqld
mysql 606 605 0 21:06 ? 00:00:00 /usr/local/mysql/libexec/mysqld
만약 MySQL 서버가 구동중이지 않다면 구동한다
2.사용자 분류
UNIX나 DBMS 와 같은 멀티유저 환경에서는 사용자를 크게 두 부류로 구분하는데 하나는
DBA(database administrator)와 나머지 하나는 일반 사용자이다(UNIX 사용자 계정과 DBMS의 사용자(계정)은
틀린 개념이다). DBA는 주로 DBMS의 관리에 일반 사용자는 사용에 역점을 둔다.
MySQL 에서는 DB user를 하나 만들고 이 사용자를 DBA로 할지 일반 사용자로 할지 결정하는 일을 관리자
마음대로 바꿀 수 있다. 즉 권한(grant, privilege)라는 개념을 이용하여 바꿀 수 있다
하지만 MySQL 을 설치시(초기설정 스크립트파일 mysql_install_db을 실행하면 생성딤) 기본 DBA가 하나
만들어 지는데 root (UNIX 사용자 계정이 아님)라는 DBA 사용자 계정이다. 아래에서 설명되겠지만
db 라는 table 에 등록되어 있지 않은 좀 이상한(?) 계정이다
먼저 MySQL 설치 직후에 기본으로 생성된 DB/Table/User 등을 살펴보자
MySQL을 설치한 디렉토리(여기서는 /usr/local/mysql 으로 가정)의 bin에 보면 MySQL 시스템을 관리하기
위한 여러 실행파일들이 있다. 여기서 기본적으로 mysql 명령어를 자주 사용하게 되는데 이것은
MySQL 데이터베이스에 로그온하여 Db/Table/user/SQL 질의등을 수행하는 프로그램으로 Oracle의 sqlplus 와
비슷하다.
mysql 명령어의 사용은 아래와 같이 3개의 옵션을 지정한다
mysql -u사용자명 -p패스워드 DB명
MySQL의 이미 만들어진 DBA 계정인 root 계정으로 mysql DB를 접속해보자
[root@howto bin]# mysql -uroot mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1 to server version: 3.23.51-log
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
위의 경우 "-p패스워드" 는 생략된 형태로 mysql DB에 로그인 했는데 이는 root 계정에는 초기 설치시 패스워드가
지정되어 있지 않으므로 나중에라도 반드시 패스워드를 지정하기 바란다
3.기본으로 생성된 DB/table 보기
기본으로 생성된 DB 는 아래와 같이 2개가 있다
mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.01 sec)
실제 DB가 생성되어 물리적으로 저장되는 디렉토리는 MySQL 설치시 configure 명령어를 이용할때
--localstatedir 옵션을 사용하여 DB의 데이터 table을 파일로 저장할 디렉토리를 지정했을것이다
여기서는 /var/lib/mysql 에 만들었으므로 이곳의 내용을 보면 위 내용과 같이 mysql,test 두 DB의
실제 디렉토리를 볼 수 있다
[root@howto mysql]# ls -la
합계 10
drwx------ 5 mysql mysql 1024 4월 15 21:06 .
drwxr-xr-x 11 root root 1024 4월 15 00:59 ..
drwx------ 2 mysql mysql 1024 4월 15 00:59 mysql
-rw-rw---- 1 mysql mysql 86 4월 15 01:44 slave-bin.001
-rw-rw---- 1 mysql mysql 845 4월 15 16:01 slave-bin.002
-rw-rw---- 1 mysql mysql 73 4월 15 21:06 slave-bin.003
-rw-rw---- 1 mysql mysql 48 4월 15 21:06 slave-bin.index
-rw-r--r-- 1 mysql mysql 594 4월 15 21:06 slave.err
-rw-rw---- 1 mysql mysql 3 4월 15 21:06 slave.pid
drwx------ 2 mysql mysql 1024 4월 15 00:59 test
여기서 mysql DB는 DB전체에 대한 관리자격 DB이며, 이곳에는 mysql에 존재하는 DB의 이름과 해당DB의
소유자, 사용권한 등이 설정되는 곳이다
mysql DB를 접근하기 위해서는 아래와 같이 use 명령어를 이용하여 사용을 알려야 한다
이 use 명령어는 하나의 DB를 사용하다가 다른 DB 로 전환할경우 주로 사용된다
mysql> use mysql;
Database changed
그럼 mysql DB에 있는 table 들을 보자
mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+-----------------+
6 rows in set (0.00 sec)
여기서 우리가 과심을 가져야 하는 table 은 db, user 인데 이름에서 알 수 있듯이
db 라는 table 에는 물리적으로 생성된 DB 자체에 대한 정보라기 보다는 해당 DB의 사용자 접근 정보들이
저장되어 있고 user 라는 table 에는 사용자와 권한 정보들이 저장되어 있다
여기서 주의할것은 create database 명령어를 이용하여 DB를 새로 생성할지라도 그 정보는 db 라는
table에 저장되지 않는다. db라는 table 은 일종의 db 와 user 를 매핑시키는 table 이다
desc (description) 명령어를 이용하여 table 의 구조를 볼 수 있다
mysql> desc db;
+-----------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI | | |
| Db | char(64) binary | | PRI | | |
| User | char(16) binary | | PRI | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
+-----------------+-----------------+------+-----+---------+-------+
13 rows in set (0.00 sec)
mysql> desc user;
+-----------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI | | |
| User | char(16) binary | | PRI | | |
| Password | char(16) binary | | | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Reload_priv | enum('N','Y') | | | N | |
| Shutdown_priv | enum('N','Y') | | | N | |
| Process_priv | enum('N','Y') | | | N | |
| File_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
+-----------------+-----------------+------+-----+---------+-------+
17 rows in set (0.00 sec)
mysql DB 내의 db, user 라는 table을 볼 수 있는 유일한 존재는 root 뿐이다
만일 mysql DB의 db, user 라는 table 의 일반 사용자들이 알고 있다면 적어도 같은 호스트상의
어떤 유저라도 불법적으로 남의 데이터를 접근할 수 있다
MySQL 최초 설치시 root 의 비밀번호는 없는 상태이므로 비밀번호를 넣어보자
mysql> update user
-> set password = password('test1111')
-> where user = 'root';
Query OK, 2 row affected (0.00 sec)
위를 실행하면 일반적으로 user 테이블상에 root사용자가 localhost와 host명으로 2개 등록 되어 있으므로
2 row 의 비밀번호가 변경이 된다.
이제부터는 root로 MySQL에 접속할 경우 password를 물어보는 화면을 만나게 될것이다
[root@howto bin]# mysql -uroot mysql
Enter password:
이전에도 말했듯이 MySQL의 root 계정은 UNIX 로그인시 사용하는 root 와는 다른것이므로 만일 MySQL의 root
패스워드를 잊어버렸다면 MySQL에 로그인할수없다. 이때는 어쩔 수 없이 MySQL 을 시스템에서 제거하든지
새로 인스톨해야 한다
4.새로운 DB/table/user 만들기
먼저 예제로서 ei 라는 DB를 만들어 보자
mysql> create database ei;
Query OK, 1 row affected (0.01 sec)
그러면 다음과 같이 ei 라는 DB 가 추가되었을것이다
mysql> show databases;
+----------+
| Database |
+----------+
| ei |
| mysql |
| test |
+----------+
3 rows in set (0.00 sec)
방금만든 ei 라는 DB 를 사용하겠다
mysql> use ei;
Database changed
다음은 table을 하나 만들어 보자
mysql> create table customer (
-> name char(20) not null,
-> juminid char(12) primary key,
-> telno char(13),
-> addr char(80)
-> );
Query OK, 0 rows affected (0.01 sec)
실제 생성되었는지 확인해보면
mysql> show tables;
+--------------+
| Tables_in_ei |
+--------------+
| customer |
+--------------+
1 row in set (0.00 sec)
table 에 data 를 insert 해보자
mysql> insert into customer
-> values ('김영대','123456-1234567','016-xxx-xxxx','http://www.howto.pe.kr');
Query OK, 1 row affected (0.03 sec)
실제 저장되어 있는지 확인하자
mysql> select * from customer;
+--------+--------------+--------------+------------------------+
| name | juminid | telno | addr |
+--------+--------------+--------------+------------------------+
| 김영대 | 123456-12345 | 016-xxx-xxxx | http://www.howto.pe.kr |
+--------+--------------+--------------+------------------------+
1 row in set (0.01 sec)
여기서 의심이 많은 분들을 위하여 실제 DB가 생성되고 table이 생성되어 잘 모셔저 밌는지 확인해보자
MySQL 설치시 configure 명령어를 이용할때 --localstatedir 옵션을 사용하여 DB의 데이터 table을 파일로 저장할
디렉토리를 지정했을것이다
여기서는 /var/lib/mysql 에 만들었으므로 이곳의 내용을 보면 mysql,test 과 방금만든 ei 라는 DB를 위한
디렉토리를 볼 수 있다
[root@howto mysql]# ls -la
합계 11
drwx------ 5 mysql mysql 1024 4월 15 21:06 .
drwxr-xr-x 11 root root 1024 4월 15 00:59 ..
drwx------ 2 mysql mysql 1024 4월 15 23:38 ei
drwx------ 2 mysql mysql 1024 4월 15 00:59 mysql
-rw-rw---- 1 mysql mysql 86 4월 15 01:44 slave-bin.001
-rw-rw---- 1 mysql mysql 845 4월 15 16:01 slave-bin.002
-rw-rw---- 1 mysql mysql 379 4월 15 23:42 slave-bin.003
-rw-rw---- 1 mysql mysql 48 4월 15 21:06 slave-bin.index
-rw-r--r-- 1 mysql root 594 4월 15 21:06 slave.err
-rw-rw---- 1 mysql mysql 3 4월 15 21:06 slave.pid
drwx------ 2 mysql mysql 1024 4월 15 00:59 test
그럼 해당 ei 라는 디렉토리로 이동하여 table 이 만들어져 있는지 확인한다
[root@howto mysql]# cd ei
[root@howto ei]# ls -la
합계 14
drwx------ 2 mysql mysql 1024 4월 15 23:38 .
drwx------ 5 mysql mysql 1024 4월 15 21:06 ..
-rw-rw---- 1 mysql mysql 126 4월 15 23:42 customer.MYD
-rw-rw---- 1 mysql mysql 2048 4월 15 23:42 customer.MYI
-rw-rw---- 1 mysql mysql 8634 4월 15 23:38 customer.frm
여기까지 했다고 해서 아무 계정이나 이 db 를 참조할 수 없다(root 제외)
먼저 MySQL 사용자 계정을 만든다. 아래는 localhost 에서 eiuser 라는 계정으로
접근할 수 있도록 한다
mysql> insert into user
-> (host,user,password)
-> values ('localhost','eiuser',password('test2222'));
Query OK, 1 row affected (0.00 sec)
마찬가지로 원격으로 MySQL 에 접속하려면 접근할 IP를 host 로 하여 계정을 추가 등록하여야 한다
아래는 IP '211.230.73.100' 에서 eiuser 로 서버에 접근함을 허락하는것이다
mysql> insert into user
-> (host,user,password)
-> values ('211.230.73.100','eiuser',password('test2222'));
Query OK, 1 row affected (0.00 sec)
그 다음 이 사용자가 접근할 수 있는 DB 를 지정해야 한다
mysql> insert into db
-> (host,db,user,select_priv,insert_priv,update_priv
-> ,delete_priv,create_priv,drop_priv,grant_priv,
-> references_priv,index_priv,alter_priv)
-> values ('%','ei','eiuser',
-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')
-> ;
Query OK, 1 row affected (0.01 sec)
user 테이블에 사용자를 insert, update 한 경우 MySQL을 꼭 재기동시켜 주어야만 한다.
MySQL 의 설치 디렉토리의 bin 에서 다음을 실행한다
[root@howto bin]# mysqladmin -uroot reload
여기서 한가지 중요한 개념인 MySQL의 권한(grant, privilege)에 대해 알아보자
user 라는 table 과 db 라는 table 의 필드들을 보면 비슷한 권한의 필드가 있을것이다
user 라는 table의 host, user, password외의 필드들은 모두 디폴트로 'N' 으로 설정되어 있다
즉 권한을 주지 않는다는 의미이다
만약 db 테이블 상에 사용자가 등록되어 있지않고 user 테이블 상에만 등록된 사용자는
user 테이블 상에서 설정된 권한을 가지고 모든 존재하는 데이타베이스에 대하여 인정받는다
이것은 보안상의 문제가 발생한다. 특정 사용자는 특정 DB 만 사용할 수 있도록 권한을 주는것이 보통이다
대부분 일반 사용자인 경우는 user 테이블 상에는 모든 권한을 'N' 로 설정하고 사용자가 사용할 DB에 대한
권한을 db라는 table 에 다시 정해주는 것이다
db 라는 table의 용도를 여기쯤에서 이해 했을것이다
방금만든 DB에 방금만든 사용자로 로그인 해보자
[root@howto bin]# mysql -ueiuser -ptest2222 ei
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5 to server version: 3.23.51-log
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> show tables;
+--------------+
| Tables_in_ei |
+--------------+
| customer |
+--------------+
1 row in set (0.01 sec)
mysql> select * from customer;
+--------+--------------+--------------+------------------------+
| name | juminid | telno | addr |
+--------+--------------+--------------+------------------------+
| 김영대 | 123456-12345 | 016-xxx-xxxx | http://www.howto.pe.kr |
+--------+--------------+--------------+------------------------+
1 row in set (0.00 sec)
5.table에 PHP 로 접속해보기
아래 소스로 php 파일을 만들어 위에서 만든 customer 테이블을 내용과 정보를 읽어본다
<?
$comin = mysql_connect("localhost", "eiuser", "eiuser"); // DB 접속
mysql_select_db("ei"); // DB 선택
$result = mysql_query("SELECT * FROM customer");
// 테이블의 field 갯수를 구한다
$fields = mysql_num_fields($result);
// 테이블의 row 개수를 구한다
$rows = mysql_num_rows($result);
// 해당 필드를 가져온 테이블 이름을 구한다.
$table = mysql_field_table($result, 0);
echo ("Your '$table' table has '$fields' fields and '$rows' records<p>");
echo "The Table has the following fields<br>";
$i=0;
while($i < $fields) {
// i 번째 필드의 type
$type = mysql_field_type($result, $i);
// i 번째 필드의 이름
$name = mysql_field_name($result, $i);
// i 번째 필드의 길이
$len = mysql_field_len($result, $i);
// i 번째 필드의 Null|Key|Default|Extra 여부
$flags = mysql_field_flags($result,$i);
echo (" Type: $type, Name: $name, Len:$len, Flags: $flags<br>");
$i++;
}
mysql_close(); // 접속 끓기
?>
|
|
