일반적인 MySQL 마이그레이션 문제 및 데이터 전송 오류 해결
MySQL 마이그레이션 중 장애에 직면하셨나요? 이 가이드는 일반적인 데이터 전송 오류, 호환성 실패, 성능 병목 현상에 대한 전문적인 문제 해결 팁을 제공합니다. 외래 키 충돌 처리, 문자 집합 손상(utf8mb4 사용) 해결, 버전 차이(예: MySQL 5.7에서 8.0) 관리, 효과적인 `mysqldump` 기술 및 서버 구성을 사용한 대량 데이터 가져오기 최적화 방법을 알아보세요. 이 실용적이고 단계별 접근 방식을 통해 원활하고 안정적인 데이터베이스 전환을 보장하세요.
일반적인 MySQL 마이그레이션 문제 및 데이터 전송 오류 해결
MySQL 마이그레이션은 몇 가지 익숙한 방식으로 실패합니다. 가져오기가 외래 키에서 중단됩니다. 문자가 물음표로 바뀝니다. MySQL 5.7의 덤프가 MySQL 8.0에 깔끔하게 로드되지 않습니다. 데이터는 로드되지만 저장 프로시저, 트리거, 사용자 또는 SQL 모드가 예상대로 전달되지 않아 애플리케이션이 중단됩니다. 이러한 문제 중 어느 것도 드문 일이 아니지만, 마이그레이션을 일회성 복사가 아닌 반복 가능한 프로세스로 취급하면 처리하기가 훨씬 쉽습니다.
가장 좋은 마이그레이션 습관은 리허설을 하는 것입니다. 실제 백업을 가져와 스테이징 대상에 복원하고, 프로덕션에서 사용할 계획과 동일한 가져오기 명령을 실행하고, 모든 경고를 기록하십시오. 리허설을 통해 덤프가 완전한지, 대상 구성이 호환되는지, 로드에 실제로 얼마나 오래 걸리는지 알 수 있습니다. 또한 "유지 관리 기간 중에 알아내겠다"보다 더 현실적인 롤백 계획을 제공합니다.
실패 유형 식별부터 시작
마이그레이션이 중단되면 서버 변수를 무작위로 변경하지 마십시오. 오류를 다음 버킷 중 하나에 넣으십시오:
- 호환성: 버전 차이, 예약어, 제거된 기능, 변경된 기본값.
- 인코딩: 문자 집합 및 데이터 정렬 불일치.
- 제약 조건: 외래 키, 고유 키, 검사 제약 조건, 생성된 열.
- 객체 범위: 누락된 트리거, 루틴, 이벤트, 뷰, 사용자 또는 권한.
- 성능: 가져오기가 너무 느림, 디스크 가득 참, 바이너리 로그 증가, 인덱스 시간이 너무 오래 걸림.
- 애플리케이션 동작: 데이터는 가져왔지만 쿼리나 쓰기 동작이 다름.
이 분류를 통해 다음에 실행할 명령을 알 수 있습니다. 중복 키 오류와 손상된 이모지는 모두 "마이그레이션 문제"이지만 원인이 완전히 다릅니다.
버전 불일치: MySQL 5.7에서 8.0 및 유사한 점프
주요 버전 업그레이드는 많은 예상치 못한 문제가 나타나는 곳입니다. MySQL 8.0은 5.7과 비교하여 기본값, 예약어, 인증 동작, 데이터 사전 내부, 최적화 프로그램 동작을 변경했습니다. 일부 이전 구문은 여전히 작동하고 일부는 그렇지 않습니다. MariaDB는 모든 MySQL 기능을 대체할 수 있는 드롭인 교체가 아니기 때문에 또 다른 호환성 계층을 추가합니다.
마이그레이션 전에 소스 설정을 캡처하십시오:
SHOW VARIABLES LIKE 'version';
SHOW VARIABLES LIKE 'sql_mode';
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
대상에서 동일한 검사를 실행하고 비교하십시오. sql_mode는 특별한 주의가 필요합니다. 허용적인 소스에서 로드되는 덤프는 유효하지 않은 날짜, NOT NULL 열에 대한 기본값 누락, 대상 모드에서 더 이상 허용되지 않는 0 날짜 등과 같은 오류로 인해 더 엄격한 대상에서 실패할 수 있습니다.
다음과 같은 오류가 발생하면:
ERROR 1067 (42000): 'created_at'에 대한 기본값이 잘못되었습니다.
즉시 sql_mode를 영구적으로 완화하지 마십시오. 먼저 테이블 정의와 데이터를 검사하십시오. 기본값을 수정하거나, 0 날짜를 변환하거나, 애플리케이션 가정을 업데이트해야 할 수도 있습니다. 가져오기 중에 소스 sql_mode를 일시적으로 일치시키면 단계별 복원을 완료하는 데 도움이 될 수 있지만, 프로덕션은 애플리케이션이 테스트된 알려진 명시적 모드로 이동해야 합니다.
예약어는 이전 스키마를 손상시킬 수도 있습니다. rank, groups 또는 다른 최신 예약어로 명명된 열이나 테이블은 따옴표로 묶거나 이름을 바꿔야 할 수 있습니다. DDL에 오류가 나타나면 덤프에서 정확한 명령문을 검사하고 수정된 버전을 대상에서 테스트하십시오.
인증 플러그인 문제
애플리케이션 전환을 포함하는 마이그레이션은 클라이언트가 인증할 수 없기 때문에 쿼리가 실행되기 전에 실패하는 경우가 많습니다. MySQL 8.0은 기본적으로 caching_sha2_password를 사용하는 반면, 이전 클라이언트는 mysql_native_password를 예상할 수 있습니다.
대상 사용자를 확인하십시오:
SELECT user, host, plugin FROM mysql.user;
더 나은 수정 방법은 일반적으로 클라이언트 라이브러리나 드라이버를 업데이트하는 것입니다. 전환 전에それが 불가능한 경우 임시 호환성 계정이 필요할 수 있습니다:
ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'new_secret';
이것을 일반적인 모범 사례가 아닌 호환성 결정으로 취급하십시오. 인증 설정은 보안에 영향을 미치며, 올바른 답변은 클라이언트 버전과 위험 모델에 따라 다릅니다.
문자 집합 및 데이터 정렬 문제
문자 집합 문제는 가져오기가 성공적으로 완료될 수 있지만 데이터가 이미 손상되었기 때문에 고통스럽습니다. 전형적인 증상은 ?, 모지바케, 깨진 악센트 문자, 또는 이모지와 관련된 삽입 실패입니다.
소스 데이터베이스 및 테이블 정의를 확인하십시오:
SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.SCHEMATA
WHERE schema_name = 'appdb';
열도 확인하십시오:
SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.COLUMNS
WHERE table_schema = 'appdb'
AND character_set_name IS NOT NULL;
대부분의 최신 애플리케이션의 경우 utf8mb4는 이모지를 포함한 전체 유니코드 범위를 지원하므로 올바른 대상 문자 집합입니다. MySQL의 이전 utf8 이름은 이전 버전에서 완전한 UTF-8과 동일하지 않습니다. 일반적으로 3바이트 문자 집합입니다.
덤프 및 가져오기 시 명시적으로 지정하십시오:
mysqldump --default-character-set=utf8mb4 -u user -p appdb > appdb.sql
mysql --default-character-set=utf8mb4 -u user -p appdb < appdb.sql
소스 데이터가 실제로 latin1인 경우 맹목적으로 utf8mb4로 선언하고 기대하지 마십시오. 먼저 바이트가 소스 인코딩에서 유효한지 확인하십시오. 일부 이전 시스템에는 열이 하나의 문자 집합을 주장하지만 애플리케이션이 다른 문자 집합의 바이트를 저장한 "이중 인코딩" 데이터가 포함되어 있습니다. 전역 검색 및 바꾸기가 아닌 테스트된 변환이 필요합니다.
데이터 정렬 차이로 인해 동작이 변경될 수도 있습니다. 정렬 순서, 고유성 비교 및 대소문자 구분은 데이터 정렬에 따라 다를 수 있습니다. 마이그레이션 중에 고유 인덱스가 실패하면 대상 데이터 정렬이 소스에서 그렇지 않을 때 두 문자열을 동일하게 처리하는지 확인하십시오.
외래 키 실패
외래 키 오류는 일반적으로 다음 네 가지 중 하나를 의미합니다:
- 하위 테이블이 상위 테이블보다 먼저 가져와졌습니다.
- 덤프가 부분적이고 참조된 행이 누락되었습니다.
- 소스 데이터에 이미 일관성 없는 참조가 있었습니다.
- 대상 스키마가 소스와 다릅니다.
일반적인 대량 로드 해결 방법은 다음과 같습니다:
SET FOREIGN_KEY_CHECKS = 0;
-- 데이터 가져오기
SET FOREIGN_KEY_CHECKS = 1;
이는 신뢰할 수 있는 덤프에서 전체 논리적 복원에 적합할 수 있습니다. 정리 도구가 아닙니다. FOREIGN_KEY_CHECKS를 다시 활성화해도 많은 사람들이 생각하는 방식으로 모든 기존 행을 완전히 재검증하지 않으므로 잘못된 관계를 가져와 나중에야 알 수 있습니다.
데이터를 병합하거나 스키마의 일부만 가져오는 경우 가능하면 검사를 활성화된 상태로 유지하고 상위 테이블을 먼저 로드하십시오. 검사를 비활성화해야 하는 경우 나중에 유효성 검사 쿼리를 실행하십시오. 예를 들어:
SELECT c.*
FROM orders c
LEFT JOIN customers p ON p.id = c.customer_id
WHERE c.customer_id IS NOT NULL
AND p.id IS NULL
LIMIT 20;
특히 주문, 결제, 계정 및 권한과 같은 중요도가 높은 테이블의 실제 관계에 대해 이러한 쿼리를 사용하십시오.
중복 키 오류
중복 키 오류는 대상에 들어오는 데이터가 삽입하려는 값이 이미 있음을 의미합니다:
ERROR 1062 (23000): 키 'PRIMARY'에 대한 중복 항목 '123'
대상이 정확한 복사본이어야 하는 경우 일반적으로 대상 데이터베이스를 삭제하고 다시 만든 다음 다시 가져오는 것이 깔끔한 수정 방법입니다. 프로세스가 재개하도록 설계되지 않은 경우 반쯤 로드된 대상은 두 번째 시도의 좋은 시작점이 아닙니다.
데이터를 병합하는 경우 가져오기 전에 충돌 정책을 결정하십시오. INSERT IGNORE는 행을 건너뛰어 중복을 숨깁니다. REPLACE INTO는 기존 행을 삭제하고 새 행을 삽입하며, 이는 계단식 실행을 발생시키고 자동 업데이트된 열을 변경할 수 있습니다. ON DUPLICATE KEY UPDATE는 더 명시적이지만 여전히 신중한 규칙이 필요합니다.
마이그레이션의 경우 병합을 위해 스테이징 테이블을 선호합니다. 들어오는 데이터를 staging_* 테이블에 로드하고, 충돌을 검사한 다음, 의도적인 INSERT ... SELECT 또는 UPDATE ... JOIN 문을 작성하십시오. 설계하는 데는 느리지만 데이터를 자동으로 버리는 것을 방지합니다.
누락된 트리거, 루틴, 이벤트 및 뷰
마이그레이션은 테이블과 행이 존재하기 때문에 성공적으로 보일 수 있지만 중요한 데이터베이스 로직이 누락될 수 있습니다. mysqldump 옵션이 중요합니다:
mysqldump -u user -p \
--single-transaction \
--routines \
--triggers \
--events \
appdb > appdb.sql
뷰와 루틴은 정의자 계정으로 인해 가져오기에서 실패할 수 있습니다. 뷰가 다음을 참조할 수 있습니다:
DEFINER=`old_user`@`old_host`
해당 계정이 대상에 없으면 객체를 생성하지 못하거나 사용 시 실패할 수 있습니다. 필요한 정의자 계정을 적절한 권한으로 생성하거나 제어된 마이그레이션 프로세스 중에 정의자를 조정할 수 있습니다. 애플리케이션의 보안 모델을 이해하지 않고 정의자를 맹목적으로 제거하지 마십시오.
가져오기 후 객체 수를 비교하십시오:
SELECT ROUTINE_TYPE, COUNT(*)
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'appdb'
GROUP BY ROUTINE_TYPE;
SELECT TRIGGER_SCHEMA, COUNT(*)
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'appdb'
GROUP BY TRIGGER_SCHEMA;
또한 애플리케이션이 예약된 이벤트에 의존하는 경우 확인하십시오:
SHOW EVENTS FROM appdb;
느린 가져오기 및 대형 테이블
대규모 가져오기는 일반적으로 디스크 I/O, 인덱스 유지 관리, 바이너리 로깅, 외래 키 검사 또는 트랜잭션 크기에 의해 제한됩니다. 튜닝 전에 대상을 관찰하십시오:
iostat -xz 1
df -h
top
논리적 덤프의 경우 확장 삽입을 사용하십시오. mysqldump는 대부분의 경우 기본적으로 이 작업을 수행하지만, 속도보다 사람이 읽을 수 있는 diff가 더 필요한 경우가 아니라면 --skip-extended-insert를 사용하지 않는지 확인하십시오.
InnoDB 가져오기의 경우 대상에 사용 가능한 메모리가 있으면 더 큰 innodb_buffer_pool_size가 도움이 될 수 있습니다. OS가 스와핑을 시작할 정도로 높게 설정하지 마십시오. 일회성 로드 중에 일부 팀은 innodb_flush_log_at_trx_commit과 같은 내구성 설정을 일시적으로 완화하거나 가져오기 세션 동안 바이너리 로깅을 비활성화합니다. 이러한 선택은 속도를 위해 충돌 복구 또는 특정 시점 복구를 희생하므로 알려진 백업에서 가져오기를 다시 시작할 수 있는 경우에만 사용해야 합니다.
대상이 복제 소스이기도 한 경우 바이너리 로그에 주의하십시오. 바이너리 로깅을 비활성화하면 가져오기 속도가 빨라질 수 있지만 다운스트림 복제본은 해당 변경 사항을 수신하지 않습니다. 복제본이 있는 토폴로지에서는 로그를 끄기 전에 가져오기가 발생해야 하는 위치와 변경 사항이 어떻게 흘러야 하는지 결정하십시오.
매우 큰 테이블의 경우 일반 mysqldump 대신 물리적 백업 도구 또는 MySQL Shell 덤프 및 로드 유틸리티를 고려하십시오. 논리적 덤프는 이식 가능하고 검사하기 쉽지만 수백 기가바이트 데이터 세트의 경우 항상 가장 빠른 경로는 아닙니다.
디스크 공간 실패
마이그레이션 중 디스크 오류는 일반적이며 피할 수 있습니다. 덤프 파일, 가져온 데이터, 인덱스, 임시 파일, 바이너리 로그, 그리고 테이블이 재구축되는 동안 이중 저장 공간이 필요합니다.
가져오기 전에 확인하십시오:
df -h
du -sh /var/lib/mysql
MySQL 내부에서 테이블 크기를 확인하십시오:
SELECT table_schema,
ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY size_gb DESC;
디스크가 가득 차서 가져오기가 실패하면 데이터 디렉토리에서 임의의 파일을 삭제하지 마십시오. 안전하게 공간을 확보하고, 대상이 부분적으로 로드되었는지 검사한 다음, 처음부터 다시 시작할지 결정하십시오.
마이그레이션 후 검증
마이그레이션은 가져오기 명령이 종료될 때 완료되지 않습니다. 결과를 검증하십시오.
중요한 테이블의 행 수부터 시작하십시오:
SELECT COUNT(*) FROM customers;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM payments;
행 수만으로는 충분하지 않습니다. 비즈니스에 중요한 데이터의 합계 또는 검사를 비교하십시오:
SELECT COUNT(*), SUM(total_amount), MIN(created_at), MAX(created_at)
FROM orders;
마지막 조용한 기간 동안 소스와 대상에서 동일한 쿼리를 실행하십시오. 마이그레이션 중에 계속 변경되는 테이블의 경우 계획된 중단, 복제 따라잡기 또는 애플리케이션 수준 조정을 사용하십시오.
전환 전에 대상에 대해 애플리케이션 워크플로를 테스트하십시오:
- 로그인 및 세션 생성.
- 핵심 레코드 생성 및 업데이트.
- 데이터 정렬 또는 인덱스에 의존하는 검색 및 보고서.
- 백그라운드 작업, 트리거 및 예약된 이벤트.
- 권한 검사 및 관리자 작업.
애플리케이션 테스트는 데이터베이스가 기술적으로 가져와졌지만 동작적으로 잘못될 수 있기 때문에 중요합니다.
실용적인 마이그레이션 분류 체크리스트
마이그레이션 오류가 나타나면 다음 순서를 사용하십시오:
- 정확한 오류 메시지와 가능한 경우 실패한 SQL 문을 저장하십시오.
- 버킷 식별: 호환성, 인코딩, 제약 조건, 객체 범위, 성능 또는 애플리케이션 동작.
- 소스 및 대상 MySQL 버전,
sql_mode, 문자 집합 및 데이터 정렬을 비교하십시오. - 제약 조건 오류의 경우 특정 부모 및 자식 행 또는 중복 키를 검사하십시오.
- 인코딩 문제의 경우 소스 바이트가 유효한지와 클라이언트 연결이 이를 어떻게 해석하는지 알 때까지 가져오기를 중지하십시오.
- 느린 가져오기의 경우 임의의 변수를 변경하기 전에 디스크, 메모리, 바이너리 로그 및 인덱스 유지 관리를 확인하십시오.
- 수정 후 프로덕션에 적용하기 전에 스테이징에서 마이그레이션을 다시 실행하십시오.
가장 안정적인 MySQL 마이그레이션은 버리고 반복할 수 있는 마이그레이션입니다. 명령을 스크립트에 유지하고, 구성 변경 사항을 문서화하고, 검증을 희망적인 최종 확인이 아닌 계획의 일부로 만드십시오.