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 &
백업한.sql 스크립트를 실행해주는 구문인데 여러개를 한꺼번에 돌리기 위해 백그라운드로 돌리도록 해놓고 돌리면 된다. mysql에 접속해서 show processlist;를 봐보면 백업해뒀던 insert구문이 바뀌면서 들어가고 있음을 확인할 수 있다.
위에 용량 구하는 쿼리
select table_name, round(((data_length + index_length) / 1024 / 1024 / 1024), 2) `size (gb)` from information_schema.TABLES WHERE TABLE_SCHEMA = "{내 디비}";
로 계속 실행하면서 어느정도 진행되었는지 확인해보자. 근데 디비 버전이 바뀌거나 아니면 인덱스 크기가 잘 측정이 안되어서 그런지 초기 사이즈와 restore된 사이즈가 다를수 있다. 이 경우에는 용량은 가볍게 참고만 하고 count를 쳐서 갯수가 동일한지 확인해보자.
% 개인적으로 궁금한거.
생각보다 mysqldump로 dump후에 insert를 하나하나 실행하는게 생각보다 느린데.. spark로 mysql의 전 데이터를 가져다가 다른 디비의 동일한 테이블에 복사하면 그것도 백업의 한 방법이 될텐데.. spark의 자원이 어느정도 이상이면 오히려 이게 더 빠르지 않을까? 테스트해보고싶다.