VIPS/docs/리눅스 rocky MariaDB 설정.md

# Rocky Linux 9.5 MariaDB 설치 및 설정 가이드

## 요구사항
1. Rocky Linux 9.5 version
2. root 권한 또는 sudo 권한이 있는 계정
3. 목표: MariaDB 설치, 보안 설정, 사용자 생성, 데이터베이스 생성, 백업 스키마 복원

---

## 1단계: MariaDB 설치

### 1.1 MariaDB 서버 설치
```bash
# MariaDB 서버 및 클라이언트 설치
dnf install -y mariadb-server mariadb

# 또는 yum 사용
yum install -y mariadb-server mariadb
```

### 1.2 MariaDB 서비스 시작 및 자동 시작 설정
```bash
# MariaDB 서비스 시작
systemctl start mariadb

# 부팅 시 자동 시작 설정
systemctl enable mariadb

# 서비스 상태 확인
systemctl status mariadb
```

### 1.3 MariaDB 버전 확인
```bash
# MariaDB 버전 확인
mysql --version

# 또는 MariaDB 접속 후 확인
mysql -e "SELECT VERSION();"
```

---

## 2단계: MariaDB 보안 설정

### 2.1 초기 보안 설정 실행
```bash
# MariaDB 보안 설정 스크립트 실행
mysql_secure_installation
```

### 2.2 보안 설정 질문 및 권장 답변
설정 중 아래와 같은 질문이 나타납니다:

```
1. Enter current password for root (enter for none):
   → 처음 설치 시 비밀번호가 없으므로 Enter 입력

2. Switch to unix_socket authentication [Y/n]
   → n (기존 방식 유지 권장)

3. Change the root password? [Y/n]
   → Y (root 비밀번호 설정)
   → 새 비밀번호 입력 및 확인

4. Remove anonymous users? [Y/n]
   → Y (익명 사용자 제거)

5. Disallow root login remotely? [Y/n]
   → Y (원격 root 로그인 차단 - 보안 강화)
   → n (원격 접속이 필요한 경우)

6. Remove test database and access to it? [Y/n]
   → Y (테스트 데이터베이스 제거)

7. Reload privilege tables now? [Y/n]
   → Y (권한 테이블 즉시 적용)
```

### 2.3 root로 MariaDB 접속 확인
```bash
# root 계정으로 MariaDB 접속
mysql -u root -p
# 비밀번호 입력 후 접속

# 접속 확인 후 종료
MariaDB [(none)]> show databases;
MariaDB [(none)]> exit;
```

---

## 3단계: 커스텀 포트 설정 (53306)

### 3.1 MariaDB 포트 변경
```bash
# MariaDB 설정 파일 편집
vi /etc/my.cnf.d/mariadb-server.cnf
```

설정 파일에 포트 추가:
```ini
[mysqld]
port=53306
```

설정 변경 후 서비스 재시작:
```bash
# MariaDB 재시작
systemctl restart mariadb

# 포트 확인
ss -tlnp | grep 53306
```

### 3.2 방화벽 설정 (외부 접속 허용)
```bash
# 커스텀 포트(53306) 방화벽 허용
firewall-cmd --permanent --add-port=53306/tcp
firewall-cmd --reload

# 방화벽 설정 확인
firewall-cmd --list-all
```

---

## 4단계: 데이터베이스 생성

### 4.1 MariaDB 접속
```bash
# root로 MariaDB 접속
mysql -u root -p
# 비밀번호 입력
```

### 4.2 데이터베이스 생성
```sql
-- VIPS 데이터베이스 생성
CREATE DATABASE vips CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 데이터베이스 목록 확인
SHOW DATABASES;

-- VIPS 데이터베이스 선택
USE vips;
```

### 4.3 데이터베이스 문자셋 확인
```sql
-- 데이터베이스 문자셋 확인
SELECT
    SCHEMA_NAME,
    DEFAULT_CHARACTER_SET_NAME,
    DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'vips';
```

---

## 5단계: 사용자 생성 및 권한 부여

### 5.1 로컬 접속용 사용자 생성
```sql
-- VIPS 로컬 접속용 사용자 생성
CREATE USER 'vips'@'localhost' IDENTIFIED BY 'xit5811807';

-- VIPS 데이터베이스에 모든 권한 부여
GRANT ALL PRIVILEGES ON vips.* TO 'vips'@'localhost';

-- 권한 적용
FLUSH PRIVILEGES;
```

