#6 데이터베이스 구축 — MariaDB & SQLyog

 

📌 서비스 운영을 위한 데이터베이스 구축

 

웹 서비스는 대부분 데이터베이스와 함께 동작한다. 회원 정보, 게시글, 상품 목록 등 모든 데이터를 저장하고 꺼내오는 역할을 DB가 담당한다. 이번 편에서 MariaDB를 먼저 구축해 두는 이유는 다음 편(FTP 서버 구축)에서 Pure-FTPd가 MariaDB와 연동되기 때문이다. DB가 먼저 세팅되어 있어야 FTP 사용자 정보를 DB로 관리할 수 있다.

 

MariaDB 설치

→

초기 설정

→

.계정 생성

→

SQLYog 연결

 

---

📌 미리 알아두면 좋은 개념

 

❑ MariaDB란? 

• MariaDB : 무료 오픈소스 관계형 DB(RDBMS). MySQL에서 파생되어 거의 동일한 문법을 사용하며, MySQL을 직접 대체할 수 있다.
• RDBMS : 관계형 데이터베이스 관리 시스템. 데이터를 테이블(행/열) 구조로 저장하고 SQL로 조회·수정한다.
• SQLYog : MariaDB/MySQL을 GUI로 관리하는 외부 접속 툴. 원격 서버의 DB를 로컬 PC에서 편리하게 다룰 수 있다.
• 포트 3306 : MariaDB/MySQL의 기본 포트. SQLYog 같은 외부 툴로 접속하려면 포트포워딩에 3306을 등록해야 한다.

 

❑ 권한 범위 3단계

MariaDB에서 GRANT 명령어로 권한을 줄 때, 범위를 어떻게 지정하느냐가 중요하다.

*.*                                  모든 데이터베이스, 모든 테이블 — 관리자(root급) 계정에 사용
db이름.*특정              DB의 모든 테이블 — 일반 사용자 계정에 권장
db이름.테이블명        특정 DB의 특정 테이블만 — 최소 권한 원칙 적용 시

 

❑ utf8mb4를 써야 하는 이유

  utf8   vs   utf8mb4  

MariaDB의 기본   utf8  은 실제로 3바이트만 지원해서 이모지나 일부 한자가 깨진다.   utf8mb4  는 진짜 4바이트 UTF-8로, 한글/이모지/특수문자를 모두 정상 저장할 수 있다. 처음 설치할 때 반드시   utf8mb4  로 설정해 두는 것이 좋다.

 

❑ 접속 호스트 표기 차이

• localhost : 서버 내부에서만 접속 가능. Unix 소켓 방식으로 동작하며 보안상 가장 제한적이다.
• 127.0.0.1 : 루프백 IP로 접속. TCP 방식으로 동작하며 SQLYog 같은 툴 연결에 사용한다.
• % (와일드카드) : 모든 호스트에서 접속 허용. 외부 접속을 허용할 때 사용하며 SQLYog 원격 연결에 필요하다.

 

---

📌 진행한 실습 과정 정리

 

❑ MariaDB 최신 버전 설치

기본 yum 저장소의 MariaDB는 구버전이다. 공식 repo 파일을 직접 만들어 최신 버전을 설치한다. 기존에 설치된 MariaDB가 있다면 먼저 깔끔하게 삭제한다.

# 기존 MariaDB 완전 삭제 (라이브러리까지)
sudo yum remove mariadb 0y
sudo rm -rf /var/lib/mysql
sudo rm -rf /etc/my.cnf*

# repo 파일 생성
sudo vim /etc/yum.repos.d/MariaDB.repo

# 아래 내용 추가 (버전이 바뀌면 11.3 부분을 수정)
[mariadb]
name = MariaDB
baseurl = https://tw1.mirror.blendbyte.net/mariadb/yum/11.3/centos/$releasever/$basearch
module_hotfixes = 1
gpgkey = https://tw1.mirror.blendbyte.net/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1

repo 파일에 내용 추가

# my.cnf 파일에 설정 디렉토리 지정
sudo vim /etc/my.cnf

# 아래 내용 추가
!includedir /etc/my.cnf.d

# MariaDB 서버 + 클라이언트 설치
sudo yum install MariaDB-server MariaDB-client -y

💡 MariaDB 버전은 주기적으로 업데이트된다. repo 파일의 11.3 부분을 최신 버전 번호로 바꾸면 된다. 현재 최신 버전은 mariadb.org에서 확인할 수 있다.

 

❑ 인코딩 (utf8mb4) 설정

한글 데이터가 깨지지 않도록 서버와 클라이언트 모두 utf8mb4로 설정한다. 설치 직후 서비스 시작 전에 반드시 해둬야 한다.

# 서버 인코딩 설정
sudo vim etc/my.cnf.d/server.cnf

# [mysqld] 아래에 추가
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
skip-character-set-client-handshake

서버 인코딩 설정 추가

# 클라이언트 인코딩 설정
sudo vim /etc/my.cnf.d/mysql-clients.cnf

# [mysql] 과 [mysqldump] 아래 각각 추가
[mysql]
default-character-set = utf8mb4

[mysqldump]
default-character-set = utf8mb4

클라이언트 인코딩 설정 추가

💡 MariaDB를 먼저 시작한 뒤에 인코딩을 바꾸면 기존 DB는 적용되지 않는다. 설치 직후, 서비스 시작 전에 설정 파일을 먼저 수정하는 것이 원칙이다.

 

