mysql 사용법[콘솔이용]

Mysql 실행과 종료

SQL 클라이언트 프로그램

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

mysql client의 실행

./bin/mysql을 실행시킨다. (mysql 실행 파일이 존재하는 bin 디렉토리를 경로명에 추가하면 mysql만 실행시키면 되지만 기본 값인 ./bin/mysql로 설명한다.)

[root@server mysql]# ./bin/mysql
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 7 to server version : 3.22.30
Type 'help' for help.

mysql>

mysql client 프로그램이 실행되면서 "mysql>" 다음에 SQL 문을 입력하여 database 내의 데이터를 조작할 수 있게 된다.

mysql client의 종료

mysql client에서 빠져 나오려면 "\q" 나 "exit" 명령어를 준다.

[root@server mysql]# ./bin/mysql 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version : 3.22.30
Type 'help' for help. 
mysql> \q 
Bye 
[root@server mysql]#

DB/table 정보 출력

데이터베이스와 client정보 출력
MySQL 데이터베이스를 설치하면 기본적으로 mysql과 test라는 이름을 갖는 두 개의 데이터베이스가 MySQL server에 생성된다.
show databases : MySQL 서버에 생성되어 있는 데이터베이스의 목록을 출력

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

Type 'help' for help.
mysql> show

databases;

Database

mysql test

./bin/mysql db_name

db_name db로 접속
사용자의 데이터베이스나 그 안의 테이블을 보려면 mysql client 프로그램을 실행할 때 연결하고자 하는 데이터베이스를 지정해준다.

[root@ns1 mysql]# ./bin/mysql 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 3009 to server version: 3.22.30 
Type 'help' for help. mysql>

show tables : 데이터베이스내의 테이블 리스트를 출력

mysql> show tables;

Tables in mysql

columns_priv db func host tables_priv user

show columns from db

테이블 db내의 열(필드)에 대한 정보를 출력

mysql> show columns from db; // or mysql> desc db; 

Field	Type	Null	Key	Default	Extra
Host	char(60)		PRI
Db	char(32)		PRI
User	char(16)		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>

DB 사용권한 설정

데이터베이스의 사용 권한 설정
mysql라는 데이터베이스에는 MySQL server에 저장되어 있는 모든 데이터베이스에 대한 정보를 담고 있어 '마스터 데이터베이스'라 할 수 있다.
현재 mysql라는 db에는 columns_priv, db, func, host, tables_priv, user의 6개 테이블이 들어있다.
user 테이블은 Host, User, Password 등 사용자 정보와 해당 사용자가 데이터베이스에 대해 가질 수 있는 권한에 대한 정보로 구성되어 있다.

user 테이블에서 설정한 권한은 MySQL server에 저장되어 있는 모든 database에 적용된다. 고로 새로운 사용자를 user 테이블에 등록할 때는 Host,User,Password를 제외한 나머지 권한은 허가하지 않음 ('N')으로 설정해야 한다.

mysql> select host,user,password from user; 

host	user	password
localhost	root	********
ns1.nettee.net	root
localhost
ns1.nettee.net
localhost	server	********
localhost	user1	********
localhost	user2	********
localhost	user3	********
localhost	hosting	********

9 rows in set (0.00 sec)

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

위의 root 사용자의 패스워드는 8자리로 지정되어 있는데 데이터베이스의 안전한 관리를 위해 주기적으로 바꾸어 줄 필요가 있다.
root 사용자의 패스워드를 로 바꾸어 보자.

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

mysql> update user set password = password('*****') 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	*****
ns1.nettee.net	root
localhost
ns1.nettee.net
localhost	server	********
localhost	user1	********
localhost	user2	********
localhost	user3	********
localhost	hosting	********

변경된 암호를 사용해야 하는 시점은 MySQL server가 재 기동되는 순간부터이다.
즉, INSERT나 UPDATE 명령으로 user 테이블의 사용자 정보를 변경시킨 후에는 다음과 같이 MySQL server를 한번 재 기동시켜야 한다.

[root@server mysql]#./bin/mysqladmin -u root -p reload 
Enter password : ******** 

이제 mysql에 접속할 때 새로운 패스워드로 접속해야 한다.
-p 옵션은 암호를 입력하겠다는 뜻이다. (-p 옵션 뒤에 바로 붙여서 암호를 입력하는 방법이 있지만 안전한 방법이 아니므로 생략한다)

