Mysqldump를 이용한 백업&복구

Mysql의 백업 프로그램입니다. 해당 프로그램은 스토리지 엔진에 상관없이 백업을 받을 수 있습니다.

mysqldump는 기본적으로 dump하려는 trigger에 대한 트리거 권한을 가지고 있어야 합니다.

만약 --single-transaction 옵션을 사용할 수 없는 스토리지 엔진이라면 LOCK TABLES 권한이 추가적으로 필요합니다.

만약, 다른 옵션을 추가적으로 사용한다면 추가적인 다른 권한도 필요하게 됩니다.

복구시에도 dump file을 생성했을 때의 권한을 가지고 있어야합니다.

 

출처 : code-factory.tistory.com/21

 

 

mysqldump 중 DB전체를 백업받는 것은 Oralce의 COLD BACKUP과 유사한 성격을 가지고 있습니다.

백업받은 시점으로 완전복구가 가능하기 때문입니다. 

뿐만 아니라, 스키마별(database),테이블별로도 백업이 가능합니다.

단점으로는, 완전 복구 시간 이후에 유실된 데이터는 복구가 불가능하다는 것입니다. 

 

예를들어, 매일 00:00시에 mysqldump를 이용하여 backup을 받는 crontab이 돌고 있는 서버가 있습니다.

해당 서버가 12.18일 16:00시에 장애가 발생한다면, mysqldump를 이용해서는 12.18일 00:00분으로는 복구가 가능하지만 00:00 ~16:00 까지의 데이터 복구는 불가능하다는 것입니다. 

그래서 Oracle의 COLD BACKUP과 유사한 성격을 가지고 있다고 말씀드린것입니다. 물론, mysqldump 백업을 자주 받으면 데이터 유실 텀이 줄어들긴 합니다만, 자주 백업을 수행하면 성능의 이슈가 생길 수도 있을 것 같습니다.

테스트 시에는, SUPER유저인 root유저로 진행하였습니다.

 

-----------------------------------------------------

Mysql version : 8.0.22

OS : Red Hat Enterprise Linux Server release 7.6

------------------------------------------------------

 

 

1.스키마 백업 복구

mysqldump -u [유저명] -p [스키마명] > [백업스크립트 이름.sql]

의 형식으로 백업을 받을 수 있습니다.

 

복구는

mysql -u root -p < [백업스크립트 이름.sql]

로 복구가 가능합니다.

 

장애상황 : TEST 스키마의 테이블을 지우고 복구해보기

 

1.백업
# mysqldump -u root -p test > backup_test.sql
Enter password:


2.테이블 삭제
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| professor      |
| student        |
| test_dummy     |
| test_inno      |
+----------------+
4 rows in set (0.00 sec)


mysql> select count(*) from test_dummy;
+----------+
| count(*) |
+----------+
|  2702270 |
+----------+
1 row in set (0.24 sec)


mysql> drop table test_dummy;
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| professor      |
| student        |
| test_inno      |
+----------------+
3 rows in set (0.01 sec)

mysql> select count(*) from test_dummy;
ERROR 1146 (42S02): Table 'test.test_dummy' doesn't exist


3.복구
# mysql -u root -p test < backup_test.sql
Enter password:

4.확인
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| professor      |
| student        |
| test_dummy     |
| test_inno      |
+----------------+
4 rows in set (0.00 sec)

mysql> select count(*) from test_dummy;
+----------+
| count(*) |
+----------+
|  2702270 |
+----------+
1 row in set (0.28 sec)
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| professor      |
| student        |
| test_dummy     |
| test_inno      |
+----------------+
4 rows in set (0.00 sec)

mysql> select count(*) from test_dummy;
+----------+
| count(*) |
+----------+
|  2702270 |
+----------+
1 row in set (0.28 sec)

 

 

2.테이블 백업 복구

 

백업은

#mysqldump -u [사용자 계정] -p [패스워드] [스키마명] [원본 백업받을 테이블명] > [백업스크립트 이름.sql]

의 형식으로 백업받을 수 있습니다.

 

복구는 스키마 복구와 마찬가지입니다.

mysql -u root -p < [백업스크립트 이름.sql]

 

 

장애상황 : TEST 스키마의 두개의 테이블을 삭제(test_dummy,test_inno) 후 복구로 확인해보기

백업은 test_dummy 한 테이블만 백업받은 후 진행

1.백업
# mysqldump -u root -p test test_dummy > backup_tab.sql
Enter password:
-rw-r--r--   1 root root  60M Dec 14 02:11 backup_tab.sql


2.테이블 삭제
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| professor      |
| student        |
| test_dummy     |
| test_inno      |
+----------------+
4 rows in set (0.00 sec)

mysql>
mysql> drop table test_dummy;
Query OK, 0 rows affected (0.11 sec)

mysql> drop table test_inno;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| professor      |
| student        |
+----------------+
2 rows in set (0.00 sec)


3.테이블 복구
[root@ysbae mysql_dir]# mysql -u root -p test < backup_tab.sql
Enter password:


4.확인
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| professor      |
| student        |
| test_dummy     |
+----------------+
3 rows in set (0.00 sec)

mysql> select count(*) from test_dummy;
+----------+
| count(*) |
+----------+
|  2702270 |
+----------+
1 row in set (0.27 sec)

백업받지 않은 test_inno는 복구되지 않음.

 

 

3.데이터베이스 백업 복구

백업 명령어는

mysqldump --all-databases -u [사용자 계정] -p --default-character-set=euckr > [백업스크립트 이름.sql]

 

복구는 스키마 복구와 마찬가지입니다.

mysql -u root -p < [백업스크립트 이름.sql]

 

장애상황 : TEST 스키마와 DB_TEST 스키마 두개를 지우고 복구해보기

 

1.데이터베이스 전체 백업
# mysqldump --all-databases -u root -p --default-character-set=utf8 > backup_all.sql
Enter password:


2.스키마 삭제
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| classicmodels      |
| db_test            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> drop database db_test;
Query OK, 1 row affected (0.03 sec)

mysql> drop database test;
Query OK, 3 rows affected (0.12 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| classicmodels      |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


3.복구
#mysql -u root -p < backup_all.sql
Enter password:


4.확인
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| classicmodels      |
| db_test            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
7 rows in set (0.00 sec)

 

 

 

테스트 결과, 복구는 매우 빠르게 복구가 되는 것을 확인하였습니다.(스키마,테이블,데이터베이스 전체 모두 포함)

스키마냐 테이블이냐 DB 전체냐에 따라 백업으로 스크립트를 생성하는 것은 시간의 차이가 발생했습니다.

하지만 굉장히 간편한 백업과 복구방법이라고 생각됩니다.

Mysql을 사용하는 환경이라면 잘 활용해서 안전하게 데이터 백업과 복구를 할 수 있을 것으로 생각됩니다.

 

 

출처 : server-talk.tistory.com/30