MySQL 초기 설정 & settings 보안 및 캐시 설정

MySQL 마이그레이션 가이드

주요 변경사항

1. TEXT 필드 인덱스 문제 해결

• dialogue_phrase, dialogue_phrase_ko: TEXT 타입 유지하되 인덱스 제거
• dialogue_hash: 중복 체크를 위한 해시 필드 추가 (VARCHAR + UNIQUE INDEX)
• user_agent: TextField → CharField(500) 변경

2. 필드 타입 최적화

• URL 필드: 최대 길이 500으로 통일
• user_agent: TextField → CharField로 변경
• search_vector: VARCHAR(1000)으로 유지 (인덱스 가능)

마이그레이션 단계

1. 기존 데이터 백업

# MySQL 데이터베이스 백업
mysqldump -u your_user -p your_database > backup_$(date +%Y%m%d).sql

2. MySQL 설정 확인

-- MySQL 콘솔에서 실행
SHOW VARIABLES LIKE 'innodb_large_prefix';
SHOW VARIABLES LIKE 'innodb_file_format';
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

3. 필요한 MySQL 설정 (권장)

-- my.cnf 또는 my.ini에 추가
[mysqld]
innodb_large_prefix = ON
innodb_file_format = Barracuda
innodb_file_per_table = ON
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

4. 마이그레이션 실행

4.1 마이그레이션 파일 생성

python manage.py makemigrations phrase

4.2 마이그레이션 SQL 확인

python manage.py sqlmigrate phrase 0001_initial

4.3 마이그레이션 실행

python manage.py migrate phrase

5. 데이터 마이그레이션 (기존 데이터가 있는 경우)

# Django shell에서 실행
python manage.py shell

from phrase.models import DialogueTable

# dialogue_hash 필드 업데이트
for dialogue in DialogueTable.objects.all():
    dialogue.dialogue_hash = dialogue.generate_dialogue_hash()
    dialogue.save(update_fields=['dialogue_hash'])

트러블슈팅

1. "Specified key was too long" 오류

-- 테이블 ROW_FORMAT 변경
ALTER TABLE dialogue_table ROW_FORMAT=DYNAMIC;

2. 문자셋 관련 오류

-- 데이터베이스 문자셋 변경
ALTER DATABASE your_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 테이블 문자셋 변경
ALTER TABLE dialogue_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. 인덱스 생성 실패

# 커스텀 마이그레이션 파일 생성
from django.db import migrations
from phrase.models.mysql_helpers import create_prefix_index

class Migration(migrations.Migration):
    operations = [
        create_prefix_index('dialogue_table', 'dialogue_phrase', 100),
    ]

성능 최적화 팁

1. 검색 성능

• search_vector 필드 활용 (VARCHAR + INDEX)
• FULLTEXT 인덱스는 필요시에만 추가

2. 쿼리 최적화

# 효율적인 검색
DialogueTable.objects.filter(search_vector__icontains=query)

# 비효율적인 검색 (TEXT 필드 직접 검색)
# DialogueTable.objects.filter(dialogue_phrase__icontains=query)

3. 인덱스 확인

-- 테이블 인덱스 확인
SHOW INDEX FROM dialogue_table;

-- 쿼리 실행 계획 확인
EXPLAIN SELECT * FROM dialogue_table WHERE search_vector LIKE '%keyword%';

주의사항

1. TEXT 필드 제한
   • TEXT 필드에는 직접 인덱스 생성 불가
   • UNIQUE 제약조건 사용 불가
   • 검색이 필요한 경우 별도 필드 사용
2. 문자셋
   • utf8mb4 사용 권장 (이모지 지원)
   • 인덱스 키 최대 길이: 191자 (utf8mb4 기준)
3. 트랜잭션
   • 대량 데이터 마이그레이션 시 배치 처리
   • 트랜잭션 크기 조절

롤백 방법

# 마이그레이션 롤백
python manage.py migrate phrase 0001_initial

# 데이터베이스 복원
mysql -u your_user -p your_database < backup_20240120.sql

---

-- phrase/sql/mysql_setup.sql
-- MySQL 데이터베이스 초기 설정 스크립트

-- 데이터베이스 생성 (필요한 경우)
-- CREATE DATABASE IF NOT EXISTS phrase_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 데이터베이스 선택
-- USE phrase_db;

-- 시스템 변수 설정 (권한이 있는 경우)
-- SET GLOBAL innodb_large_prefix = ON;
-- SET GLOBAL innodb_file_format = Barracuda;
-- SET GLOBAL innodb_file_per_table = ON;

-- 세션 변수 설정
SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;
SET character_set_connection = utf8mb4;