새로운 DB 생성

새로운 데이터베이스 생성

이제 새로운 데이터베이스 (hosting)을 생성하고 이 데이터베이스 내에 web이라는 테이블을 생성해 보자.
MySQL에서 데이터베이스를 생성하는 방법은 두 가지이다.

mysqladmin 실행파일을 이용하는 방법이다.

[root@server mysql]# ./bin/mysqladmin -u root -p create hosting 
Enter password : ***** 
Database "hosting" created. 
[root@server mysql]#

CREATE & DROP

mysqladmin으로 데이터베이스를 생성하려면 "create [생성할 DB명]" 구문을 사용하며, 생성한 데이터베이스를 삭제하려면 "drop [생성된 database명]" 구문을 사용한다.

[root@server mysql]#./bin/mysqladmin -u root -p drop hosting 
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 'hosting' database [y/n] 
y 
Database "hosting" dropped 
[root@server mysql]#

mysql client로 mysql database에 연결하여 database를 생성하는 방법

mysql client에서 DB를 생성하려면 "create database [생성할 DB명]" 구문을 사용 한다. 'hosting' DB를 생성한 후 show databases 명령으로 확인할 수 있다.
mysql client에서 DB를 삭제하려면 "drop database [삭제할 DB명]" 구문을 사용.

mysql> create database hosting; 
Query OK, 0 rows affected (0.00 sec) 
mysql> show databases; 

Database

mysql hosting test

database가 생성되면 mysql 디렉토리 밑의 data 디렉토리 밑에 생성된 database와 동일한 이름의 디렉토리(hosting)가 만들어진다.

[root@server mysql]# cd /usr/local/mysql/data/
[root@server data]# ls -l 
total 6 
drwx------ 2 root root ----- mysql/ 
drwx------ 2 root root ----- hosting/
drwx------ 2 root root ----- test/ 
[root@server data]# 

hosting 데이터베이스내에 테이블을 생성할 경우

해당 테이블에 대한 정의를 담은 파일(table_name.frm)과 인덱스 파일(table_name.ism), 데이터 파일(table_name.isd)들이 테이블과 동일한 이름으로 /usr/local/mysql/data/hosting 디렉토리 내에 각각 생성된다.

생성된 DB 등록
생성된 데이터베이스의 등록 - db 테이블

생성한 hosting이라는 DB를 어느 사용자에게 사용 허가를 할 것인지를 mysql database에 등록시켜 주어야 하는데 등록하면 mysql 데이터베이스의 db table 에 정보가 저장된다.
db 테이블은 DB명(db)과 db를 사용할 권한을 갖는 사용자(user) 및 호스트(host)와 이 db를 조작할 수 있는 여러 가지 권한에 대한 설정항목을 갖는다.
아래는 mysql 데이터베이스의 db 테이블 내용이다.

mysql> show columns from db; // or mysql> desc db;

Field	Type	Null	Key	Default	Extra
Host	char(60)		PRI
Db	char(32)		PRI
User	char(16)		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> -
db 테이블에서 host, db, user를 제외한 나머지 권한에 관한 항목은 기본 값이 'N' 인데 여기서 'server'라는 사용자에 한하여 "hosting"이라는 데이터베이스를 사용할 수 있도록 하기 위해 모든 권한을 'y'로 설정하여 부여한다.

host_name : localhost
db_name : hosting
user_id : server
password : ******

mysql> insert into db values ('%','hosting','server','y','y','y','y','y','y','y','y','y','y') \g
Query OK, 1 row affected (0.03 sec)
mysql> select host,db,user from db;

host	db	user
%	test
%	test\_%
%	hosting	server

3 rows in set (0.00 sec) mysql> -

이제 "server"라는 사용자는 "hosting"이라는 데이터베이스에 대하여 db 테이블에 정의된 권한을 갖는다.
새로운 DB "hosting"을 db 테이블에 등록했으므로 MySQL server를 재 기동시켜야 한다.

새로운 사용자 등록
새로운 사용자 (user/데이터베이스 소유자) 등록 - user 테이블
생성된 "hosting" 데이터베이스를 사용할 "server"라는 사용자를 등록해야 한다.
새로운 사용자는 user 테이블에 등록한다. (/usr/local/mysql/data/mysql)

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

host	user	password
localhost	root	******
ns1.nettee.net	root
localhost
ns1.nettee.net
localhost	server	******
localhost	user1	********
localhost	user2	********
localhost	user3	********
localhost	hosting	********

