[MariaDB] mysqldump를 활용한 백업의 모든 것과 복원

Intro

안녕하세요

초보 DBA 다뉴입니다.

 

MariaDB를 백업하는 여러 방법 중 mysqldump에 대해서 이야기해볼까 합니다.

 

제목에서 볼 수 있드시 mysqldump를 활용한 백업 그리고 복원

이렇게 두가지로 나누어 적었는데요.

 

백업하는 방법과 복원하는 방법은 다른 이야기라 그렇습니다.

하지만 백업이 있으면 복원을 해야 하니 한번에 다루도록 하겠습니다.

---

Backup

mysqldump

mysqldump란,

MariaDB를 백업하는 여러 방식 중에 하나이며, 논리적 백업을 수행합니다.

논리적 백업은 원본 데이터베이스의 개체 정의 및 테이블의 데이터를 재생성하는 SQL문을 생성하는 방식입니다.

SQL문의 파일 뿐만아니라 CSV, XML형태의 파일도 생성 가능합니다.

 

장점으로는 SQL문을 생성하다보니,

SQL문의 백업 파일이 생성된 후 SQL문의 추가나 제거를 통해 원하는 개체 정보로 변경이 가능한 점이있습니다.

단점으로는 이러한 논리적 파일을 생성하는데 있어서, 물리적 백업보다 다소 느린 작업 속도를 가집니다.

 

더 자세한 사항은 아래의 공식 문서를 참고해주세요.

mariadb-dump/mysqldump

사용하기

mysqldump의 기본적인 사용법과 주로 쓰는 옵션들에 대해 알아보도록 하겠습니다.

이 외에도 많은 옵션이 있습니다.

1. mysqldump 기본구조

mysqldump -u[user_id] -p[user_pw] [option] [db_name] [tb_name] > [backup_file_name.sql]

• -u[user_id] : MariaDB 접속 계정
• -p[user_pw] : MairaDB 접속 비밀번호
• [option] : dump 옵션, 여러개 중첩이 가능
• [db_name] : 옵션 별 필요에 의한 Database 명칭
• [tb_name] : 옵션 별 필요에 의한 Table 명칭
• [backup_file_name.sql] :  백업받을 파일 명, 경로가 지정되지 않을 경우 스크립트를 실행하는 디렉토리에 파일 생성.

2. 전체 데이터베이스 백업하기

Option : --all-databases 

mysqldump -u[user_id] -p[user_pw] --all-databases > [backup_file_name.sql]

• --all-databases 
  • 전체 데이터베이스에 대해 백업
  • mysql 등 메타정보가 담긴 데이터베이스도 함께 백업

3. 특정 데이터베이스 백업하기

Option : --databases [db_name_1] [db_name_2] ....

mysqldump -u[user_id] -p[user_pw] --databases [db_name_1] [db_name_2] .... > [backup_file_name.sql]

•  --databases [db_name_1] [db_name_2] ....
• 데이터베이스를 명시적으로 1개 이상 백업

반응형

4. 특정 테이블 백업하기

Option : --databases [db_name] --tables [tb_name_1] [tb_name_2] ....

mysqldump -u[user_id] -p[user_pw] --databases [db_name] --tables [tb_name_1] [tb_name_2] .... > [backup_file_name.sql]

• --databases [db_name] --tables [tb_name_1] [tb_name_2] ....
  • 하나의 데이터베이스에 테이블을을 명시적으로 1개이상 백업
  • 동시에 여러 데이터베이스의 테이블은 백업 할 수 없음

5. 스키마만 백업하기 (데이터 제외)

Option : --no-data

mysqldump -u[user_id] -p[user_pw] --all-databases --no-data > [backup_file_name.sql]

• --no-data
  • 명시된 데이터베이스 혹은 테이블의 개체 정보만 백업
  • sql의 insert 문을 제외하고 백업

6. 데이터만 백업하기 (스키마 제외)

Option : --no-create-info

