MySQL & MariaDB의 세계

mysqldump 마스터하기: MySQL 백업, 복원 및 모범 사례

백업과 복구
目次

1. 소개

데이터베이스 백업 및 복원은 데이터 관리의 기본 요소이며, 안정적인 운영에 필수적입니다. MySQL의 “mysqldump”는 데이터베이스를 효율적이고 유연하게 백업하는 데 널리 사용되는 도구입니다. 이 가이드에서는 기본 mysqldump 사용법부터 고급 옵션 활용, 복원 방법, 문제 해결까지 모든 것을 자세히 설명합니다. 기사 끝부분에는 모범 사례와 참고 자료도 소개하니, mysqldump를 마스터하는 데 이 가이드를 활용하세요.

2. mysqldump란?

2.1 mysqldump 개요

mysqldump는 MySQL 데이터베이스의 백업을 생성하는 명령줄 도구입니다. 전체 데이터베이스, 특정 테이블 또는 특정 기준에 맞는 데이터를 SQL 스크립트로 덤프할 수 있습니다. 이 덤프 파일은 데이터 복원이나 새 서버로의 데이터 마이그레이션에 사용됩니다.

2.2 사용 사례

  • 백업 : 시스템 장애나 데이터 손실에 대비해 정기적으로 백업을 생성합니다.
  • 데이터 마이그레이션 : 서버 간 데이터베이스 마이그레이션이나 개발 환경으로 데이터 복사에 사용됩니다.
  • 데이터 분석 : 분석 및 검증을 위한 특정 데이터 세트를 추출합니다.

3. 기본 사용법

3.1 기본 명령 구문

mysqldump의 기본 명령 구문은 다음과 같습니다:

mysqldump -u username -p database_name > output_file_name.sql
  • -u username : 데이터베이스에 접근할 사용자 이름입니다.
  • -p : 비밀번호를 입력하라는 프롬프트를 표시합니다.
  • database_name : 백업할 데이터베이스 이름입니다.
  • > output_file_name.sql : 덤프 파일의 저장 위치입니다.

3.2 사용자 인증 옵션

  • -h hostname : 데이터베이스 서버의 호스트 이름 (기본값은 localhost입니다).
  • -P port_number : 연결할 포트 번호 (기본값은 3306입니다).

3.3 예시: 전체 데이터베이스 백업

mysqldump -u root -p mydatabase > backup.sql

이 명령은 mydatabase의 모든 데이터를 backup.sql 파일로 백업합니다. 백업 파일 이름에 날짜를 포함하면 버전 관리와 이력 추적이 용이합니다.

4. 주요 옵션 설명

4.1 --all-databases (-A)

이 옵션을 사용하면 모든 데이터베이스를 한 번에 백업할 수 있습니다. 전체 서버의 백업을 원할 때 유용합니다.

mysqldump -u root -p --all-databases > all_databases_backup.sql

4.2 --no-data (-d)

이 옵션을 사용하면 테이블 스키마만 백업하고 데이터를 포함하지 않습니다. 예를 들어, 테이블 구조만 얻어 개발 환경을 설정할 때 사용할 수 있습니다.

mysqldump -u root -p mydatabase --no-data > schema_only_backup.sql

4.3 --where (-w)

특정 조건에 맞는 데이터만 백업하고 싶을 때 이 옵션을 사용합니다. 예를 들어, is_active 열이 1인 레코드만 백업하려면:

mysqldump -u root -p mydatabase --where="is_active=1" > filtered_data_backup.sql

4.4 --ignore-table

이 옵션을 사용하면 특정 테이블을 백업에서 제외할 수 있습니다. 백업하지 않으려는 테이블이 있을 때 유용합니다.

mysqldump -u root -p mydatabase --ignore-table=mydatabase.table1 > partial_backup.sql

5. 실전 예시

5.1 특정 테이블만 덤프

특정 테이블만 백업하려면 데이터베이스 이름 뒤에 테이블 이름을 지정합니다.

mysqldump -u root -p mydatabase table1 > table1_backup.sql

이 명령은 table1의 데이터만 table1_backup.sql로 저장합니다.

5.2 데이터만 덤프 / 스키마만 덤프

  • 데이터만 : mysqldump -u root -p mydatabase --no-create-info > data_only_backup.sql 테이블 구조를 포함하지 않고 데이터만 백업합니다.
  • 스키마만 : bash mysqldump -u root -p mydatabase --no-data > schema_only_backup.sql 테이블 스키마만 백업합니다.

5.3 조건부 덤프

특정 기준을 충족하는 데이터만 백업하려면 --where 옵션을 사용합니다.

mysqldump -u root -p mydatabase --where="created_at >= '2023-01-01'" > recent_data_backup.sql

이 명령은 created_at이 2023년 1월 1일 이후인 데이터만 백업합니다.

6. 복원 방법

mysqldump로 백업된 데이터베이스를 복원하려면 mysql 명령어를 사용합니다. 복원은 백업 파일을 사용하여 데이터베이스 상태를 복원하는 과정입니다.

6.1 기본 복원 구문

mysql -u username -p database_name < dump_file.sql
  • -u username : 데이터베이스에 연결할 사용자명.
  • -p : 비밀번호를 입력하라는 프롬프트.
  • database_name : 복원할 데이터베이스의 이름.
  • < dump_file.sql : 복원에 사용할 덤프 파일.