mysql> select host,user,password from user;
9 rows in set (0.00 sec)

host, user, password를 제외한 나머지 권한 설정 관련 항목의 기본 값이 모두 'N'이므로 데이터 삽입시 host와 user, password 값만 입력하였다.
user 테이블에서 설정한 권한은 MySQL server에 생성되는 모든 데이터베이스에 적용되므로 "server"와 같은 일반 사용자를 등록할 때는 "server"가 다른 DB에 접근 하지 못하도록 권한 설정항목을 모두 'N'으로 한다

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

따라서 일반적으로 새로운 사용자를 등록할 때에는 user 테이블의 권한은 모두 'N' 으로 하고 각 데이터베이스 소유자의 권한은 db 테이블에서 따로 설정해 주는 것이 안전하다.
새로운 사용자 "server"를 user 테이블에 등록했으므로 MySQL server를 재 기동시켜야 한다.


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

이제 "hosting" 데이터베이스와 소유자 "server"를 mysql database의 db 테이블과 user 테이블에 각각 등록하였으므로 "server"라는 사용자 계정으로 hosting DB에 연결할 수 있다.
연결이 되면 여러 가지 SQL 문을 이용하여 hosting database내에 새로운 테이블을 생성하거나 삭제, 변경할 수 있고 테이블에 데이터를 삽입, 수정, 삭제, 검색할 수도 있다.

[root@server mysql]# ./bin/mysql -u server -p hosting
Enter password : ***** 
Welcome to the MySQL monitor.
Commands end with ; or \g. 
Your MySQL connection id is 9 to server version: 3.22.24 
Type 'help' for help.
mysql> show tables; 
Empty set (0.00 sec) 
mysql> - 

hosting database에 table_name 테이블을 CREATE TABLE을 이용하여 생성한다.

mysql> create table table_name( 
  -> name CHAR(8), 
  -> id VARCHAR(10) NOT NULL,
  -> url VARCHAR(40),
  -> email VARCHAR(40),
  -> milage INT(11),
  -> level CHAR(2),
  -> PRIMARY KEY(id)
  -> ) \g 
  
Query OK, 0 rows affected (0.00 sec) 
mysql> -

desc 명령으로 table_name 테이블의 구조 명세를 살펴보자.

mysql> desc table_name \g

Field	Type	Null	Key	Default	Extra
name	varchar(8)	YES		NULL
id	varchar(10)		PRI
url	varchar(40)	YES		NULL
email	varchar(40)	YES		NULL
milage	int(11)	YES		NULL
level	char(2)	YES		NULL

테이블 내용을 수정하려면 ALTER TABLE 문을 이용한다.

table_name 테이블에서 id 필드의 데이터 타입을 varchar(10)에서 varchar(12)로 수정해 보자.

mysql> alter table table_name change column id id varchar(12) NOT NULL \g
Query OK, 0 rows affected (0.02 sec)
Records : 0 Duplicates : 0 Warnings : 0 
mysql> desc table_name \g

Field	Type	Null	Key	Default	Extra
name	varchar(8)	YES		NULL
id	varchar(12)		PRI
url	varchar(40)	YES		NULL
email	varchar(40)	YES		NULL
milage	int(11)	YES		NULL
level	char(2)	YES		NULL

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

mysql> drop table table_name \g 
Query OK, 0 rows affected (0.01 sec)
mysql> show tables \g 
Empty set (0.00sec)

mysql client를 이용하지 않고 데이터베이스내에 테이블을 생성하는 방법

아래의 SQL문을 table_name.sql이라는 파일로 저장한다.

create table table_name ( 
  name CHAR(8), 
  id VARCHAR(10) NOT NULL, 
  url VARCHAR(40), 
  email VARCHAR(40),
  milage INT(11),
  level CHAR(2),
  PRIMARY KEY(id)) \g

이제 계정의 커맨드 라인에서 아래와 같은 명령을 내리면 mysql client를 통해 hosting DB에 연결하지 않고도 데이터베이스내에 table_name 테이블을 생성.

아래 명령은 server라는 사용자로 hosting 데이터베이스에 연결하여 table_name.sql 파일에 담긴 sql문을 실행한다는 의미이다.
(주의 : -u와 -p에 공백없이 user와 password를 입력)

[root@server mysql]#./bin/mysql -userver -p***** hosting < table_name.sql