❑ MariaDB 시작 및 초기 보안 설정

# MariaDB 자동 시작 등록 및 시작
sudo systemctl enable mariadb
sudo systemctl start mariadb

# 초기 보안 설정 (최초 1회만 실행)
sudo /usr/bin/mariadb-secure-installation

# 보안 설정 과정에서 아래와 같이 응답한다. 
Enter current password for root (enter for none):	Enter
Switch to unix_socket authentication: 			N
Change the root password: 				Y  # 비밀번호 설정
Remove anonymous users: 				Y
Disallow root login remotely: 				Y
Remove test database: 					Y
Reload privilege tables: 				Y

💡 Disallow root login remotely: Y 로 설정하면 외부에서 root 계정으로 직접 접속할 수 없다. SQLYog 연결 시에는 별도 관리자 계정을 만들어 사용한다.

# 접속 확인
mysql -h 127.0.0.1 -u root -p

접속 확인

 

❑ 내부 접속 계정 생성

root 계정을 직접 외부에 노출하지 않기 위해 별도 관리자 계정을 만든다.

-- 패턴 1: 관리자용 계정 생성 (모든 DB 접근 허용)
GRANT ALL PRIVILEGES ON *.* TO 계정이름@`%` IDENTIFIED BY '비밀번호';

-- 패턴 2 : 일반 사용자 계정 생성 (특정 DB만 접근 허용 - 권장)
CREATE DATABASE myapp;
GRANT ALL PRIVILEGES ON myapp.* TO myapp_user@`%` IDENTIFIED BY '비밀번호';

-- 생성된 계정 확인
SELECT user, host FROM mysql.user;

# root 계정으로 접속
mysql -u root -p


# 관리자 계정 생성
GRANT ALL PRIVILEGES ON *.* TO 계정이름@`%` IDENTIFIED BY '비밀번호';

# 권한 즉시 적용
FLUSH PRIVILEGES;

# root 로그아웃
exit

# 생성한 계정으로 재로그인 확인
/usr/bin/mariadb -u 계정이름 -p

💡 권한을 변경한 후 FLUSH PRIVILEGES를 실행해야 변경사항이 즉시 반영된다. 이 명령을 빠뜨리면 재시작 전까지 권한이 적용되지 않는다.

 

❑ SQLYog 설치 및 연결

SQLYog는 원격에서 MariaDB를 GUI로 관리하는 툴이다. 먼저 VirtualBox 포트포워딩에 3306 포트를 추가해야 한다.

1. VirtualBox → 포트포워딩 → 규칙추가 (호스트포트 : 임의 설정 / 게스트 포트 : 3306)
2. SQLYog 설치 후 실행 → 파일 → 새 연결
3. 정보 입력 후 연결
4. 연결 성공 후 폰트 설정과 삽입 공간 체크
5. 쿼리창에서 SHOW DATABASES; 실행 → PuTTY와 동일한 결과 확인

포트포워딩

SQLYog 연결 정보

호스트                      :   127.0.0.1
사용자                      :   위에서 생성한 관리자 계정 (root 아님)
비밀번호                  :   설정한 비밀번호
포트                          :   포트포워딩에서 설정한 호스트 포트
데이터베이스          :   (비워도 됨)

💡 SQLYog에서 실행한 SQL과 PuTTY에서 실행한 SQL은 같은 MariaDB에 연결되어 있기 때문에 결과가 동일하다. 단순 조회는 SQLYog 가 편하고, 서버 설정이나 파일 작업은 PuTTY로 하는 식으로 역할을 나눠 사용하면 효율적이다.

 

❑ 자주 쓰는 MariaDB 명령어 모음

-- 데이터베이스 목록 보기
SHOW DATABASES;

-- 데이터베이스 선택
USE db이름;

-- 테이블 목록 보기
SHOW TABLES;

-- 테이블 구조 확인
DESCRIBE 테이블명; (DESC)

-- 현재 인코딩 확인
SHOW VARIABLES LIKE 'character%';

-- 계정 및 권한 목록 확인
SELECT user, host FROM mysql.user;
SHOW GRANTS FOR 계정이름@`%`;

 

---

⚠️ 오류 / 주의

 

🚨 오류 — SQLYog 연결 오류 (Access denied)
계정 생성 시 호스트를 localhost로 지정하면 외부(SQLYog)에서 접속이 거부된다. 외부 접속 허용은 반드시 호스트를 %로 지정해야 한다.

🚨 오류 — 한글 데이터가 ???로 깨질 때
인코딩 설정을 MariaDB 시작 후에 변경했거나, server.cnf와 mysql-clients.cnf 중 하나를 빠뜨린 경우다. 설정 파일을 다시 확인하고 sudo systemctl restart mariadb로 재시작한다. SHOW VARIABLES LIKE 'character%';로 모든 항목이 utf8mb4인지 확인한다.

 

---

📌 요약

 

✔️ 공식 repo를 추가해 MariaDB 최신 버전 설치

✔️ 서비스 시작 전에 utf8mb4 인코딩을 설정

✔️ MariaDB의 초기 보안 설정 마침

✔️ root 대신 별도 관리자 계정을 만들어 보안 원칙을 준수

✔️ 포트포워딩 3306 추가 후 SQLYog로 외부 GUI 접속에 성공

 

---

다음 편으로 이동  🔜 #7 FTP 서버 구축 — Pure-FTPd & FileZilla & 호스트 전용 어댑터