-- 기존 테이블이 있는 경우 ROW_FORMAT 변경
-- ALTER TABLE request_table ROW_FORMAT=DYNAMIC;
-- ALTER TABLE movie_table ROW_FORMAT=DYNAMIC;
-- ALTER TABLE dialogue_table ROW_FORMAT=DYNAMIC;

-- 기존 테이블 문자셋 변경 (필요한 경우)
-- ALTER TABLE request_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- ALTER TABLE movie_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- ALTER TABLE dialogue_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 사용자 권한 설정 예시
-- GRANT ALL PRIVILEGES ON phrase_db.* TO 'phrase_user'@'localhost';
-- FLUSH PRIVILEGES;

-- 최적화 관련 설정 확인
SELECT 
    @@innodb_large_prefix as 'Large Prefix',
    @@innodb_file_format as 'File Format',
    @@character_set_database as 'DB Charset',
    @@collation_database as 'DB Collation',
    @@innodb_buffer_pool_size as 'Buffer Pool Size',
    @@max_allowed_packet as 'Max Packet Size';

-- 테이블 상태 확인 (마이그레이션 후)
-- SHOW TABLE STATUS WHERE Name IN ('request_table', 'movie_table', 'dialogue_table');

-- 인덱스 확인 (마이그레이션 후)
-- SHOW INDEX FROM dialogue_table;
-- SHOW INDEX FROM movie_table;
-- SHOW INDEX FROM request_table;

---

# settings.py MySQL 설정 예시

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'phrase_db',
        'USER': 'your_user',
        'PASSWORD': 'your_password',
        'HOST': 'localhost',
        'PORT': '3306',
        'OPTIONS': {
            'charset': 'utf8mb4',
            'collation': 'utf8mb4_unicode_ci',
            'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
            'isolation_level': 'read committed',
            # 연결 풀 설정
            'connect_timeout': 60,
            'read_timeout': 60,
            'write_timeout': 60,
            # SSL 설정 (필요한 경우)
            # 'ssl': {
            #     'ca': '/path/to/ca.pem',
            #     'cert': '/path/to/client-cert.pem',
            #     'key': '/path/to/client-key.pem'
            # }
        },
        # 연결 풀링 설정
        'CONN_MAX_AGE': 600,  # 10분
        'CONN_HEALTH_CHECKS': True,
    }
}

# MySQL 특화 설정
DEFAULT_AUTO_FIELD = 'django.db.models.BigAutoField'

# 대용량 파일 업로드를 위한 설정
FILE_UPLOAD_MAX_MEMORY_SIZE = 10485760  # 10MB
DATA_UPLOAD_MAX_MEMORY_SIZE = 10485760  # 10MB

# 쿼리 로깅 (개발 환경에서만)
if DEBUG:
    LOGGING = {
        'version': 1,
        'disable_existing_loggers': False,
        'handlers': {
            'console': {
                'class': 'logging.StreamHandler',
            },
        },
        'loggers': {
            'django.db.backends': {
                'handlers': ['console'],
                'level': 'DEBUG',
                'propagate': False,
            },
            'phrase.models': {
                'handlers': ['console'],
                'level': 'INFO',
                'propagate': False,
            },
        },
    }

# 캐시 설정 (Redis 권장)
CACHES = {
    'default': {
        'BACKEND': 'django.core.cache.backends.redis.RedisCache',
        'LOCATION': 'redis://127.0.0.1:6379/1',
        'OPTIONS': {
            'CLIENT_CLASS': 'django_redis.client.DefaultClient',
            'CONNECTION_POOL_KWARGS': {
                'max_connections': 50,
                'retry_on_timeout': True,
            },
            'COMPRESSOR': 'django_redis.compressors.zlib.ZlibCompressor',
            'IGNORE_EXCEPTIONS': True,
        },
        'KEY_PREFIX': 'phrase',
        'TIMEOUT': 300,  # 기본 5분
    }
}

# 세션 설정
SESSION_ENGINE = 'django.contrib.sessions.backends.cached_db'
SESSION_CACHE_ALIAS = 'default'

# 파일 업로드 설정
MEDIA_ROOT = os.path.join(BASE_DIR, 'media')
MEDIA_URL = '/media/'

# 정적 파일 설정
STATIC_ROOT = os.path.join(BASE_DIR, 'staticfiles')
STATIC_URL = '/static/'

# 보안 설정
SECURE_BROWSER_XSS_FILTER = True
SECURE_CONTENT_TYPE_NOSNIFF = True
X_FRAME_OPTIONS = 'DENY'

# CORS 설정 (필요한 경우)
# CORS_ALLOWED_ORIGINS = [
#     "https://example.com",
#     "https://sub.example.com",
# ]