MySQL mysqldump 알아보기

- 목차

 

소개.

mysqldump 는 MySQL 의 데이터 상태를 백업할 수 있는 MySQL 툴입니다.
특정 시점의 MySQL 의 데이터 상태를 스냅샷이라고도 하는데요.
mysqldump 를 통해서 특정 시점의 스냅샷을 생성할 수 있습니다.
 
mysqldump 는 Logical Backup 이라고 불립니다.
이와 반대되는 backup 방식은 Physical Backup 입니다.
MySQL 의 On-Disk 요소인 Tablespace 와 Redo Log 등을 고려하는
Physical Backup 과 달리 Logical Backup 은 단순히 dump 시점의 데이터 상태만들 고려합니다.
즉, Logical Backup 은 Table 에 Lock 을 걸어 현재 시점의 데이터들을 전부 조회하고 조회된 데이터들을 Write 하는 sql 파일을 생성합니다.

이 상황에서 In-Memory <-> On-Disk 의 동기화가 되지 않을 수도 있습니다.
 
따라서 Logical Backup 은 필연적으로 
- "LOCK table write"
- "select * from table"
두 쿼리가 사용됩니다.
 
 

How to User mysqldump?

mysqldump 의 사용법을 설명해보록 하겠습니다.
 

Table Level Dump.

Table Level Dump 는 하나의 Table 을 dump 하는 방식입니다.
 
먼저 docker 를 활용하여 MySQL 컨테이너를 생성합니다.
 
< MySQL 실행 >

docker run -d -it --name mysql --platform linux/amd64 -e MYSQL_ROOT_PASSWORD=1234 mysql:8.0.23

 
아래의 코드예시는 test_db 라는 데이터베이스와 test_table 이라는 테이블을 생성하는 코드흐름입니다.
 
< 테이블 생성 >

// MySQL Docker Container 로 진입
docker exec -it mysql /bin/sh

// MySQL 로 진입
mysql -u root -p 1234

// 테이블을 생성
create database test_db;
use test_db;
create table test_table(
	id int not null auto_increment primary key,
    test_col varchar(32)
);

 
좀더 세부적인 실험을 위하여 insert query 하나와 update query 하나를 실행합니다.
결과적으로 test_table 이라는 테이블은 test_col 이 "test2" 인 Row 하나만이 존재합니다.
 
< 1개의 insert 와 1개의 update >

mysql> insert into test_table(test_col) values('test1');
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update test_table set test_col = 'test2' where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 
mysqldump 를 활용하여 test_table 을 dump 합니다.
 
< test_table 의 backup >

mysqldump -u root -p test_db test_table > /tmp/backup.sql

 
mysqldump 를 사용하여 하나의 sql 파일이 생성됩니다.
backup.sql 이라고 파일명을 지었구요.
backup.sql 파일은 mysqldump 를 실행하는 시점의 test_table 의 상태를 복원할 수 있는 DDL & DML Query 로 구성됩니다.
아래의 코드들은 backup.sql 의 내용입니다.
test_table 테이블을 생성하는 DDL 쿼리와
test_table 의 Row 를 생성하는 DML 쿼리로 구성됩니다.
그리고 Table Lock 을 적용하는 부분도 있죠.
 
 
< dump 결과 확인 >

cat /tmp/backup.sql

-- MySQL dump 10.13  Distrib 8.0.23, for Linux (x86_64)
--
-- Host: localhost    Database: test_db
-- ------------------------------------------------------
-- Server version	8.0.23

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `test_table`
--

DROP TABLE IF EXISTS `test_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `test_col` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test_table`
--

