SQL

MySQL 데이터베이스 백업 및 복원 가이드

Keun.R
Keun.R
13 min read
MySQL 데이터베이스 백업 및 복원 가이드
Photo by Sunder Muthukumaran / Unsplash

On this page

데이터베이스 관리자는 시스템 오류나 데이터 손실에 대비해 주기적인 백업을 수행하는 것이 매우 중요합니다. MySQL에서는 mysqldump 명령어를 통해 데이터베이스를 백업하고 복원할 수 있습니다. 이 블로그 포스팅에서는 MySQL 데이터베이스를 백업하고 복원하는 방법을 자세히 설명합니다.

배경 지식

MySQL은 오픈 소스 관계형 데이터베이스 관리 시스템(RDBMS)으로, 전 세계적으로 널리 사용됩니다. 데이터베이스 백업은 데이터 손실을 방지하고, 시스템 복구를 용이하게 하기 위해 필수적인 작업입니다. mysqldump는 MySQL에서 제공하는 데이터베이스 백업 도구로, SQL 형식의 덤프 파일을 생성합니다. 이 덤프 파일을 사용하여 데이터베이스를 복원할 수 있습니다.

데이터베이스 백업

mysqldump 명령어를 사용한 데이터베이스 백업

mysqldump 명령어는 MySQL 데이터베이스를 백업하는 가장 일반적인 방법입니다. 다음은 mysqldump 명령어를 사용하여 데이터베이스를 백업하는 방법입니다.

mysqldump -u [사용자이름] -p [데이터베이스이름] > [백업파일경로/파일명.sql]

예시

  • 사용자 이름: root
  • 데이터베이스 이름: mydatabase
  • 백업 파일 경로 및 이름: /path/to/backup/mydatabase_backup.sql
mysqldump -u root -p mydatabase > /path/to/backup/mydatabase_backup.sql

이 명령어를 실행하면 비밀번호를 입력하라는 프롬프트가 나타납니다. 비밀번호를 입력하면 데이터베이스가 백업 파일로 저장됩니다.

데이터베이스 복원

mysql 명령어를 사용한 데이터베이스 복원

백업된 SQL 파일을 사용하여 데이터베이스를 복원할 수 있습니다. 다음은 mysql 명령어를 사용하여 데이터베이스를 복원하는 방법입니다.

mysql -u [사용자이름] -p [데이터베이스이름] < [백업파일경로/파일명.sql]

예시

  • 사용자 이름: root
  • 데이터베이스 이름: mydatabase
  • 백업 파일 경로 및 이름: /path/to/backup/mydatabase_backup.sql
mysql -u root -p mydatabase < /path/to/backup/mydatabase_backup.sql

이 명령어를 실행하면 비밀번호를 입력하라는 프롬프트가 나타납니다. 비밀번호를 입력하면 백업 파일에 저장된 데이터가 지정된 데이터베이스로 복원됩니다.

주의사항

  1. 파일 경로 정확성: 백업 파일의 경로가 정확한지 확인합니다. 경로가 잘못되면 파일을 찾을 수 없어 복원에 실패할 수 있습니다.
  2. 접근 권한: 백업 및 복원 작업을 수행하는 사용자에게 적절한 권한이 부여되어 있는지 확인합니다. 백업 작업을 수행하려면 SELECT 권한이 필요하며, 복원 작업을 수행하려면 INSERT 권한이 필요합니다.

데이터베이스 이름 확인: 복원할 데이터베이스가 이미 존재해야 합니다. 데이터베이스가 존재하지 않으면 복원하기 전에 데이터베이스를 생성해야 합니다.

CREATE DATABASE mydatabase;

결론

MySQL 데이터베이스의 백업과 복원은 데이터 손실을 방지하고 시스템 복구를 위해 필수적인 작업입니다. mysqldump와 mysql 명령어를 사용하면 손쉽게 백업과 복원을 수행할 수 있습니다. 이 가이드를 참고하여 정기적인 백업을 통해 데이터를 안전하게 보호하세요.

AWS RDS 또는 NHN Toast DB와 같은 클라우드 기반 데이터베이스를 사용할 때는 로컬 데이터베이스와 다소 다른 방법으로 데이터를 가져오고 백업할 수 있습니다. 아래는 이러한 클라우드 DB에서 데이터를 백업하고 가져오는 방법에 대해 설명합니다.

AWS RDS를 사용한 데이터베이스 백업 및 복원

AWS RDS 데이터베이스 백업

AWS RDS에서는 자동 백업과 수동 스냅샷을 사용하여 데이터베이스를 백업할 수 있습니다.

  1. 자동 백업: RDS 인스턴스를 생성할 때 자동 백업을 활성화할 수 있습니다. 자동 백업은 정해진 기간 동안 유지되며, 필요에 따라 복원할 수 있습니다.
  2. 수동 스냅샷: 수동으로 RDS 인스턴스의 스냅샷을 생성하여 백업할 수 있습니다.
    • AWS Management Console에서 RDS 인스턴스로 이동합니다.
    • 백업할 인스턴스를 선택하고 Actions 버튼을 클릭합니다.
    • Take snapshot을 선택하고 스냅샷 이름을 입력한 후 Take snapshot을 클릭합니다.

