Mysqldump

이기현·2021년 11월 22일

MariaDB

목록 보기

8/29

mysqldump는 현재 database의 data를 읽어와서 동일한 데이터를 생성하는 쿼리를 만든다.
만약 운영중인 상태에서 mysqldump를 통해 백업을 받게 되면 inconsistence state가 발생할 수 있다.

mysqldump는 db에 있는 table을 순차적으로 백업 받는다. 예를 들어 20개의 테이블이 있는 db를 백업 받을 때 1~5번 테이블까지 백업을 받은 상태에서 1번,20번 테이블의 PK/FK 컬럼에 insert가 발생한다면 dump 파일에는 20번 테이블에 row는 기록되지만 1번 테이블에 row는 저장되지 않았기 때문에, 정합성의 문제가 발생할 수 있다.

따라서 Online중에 일관된 데이터 백업을 받기 위해서는 별도의 옵션을 지정해야 한다.

InnoDB 스토리지 엔진만을 사용하는 경우

--single-transaction 옵션을 사용하면 된다.

--single-transaction 옵션을 사용하면 Transaction Isolation Level을 REPEATABLE READ 로 설정 한 후 data를 dump 받기 전에 START TRANSACTION 문을 수행한다.

따라서 dump는 하나의 트랜잭션 안에서 이루어지기 때문에 데이터 일관성이 보장된다.
또한 MVCC 구조로 인해 다른 커넥션에서 insert , update, delete 명령어를 사용할 수 있다.

주의점으로는 백업 수행중에 ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE 구문을 사용하면 안된다. 해당 문들을 사용하면 mysqdump에서 수행한 SELECT가 테이블 내용을 검색하여 잘못된 내용을 가져오거나 실패할 수 있다.

참고 : https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

수행구문

mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

--routines dumps all stored procedures and stored functions
--triggers dumps all triggers for each table that has them
if you have a mix of MyISAM and InnoDB tables, using the above options can leave your MyISAM (or Memory tables, for that matter) in an inconsistent state, since it does not lock reads/writes to MyISAM tables.

InnoDB/MyISAM을 같이 사용하는 경우

--lock-tables 옵션을 사용해야 한다.

MyISAM은 Transaction을 지원하지 않기 때문에, --single-transaction옵션을 사용해도 consistency가 보장되지 않는다. 따라서 MyISAM엔진 테이블에 대한 consistency를 보장하기 위해서는 모든 테이블에 lock을 건 후 백업을 수행해야 한다

--lock-tables는 모든 테이블에 대한 lock을 걸기 때문에 운영중인 DB에서 사용 시 업무 영향도가 클 수 있다. 따라서 InnoDB만 사용하는 경우는 --single-transaction을 사용해야 한다.

--lock-tables locks tables for each database separately, this option does not guarantee that the tables in the dump file are logically consistent between databases. Tables in different databases may be dumped in completely different states.