LOCK TABLES `test_table` WRITE;
/*!40000 ALTER TABLE `test_table` DISABLE KEYS */;
INSERT INTO `test_table` VALUES (1,'test2');
/*!40000 ALTER TABLE `test_table` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2023-10-22  5:51:18

 

 

주목할 점은
- LOCK TABLE `test_table` WRITE
- INSERT query
입니다.
 
dump 시에는 해당 table 에 WRITE LOCK 이 걸리므로 단순읽기를 제외한
shared lock, exclusive lock 이 걸린 Read Query 와 모든 Write Query 는 Wait 상태에 머물게 됩니다.
 
그리고 test_table 테이블은 Insert 쿼리와 Update 쿼리라는 변경 기록이 존재하지만,
mysqldump 의 backup.sql 에는 마지막 상태를 insert 하기 위한 쿼리만이 존재합니다.
 

INSERT INTO test_table(name) VALUES('test1');
UDPATE test_table SET name = 'test2' WHERE id = 1';

아래 상태로 변경.

INSERT INTO test_table(name) VALUES('test2');

 
 

Database Level Dump.

데이터베이스 레벨의 mysqldump 를 테스트하기 위해서
하나의 테이블을 더 생성하도록 하겠습니다.
 
< 추가적인 테이블 생성 >

create table test_table2(
	id int not null auto_increment primary key,
    test_col varchar(32)
);

insert into test_table2(test_col) values('value1'), ('value2'), ('value3');

 
데이터베이스 레벨의 mysqldump 는 데이터베이스 이름만 사용합니다.
 
< Database Level mysqldump >

mysqldump -u root -p test_db > /tmp/backup.sql

 
backup.sql 의 내용은 테이블 레벨, 데이터베이스 레벨과 상관없이 유사합니다.
데이터베이스가 관리하는 모든 테이블의 DDL 과 DML 쿼리들이 기록됩니다.
 
< backup.sql 내용 >

cat /tmp/backup.sql

-- MySQL dump 10.13  Distrib 8.0.23, for Linux (x86_64)
--
-- Host: localhost    Database: test_db
-- ------------------------------------------------------
-- Server version	8.0.23

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `test_table`
--

DROP TABLE IF EXISTS `test_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `test_col` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test_table`
--

LOCK TABLES `test_table` WRITE;
/*!40000 ALTER TABLE `test_table` DISABLE KEYS */;
INSERT INTO `test_table` VALUES (1,'test2');
/*!40000 ALTER TABLE `test_table` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `test_table2`
--

DROP TABLE IF EXISTS `test_table2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test_table2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `test_col` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test_table2`
--

LOCK TABLES `test_table2` WRITE;
/*!40000 ALTER TABLE `test_table2` DISABLE KEYS */;
INSERT INTO `test_table2` VALUES (1,'value1'),(2,'value2'),(3,'value3');
/*!40000 ALTER TABLE `test_table2` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2023-10-22  6:05:36

 

Heavy 한 테이블의 backup.

Heavy 한 테이블을 만들어보겠습니다.
25 개의 column 을 가지며, 1백만개의 row 를 가지는 테이블을 backup 하는 테스트입니다.
 
< test_table 테이블 >

create table test_table (
	a int not null auto_increment primary key, 
	b varchar(256) default (UUID()), c varchar(256) default (UUID()), d varchar(256) default (UUID()),
	e varchar(256) default (UUID()), f varchar(256) default (UUID()), g varchar(256) default (UUID()),
	h varchar(256) default (UUID()), i varchar(256) default (UUID()), j varchar(256) default (UUID()),
	k varchar(256) default (UUID()), l varchar(256) default (UUID()), m varchar(256) default (UUID()),
	n varchar(256) default (UUID()), o varchar(256) default (UUID()), p varchar(256) default (UUID()),
	q varchar(256) default (UUID()), r varchar(256) default (UUID()), s varchar(256) default (UUID()),
	t varchar(256) default (UUID()), u varchar(256) default (UUID()), v varchar(256) default (UUID()),
	x varchar(256) default (UUID()), y varchar(256) default (UUID()), z varchar(256) default (UUID())		
);

 
< 1백만개 row insert procedure >

DELIMITER $$
    DROP PROCEDURE IF EXISTS insert_bulk_data;
	CREATE PROCEDURE insert_bulk_data ()
	BEGIN
		DECLARE X INT;
		SET X=1;
		WHILE X <= 1000000 DO
			INSERT INTO test_db.test_table() values();
		SET X=X+1;
		END WHILE;
	END$$
DELIMITER ;
CALL insert_bulk_data();
SELECT count(*) from test_table;

 

1. mysqldump 하는 시간동안에 read query 가 동작하는가?

 
mysqldump 동안에는 단순한 select 쿼리와 share lock 을 동작합니다.
 
< mysqldump 동안 가능한 쿼리 >

select a from test_table order by a desc limit 1;

select a from test_table order by a desc limit 1 for share;

 
반면, DML query 들과 write lock 을 동작하지 않습니다.
 
< mysqldump 동안 불가능한 쿼리 >

select a from test_table order by a desc limit 1 for update;

insert into test_table values();

update test_table set b = 'test' where a != 'test';

delete from test_table limit 1;

 

2. insert query 가 끝난 직후 mysqldump 하면 완벽하게 backup 이 될까 ?

mysqldump 를 통해서 backup.sql 파일을 만들 때에
"select * from table" 과 같은 쿼리문을 통해서 데이터들을 조회합니다.

이 과정에서 데이터들이 In-Memory, On-Disk 의 상태를 상관하지 않고,
현재의 데이터 상태를 조회합니다.
그래서 backup.sql 의 결과는 실제 MySQL 의 데이터 스냅샷과 동일합니다.
 
< 547677 개의 row 를 backup 한 이후의 count 조회 >
 

select count(*) as count from test_table;
+--------+
| count  |
+--------+
| 547677 |
+--------+

 
 
 

Backup sql 적용하기.

 
backup.sql 파일을 적용하는 것은 간단합니다.
mysql 내부에서 source 명령어를 사용하면 됩니다.
 

mysql -u root -p

use test_db;

source /tmp/back.up;

 
< 실행 결과 >

Query OK, 1106 rows affected (0.19 sec)
Records: 1106  Duplicates: 0  Warnings: 0

Query OK, 1106 rows affected (0.19 sec)
Records: 1106  Duplicates: 0  Warnings: 0

Query OK, 1106 rows affected (0.18 sec)
Records: 1106  Duplicates: 0  Warnings: 0

Query OK, 1106 rows affected (0.20 sec)
Records: 1106  Duplicates: 0  Warnings: 0

Query OK, 1106 rows affected (0.19 sec)
Records: 1106  Duplicates: 0  Warnings: 0