### 5.2 원격 접속용 사용자 생성
```sql
-- 특정 IP에서 접속 가능한 사용자 생성
CREATE USER 'vips'@'192.168.1.100' IDENTIFIED BY 'xit5811807';
GRANT ALL PRIVILEGES ON vips.* TO 'vips'@'192.168.1.100';

-- 모든 IP에서 접속 가능한 사용자 생성 (application-prd.yml 설정)
CREATE USER 'vips'@'%' IDENTIFIED BY 'xit5811807';
GRANT ALL PRIVILEGES ON vips.* TO 'vips'@'%';

-- 권한 적용
FLUSH PRIVILEGES;
```

### 5.3 읽기 전용 사용자 생성 (선택사항)
```sql
-- 읽기 전용 사용자 생성
CREATE USER 'vips_readonly'@'%' IDENTIFIED BY 'vips_readonly_password';
GRANT SELECT ON vips.* TO 'vips_readonly'@'%';
FLUSH PRIVILEGES;
```

### 5.4 사용자 목록 및 권한 확인
```sql
-- 사용자 목록 확인
SELECT User, Host FROM mysql.user;

-- VIPS 사용자의 권한 확인
SHOW GRANTS FOR 'vips'@'localhost';
SHOW GRANTS FOR 'vips'@'%';

-- 현재 사용자 확인
SELECT USER(), CURRENT_USER();
```

### 5.5 원격 접속을 위한 MariaDB 설정 변경
```bash
# MariaDB 설정 파일 편집
vi /etc/my.cnf.d/mariadb-server.cnf
```

설정 파일에서 다음 부분 수정:
```ini
[mysqld]
# bind-address를 주석 처리하거나 0.0.0.0으로 변경
# bind-address=127.0.0.1
bind-address=0.0.0.0

# 또는 특정 IP만 허용
# bind-address=192.168.1.50
```

설정 변경 후 서비스 재시작:
```bash
# MariaDB 재시작
systemctl restart mariadb

# 재시작 확인
systemctl status mariadb
```

---

## 6단계: 백업 스키마 업로드 (전체 데이터베이스 복원)

### 6.1 백업 파일 서버로 전송

**방법 1: SCP 사용 (로컬 PC에서 실행)**
```bash
# Windows PowerShell 또는 CMD에서 실행
scp C:\backup\vips_backup.sql root@서버IP:/tmp/
```

**방법 2: SFTP 사용**
```bash
# SFTP 접속
sftp root@서버IP

# 파일 전송
put vips_backup.sql /tmp/
exit
```

### 6.2 백업 파일 권한 설정
```bash
# 백업 파일 권한 확인
ls -la /tmp/vips_backup.sql

# 필요시 권한 변경
chmod 644 /tmp/vips_backup.sql
```

### 6.3 백업 파일 복원 - 방법 1 (명령행에서 직접 복원)
```bash
# 커스텀 포트 53306 사용하여 복원
mysql -u root -p -P 53306 vips < /tmp/vips_backup.sql

# 비밀번호 입력 없이 실행 (스크립트용)
mysql -u root -p비밀번호 -P 53306 vips < /tmp/vips_backup.sql
```

### 6.4 백업 파일 복원 - 방법 2 (MySQL 내부에서 복원)
```bash
# MariaDB 접속 (포트 53306 명시)
mysql -u root -p -P 53306
```

```sql
-- VIPS 데이터베이스 선택
USE vips;

-- 백업 파일 실행
SOURCE /tmp/vips_backup.sql;

-- 또는
\. /tmp/vips_backup.sql
```

### 6.5 백업 파일 복원 - 방법 3 (새 데이터베이스 생성 포함)
백업 파일에 CREATE DATABASE가 포함된 경우:
```bash
# 데이터베이스 이름 지정 없이 복원 (포트 53306)
mysql -u root -p -P 53306 < /tmp/vips_backup.sql
```

### 6.6 대용량 백업 파일 복원
대용량 파일의 경우 타임아웃 방지를 위한 옵션 추가:
```bash
# 타임아웃 설정 증가 (포트 53306)
mysql -u root -p -P 53306 \
  --max_allowed_packet=512M \
  --connect_timeout=3600 \
  --wait_timeout=3600 \
  vips < /tmp/vips_backup.sql
```

### 6.7 압축된 백업 파일 복원
```bash
# .gz 파일 복원 (포트 53306)
gunzip < /tmp/vips_backup.sql.gz | mysql -u root -p -P 53306 vips

# .zip 파일 복원 (포트 53306)
unzip -p /tmp/vips_backup.sql.zip | mysql -u root -p -P 53306 vips
```

---

## 7단계: 복원 결과 확인

