1. 소개
MySQL은 웹 서비스와 비즈니스 애플리케이션에서 가장 널리 사용되는 관계형 데이터베이스 관리 시스템(RDBMS) 중 하나입니다. MySQL로 구축된 데이터베이스는 일상적인 운영 및 서비스의 핵심 역할을 하는 경우가 많습니다. 시스템 장애나 인적 오류로 인해 데이터가 손실되면 비즈니스 연속성에 큰 영향을 미칠 수 있습니다.
이때 “백업”이 중요한 역할을 합니다. MySQL은 데이터베이스 내용을 쉽게 내보내고 백업 파일로 저장할 수 있는 공식 명령줄 도구인 “mysqldump”를 제공합니다.
이 도구를 활용하면 재해 복구, 다른 환경으로의 마이그레이션, 정기적인 아카이브 저장 등 다양한 운영 요구 사항을 처리할 수 있습니다.
본 문서에서는 “mysqldump”를 사용해 MySQL 데이터베이스를 내보내는 방법을 기본 사용법부터 고급 설정까지 모두 설명합니다. 초보자도 따라 할 수 있도록 실용적인 명령 예시와 함께 자세히 안내합니다.
2. mysqldump 기본 사용법
mysqldump는 MySQL 데이터베이스의 내용을 SQL 파일로 내보내는 명령줄 도구입니다. 기본 구문은 매우 간단하며 몇 줄의 명령만으로 백업을 생성할 수 있습니다. 이 섹션에서는 흔히 사용되는 내보내기 방법을 설명합니다.
단일 데이터베이스 내보내기
가장 일반적인 사용 사례는 단일 데이터베이스 전체를 내보내는 것입니다.
mysqldump -u username -p database_name > backup.sql
실행 후 비밀번호 입력을 요청받으며, 지정한 데이터베이스의 내용이 backup.sql이라는 파일에 출력됩니다.
여러 데이터베이스 내보내기
한 번에 여러 데이터베이스를 백업하려면 --databases 옵션을 사용합니다.
mysqldump -u username -p --databases db1 db2 db3 > multi_backup.sql
이 형식에서는 각 데이터베이스마다 CREATE DATABASE 문이 포함되어 복원 시 더 신뢰성 있고 편리합니다.
모든 데이터베이스 내보내기
서버에 존재하는 모든 데이터베이스를 백업하려면 --all-databases 옵션을 사용합니다.
mysqldump -u username -p --all-databases > all_backup.sql
이 명령은 MySQL 서버에 존재하는 모든 데이터베이스(예: mysql, information_schema, performance_schema 등)를 내보냅니다.
예시: 출력 파일 이름에 날짜 포함하기
정기 백업의 경우 파일 이름에 날짜를 포함하면 관리가 용이합니다. 아래는 UNIX 셸을 이용한 예시입니다.
mysqldump -u username -p database_name > backup_$(date +%F).sql
이 방법을 사용하면 backup_2025-04-13.sql과 같은 파일이 자동으로 생성되어 백업 관리가 보다 체계적으로 이루어집니다.
3. 내보내기 변형
mysqldump는 전체 데이터베이스를 단순히 내보내는 것 이상의 기능을 제공합니다. 필요에 따라 유연한 내보내기 옵션을 지원합니다. 이 섹션에서는 특정 테이블만 내보내기, 스키마만 또는 데이터만 내보내기, 조건을 사용해 필터링된 데이터를 내보내는 고급 기술을 소개합니다.
특정 테이블 내보내기
데이터베이스 내에서 특정 테이블만 백업하려면 테이블 이름을 명시적으로 지정하면 됩니다.
mysqldump -u username -p database_name table1 table2 > selected_tables.sql
예시:
mysqldump -u root -p mydb users orders > users_orders.sql
이 명령은 mydb 데이터베이스에서 users와 orders 테이블만 내보냅니다.
데이터만 또는 스키마만 내보내기
mysqldump는 스키마 정의만 내보내거나 데이터만 내보내는 옵션을 제공합니다.
스키마(구조)만 내보내기:
mysqldump -u username -p --no-data database_name > schema_only.sql데이터만 내보내기 (CREATE TABLE 문 제외):
mysqldump -u username -p --no-create-info database_name > data_only.sql
이 옵션은 개발 환경과 운영 환경 간에 스키마만 공유하거나, 증분 데이터를 가져올 때 유용합니다.
조건을 사용한 데이터 내보내기 (–where)
데이터의 일부만 내보내려면 --where 옵션을 사용하십시오. 이 옵션은 SQL WHERE 절과 동일한 구문을 사용합니다.
mysqldump -u username -p database_name table_name --where="condition" > filtered_data.sql
예시:
mysqldump -u root -p mydb users --where="created_at >= '2025-01-01'" > users_2025.sql
이 예시에서는 users 테이블에서 2025년 이후에 생성된 레코드만 내보냅니다.
4. 자주 사용되는 옵션 및 설명
mysqldump는 간단하지만 옵션을 조합하면 보다 안전하고 효율적인 백업이 가능합니다. 이 섹션에서는 실제 환경에서 흔히 사용되는 옵션들을 설명합니다.
–single-transaction: 트랜잭션 일관성 유지
mysqldump -u username -p --single-transaction database_name > backup.sql
이 옵션은 InnoDB와 같이 트랜잭션을 지원하는 스토리지 엔진을 사용할 때 효과적입니다. 덤프 과정을 단일 트랜잭션 내에서 실행하여 읽기 잠금을 걸지 않고도 내보내기 중 일관성을 보장합니다. 백업 중에도 서비스를 계속 운영해야 할 때 특히 유용합니다.
참고: 이 옵션은 MyISAM과 같은 비트랜잭션 엔진에는 영향을 주지 않습니다.
–quick: 메모리 사용량 감소
mysqldump -u username -p --quick database_name > backup.sql
이 옵션을 사용하면 mysqldump가 모든 데이터를 한 번에 메모리로 로드하는 대신 행을 하나씩 가져옵니다. 이를 통해 메모리 사용량을 줄이고 대용량 테이블을 내보낼 때 안정성을 향상시킵니다.
–routines 및 –events: 저장 프로시저와 이벤트 포함
기본적으로 저장 프로시저와 이벤트는 내보내기에 포함되지 않습니다. 이를 포함하려면 다음 옵션을 사용하십시오:
mysqldump -u username -p --routines --events database_name > backup_with_logic.sql
--routines: 저장 프로시저와 함수를 포함합니다--events: 예약된 이벤트를 포함합니다
비즈니스 로직이 데이터베이스 수준에 많이 구현되어 있다면 이 옵션들을 잊지 마세요.
–add-drop-table: 테이블 덮어쓰기 시 유용
mysqldump -u username -p --add-drop-table database_name > backup.sql
이 옵션은 각 테이블 정의 앞에 DROP TABLE IF EXISTS 구문을 추가합니다. 대상 환경에 동일한 이름의 테이블이 이미 존재하면 재생성하기 전에 해당 테이블이 삭제됩니다.
–lock-tables: MyISAM에 효과적
mysqldump -u username -p --lock-tables database_name > backup.sql
InnoDB에서는 일반적으로 필요하지 않지만, MyISAM을 사용할 때 이 옵션은 내보내기 중 쓰기를 방지하기 위해 테이블을 잠급니다. 일관성이 중요한 경우에 유용합니다.
5. 내보낸 파일을 가져오는 방법
mysqldump로 내보낸 SQL 파일은 MySQL의 표준 가져오기 방법을 사용해 복원(가져오기)할 수 있습니다. 이 섹션에서는 백업 파일에서 가져오는 기본 방법, 실용적인 복원 예시, 주요 주의사항을 설명합니다.
기본 가져오기 명령
내보낸 SQL 파일은 mysql 명령을 사용해 쉽게 가져올 수 있습니다. 기본 구문은 다음과 같습니다:
mysql -u username -p database_name < backup.sql
예시:
mysql -u root -p mydb < backup.sql
이 명령을 실행하면 backup.sql에 포함된 SQL 문이 순차적으로 실행되어 데이터베이스가 원래 상태로 복원됩니다.
새 데이터베이스에 가져오기
백업 파일에 CREATE DATABASE 구문이 포함되지 않을 수 있으므로, 다른 이름의 데이터베이스에 가져오려면 미리 새 데이터베이스를 생성해야 합니다.
예시: 새 데이터베이스 “mydb_restore” 생성 및 가져오기
CREATE DATABASE mydb_restore;
mysql -u root -p mydb_restore < backup.sql
참고: --databases 옵션으로 내보낸 SQL은 CREATE DATABASE 구문을 포함하므로, 이 경우 절차가 다르다는 점을 유의하십시오.
압축 파일(.gz) 가져오기
백업 파일이 gzip 등으로 압축된 경우, 압축을 해제하면서 직접 가져올 수 있습니다:
gunzip < backup.sql.gz | mysql -u username -p database_name
이 방법은 디스크 공간을 절약하면서 빠르게 복원할 수 있게 해줍니다.
일반적인 가져오기 오류 및 해결 방법
| Error | Cause | Solution |
|---|---|---|
ERROR 1049 (42000): Unknown database | The target database does not exist | Create it in advance with CREATE DATABASE |
Access denied | Insufficient permissions or incorrect credentials | Recheck the username, password, and privileges |
ERROR 1064 (42000): You have an error in your SQL syntax | SQL format incompatibility between versions | Verify the dump matches the MySQL version you are using |
요약: 가져오기를 백업 프로세스의 일부로 간주하기
mysqldump로 생성된 백업 파일은 단순히 만들기만 해서는 가치가 없습니다. 백업의 진정한 가치는 필요할 때 복원할 수 있도록 보장하는 것입니다. 따라서 정기적으로 가져오기를 테스트하고 파일이 올바르게 로드되는지 확인하는 것이 권장됩니다.
6. 실용적인 팁 및 주의사항
mysqldump는 사용하기 쉽지만, 대규모 데이터베이스와 운영 환경에서는 때때로 신중한 작업과 추가 전략이 필요합니다. 이 섹션에서는 문제를 방지하기 위한 유용한 실용 기술과 주의사항을 소개합니다.
gzip를 사용해 대규모 데이터베이스 압축하기
mysqldump는 평문 SQL 파일을 출력하기 때문에 파일 크기가 매우 커질 수 있습니다. 몇 기가바이트를 초과하는 대규모 데이터베이스의 경우, mysqldump와 gzip을 결합해 출력을 압축하는 것이 일반적입니다.
mysqldump -u username -p database_name | gzip > backup.sql.gz
이 방법은 디스크 사용량을 크게 줄이고 원격 전송 시 부하도 감소시킵니다.
내보내기 및 가져오기 검증을 습관화하기
필요할 때 복원할 수 없다면 백업은 무용지물입니다. 다음과 같은 작업을 권장합니다:
- 정기적으로 별도 환경에서 백업을 복원하여 테스트
- md5sum 또는 sha256sum으로 파일 무결성 확인
- 중요한 데이터베이스에 대해 여러 백업 세대 유지
버전 차이에 주의하기
내보내기 소스와 가져오기 대상의 MySQL 버전이 다르면, 구문 및 내부 동작 차이로 인해 SQL 파일이 올바르게 실행되지 않을 수 있습니다.
- 가능하면 같은 버전에서 작업
- 버전을 넘나들어야 한다면, 옵션으로 동작을 제어 (예:
--set-gtid-purged=OFF) - 업그레이드 전후에 항상 스키마 정의 호환성 을 확인
자동화를 위해 cron 및 스크립트 사용하기
자동 일일 또는 주간 백업을 실행하고 싶다면, 셸 스크립트와 cron을 사용하면 관리가 효율적입니다.
#!/bin/bash
DATE=$(date +%F)
mysqldump -u root -p[password] mydb | gzip > /backup/mydb_$DATE.sql.gz
이와 같은 스크립트를 /etc/cron.daily/에 배치하면 매일 자동으로 백업을 수집할 수 있습니다.
참고: 보안상의 이유로 비밀번호를 직접 작성하지 마세요.
.my.cnf파일을 사용해 안전하게 관리하는 것이 권장됩니다.
보안도 고려하기
백업 파일에는 민감한 정보가 포함될 수 있습니다. 다음과 같은 조치를 구현하세요:
- 저장 위치에 적절한 접근 제한 설정
- 저장 및 전송을 위한 백업 암호화 (예: GPG 또는 SFTP 사용)
- 클라우드에 저장할 경우, 자동 백업 설정 및 수명 주기 관리 를 고려
7. 자주 묻는 질문 (FAQ)
이 섹션에서는 mysqldump 사용 시 흔히 묻는 질문과 자주 발생하는 문제들을 Q&A 형식으로 정리합니다.
Q1. 내보내기 중 “Access denied” 오류가 발생하는 이유는 무엇인가요?
A. 지정된 MySQL 사용자에게 대상 데이터베이스에 대한 “SELECT” 또는 “LOCK TABLES”와 같은 필요한 권한이 없을 수 있습니다. 필요한 권한을 확인하고 필요하면 관리자에게 부여하도록 요청하세요. 테이블 잠금이 실패할 경우, --single-transaction 옵션을 사용하면 도움이 될 수 있습니다.
Q2. 백업 파일 크기가 매우 큽니다. 줄일 방법이 있나요?
A. 대형 테이블이나 대량의 데이터가 있는 경우, SQL 파일이 기가바이트 규모에 이를 수 있습니다. 다음 방법으로 크기를 줄일 수 있습니다:
gzip으로 압축 (예:mysqldump ... | gzip > backup.sql.gz)--no-data또는--no-create-info옵션을 사용해 필요한 부분만 내보내기--where옵션을 사용해 내보낼 데이터를 필터링 (예: 특정 날짜 범위)
Q3. 특정 테이블만 내보내려면 어떻게 해야 하나요?
A. 명령어 끝에 테이블 이름을 나열하면 선택한 테이블만 내보낼 수 있습니다.
mysqldump -u root -p mydb users orders > selected.sql
이것은 전체 데이터베이스가 아니라 특정 테이블만 백업하고 싶을 때 편리합니다.
Q4. 저장 프로시저와 이벤트가 내보내기에 포함되지 않는 이유는 무엇인가요?
A. 기본적으로 mysqldump는 저장 프로시저(루틴)와 예약된 이벤트를 포함하지 않습니다. 이를 포함하려면 다음 옵션을 추가하십시오:
--routines --events
또한 사용자가 이러한 객체에 접근할 수 있는 충분한 권한을 가지고 있는지 확인하십시오.
Q5. 백업 파일을 다른 서버로 전송할 때 주의해야 할 점은 무엇인가요?
A. 고려해야 할 주요 포인트:
- 문자 인코딩 : 서버 간 인코딩이 다르면 텍스트가 깨질 수 있습니다. 필요하다면
--default-character-set=utf8을 명시적으로 지정하십시오. - 버전 차이 : 소스와 대상 MySQL 버전 간 호환성을 확인하십시오.
- 보안 파일 전송 :
scp,rsync,SFTP등을 사용하여 안전하게 전송하십시오. - 파일 무결성 검사 : 전송 후
md5sum또는sha256sum을 사용해 무결성을 검증하십시오.
Q6. Windows와 Mac/Linux 명령어에 차이가 있나요?
A. 기본 명령 구문은 동일하지만, 셸 동작, 배치 처리, 날짜 명령 사용 방식 등에 차이가 있습니다. 예를 들어 날짜를 포함한 파일명을 생성할 때 Windows는 PowerShell이나 %DATE% 변수를 사용할 수 있고, Linux와 macOS는 date 명령을 사용합니다.
8. 결론
이 글에서는 MySQL 백업 및 마이그레이션 도구인 mysqldump를 기본 사용법부터 고급 기법 및 문제 해결까지 다루었습니다.
mysqldump는 간단한 구문을 사용하지만, 목적에 맞는 올바른 옵션과 명령 구성을 선택하면 백업 품질과 복구 신뢰성에 큰 차이를 만들 수 있습니다.
✅ 이 글의 핵심 정리
- 기본 mysqldump 구문 및 세 가지 내보내기 방법 (단일, 다중, 전체 데이터베이스)
- 스키마 전용, 데이터 전용, 선택 테이블 등 유연한 내보내기 변형
- 실제 사용에 중요한 옵션
--single-transaction,--routines포함 - 기본 복원 명령 및 가져오기 오류 처리 방법
- gzip 압축 및 cron 자동화 와 같은 실용적인 팁
- 자주 묻는 질문(FAQ) 섹션에 제공된 유용한 문제 해결 지식
🛡 mysqldump 사용 시 권장 베스트 프랙티스
- 백업만 만들지 말고 복원 가능 여부를 검증하십시오
- 버전 차이와 문자 인코딩으로 인한 문제에 대비하십시오
- 압축, 자동화, 적절한 접근 제어를 포함한 백업 작업을 설계하십시오
- 프로덕션 환경과 일치하도록 저장 프로시저와 이벤트를 포함하십시오
mysqldump를 활용한 적절히 설계되고 운영되는 백업 시스템은 예기치 않은 장애 발생 시 신속한 복구를 가능하게 하며, 시스템 운영의 신뢰성을 높여줍니다. 특히 데이터 손실이 치명적인 기업 시스템이나 WordPress 사이트의 경우, mysqldump를 이해하고 효과적으로 사용하는 것이 필수적입니다.
이 가이드를 참고하여 여러분의 환경에 최적화된 백업 전략을 구축하십시오.
