[MySQL/MariaDB] 신규 DB 생성부터 테이블 생성까지

MySQL 및 MariaDB에서 신규 DB 생성과 해당 DB에 권한을 가진 유저 생성, 그리고 Table 생성까지 알아보자.

 

Prerequisite: 기존에 DB서버가 설치되어 있고, root 계정으로 접속이 가능한 상태여야 한다.

Use case: AWS나 in-house 서버에 이미 MySQL/MariaDB가 설치되어 있고, 신규 시스템 개발 시 필요한 신규 DB를 생성할 때 사용.

 

 

1. MySQL/MariaDB 접속

$>mysql -u root -p
Enter password : 패스워드 입력

일반 사용자로 접속할 때에는 root 대신 사용자 계정 입력

2. 현재 생성되어 있는 Database 조회

mysql> show databases;

3. 신규 Database 생성

mysql> create database [DB명];

주의: mysql 은 대소문자를 가린다

 

4. 생성한 Database에 신규 계정 및 암호, 접속권한까지 한 번에 생성

mysql> GRANT ALL PRIVILEGES ON [DB명].* TO '[계정]'@'%' IDENTIFIED BY '[계정 비밀번호]';

생성한 계정에 명시한 DB의 모든 권한을 주고 %로 외부 접속 가능한 권한 추가

 

[참고] usage 권한 만 줄 경우

mysql> GRANT USAGE ON *.* to root@'%' IDENTIFIED BY '[비밀번호]';

 

5. 유저 목록 조회

mysql> SELECT user FROM user;

 

6. Database 선택

mysql> use [DB명];

 

7. 선택한 Database에 Table 생성

 

mysql> 
CREATE TABLE `[테이블명]` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`email` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`name` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`birth` DATE NULL DEFAULT NULL,
	`gender` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '남:1 여:2',
	`status` TINYINT(4) NOT NULL DEFAULT '1',
	`created_date` TIMESTAMP NOT NULL DEFAULT current_timestamp(),
	`updated_date` TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
	PRIMARY KEY (`id`) USING BTREE,
	UNIQUE INDEX `email` (`email`) USING BTREE,
	UNIQUE INDEX `member_name_birth_gender` (`name`, `birth`, `gender`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=10000
;

테이블 생성의 경우는 HeidiSQL이나 DBeaver와 같은 무료 툴을 통해서 생성하는 것이 조금 더 편하다.

특히, DBeaver는 ERD까지 그려주기 때문에 활용도가 높은 편이다.

 

8. 선택한 Database의 Table 리스트 

mysql> show tables;

 

여기까지하면 신규 DB, 신규 계정, Table까지 생성 완료된다.

---

 

 

그 외 유지 관리를 위한 설정들

 

유저 권한 조회

mysql> SHOW GRANTS FOR [계정]@'localhost'; #로컬 환경만 조회 시
mysql> SHOW GRANTS FOR [계정]@'%'; #외부 접속 환경만 조회 시

 

유저 권한 삭제

mysql> REVOKE ALL PRIVILEGES ON [DB명].* FROM '[계정]'@'%';

 

유저 삭제

mysql> DROP USER '[계정]'@'%';

 

Table 삭제

mysql> DROP TABLE IF EXISTS [Table명];

 

Database 삭제

mysql> DROP DATABASE [DB명];

웬만하면 이런 명령어는 날리지 말자. Backup은 하셨나요?

 

변경된 환경 설정 즉시 적용

mysql> FLUSH PRIVILEGES;