### 7.1 MariaDB 접속
```bash
# VIPS 사용자로 접속 (포트 53306)
mysql -u vips -p -P 53306 vips
# 비밀번호 입력: xit5811807
```

### 7.2 데이터베이스 및 테이블 확인
```sql
-- 현재 데이터베이스 확인
SELECT DATABASE();

-- 테이블 목록 확인
SHOW TABLES;

-- 테이블 개수 확인
SELECT COUNT(*) AS table_count
FROM information_schema.tables
WHERE table_schema = 'vips';

-- 각 테이블의 상세 정보 확인
SHOW TABLE STATUS FROM vips;
```

### 7.3 테이블 구조 확인
```sql
-- 특정 테이블 구조 확인 (VIPS 테이블 예시)
DESC tb_vehicle_info;
-- 또는
DESCRIBE tb_vehicle_info;
-- 또는
SHOW COLUMNS FROM tb_vehicle_info;

-- 테이블 생성 DDL 확인
SHOW CREATE TABLE tb_vehicle_info;
```

### 7.4 데이터 확인
```sql
-- 특정 테이블의 레코드 수 확인
SELECT COUNT(*) FROM tb_vehicle_info;

-- 상위 10개 데이터 조회
SELECT * FROM tb_vehicle_info LIMIT 10;

-- 모든 테이블의 레코드 수 확인
SELECT
    TABLE_NAME,
    TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'vips'
ORDER BY TABLE_ROWS DESC;
```

### 7.5 인덱스 및 제약조건 확인
```sql
-- 특정 테이블의 인덱스 확인
SHOW INDEX FROM tb_vehicle_info;

-- 외래키 제약조건 확인
SELECT
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'vips'
AND REFERENCED_TABLE_NAME IS NOT NULL;
```

### 7.6 데이터베이스 용량 확인
```sql
-- VIPS 데이터베이스 전체 크기 확인
SELECT
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'vips'
GROUP BY table_schema;

-- 테이블별 크기 확인
SELECT
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'vips'
ORDER BY (data_length + index_length) DESC;
```

### 7.7 문자셋 확인
```sql
-- 데이터베이스 문자셋 확인
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- 포트 확인
SHOW VARIABLES LIKE 'port';

-- VIPS 테이블의 문자셋 확인
SELECT
    TABLE_NAME,
    TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'vips';
```

---

## 8단계: 접속 테스트 및 문제 해결

### 8.1 로컬 접속 테스트
```bash
# VIPS 사용자로 로컬 접속 테스트 (포트 53306)
mysql -u vips -p -P 53306 vips

# 접속 후 간단한 쿼리 실행
mysql -u vips -pxit5811807 -P 53306 vips -e "SELECT COUNT(*) AS table_count FROM information_schema.tables WHERE table_schema='vips';"
```

### 8.2 원격 접속 테스트 (다른 서버에서)
```bash
# 원격 서버에서 접속 테스트 (application-prd.yml 설정과 동일)
mysql -h 211.119.124.117 -P 53306 -u vips -p vips

# 비밀번호 포함하여 테스트 (스크립트용)
mysql -h 211.119.124.117 -P 53306 -u vips -pxit5811807 vips -e "SHOW TABLES;"
```

### 8.3 접속 문제 해결

**문제 1: Access denied for user**
```sql
-- MariaDB에 root로 접속하여 사용자 확인
SELECT User, Host FROM mysql.user WHERE User='vips';

-- 호스트가 일치하지 않으면 재생성
DROP USER 'vips'@'잘못된호스트';
CREATE USER 'vips'@'올바른호스트' IDENTIFIED BY 'xit5811807';
GRANT ALL PRIVILEGES ON vips.* TO 'vips'@'올바른호스트';
FLUSH PRIVILEGES;
```

**문제 2: Can't connect to MySQL server**
```bash
# MariaDB 서비스 상태 확인
systemctl status mariadb

# 서비스가 중지되어 있으면 시작
systemctl start mariadb

# 커스텀 포트 53306 리스닝 확인
ss -tlnp | grep 53306
netstat -tlnp | grep 53306

# 포트 설정 확인
mysql -u root -p -e "SHOW VARIABLES LIKE 'port';"

# 방화벽 확인
firewall-cmd --list-all
```

**문제 3: 원격 접속 불가**
```bash
# bind-address 설정 확인
grep bind-address /etc/my.cnf.d/mariadb-server.cnf

# 0.0.0.0 또는 원하는 IP로 설정되어 있는지 확인
# 변경 후 재시작 필요
systemctl restart mariadb
```

