mysqldump 명령어 사용법 (MySQL 데이터베이스 백업 및 복구)

작성일자 글쓴이

데이터베이스를 운영하다 보면 서버 이전, 개발 환경 구축, 장애 복구 등을 위해 MySQL 백업이 필요한 경우가 많습니다.
이때 가장 많이 사용하는 명령어가 바로 mysqldump입니다.

특히 웹사이트를 운영하는 경우 실수로 데이터를 삭제하거나 서버 장애가 발생할 수 있기 때문에 정기적인 백업은 필수입니다.
이번 글에서는 mysqldump 명령어 사용법, 백업 및 복구 방법, 그리고 실무에서 자주 사용하는 옵션까지 정리해보겠습니다.

왜 MySQL 백업이 중요한가

데이터베이스는 사이트의 핵심 데이터가 저장되는 공간입니다.

다음과 같은 상황에서는 반드시 백업 파일이 필요합니다.

  • 서버 이전 작업
  • 개발 서버 구축
  • 데이터 실수 삭제
  • 해킹 또는 랜섬웨어 피해
  • 프로그램 업데이트 전 안전장치 확보

실제로 운영 중인 사이트에서 테이블을 잘못 삭제한 경험이 있었는데, 백업 파일이 있어서 몇 분 만에 복구할 수 있었습니다.
반대로 백업이 없었다면 데이터 복구 비용과 시간이 크게 증가했을 것입니다.

mysqldump란 무엇인가

mysqldump는 MySQL에서 제공하는 데이터베이스 백업 도구입니다.

데이터베이스 구조와 데이터를 SQL 형태로 저장하여 나중에 동일한 상태로 복원할 수 있습니다.

기본 형식은 다음과 같습니다.

mysqldump -u 사용자명 -p 데이터베이스명 > backup.sql

명령어를 실행하면 비밀번호 입력 후 백업 파일이 생성됩니다.

데이터베이스 백업 방법

가장 기본적인 백업 명령어입니다.

mysqldump -u root -p mydb > backup.sql

설명

  • root : MySQL 계정
  • mydb : 백업할 데이터베이스
  • backup.sql : 생성될 백업 파일

실행 후 비밀번호를 입력하면 현재 데이터베이스 전체가 SQL 파일로 저장됩니다.

백업 파일 크기는 데이터 양에 따라 달라집니다.

데이터베이스 복구 방법

백업 파일을 다시 데이터베이스에 적용할 때는 mysql 명령어를 사용합니다.

mysql -u root -p mydb < backup.sql

설명

  • root : MySQL 계정
  • mydb : 복구 대상 데이터베이스
  • backup.sql : 백업 파일

주의할 점은 복구 대상 데이터베이스가 미리 생성되어 있어야 한다는 것입니다.

예를 들어 데이터베이스 생성은 다음과 같이 할 수 있습니다.

CREATE DATABASE mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

특정 테이블만 백업하기

전체 데이터베이스가 아닌 특정 테이블만 백업할 수도 있습니다.

mysqldump -u root -p mydb member > member.sql

여러 개의 테이블을 지정할 수도 있습니다.

mysqldump -u root -p mydb member board log > backup.sql

실무에서는 대용량 로그 테이블을 제외하고 필요한 데이터만 백업할 때 자주 사용합니다.

데이터만 백업하기

테이블 구조는 제외하고 데이터만 백업하려면 다음 옵션을 사용합니다.

mysqldump -u root -p --no-create-info mydb > data.sql

반대로 구조만 백업하려면 다음과 같이 사용합니다.

mysqldump -u root -p --no-data mydb > structure.sql

압축하여 백업하기

데이터베이스 용량이 큰 경우 압축 파일로 저장하면 공간을 절약할 수 있습니다.

mysqldump -u root -p mydb | gzip > backup.sql.gz

복구할 때는 다음과 같이 사용합니다.

gunzip < backup.sql.gz | mysql -u root -p mydb

수 GB 이상의 데이터베이스에서는 압축 백업을 사용하는 것이 일반적입니다.

자주 발생하는 오류

Unknown command ‘-‘

복구 중 아래와 같은 오류가 발생할 수 있습니다.

ERROR at line 1: Unknown command '\-'

이 경우 SQL 파일이 손상되었거나, SQL 파일이 아닌 다른 형식의 파일을 복구하려고 할 가능성이 높습니다.

또는 Windows 편집기로 수정하면서 특수문자가 추가된 경우에도 발생할 수 있습니다.

백업 파일의 첫 부분을 확인해보는 것이 좋습니다.

head backup.sql

Access denied

ERROR 1045 (28000): Access denied

사용자 계정이나 비밀번호가 잘못된 경우 발생합니다.

MySQL 계정 권한을 확인해야 합니다.

Unknown database

ERROR 1049 (42000): Unknown database

복구 대상 데이터베이스가 존재하지 않는 경우 발생합니다.

먼저 데이터베이스를 생성한 후 복구를 진행해야 합니다.

운영 서버에서 백업할 때 알아두면 좋은 팁

  1. 중요한 작업 전에는 반드시 백업을 생성한다.
  2. 백업 파일은 서버 외부에도 보관한다.
  3. 정기 백업 스케줄을 설정한다.
  4. 복구 테스트를 주기적으로 진행한다.
  5. 압축 백업으로 저장 공간을 절약한다.

특히 백업만 해두고 복구 테스트를 하지 않는 경우가 많은데, 실제 장애 발생 시 복구가 안 되는 경우도 있으므로 반드시 테스트까지 진행하는 것이 좋습니다.

마무리

mysqldump는 MySQL 데이터베이스를 안전하게 백업하고 복구할 수 있는 가장 기본적인 도구입니다.

운영 중인 사이트라면 정기적인 백업은 선택이 아닌 필수입니다.
mysqldump를 이용하면 전체 데이터베이스, 특정 테이블, 데이터만 또는 구조만 백업하는 등 다양한 상황에 대응할 수 있습니다.

데이터는 한 번 잃어버리면 되돌리기 어렵습니다.
서버 이전이나 프로그램 수정 작업 전에 백업을 생활화하는 습관을 들이는 것이 가장 좋은 데이터 보호 방법입니다.