AWS RDS 데이터베이스 복원

  1. 자동 백업 복원:
    • AWS Management Console에서 RDS 인스턴스로 이동합니다.
    • Snapshots 탭에서 복원할 스냅샷을 선택합니다.
    • Actions 버튼을 클릭하고 Restore snapshot을 선택합니다.
    • 새로운 인스턴스 이름과 설정을 입력한 후 Restore DB instance를 클릭합니다.
  2. 수동 스냅샷 복원:
    • AWS Management Console에서 RDS 인스턴스로 이동합니다.
    • Snapshots 탭에서 복원할 스냅샷을 선택합니다.
    • Actions 버튼을 클릭하고 Restore snapshot을 선택합니다.
    • 새로운 인스턴스 이름과 설정을 입력한 후 Restore DB instance를 클릭합니다.

데이터 내보내기 및 가져오기

AWS RDS에서도 mysqldump와 mysql 명령어를 사용할 수 있습니다.

데이터베이스 내보내기

mysqldump -h [RDS 엔드포인트] -u [사용자이름] -p [데이터베이스이름] > [백업파일경로/파일명.sql]

데이터베이스 가져오기

mysql -h [RDS 엔드포인트] -u [사용자이름] -p [데이터베이스이름] < [백업파일경로/파일명.sql]

NHN Toast DB를 사용한 데이터베이스 백업 및 복원

NHN Toast DB에서도 AWS RDS와 유사한 방식으로 데이터베이스를 백업하고 복원할 수 있습니다. NHN Toast DB는 자체 관리 콘솔을 통해 백업과 복원을 관리할 수 있습니다.

NHN Toast DB 데이터베이스 백업

  1. 자동 백업 설정:
    • NHN Toast 콘솔에 로그인합니다.
    • DB 인스턴스로 이동하여 백업 설정을 구성합니다.
    • 자동 백업 주기와 보관 기간을 설정합니다.
  2. 수동 백업:
    • NHN Toast 콘솔에서 DB 인스턴스를 선택합니다.
    • 백업 메뉴로 이동하여 수동 백업을 선택합니다.
    • 백업 이름을 입력하고 백업을 생성합니다.

NHN Toast DB 데이터베이스 복원

  1. 자동 백업 복원:
    • NHN Toast 콘솔에서 DB 인스턴스를 선택합니다.
    • 백업 및 복원 메뉴로 이동하여 복원할 백업을 선택합니다.
    • 복원 버튼을 클릭하고 복원 설정을 입력합니다.
  2. 수동 백업 복원:
    • NHN Toast 콘솔에서 DB 인스턴스를 선택합니다.
    • 백업 및 복원 메뉴로 이동하여 복원할 수동 백업을 선택합니다.
    • 복원 버튼을 클릭하고 복원 설정을 입력합니다.

데이터 내보내기 및 가져오기

NHN Toast DB에서도 mysqldump와 mysql 명령어를 사용할 수 있습니다.

데이터베이스 내보내기

mysqldump -h [NHN Toast DB 엔드포인트] -u [사용자이름] -p [데이터베이스이름] > [백업파일경로/파일명.sql]

데이터베이스 가져오기

mysql -h [NHN Toast DB 엔드포인트] -u [사용자이름] -p [데이터베이스이름] < [백업파일경로/파일명.sql]

결론

AWS RDS와 NHN Toast DB 모두 백업 및 복원 기능을 제공하여 데이터베이스의 안정성을 높이고 데이터 손실을 방지할 수 있습니다. mysqldump와 mysql 명령어를 사용하면 데이터를 쉽게 내보내고 가져올 수 있습니다. 정기적인 백업을 통해 데이터의 안전성을 확보하고, 필요 시 빠르게 복원할 수 있도록 대비하는 것이 중요합니다.

도커에서 Dump

1. MySQL 데이터 덤프 생성

먼저, 현재 실행 중인 MySQL 컨테이너에서 데이터베이스 덤프를 생성해야 합니다. 도커 exec 명령을 사용하여 컨테이너 내에서 mysqldump를 실행합니다.