**문제 4: 복원 중 에러 발생**
```bash
# 에러 로그 확인
tail -100 /var/log/mariadb/mariadb.log

# 또는
journalctl -u mariadb -n 100

# SQL 파일 문법 확인 (일부만 실행해보기)
head -100 /tmp/myapp_db_backup.sql
```

---

## 9단계: 백업 설정 (추가 운영 관리)

### 9.1 수동 백업 명령어
```bash
# VIPS 전체 데이터베이스 백업 (포트 53306)
mysqldump -u root -p -P 53306 vips > /backup/vips_$(date +%Y%m%d_%H%M%S).sql

# 압축 백업
mysqldump -u root -p -P 53306 vips | gzip > /backup/vips_$(date +%Y%m%d_%H%M%S).sql.gz

# 특정 테이블만 백업
mysqldump -u root -p -P 53306 vips tb_vehicle_info tb_user > /backup/vips_tables_$(date +%Y%m%d).sql

# 구조만 백업 (데이터 제외)
mysqldump -u root -p -P 53306 --no-data vips > /backup/vips_schema_only.sql

# 데이터만 백업 (구조 제외)
mysqldump -u root -p -P 53306 --no-create-info vips > /backup/vips_data_only.sql
```

### 9.2 백업 디렉토리 생성 및 권한 설정
```bash
# 백업 디렉토리 생성
mkdir -p /backup/mariadb

# 권한 설정
chown root:root /backup/mariadb
chmod 700 /backup/mariadb
```

### 9.3 자동 백업 스크립트 생성
```bash
# 백업 스크립트 생성
vi /backup/mariadb_backup.sh
```

스크립트 내용:
```bash
#!/bin/bash

# VIPS 데이터베이스 백업 스크립트
DB_NAME="vips"
DB_USER="root"
DB_PASS="your_root_password"
DB_PORT="53306"
BACKUP_DIR="/backup/mariadb"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"
RETENTION_DAYS=7

# 백업 디렉토리 생성 (없으면)
mkdir -p $BACKUP_DIR

# 백업 실행
echo "Starting VIPS database backup at $(date)"
mysqldump -u $DB_USER -p$DB_PASS -P $DB_PORT $DB_NAME | gzip > $BACKUP_FILE

# 백업 결과 확인
if [ $? -eq 0 ]; then
    echo "Backup completed successfully: $BACKUP_FILE"

    # 파일 크기 확인
    FILE_SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
    echo "Backup file size: $FILE_SIZE"

    # 오래된 백업 파일 삭제 (7일 이상)
    find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -delete
    echo "Old backups cleaned up (retention: $RETENTION_DAYS days)"
else
    echo "Backup failed!"
    exit 1
fi

echo "Backup process finished at $(date)"
```

스크립트 실행 권한 부여:
```bash
chmod 700 /backup/mariadb_backup.sh
```

### 9.4 cron을 이용한 자동 백업 설정
```bash
# crontab 편집
crontab -e
```

cron 설정 추가:
```bash
# 매일 새벽 2시에 백업 실행
0 2 * * * /backup/mariadb_backup.sh >> /backup/mariadb/backup.log 2>&1

# 매주 일요일 새벽 3시에 백업 실행
0 3 * * 0 /backup/mariadb_backup.sh >> /backup/mariadb/backup.log 2>&1

# 매월 1일 새벽 4시에 백업 실행
0 4 1 * * /backup/mariadb_backup.sh >> /backup/mariadb/backup.log 2>&1
```

cron 설정 확인:
```bash
# crontab 목록 확인
crontab -l

# cron 서비스 상태 확인
systemctl status crond

# cron 로그 확인
tail -f /var/log/cron
```

---

## 전체 실행 순서 요약

```bash
# 1. MariaDB 설치
dnf install -y mariadb-server mariadb
systemctl start mariadb
systemctl enable mariadb

# 2. 보안 설정
mysql_secure_installation

# 3. 커스텀 포트 설정 (53306)
vi /etc/my.cnf.d/mariadb-server.cnf
# [mysqld] 섹션에 port=53306 추가

# 4. 원격 접속 설정
vi /etc/my.cnf.d/mariadb-server.cnf
# bind-address=0.0.0.0 설정
systemctl restart mariadb

# 5. 방화벽 설정
firewall-cmd --permanent --add-port=53306/tcp
firewall-cmd --reload

# 6. 포트 확인
ss -tlnp | grep 53306

# 7. MariaDB 접속하여 데이터베이스 및 사용자 생성
mysql -u root -p -P 53306
```

