[MySQL] mysqldump 백업

개요

mysql 백업으로 가장 많이 사용되는 유틸리티 중 하나가 mysql에 내장된 mysqldump 명령어 입니다. 다른 유틸리티로는 mysqlhotcopy, xtrabackup 소프트웨어 등이 있다.  다만 mysqlhotcopy는 5.6버전에서 지원되었지만 5.7 버전에서는 제거 되었다. xtrabackup는 별도의 소프트웨어를 설치해야만 사용가능하다.

 

구성환경

mysql-5.7.26 / CentOS 7.6

 

문법

mysqldump  [--옵션] --all-databases mysqldump  [--옵션] --databases db_name mysqldump  [--옵션] --databases db_name --tables table_Name

옵션

--all-database(-A) : mysql와 모든 사용자 데이타베이스

--database(-B) : 덤프할 데이타베이스 지정

--tables : 데이타베이스에서 특정 테이블만 덤프

--flush-logs(-F) : 덤프 실행전 바이너리 로그 switch(새로운 바이너리 로그 파일 생성), --all-databases 옵션과 함께 사용할 경우 각 데이타베이스 백업마다 flush가 일어난다 예로 3개의 데이타베이스가 있고, 현재 바이너리 로그 파일이 bin-log.000004일 경우 이 옵션을 사용하여 백업하면 다음 바이너리 로그 파일은 bin-log.000007이 된다. 예외적으로 --single-transaction 과 함께 사용될 경우는 데이타베이스 수와 상관 없이 한번만 flush가 일어난다.

--single-transaction :  데이타베이스 일관성 유지를 위해 mysqldump 실행전 서버에 START_TRANSACTION SQL 문을 보낸다. (원문)This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data

 

예제) 전체 데이타 베이스 백업

shell> mysqldump -u <username> -p<PASSWORD> -F --single-transaction --all-databases > alldatabase.sql

예제2) 특정 데이타 베이스(testdb) 백업

shell> mysqldump -u <username> -p<PASSWORD> -F --single-transaction --databases testdb > testdb.sql

예제3) 특정 테이블(t1) 백업

shell> mysqldump -u <username> -p<PASSWORD> --databases db_name --tables t1 > t1.sql

예제4) 테이블 구조만 백업

shell> mysqldump  -u <username> -p<PASSWORD> --no-data <데이터베이스명> > <백업파일명>.sql

예제5) 바이너리로그를 이용한 PIT(Point-to-Time) 복구

 - 데이타베이스 덤프파일 : testdb.sql

 - 덤프백업후 생성된 바이너리 로그 파일 확인(ls -l 확인), 덤프후 생성된 바이너리 파일이 2개 이상일 경우 각각 바이너리 로그 파일 마다 mysqlbinlog 를 해줘야 한다.

 - 현재 바이너리로그 파일 확인

mysql> show master status ; +--------------------+----------+--------------+------------------+-------------------+ | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+-------------------+ | testsvr-bin.000031 |     7443 |              |                  |                   | +--------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql>

 - 장애 발생

mysql> drop database testdb ;

 - 바이너리 로그 파일 변환

shell> mysqlbinlog testsvr-bin.000031 > binlog.sql

 - 데이타베이스 복구

shell> mysql -u root -pPASSWORD < testdb.sql

 - 바이너리 로그 파일 복원

shell> mysql -u root -pPASSWORD < binlog.sql