MYSQL DB 백업하는 방법

MYSQL DB 백업이 필요한 이유가 몇 가지 있습니다. 1. 데이터 보존 차원에서 2. 다른 서버에 DB를 이관하기 위해 3. 테이블스페이스 공간을 확보하기 위해.

 

DB 백업이라고 해서 거창한 건 없습니다. 데이터가 날아가면 연계된 서비스에 치명적이기 때문에 조심스러운 것뿐이지 방법 자체는 간단합니다. 예시를 통해 백업 과정을 알아보겠습니다.

MYSQL DB 백업하기

MYSQL DB 백업은 mysqldump 명령을 사용합니다. mysqldump 사용 구문은 아래와 같습니다.

 

1. 모든 DB를 백업하는 경우

mysql에 저장된 모든 db를 백업하는 경우엔 아래의 명령어를 사용합니다.

mysqldump -u [유저명] -p --all-databases > [백업 파일명].sql

 

2. 특정 DB를 백업하는 경우

mysqldump -u [유저명] -p [데이터베이스명] > [백업 파일명].sql

 

mysqldump로 생성한 백업 파일은 SQL 스크립트 형태로 저장됩니다. 이 파일을 통해 저장된 데이터를 한번에 복원하는 개념입니다. 복원할 땐 아래의 구문을 이용합니다.

mysql -u [유저명] -p [데이터베이스명] < [백업 파일명].sql

 

예시) DB 백업 후 ibd 파일 삭제 후 재생성하기

서두에 언급한 DB 백업의 이유 중 하나인 테이블스페이스 공간을 확보하기 위해 DB 백업 후 ibd 파일을 삭제한 뒤 다시 생성하는 예시를 진행해 보겠습니다.

 

이 작업을 하는 이유를 간단히 설명드리면, ibd 파일의 특성 때문입니다. ibd 파일에는 데이터베이스에 저장되는 데이터가 담깁니다. mysql에서 table이라는 테이블을 create 하면 컴퓨터에 table.ibd라는 이름의 ibd 파일이 생성됩니다. 테이블에 데이터가 더 많이 저장될수록 ibd 파일 용량은 커집니다. 문제는 한번 데이터가 insert 되면서 늘어난 용량은 다시 데이터를 delete 하더라도 줄어들지 않는다는 점입니다. 그래서 데이터를 delete 한 이후 다시 테이블스페이스를 줄이기 위해서는 ibd 파일을 삭제하고 다시 db를 복원하는 작업이 필요합니다.

 

TEST_DB를 만들어서 진행하겠습니다. orders, products, users 세 개의 테이블이 있는 db입니다. 각 테이블에는 값이 들어가 있습니다.

mysql> use TEST_DB
Database changed
mysql> show tables
    -> ;
+-------------------+
| Tables_in_TEST_DB |
+-------------------+
| orders            |
| products          |
| users             |
+-------------------+
3 rows in set (0.00 sec)

mysql> select * from orders;
+----+---------+------------+----------+------------+
| id | user_id | product_id | quantity | order_date |
+----+---------+------------+----------+------------+
|  1 |       1 |          1 |        2 | 2023-01-01 |
|  2 |       1 |          3 |        1 | 2023-01-15 |
|  3 |       2 |          2 |        1 | 2023-02-01 |
|  4 |       3 |          5 |        3 | 2023-02-10 |
|  5 |       4 |          4 |        1 | 2023-03-01 |
+----+---------+------------+----------+------------+
5 rows in set (0.00 sec)

mysql> select * from products;
+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  1 | Product A | 10.99 |
|  2 | Product B | 15.99 |
|  3 | Product C |  8.99 |
|  4 | Product D | 20.99 |
|  5 | Product E | 25.99 |
+----+-----------+-------+
5 rows in set (0.00 sec)

mysql>

 

TEST_DB의 ibd 파일은 /var/lib/mysql/TEST_DB 경로에 있습니다. 직접 가서 확인해 보면 실제로 테이블별로 ibd 파일이 생성되어 있는 걸 보실 수 있습니다.

[root@localhost TEST_DB]# cd /var/lib/mysql/TEST_DB
[root@localhost TEST_DB]# ls
orders.ibd  products.ibd  users.ibd

 

mysql이 실행되어 있는 상태에서 백업을 진행합니다. 백업 명령 이후 TEST_DB_BAK.sql이라는 파일이 생성된 게 확인됩니다.

[root@localhost TEST_DB]# mysqldump -u root -p TEST_DB > TEST_DB_BAK.sql
Enter password:
[root@localhost TEST_DB]# ls
orders.ibd  products.ibd  TEST_DB_BAK.sql  users.ibd

 

mysql을 종료합니다.

[root@localhost TEST_DB]# systemctl stop mysqld

 

생성되어 있던 ibd을 삭제합니다.

[root@localhost TEST_DB]# rm -rf orders.ibd
[root@localhost TEST_DB]# rm -rf products.ibd
[root@localhost TEST_DB]# rm -rf users.ibd
[root@localhost TEST_DB]# ls
TEST_DB_BAK.sql

 

다시 mysql을 실행합니다.

[root@localhost TEST_DB]# systemctl start mysqld

 

ibd 파일 삭제 후 테이블 데이터를 확인해 보면, 모두 사라진 걸 알 수 있습니다. Tablespace is missing for table TEST_DB.orders라는 오류가 확인됩니다.

mysql> use TEST_DB
Database changed
mysql> show tables;
+-------------------+
| Tables_in_TEST_DB |
+-------------------+
| orders            |
| products          |
| users             |
+-------------------+
3 rows in set (0.00 sec)

mysql> select * from orders;
ERROR 1812 (HY000): Tablespace is missing for table `TEST_DB`.`orders`.

 

이제 아래의 명령으로 DB를 복원합니다. 복원 후 폴더에 테이블별 ibd가 다시 생성된 걸 볼 수 있습니다.

[root@localhost TEST_DB]# mysql -u root -p TEST_DB < TEST_DB_BAK.sql
Enter password:
[root@localhost TEST_DB]# ls
orders.ibd  products.ibd  TEST_DB_BAK.sql  users.ibd

 

테이블을 확인해보면 정상적으로 복원된 것이 확인됩니다.

mysql> use TEST_DB
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_DB |
+-------------------+
| orders            |
| products          |
| users             |
+-------------------+
3 rows in set (0.00 sec)

mysql> select * from orders;
+----+---------+------------+----------+------------+
| id | user_id | product_id | quantity | order_date |
+----+---------+------------+----------+------------+
|  1 |       1 |          1 |        2 | 2023-01-01 |
|  2 |       1 |          3 |        1 | 2023-01-15 |
|  3 |       2 |          2 |        1 | 2023-02-01 |
|  4 |       3 |          5 |        3 | 2023-02-10 |
|  5 |       4 |          4 |        1 | 2023-03-01 |
+----+---------+------------+----------+------------+
5 rows in set (0.00 sec)

mysql>

 

mysql db 백업 및 복원 과정을 보여드렸습니다. 간단하긴 해도 db는 매우 중요한 데이터입니다. 작업 시엔 신중하게 진행해야 합니다.