You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

18 KiB

Raw Permalink Blame History

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

요구사항

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

1단계: MariaDB 설치

1.1 MariaDB 서버 설치

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

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

1.2 MariaDB 서비스 시작 및 자동 시작 설정

# MariaDB 서비스 시작
systemctl start mariadb

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

# 서비스 상태 확인
systemctl status mariadb

1.3 MariaDB 버전 확인

# MariaDB 버전 확인
mysql --version

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

2단계: MariaDB 보안 설정

2.1 초기 보안 설정 실행

# 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 접속 확인

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

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

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

3.1 MariaDB 포트 변경

# MariaDB 설정 파일 편집
vi /etc/my.cnf.d/mariadb-server.cnf

설정 파일에 포트 추가:

[mysqld]
port=53306

설정 변경 후 서비스 재시작:

# MariaDB 재시작
systemctl restart mariadb

# 포트 확인
ss -tlnp | grep 53306

3.2 방화벽 설정 (외부 접속 허용)

# 커스텀 포트(53306) 방화벽 허용
firewall-cmd --permanent --add-port=53306/tcp
firewall-cmd --reload

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

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

4.1 MariaDB 접속

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

4.2 데이터베이스 생성

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

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

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

4.3 데이터베이스 문자셋 확인

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

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

5.1 로컬 접속용 사용자 생성

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

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

-- 권한 적용
FLUSH PRIVILEGES;

5.2 원격 접속용 사용자 생성

-- 특정 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 읽기 전용 사용자 생성 (선택사항)

-- 읽기 전용 사용자 생성
CREATE USER 'vips_readonly'@'%' IDENTIFIED BY 'vips_readonly_password';
GRANT SELECT ON vips.* TO 'vips_readonly'@'%';
FLUSH PRIVILEGES;

5.4 사용자 목록 및 권한 확인

-- 사용자 목록 확인
SELECT User, Host FROM mysql.user;

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

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

5.5 원격 접속을 위한 MariaDB 설정 변경

# MariaDB 설정 파일 편집
vi /etc/my.cnf.d/mariadb-server.cnf

설정 파일에서 다음 부분 수정:

[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

설정 변경 후 서비스 재시작:

# MariaDB 재시작
systemctl restart mariadb

# 재시작 확인
systemctl status mariadb

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

6.1 백업 파일 서버로 전송

방법 1: SCP 사용 (로컬 PC에서 실행)

# Windows PowerShell 또는 CMD에서 실행
scp C:\backup\vips_backup.sql root@서버IP:/tmp/

방법 2: SFTP 사용

# SFTP 접속
sftp root@서버IP

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

6.2 백업 파일 권한 설정

# 백업 파일 권한 확인
ls -la /tmp/vips_backup.sql

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

6.3 백업 파일 복원 - 방법 1 (명령행에서 직접 복원)

# 커스텀 포트 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 내부에서 복원)

# MariaDB 접속 (포트 53306 명시)
mysql -u root -p -P 53306

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

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

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

6.5 백업 파일 복원 - 방법 3 (새 데이터베이스 생성 포함)

백업 파일에 CREATE DATABASE가 포함된 경우:

# 데이터베이스 이름 지정 없이 복원 (포트 53306)
mysql -u root -p -P 53306 < /tmp/vips_backup.sql

6.6 대용량 백업 파일 복원

대용량 파일의 경우 타임아웃 방지를 위한 옵션 추가:

# 타임아웃 설정 증가 (포트 53306)
mysql -u root -p -P 53306 \
  --max_allowed_packet=512M \
  --connect_timeout=3600 \
  --wait_timeout=3600 \
  vips < /tmp/vips_backup.sql

6.7 압축된 백업 파일 복원

# .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 접속

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

7.2 데이터베이스 및 테이블 확인

-- 현재 데이터베이스 확인
SELECT DATABASE();

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

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

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

7.3 테이블 구조 확인

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

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

7.4 데이터 확인

-- 특정 테이블의 레코드 수 확인
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 인덱스 및 제약조건 확인

-- 특정 테이블의 인덱스 확인
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 데이터베이스 용량 확인

-- 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 문자셋 확인

-- 데이터베이스 문자셋 확인
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 로컬 접속 테스트

# 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 원격 접속 테스트 (다른 서버에서)

# 원격 서버에서 접속 테스트 (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

-- 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

# 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: 원격 접속 불가

# bind-address 설정 확인
grep bind-address /etc/my.cnf.d/mariadb-server.cnf

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

문제 4: 복원 중 에러 발생

# 에러 로그 확인
tail -100 /var/log/mariadb/mariadb.log

# 또는
journalctl -u mariadb -n 100

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

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

9.1 수동 백업 명령어

# 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 백업 디렉토리 생성 및 권한 설정

# 백업 디렉토리 생성
mkdir -p /backup/mariadb

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

9.3 자동 백업 스크립트 생성

# 백업 스크립트 생성
vi /backup/mariadb_backup.sh

스크립트 내용:

#!/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)"

스크립트 실행 권한 부여:

chmod 700 /backup/mariadb_backup.sh

9.4 cron을 이용한 자동 백업 설정

# crontab 편집
crontab -e

cron 설정 추가:

# 매일 새벽 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 설정 확인:

# crontab 목록 확인
crontab -l

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

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

전체 실행 순서 요약

# 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

-- 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;

# 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

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

유용한 MariaDB 관리 명령어

데이터베이스 관리

-- 모든 데이터베이스 목록
SHOW DATABASES;

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

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

사용자 관리

-- 모든 사용자 목록
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';

테이블 관리

-- 테이블 삭제
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;

성능 모니터링

-- 현재 실행 중인 쿼리 확인
SHOW PROCESSLIST;

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

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

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

보안 권장사항

강력한 비밀번호 사용
- 최소 12자 이상, 대소문자/숫자/특수문자 조합
최소 권한 원칙
- 애플리케이션 사용자에게 필요한 최소 권한만 부여
- 읽기 전용 작업에는 SELECT 권한만 부여
원격 root 접속 차단
- root는 localhost에서만 접속 허용
정기적인 백업
- 매일 자동 백업 설정
- 백업 파일 주기적 확인

방화벽 설정

필요한 IP만 53306 포트 접근 허용
특정 IP 대역만 접근 허용 설정

# 특정 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

MariaDB 업데이트
- 정기적인 보안 패치 적용
```
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/

18 KiB Raw Permalink Blame History