data engineering
mysql simple dump process
qkqhxla1
2021. 11. 4. 14:11
1. 걸릴 시간을 대략적으로 체크해보기 위해 테이블의 크기를 확인한다.
참조 : https://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database
아래 쿼리를 돌려본다. 테이블의 크기가 어느정도 큰 경우로 생각해서 gb단위로 출력해본다.
mysql> select table_name, round(((data_length + index_length) / 1024 / 1024 / 1024), 2) `size (gb)` \
-> from information_schema.TABLES WHERE TABLE_SCHEMA = "{내 디비}";
+-----------------------------------+-----------+
| table_name | size (gb) |
+-----------------------------------+-----------+
.......................................
| table1 | 39.23 |
............................................
| table2 | 72.17 |
| table3 | 47.08 |
.............................................
+-----------------------------------+-----------+
13 rows in set (0.00 sec)
나같은경우 이렇게 72gb짜리 테이블이 가장 큰 테이블이었다. 미리 적자면 72gb짜리의 경우 덤프 자체는 1시간 내로 끝났는데, restore할 경우에 2,3시간이 걸렸다. 테이블의 크기가 상당히 큰 경우에는 파티셔닝이 되어있으면 파티션을 필요한것만 남기고 정리해주자.
2. mysqldump를 사용해 덤프한다.
테이블이 여러개이므로, 동시에 여러개를 덤프하기 위해 쉘파일 내부에 mysqldump를 백그라운드로 실행하는 형식을 취한다. iostat 1같은거로 io를 잘 체크하면서 너무 많은양의 테이블을 동시에 덤프하지 않도록 한다. 쉘파일 예시는 아래와 같다.
dumpscript.sh
#!/bin/bash
mysqldump -u root -p {내 디비} {테이블1} --single-transaction=1 --no-autocommit=1 \
--extended-insert=1 --password=$@ > 테이블1.sql &
mysqldump -u root -p {내 디비} {테이블2} --single-transaction=1 --no-autocommit=1 \
--extended-insert=1 --password=$@ > 테이블2.sql &
옵션은 확인해보고 더 추가하던지 지우던지 한다 : mysqldump 공식문서
파티셔닝이 되어있는 테이블중에 일부 파티션만 백업하고자 하면 --where옵션을 넣어주자.
ex) --where="day > '20210931'"
password옵션을 $@로 넣었는데, 이것은 쉘 파일 실행시 받은 인자를 넣겠다는 말이다. stackoverflow 참조
저런식으로 테이블별로 여러줄을 만들거나 쉘 스크립트를 짠 후 비밀번호를 인자로 위의 스크립트를 실행시켜준다.
./dumpscript.sh {mysql password}
dump된 내 테이블을 보면 아래와 같다.
............................................
DROP TABLE IF EXISTS `{테이블1}`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `{테이블1}` (
`id` int(11) NOT NULL AUTO_INCREMENT,
..............................
PRIMARY KEY (`key`)
) ENGINE=InnoDB AUTO_INCREMENT=65109 DEFAULT CHARSET=utf8;
...........................................
INSERT INTO `{테이블1}` VALUES (1,..........)
.......................................
sql내부에 이미 테이블이 존재하면 drop하는것부터, create table하고 인덱스가 있으면 인덱스 생성구문에, 그 이후로 일반적인 데이터를 넣듯이 insert into구문으로 존재하던 모든 데이터가 다 적혀져있다.
3. dump된 데이터를 적절한 위치로 옮긴 후, restore해준다.
나같은 경우는 이름을 '테이블1.sql', '테이블2.sql'같이 만들었었다. 이후 적당한 위치로 옮긴 후 restore용 스크립트를 만들어준다.
restore.sh
#!/bin/bash
mysql -u root -p {내 디비} --password=$@ < {테이블1}.sql &
mysql -u root -p {내 디비} --password=$@ < {테이블2}.sql &