```sql
-- VIPS 데이터베이스 생성
CREATE DATABASE vips CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- VIPS 사용자 생성 및 권한 부여
CREATE USER 'vips'@'localhost' IDENTIFIED BY 'xit5811807';
GRANT ALL PRIVILEGES ON vips.* TO 'vips'@'localhost';

-- 원격 접속용 사용자 (application-prd.yml 설정)
CREATE USER 'vips'@'%' IDENTIFIED BY 'xit5811807';
GRANT ALL PRIVILEGES ON vips.* TO 'vips'@'%';

FLUSH PRIVILEGES;
exit;
```

```bash
# 8. 백업 파일 업로드 (로컬 PC에서)
scp vips_backup.sql root@서버IP:/tmp/

# 9. 백업 복원 (서버에서, 포트 53306 명시)
mysql -u root -p -P 53306 vips < /tmp/vips_backup.sql

# 10. 복원 확인
mysql -u vips -pxit5811807 -P 53306 vips
```

```sql
-- 테이블 확인
SHOW TABLES;
SELECT COUNT(*) AS table_count FROM information_schema.tables WHERE table_schema='vips';
exit;
```

---

## 유용한 MariaDB 관리 명령어

### 데이터베이스 관리
```sql
-- 모든 데이터베이스 목록
SHOW DATABASES;

-- 데이터베이스 삭제 (주의!)
DROP DATABASE vips;

-- 데이터베이스 이름 변경 (직접 지원 안함, 백업/복원 필요)
-- 방법: 백업 → 새 DB 생성 → 복원 → 기존 DB 삭제
```

### 사용자 관리
```sql
-- 모든 사용자 목록
SELECT User, Host FROM mysql.user;

-- VIPS 사용자 비밀번호 변경
ALTER USER 'vips'@'localhost' IDENTIFIED BY 'new_password';
ALTER USER 'vips'@'%' IDENTIFIED BY 'new_password';

-- 사용자 삭제
DROP USER 'vips'@'localhost';
DROP USER 'vips'@'%';

-- 특정 권한만 회수
REVOKE DELETE ON vips.* FROM 'vips'@'localhost';

-- 모든 권한 회수
REVOKE ALL PRIVILEGES ON vips.* FROM 'vips'@'localhost';
```

### 테이블 관리
```sql
-- 테이블 삭제
DROP TABLE table_name;

-- 테이블 비우기 (구조는 유지)
TRUNCATE TABLE table_name;

-- 테이블 이름 변경
RENAME TABLE old_name TO new_name;

-- 테이블 복사
CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table SELECT * FROM old_table;
```

### 성능 모니터링
```sql
-- 현재 실행 중인 쿼리 확인
SHOW PROCESSLIST;

-- 특정 프로세스 강제 종료
KILL [process_id];

-- 상태 변수 확인
SHOW STATUS;
SHOW VARIABLES;

-- 느린 쿼리 확인
SHOW VARIABLES LIKE 'slow_query%';
```

---

## 보안 권장사항

1. **강력한 비밀번호 사용**
   - 최소 12자 이상, 대소문자/숫자/특수문자 조합

2. **최소 권한 원칙**
   - 애플리케이션 사용자에게 필요한 최소 권한만 부여
   - 읽기 전용 작업에는 SELECT 권한만 부여

3. **원격 root 접속 차단**
   - root는 localhost에서만 접속 허용

4. **정기적인 백업**
   - 매일 자동 백업 설정
   - 백업 파일 주기적 확인

5. **방화벽 설정**
   - 필요한 IP만 53306 포트 접근 허용
   - 특정 IP 대역만 접근 허용 설정
   ```bash
   # 특정 IP만 허용 (예시)
   firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port protocol="tcp" port="53306" accept'
   firewall-cmd --reload
   ```

6. **MariaDB 업데이트**
   - 정기적인 보안 패치 적용
   ```bash
   dnf update mariadb-server
   ```

---

## 참고사항

- **VIPS 프로젝트 설정**
  - 데이터베이스: vips
  - 사용자: vips
  - 비밀번호: xit5811807
  - 포트: 53306 (커스텀 포트)
  - 문자셋: UTF-8 (utf8mb4)

- **MariaDB 기본 정보**
  - 기본 포트: 3306 (VIPS는 53306 사용)
  - 설정 파일 위치: `/etc/my.cnf`, `/etc/my.cnf.d/mariadb-server.cnf`
  - 데이터 디렉토리: `/var/lib/mysql/`
  - 로그 파일: `/var/log/mariadb/mariadb.log`
  - 소켓 파일: `/var/lib/mysql/mysql.sock`

---

## 문의 및 추가 정보

- MariaDB 공식 문서: https://mariadb.com/kb/
- Rocky Linux 공식 문서: https://docs.rockylinux.org/