MariaDB 이전 전략 (mariadb-dump)

개요

DB 를 이전해야합니다! public 서브넷의 인스턴스에서 private 서브넷의 인스턴스로 이전해야 합니다.

현재 MariaDB 를 사용하고 있는데 이를 위해 간단히 mariadb-dump 를 통해서 해보려 합니다.

mariadb-dump 는 MariaDB 데이터베이스에서 백업 및 복원을 위한 유틸리티 기능입니다. 주요 기능은 DB 구조나 데이터를 SQL 스크립트로 덤프하는 것입니다.

DB 에서 덤프 는 DB 구조, 데이터를 파일로 내보내는 작업을 의미합니다.

mariadb-dump, mysqldump 는 대부분 기능과 옵션이 호환 가능합니다. MariaDB 11.0.1 부터 mysqldump (symlink) 는 사용되지 않고 mariadb-dump 로 사용됩니다.

본문

기본 사용법

# 백업
# 특정 데이터베이스 이하 테이블들을 덤프
mariadb-dump [options] -p [database_name] [tbl_name ...] > backup.sql

ex ) dump 데이터베이스의 user, comment 테이블 덤핑
mariadb-dump -p dump user comment > dump.sql

--

# 여러 데이터베이스들을 덤프
mariadb-dump [options] -p --databases db_name ... > backup.sql

ex ) dump, dummy 데이터베이스 덤핑
mariadb-dump -p dump dummy > dump.sql

--

# 모든 데이터베이스 덤프
mariadb-dump [options] -p --all-databases > backup.sql

--

# 복원
mariadb --host [local or remote] -u [username] -p [database_name] < backup.sql

ex ) 도커 컨테이너에 원격 이전 (private ip)
# 단일 데이터베이스 덤프 적용
mariadb --host 172.17.0.8 -p --database dump < dump.sql

# 전체 데이터베이스 덤프 적용
mariadb --host 172.17.0.8 -p  < dump.sql

옵션

• --all-databases : 모든 데이터베이스를 덤프합니다.
• --databases [db1] [db2] : 특정 데이터베이스를 덤프합니다.
• --single-transaction : 트랜잭션 기반 스토리지 엔진 사용 시 스냅샷 기반으로 데이터 일관성 보장합니다. (DB 테이블 락을 걸지 않으며 InnoDB 에 잘 동작합니다.)
• --routines : 저장 프로시저 및 함수 포함합니다.
• --events : 이벤트를 포함합니다.
• --quick : row by row 로 실행해 한 번에 많은 메모리를 차지하는 걸 방지합니다.
• --compact : 부가적인 주석 등을 제거해 경량화에 도움을 줍니다.
• --host : dump 를 할 DB 서버의 Host 지정 (원격으로 가능하게 한다.)
• --port : Host 의 포트 (기본값 : 3306)
• --user : user 지정 가능
• -- extended-insert : 기본적으로 켜져있으며 여러 줄의 insert 를 일괄적으로 처리하게 해준다.
• --hex-blob : BINARY, VARBINARY, BLOB, BIT 등 바이너리 문자를 HEX 값으로 바꿔 저장합니다.
• --default-character-set : 기본 문자 인코딩 지정. 기본값은 utf8mb4

최적화 기법
기존 DB 가 어느 정도 규모가 있다면 한 덤프 파일로 전부 이전하는 것은 힘듭니다.

그럴 땐 다음 옵션들을 고려해주세요.

1. mariadb 최적화

mariadb --single-transaction --quick -p [database_name] > backup.sql

• --single-transaction : READ_COMMITTED , REPEATABLE READ 같은 격리수준으로 데이터를 덤프합니다.
• --quick : 데이터를 한 번에 메모리에 올리지 않고 한 줄 씩 처리합니다. (메모리 사용량을 줄여줍니다.)

2. 파티션으로 나누기

# 테이블 명으로 나누기
mariadb -u [username] -p [database_name] [table_name] > backup.sql

# 날짜를 기준으로 나누기
mariadb -u user -p --where="created_at < '2025-01-01'" database table > table_part1.sql
mariadb -u user -p --where="created_at >= '2025-01-01'" database table > table_part2.sql

테이블 단위로 나누어 덤프해 큰 테이블을 처리합니다.

3. 압축

# 백업
mariadb -u [username] -p [database_name] | gzip > backup.sql.hz

# 복원
gunzip < backup.sql.gz | mariadb -u [username] -p [database_name]

전략

• 작은 규모 DB : mariadb 이용
• 중간 규모 DB : mariadb 최적화 옵션
• 큰 규모 DB : Persona XtraBackup 기능 고려 (따로 공부 필요)

---

추가사항

1. single-transaction 옵션이 없을 시 테이블에 대한 lock 을 걸게 됩니다. 따라서 user 에게 해당 테이블에 대한 lock 권한이 있어야합니다.

   single-transaction 옵션이 있다고 lock 발동하지 않는 이유엔 트랜잭션을 통해 일관성이 지켜짐도 있고 테이블 lock 이 발동할 경우 지연 중인 트랜잭션이 커밋되기 때문에 서로 mutually exclusive 한 관계라 시스템 차원에서 table lock 옵션을 해제 합니다.

2. DB 서버에 접근만할 수 있다면 mariadb cli 가 있는 어디에서든 작업을 할 수 있다.

3. 성능에 관하여
   공식 문서에선 mariadb-dump 가 싱글 슬레드를 사용하여 서버의 CPU 를 많이 잡아먹진 않는다 말합니다. 대신 DB 데이터가 저장되는 디스크에 백업 파일을 생성하면 파일 입출력이 많이 발생해 성능 이슈가 발생할 수 있다합니다. 공식 문서에서는 dump 작업을 서비스 중단 없이 진행한다는 가정 하에 설명을 하는 것 같습니다. 이러한 가정 아래 서비스에서 발생하는 DB 접근 디스크 입출력과 백업을 위해 디스크에 접근하는 비용이 동시에 발생해 디스크 입출력 관련 이슈가 발생합니다. 이에 대한 해결책으로는 백업 파일을 네트워크를 통해 다른 디스크에 생성되도록 하는 것입니다. 또는 성능 좋은 디스크를 사용하는 것입니다.

딱히 처리 성능에 관한 장점이 핵심인 도구는 아닌 것 같습니다. 성능이 중요하다면 다른 도구도 고려해봐야겠습니다.

4. user 테이블이 잘 동작하지 않음
   --all-databases 옵션을 통해 모든 DB 를 이전했습니다. mysql.user 의 내용도 이전되었는데 권한은 이전되지 않았습니다. 그리고 로그인에도 실패했습니다.
   뭐가 문제인지 살펴봤는데 잘 해결이 안돼 이 부분은 따로 쿼리문을 만들어 해결할 계획입니다.

참고출처
https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html
https://mariadb.com/kb/en/mariadb-dump/