mysqldump 를 통한 mysql 백업,복구

mysqldump

- mysql or mariadb 설치시 기본적으로 제공 되는 backup util

- xtrabackup과는 다르게, 엔진 종류에 상관 없이 논리적인 백업을 제공한다.

( => 논리적인 백업은 백업 자체가 파일로 복사 되는 물리적인 방식이 아닌 백업 진행시 SQL문이 아래 그림과  집합 형태로 구성 되어 있음)

- 논리적인 백업 이기때문에 물리적인 백업에 비하여 시간이 오래 걸리지만, 사용자가 직관적으로 백업 파일을 확인 하여 처리 할 수 있어 백업 파일에 대하여

, 직접 수정, 삭제가 가능 하다.

- 대용량의 innodb 엔진을 사용 한다면 xtrabackup 혹은 mysql 엔터프라이즈 백업 유틸을 통하여 백업을 관리 하는 것이 용이 하다.

( 백업에 대한 시간, 복구 시간  등 으로 인하여 )

- 백업 결과를 SQL 파일 형식 외에 json, xml , txt  등의 형식으로 저장 가능 하다.

- mysql 설치시 아래와 같이 바이너리 파일 형태로 자동으로 추가 된다.

[root@node201 ~]# ls -al /usr/local/mariadb/bin/mysqldump

-rwxr-xr-x. 1 root mysql 6220769  5월 15 05:56 /usr/local/mariadb/bin/mysqldump

mysqldump백업

- 전체 데이터베이스 백업 

[root@node201 ~]# mysqldump --all-databases -uroot -p > all_backup.sql 

- 특정 데이터베이스 백업

[root@node201 ~]# mysqldump --databases [DB명] -uroot -p > db_backup.sql

- 특정 테이블 백업

 [root@node201 ~]# mysqldump -table [table 명] -uroot -p > table_backup.sql

mysqldump복구

- 리다이렉션 을 이용 하여 아래와 같이 sql 파일을 DB에 직접 전달 복구 할 수 있다.

[root@node201 ~]# mysql -uroot -p < all_backup.sql

Enter password:

- mysql shell 에 접속 하여 source 명령어를 통하여 복구가 가능 하다.

MariaDB [(none)]> source /root/all_backup.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

... 생략 

mysqldump 백업 파일 구조

- 위에 설명 한것 처럼 mysqldump 는 논리적 백업으로 해당 데이터베이스의 SQL문 등 논리적으로 사용자가 해당 정보를 직관적으로 볼 수 있는 구조로 되어 있다.

- vi,nano 를 이용하여 파일을 열면 아래와 같이 확인 할 수 있다.

- 가장 먼저 아래와 같이  DB를 생성 하기전 실행 되는 세션 레벨의 시스템 변수를 SET을 통하여 선언 한다.

- 사용자자 database 를 만드는 것 처럼 database  객체를 생성 한다.

- 생성한뒤 dummy 라는 db 를 사용 하여 dummy 라는 테이블을 DROP후 새로 생성 한다.

- DB와 table 을 생성후 백업 데이터에 대한 무결성을 위하여 table 을 lock 한다.

(lock 되지 않을 경우 실제 복구 중 새로운 데이터가 들어 온다면 무결성이 깨진다.)

- lock 이 된 상태에서 실제 insert 를 통하여 데이터를 삽입 한다.

- 데이터에 대한 삽입이 끝나면 lock 을 해제 한다.