mysqldump -u[user_id] -p[user_pw] --all-databases --no-create-info > [backup_file_name.sql]

• --no-create-info
  • 명시된 데이터베이스 혹은 테이블의 데이터만 백업
  • sql의 create 문을 제외하고 insert 문만 백업

7. 특정 테이블 제외하고 백업

Option : --ignore-table=[db_name_1].[tb_name_1] --ignore-table=[db_name_2].[tb_name_2] ....

mysqldump -u[user_id] -p[user_pw] --all-databases --ignore-table=[db_name].[tb_name] > [backup_file_name.sql]

• --ignore-table=[db_name_1].[tb_name_1] --ignore-table=[db_name_2].[tb_name_2] ....
  • 해당 테이블만 제외하고 백업
  • 여러 테이블을 제외하고 싶을 경우 여러 번 명시

8. No Lock으로 백업 (InnoDB의 경우)

Option : --single-transaction

mysqldump -u[user_id] -p[user_pw] --all-databases --single-transaction > [backup_file_name.sql]

• --single-transaction
  • 하나의 트랜잭션을 이용하여 No Lock 으로 백업
  • InnoDB의 경우만 No Lock 으로 가능

9. 원격지 백업

Option : -h[host_ip]

mysqldump -u[user_id] -p[user_pw] -h[host_ip] --all-databases > [backup_file_name.sql]

• -h[host_ip]
  • 원격지 [host_ip]의 데이터베이스를 옵션에 맞게 백업

---

Recovery

mysql Commend-Line Client

mysqldump로 생성된 sql 파일은 기본적으로 mysql Commend-Line Client를 이용하여 복구합니다.

정확히는 복구가 아닌 sql 파일 실행인데요.

 

mysql commend line 이 뭐지? 싶을 수 있으실텐데요.

저희가 mariadb 혹은 mysql에 접속하기 위해 콘솔창에 입력하는 아래의 명령어 입니다.

mysql -uroot -p

많이 익숙하신가요? 바로 이 문법이 mysql commend line입니다.

 

자세한 정보는 공식 문서를 참고해주세요.

mysql Command-line Client

사용하기

1. mysql commend-line client 기본 구조 (전체 백업 -> 복구)

mysql -u[user_id] -p[user_pw] < [backup_file_name.sql]

• 전체 데이터베이스 백업 파일을 전체 복구 할 경우 mysql DB와 같은 메타정보가 덮어 써질 수 있으니 주의
• 백업 파일 sql문에 있는 전체를 전부 실행하여 복구합니다.
  • Create Database문이 포함되어있기 때문에 따로 지정한 데이터베이스 명이 없을 경우 생성합니다.

2. 하나의 데이터베이스 복구

Option : --one-database

mysql -u[user_id] -p[user_pw] --one-database [db_name] < [backup_file_name.sql]

• 백업파일 sql문 중 하나의 데이터베이스만 복구 할 경우 사용합니다.
• [db_name]에 해당하는 Database는 미리 생성해둬야 합니다.
  • 다른이름의 Database로도 복구가 가능합니다.

3. 원격지 데이터베이스에 복구

Option : -h[host_ip]

mysql -u[user_id] -p[user_pw] -h[host_ip] < [backup_file_name.sql]

• [host_ip]로 원격지 정보를 입력 후 해당 데이터베이스에 복구합니다.

---

Outro

MairaDB의 논리적 백업과 복구에 대해서 알아보았는데요.

논리적 백업 mysqldump와

해당 백업파일을 실행하여 복구하는 mysql Commend-Line Client의 옵션을 몇 가지 알아보았습니다.

 

이정도만 옵션만 알면 업무에 백업/복구에 대해서는 전반적으로 적용 할 수 있을거라 생각이 드는데요.

실전에서는 여러가지 테스트를 추가로 하신다음 적용하길 바랍니다.

 

다음에는 물리적 백업에 대해서도 다루도록하겠습니다.

 

이상 다뉴였습니다. 

감사합니다.