[MySQL] 2. database schema 조회, 생성, 변경, 삭제, 이름변경

이전 시간에서 MySQL 설치 후 root 관리자 계정의 비밀번호를 재설정하는 과정을 진행했습니다.

root 계정은 관리자 계정으로, 모든 권한을 보유하고 있는 계정입니다.
이런 root 계정을 모두가 이용한다면 보안상의 문제가 발생되기 쉬울 것입니다.
필요에 따라 사용자를 생성하고, 각 사용자마다 최소한의 권한을 부여하는 것이 database 보안에 유리합니다.

DBMS를 활용하는 과정에는 아래와 같은 과정이 필수적으로 다뤄야 합니다.

• database schema 조회/생성/변경/제거
• table schema 조회/생성/변경/제거
• 사용자 계정 생성
• 사용자 계정별 권한 부여
• DML을 활용한 데이터 추가/수정/삭제

이 중 이 포스팅에서는 database schema 조회/생성/변경/삭제 하는 방법에 대해 알아볼 것입니다.

1. database schema 조회
2. database schema 생성
3. database schema 변경
4. database schema 삭제
5. database 이름 변경
   1. rename 활용
   2. mysqldump 활용

---

1. database 조회

먼저, 기존에 생성되어있는 database 목록을 출력해볼까요?

[jini@apple ~]$ mysql -u root -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysql 접속 후 show databases 쿼리를 통해 현재 로그인한 사용자가 조회할 수 있는 database 목록을 출력할 수 있습니다.

2. database 생성

database 생성은 CREATE DATABASE `데이터베이스명` [DEFAULT CHARACTER SET 문자 셋] [COLLATE 정렬방식] 으로
대괄호로 감싸진 문자셋과 정렬방식은 생략가능한 옵션입니다.

문자셋과 정렬방식을 설정하지 않는다면 mysql server에 default 설정된 문자셋과 정렬방식으로 자동 생성됩니다.

CREATE DATABASE `jiniworld_test`;

우리는 지난 시간에 my.cnf에 character-set-server, collation-server 에 문자셋과 정렬방식을 설정했습니다. [참고]

따라서, 위와 같이 문자셋과 정렬방식을 생략할 경우에는 문자셋은 utf8mb4로, 정렬방식은 utf8mb4_unicode_ci로 설정됩니다.

생성한 database 정보는 SHOW CREATE DATABASE 쿼리로 확인해 볼 수 있습니다.

mysql> SHOW CREATE DATABASE `jiniworld_test`;
+----------------+------------------------------------------------------------------------------------------------------------------------------------------+
| Database       | Create Database                                                                                                                          |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------+
| jiniworld_test | CREATE DATABASE `jiniworld_test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------+

직접 문자셋과 정렬방식을 설정하는 방법은 아래와 같습니다.

CREATE DATABASE `jiniworld_test`
DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;

CREATE DATABASE `jiniworld_test`
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

만일 😲👌💳와 같은 이모지 정보를 table 내에 담고 싶다면 utf8mb4 문자셋을 이용하면 됩니다.

3. database schema 변경

ALTER DATABASE `jiniworld_test`
DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

기존에 생성했던 database의 문자셋과 정렬방식은 ALTER 쿼리를 통해 변경할 수 있습니다.
기본적으로 database에서 문자셋이나 컬럼타입과 같은 구조의 변경에는 ALTER 쿼리를 씁니다.

4. database schema 제거

database 삭제는 CREATE DATABASE `데이터베이스명` 입니다.

DROP DATABASE `jiniworld_test`;

5. database 이름 변경

MySQL에서는 SQL 한줄을 이용한 database 이름 변경 기능이 없습니다.
그렇기 때문에, database 이름을 변경하고 싶다면 새로운 database를 생성한 후, 기존의 database 내에 들어있는 데이터를 옮겨야 합니다.

5.1. rename 활용

변경하고자 하는 database 이름으로 database 를 생성합니다.

mysql> CREATE DATABASE jiniworld_log;

원본 데이터가 들어있는 database 에 들어가, table 목록을 출력합니다.

mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| mail_log       |
| table_sequence |
| user_log       |
+----------------+

RENAME을 이용하여 table의 database를 변경합니다.
RENAME TABLE `원본 database명`.`원본 table명` TO `변경할 database명`.`변경할 table명`;

현재 원본 database인 test를 사용하고 있기 때문에 `원본 database명`은 생략할 수 있습니다.

RENAME TABLE user_log TO jiniworld_log.user_log;
RENAME TABLE table_sequence TO jiniworld_log.table_sequence;
RENAME TABLE mail_log TO jiniworld_log.mail_log;

※ 테이블명은 변경하지 않고 그대로 생성했습니다.

mysql> show tables;
Empty set

mysql> use jiniworld_log;
mysql> show tables;
+-------------------------+
| Tables_in_jiniworld_log |
+-------------------------+
| mail_log                |
| table_sequence          |
| user_log                |
+-------------------------+

RENAME을 실행 한후, test 데이터베이스의 테이블 목록을 출력해봅니다.
새로 생성한 데이터베이스로 이동이 되어 Empty set 이라고 출력되네요.
그리고, 새로 생성한 데이터베이스에서는 이동된 테이블 목록을 확인할 수 있습니다.

데이터베이스 이동을 모두 마쳤다면, test 데이터베이스를 제거합니다.

DROP DATABASE test;

5.2. mysqldump 활용

mysqldump 를 이용하여 database 의 DDL 및 DML을 dump 파일로 export 하고, 그 dump 파일을 이용하여 새 데이터베이스에 값을 옮깁니다.

rename 를 이용하는 것과 달리, 새로 값을 복사하여 구성하는 것이라고 보면 됩니다.

mysqldump -u 사용자명 -p 원본 데이터베이스명 > 덤프파일

[jini@apple ~]$ cd /usr/local/apple
[jini@apple apple]$ mkdir box_sql
[jini@apple apple]$ cd box_sql
[jini@apple box_sql]$ mysqldump -u root -p jiniworld_log > ./test.sql

현재 위치인 /usr/local/apple/box_sql 디렉토리에 test.sql 이름으로 dump 파일을 만듭니다.

[jini@apple box_sql]$ mysql -u root -p
mysql> create database test;
mysql> use test;
mysql> source ./test.sql

그리고 사용자 로그인 후, database를 생성하고
새로 생성한 database에 들어가, source 명령어를 통해 덤프파일을 실행합니다.

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| mail_log       |
| table_sequence |
| user_log       |
+----------------+

show tables 와 select를 실행하여 정상적으로 복사되었는지 확인을 해봅니다.

DROP DATABASE jiniworld_log;

데이터가 모두 복사되었다면, 원본 데이터베이스를 제거합니다.