Practical 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.sql
  • -u username : 데이터베이스에 접근할 사용자 이름.
  • -p : 비밀번호 입력을 요청합니다.
  • database_name : 백업하려는 데이터베이스 이름.
  • > output_file.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에 대해 더 자세히 배우고 실제 상황에 적용하려면 이 자료를 참고하십시오. 또한 정기적인 백업 및 복원 검증을 수행하면 데이터베이스 안전성을 유지하고 예상치 못한 데이터 손실에 대비할 수 있습니다.