sed 명령어를 통한 mysql table add colume 하기!

문제상황

약 3gb가 넘는 table에 새로운 컬럼을 하나 추가해야하는 상황이었다. (대략 4억 이상의 데이터 값들이 들어있음)

첫번째. 

처음에는 아무생각없이 아래와 같은 방법으로 컬럼을 추가해봤다.

alter table ~~ add new_colume after ~~

도저히 끝날 기미가 보이지 않고, 실제 운영중인 서비스이기 때문에 최대한 lock (downtime)이 걸리면 안되었다.

두번째.

갓  스택오버플로우의 검색결과 

abc라는 테이블에 컬럼을 추가한다고 했을 때, abc_v2 테이블을 하나 만들고 여기다가 colume 추가 후 abc의 값들을 abc_v2로 옮기는  방법이다.

30분씩 50만개씩 쉬지않고 data를 옮긴다고 가정했을 때, 4억 / 50만개  = 24000분 => 400시간 => 16.6일 이상의 걸리는 신밧드의 모험을 경험 할 수 있다.

마지막.

위에서 하는 방법은 시간이 너무 오래걸려, 서비스에 지장이 갈 수 있다. 그렇다면 만들어 둔 abc_v2를 활용하면서 더 빠르게 작업할 수 있는 방법은 있을까?

물론 있다.

흔히 우리가 mysqldump를 통한 백업을 많이 진행한다.

즉 mysql dump과정에서 백업할 때 필요한 정보들이 저장이된다. (create문, insert문 등등)

이러한 원리를 이용해보자.

컬럼을 추가하고 싶은  db의 table을 백업 받고 (abc 테이블) , 그 백업된(sql 확장자로 저장된) 파일의 메타정보를 수정하면 어떨까? 

mysqldump --opt --user=root--p! --lock-tables=false --single-transaction --no-create-info --order-by-primary --complete-insert  db_name abc > abc.sql

이렇게 백업을 받는다. 

lock-table false로 지정, single-transation을 지정한 이유는 downtime없이 (lock없이) 서비스가 실행되고 싶기 때문이다.

--no-create-info : create table 안해주기 위해 지정, 

--complete-insert : INSERT INTO `abc_v2` (`id`, `price_btc`, `is_approx`, `coin_id`, `timestamp`) 형식을 맞추기 위해 지정 => 이걸 하는 이유는 abc와는 달리 abc_v2에 새로운 컬럼을 추가했음으로, table 구조가 변했기 때문이다.

이런식으로 sql를 백업한다.

다음 리눅스에 내장되어 있는 sed 명령어를 활용하여 진행해봤다.

ed명령어와 grep명령어 기능의 일부를 합친 것이 sed(stream editor)명령어이다.

라인들을 하나씩 읽고 , 수정하고, 출력하기 때문에 기억장치 안의

버퍼를 사용하지 않는다는 것이다. 버퍼를 사용하지 않으면 파일의 크기에 제한 없이 작업을 할 수 있다.

ed와 같이 버퍼를 사용하는 경우는 버퍼의 크기보다 큰 파일은 처리할 수 없으며 대개 버퍼의 크기는

1MB정도이다. 따라서 sed는 아주 큰 화일을 처리할 때 주로 사용된다.

(출처 http://millky.com/@origoni/post/664)

저장된 abc.sql 파일을 한번 sed 명령어를 통해 살펴보자.

sed -n ‘1,10p’ abc.sql

실행결과

/*!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 */;

/*!40101 SET NAMES utf8 */;

/*!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 */;

--

-- Dumping data for table `abc`

--

실행결과 dumping된 table의 이름은 abc이렇게 나온다.

이 메타정보들을 abc_v2로 인식이 되게하여, 테이블 복원시 abc_v2 테이블에 데이터가 지정되도록 바꿔주면 된다.

즉 이번에도 sed 명령어를 통해 메타정보를 바꾸자.

sed -i ’s/abc/abc_v2/g’ abc.sql

생각보다 간단하다. 위의 명령은 abc의 텍스쳐를 abc_v2로 모두 변경하는 명령이다.

데이터가 클 수록 5분이상 걸릴 수도 있다.

변경 후

/*!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 */;

/*!40101 SET NAMES utf8 */;

/*!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 */;

--

-- Dumping data for table `abc_v2`

--

이로써 abc의 테이블의 이름은 abc_v2로 바뀌었고, 기존 abc의 값들을 abc_v2로 밀어 넣어주기만 하면 끝이 난다.

mysql -u root -p db_name < abc.sql