docker exec -it [컨테이너_이름] /usr/bin/mysqldump -u [사용자명] -p[비밀번호] [데이터베이스명] > [백업파

2. 덤프 파일을 안전한 위치로 이동

이제 생성된 덤프 파일을 호스트 머신으로 복사합니다.

docker cp [컨테이너_이름]:/path/to/backup.sql /path/on/host

utf8mb4_0900_ai_ci

ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci' 오류는 MySQL 서버가 덤프 파일에 지정된 collation인 utf8mb4_0900_ai_ci를 인식하지 못할 때 발생합니다. 이는 보통 MySQL 서버의 버전 차이에서 비롯됩니다. MySQL 8.0에서 utf8mb4_0900_ai_ci collation이 도입되었기 때문에, 이 collation을 사용하는 덤프 파일을 MySQL 8.0 이하 버전에서 복원하려고 하면 문제가 발생합니다.

이 문제를 해결하려면 덤프 파일의 collation을 지원되는 collation으로 변경하거나, 덤프를 생성할 때 호환 가능한 collation을 지정할 수 있습니다. 여기서는 덤프 파일의 collation을 변경하는 방법을 설명하겠습니다.

덤프 파일의 Collation 변경

  1. 덤프 파일을 텍스트 편집기로 엽니다.
  2. utf8mb4_0900_ai_ci를 utf8mb4_general_ci 또는 호스트 MySQL 서버가 지원하는 다른 collation으로 바꿉니다.

명령어를 사용해서 덤프 파일의 collation을 일괄적으로 변경할 수도 있습니다:

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' backup.sql

이 명령어는 backup.sql 파일에서 utf8mb4_0900_ai_ci를 utf8mb4_general_ci로 모두 변경합니다.

덤프 생성 시 호환 가능한 Collation 지정

MySQL 덤프를 생성할 때 호환 가능한 collation을 직접 지정할 수도 있습니다. 이를 위해 --default-character-set 옵션을 사용합니다:

docker exec -it 6e2 /bin/sh -c 'mysqldump --default-character-set=utf8mb4 -u ghost -pghostpass ghost > /backup.sql'

이렇게 하면 덤프 파일이 utf8mb4 character set과 기본 collation으로 생성됩니다.

덤프 파일 복원

덤프 파일의 collation을 변경한 후, 다시 MySQL 서버에 복원을 시도하세요:

mysql -u batiai --host=....com --port=3306 blog < backup.sql

윈도우에서 처리하기

  • 덤프 파일 열기:notepad++, VS Code 등 텍스트 편집기에서 덤프 파일을 엽니다.
  • 찾기 및 바꾸기:Ctrl + H를 눌러 찾기 및 바꾸기 창을 엽니다.찾을 내용에 utf8mb4_0900_ai_ci를 입력합니다.바꿀 내용에 utf8mb4_general_ci를 입력합니다.모두 바꾸기 버튼을 클릭하여 덤프 파일 내의 모든 utf8mb4_0900_ai_ci를 utf8mb4_general_ci로 변경합니다.

Trouble Shooting

SUPER privilege and binary logging

Task 'MySQL restore' started at Sat Jul 13 22:42:28 KST 2024 ERROR 1419 (HY000) at line 478: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

MySQL에서 CREATE FUNCTION이나 CREATE PROCEDURE 같은 저장된 프로시저를 생성할 때 바이너리 로깅이 활성화된 경우입니다. 이를 해결하려면 두 가지 방법이 있습니다:

1. SUPER 권한 부여

SUPER 권한이 있는 사용자로 로그인하여 SUPER 권한을 부여할 수 있습니다. 예를 들어, root 사용자로 로그인하여 권한을 부여할 수 있습니다.

sql코드 복사GRANT SUPER ON *.* TO 'batiai'@'%';
FLUSH PRIVILEGES;

2. log_bin_trust_function_creators 변수 설정

log_bin_trust_function_creators 변수를 ON으로 설정하여 덤프 파일을 가져올 때 SUPER 권한 없이 저장된 프로시저나 함수를 생성할 수 있도록 합니다. 이 설정을 변경하려면 SUPER 권한이 필요합니다.

RDS 환경에서 설정 변경

RDS 환경에서는 파라미터 그룹을 통해 설정을 변경할 수 있습니다.

  1. AWS Management Console에서 RDS 인스턴스를 선택합니다.
  2. Parameter Groups를 선택합니다.
  3. 사용 중인 파라미터 그룹을 편집하여 log_bin_trust_function_creators 변수를 1로 설정합니다.
  4. 변경 사항을 저장하고 RDS 인스턴스를 재시작합니다.

MySQL 클라이언트를 사용하여 설정 변경

만약 RDS가 아닌 자체 MySQL 서버를 사용 중이라면, MySQL 클라이언트를 사용하여 설정을 변경할 수 있습니다.

sql코드 복사SET GLOBAL log_bin_trust_function_creators = 1;

3. 예제

권한 부여 예제

sql코드 복사-- root 사용자로 로그인한 후
GRANT SUPER ON *.* TO 'batiai'@'%';
FLUSH PRIVILEGES;

설정 변경 예제 (RDS가 아닌 경우)

sql코드 복사-- root 사용자로 로그인한 후
SET GLOBAL log_bin_trust_function_creators = 1;

결론

RDS 환경에서는 파라미터 그룹을 통해 log_bin_trust_function_creators 설정을 변경하고, 그 외의 환경에서는 SUPER 권한을 부여하거나 설정을 변경하여 문제를 해결할 수 있습니다.

Subscribe to Keun's Story newsletter and stay updated.

Don't miss anything. Get all the latest posts delivered straight to your inbox. It's free!
Great! Check your inbox and click the link to confirm your subscription.
Error! Please enter a valid email address!