6.2 예제: 복원 실행

mysql -u root -p mydatabase < backup.sql

이 명령어는 backup.sql 파일에서 mydatabase로 데이터를 복원합니다.

6.3 복원 주의사항

  • 복원할 데이터베이스가 존재하지 않으면 먼저 생성해야 합니다.
  • 대량의 데이터를 복원하는 것은 시간이 오래 걸릴 수 있으므로 미리 계획하는 것이 중요합니다.

7. mysqldump 모범 사례

7.1 백업 예약

mysqldump를 스크립팅하고 cron과 같은 스케줄러를 사용하여 정기적인 백업을 자동화합니다. 다음 쉘 스크립트 예제는 모든 데이터베이스의 야간 백업을 수행합니다.

#!/bin/bash
mysqldump -u root -p'password' --all-databases > /path/to/backup/all_databases_$(date +%F).sql

7.2 백업 파일 암호화

백업 파일에는 민감한 정보가 포함되어 있으므로 gpg와 같은 도구를 사용하여 암호화하는 것이 좋습니다.

gpg -c /path/to/backup/all_databases_$(date +%F).sql

7.3 버전 호환성

다른 MySQL 버전 간 데이터 마이그레이션 시 호환성 문제를 주의해야 합니다. 업그레이드 전에 테스트 환경에서 백업 및 복원 프로세스를 시뮬레이션하여 호환성을 확인하세요.

  1. 테이블 정의 복원 : mysqldump --all-databases --no-data --routines --events > dump-defs.sql 이 명령어로 테이블 구조만 덤프하고 새 버전 환경에서 복원하여 호환성을 확인합니다.
  2. 데이터 복원 : mysqldump --all-databases --no-create-info > dump-data.sql 테이블 정의가 호환되는 것으로 확인되면 데이터만 복원합니다.
  3. 테스트 환경에서의 검증 : 버전 간 호환성을 확인하기 위해 테스트 환경에서 백업 및 복원을 수행하고, 프로덕션 환경으로 마이그레이션하기 전에 올바르게 작동하는지 확인합니다.

7.4 백업 저장 및 검증

  • 안전한 백업 저장 : 백업 파일을 외부 저장소나 클라우드에 저장하고 정기적으로 업데이트합니다. 오프사이트 저장은 물리적 손상으로부터 데이터를 보호합니다.
  • 정기적인 복원 검증 : 백업이 올바르게 복원되는지 확인하기 위해 주기적으로 복원 테스트를 수행합니다. 백업이 유효하지 않은 경우를 대비해 복원 검증을 소홀히 하지 않는 것이 중요합니다.

8. 문제 해결

8.1 일반적인 오류 및 해결 방법

  • 오류: @@GLOBAL.GTID_PURGED cannot be changed : MySQL 8.0에서 GTID 관련 문제가 발생할 때 나타나는 오류입니다. --set-gtid-purged=COMMENTED 옵션을 사용하여 GTID 설정을 주석 처리함으로써 피할 수 있습니다. mysqldump -u root -p mydatabase --set-gtid-purged=COMMENTED > backup.sql
  • 오류: 디스크 공간 부족 : 대형 데이터베이스 백업 시 디스크 공간이 부족하면 백업을 압축하거나 저장 위치를 변경합니다. 다음과 같이 gzip으로 압축하여 백업합니다: mysqldump -u root -p mydatabase | gzip > backup.sql.gz
  • 오류: 권한 부족 : 데이터베이스 사용자가 충분한 권한이 없으면 백업 또는 복원이 실패합니다. 필요한 권한(SELECT, LOCK TABLES, SHOW VIEW 등)을 부여하고 다시 시도합니다.

8.2 버전 호환성 문제

다른 MySQL 버전 간의 버전 호환성 문제는 업그레이드 전에 테스트를 통해 해결합니다. 특히 MySQL 5.7에서 8.0으로 마이그레이션할 때는 --no-data 옵션으로 테이블 정의만 복원하고 호환성을 확인하는 것이 좋습니다.

  • 호환성 테스트 : 업그레이드 전에 테스트 환경에서 마이그레이션을 시뮬레이션하여 잠재적 문제를 식별합니다. 호환되지 않는 기능과 구문에 주의하고 필요에 따라 SQL 스크립트를 수정합니다.

9. 결론

mysqldump은 MySQL 데이터베이스를 백업하고 복원하는 데 신뢰할 수 있고 강력한 도구입니다. 이 문서는 기본 사용법, 고급 옵션, 모범 사례 및 문제 해결을 포괄적으로 설명했습니다. 이 지식을 활용하면 mysqldump을 사용하여 데이터베이스를 효율적으로 보호하고 관리할 수 있습니다.

백업 일정 수립 및 파일 암호화와 같은 모범 사례를 도입하면 데이터 보안이 강화되고 데이터베이스 운영의 신뢰성이 향상됩니다. mysqldump을 적절히 활용하면 데이터베이스 문제에 대비할 수 있습니다.

10. 참고 문헌 및 추가 자료

이 리소스를 참고하여 mysqldump에 더 배우고 실제에 적용하십시오. 또한 정기적으로 백업 및 복원 검증을 수행하면 데이터베이스 보안을 유지하고 잠재적인 데이터 손실에 대비할 수 있습니다.