기존 인덱스 기반 테이블에서 UUID 기반 구조로 데이터 이관하기

들어가며

목적

기존 테이블의 데이터를 새롭게 설계된 테이블 구조로 이관한다.

이 과정에서 기존에 인덱스로 관리되던 유저 아이디는 UUID로 교체한다.

이 글에서 다룰 내용

1. 데이터 추출
2. UUID 매핑
3. 새로운 테이블 DDL 생성
4. 데이터 매핑
5. 스냅샷 생성 후 삭제

1. 데이터 추출

1.1. 기존 데이터 추출하기

mysqldump는 데이터베이스의 데이터를 가져오는 읽기 전용 명령어이고, 기본 사용은 아래와 같다.

mysqldump -h <RDS 엔드포인트> -u <유저이름> -p --databases <데이터베이스명> > <파일명>

예를 들어보면, 다음은 127.0.0.1 호스트, 3307 포트, admin 사용자, example 데이터 베이스를 backup.sql 파일명으로 저장한다는 명령어이다. 명령어 실행 후 비밀번호를 입력하면 해당 명령어를 실행한 터미널의 현재 디렉터리에 backup.sql 파일이 생성된다.

여기서 3307 포트 번호를 사용하는 것은 로컬 MySQL 서버가 3306을 사용 중일 수 있기 때문에 터널링 시 충돌을 피하기 위함이다.

mysqldump -h 127.0.0.1 -P 3307 -u admin -p --databases example > backup.sql

1.1.1. 옵션 추가하기

위의 명령어로 입력을 하면 다음 경고 문구가 뜰 것이다.

Enter password: Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that were executed during its extraction and might not be represented in the dumped data. This might result in an inconsistent data dump. In order to ensure a consistent backup of the database, pass --single-transaction or --lock-all-tables or --master-data.

 

이는 2가지 옵션에 대한 경고를 나타낸다.

1. GTID(Global Transaction ID) 관련 경고
   • RDS MySQL에서 GTID가 활성화되어 있을 때, mysqldump 명령어를 실행하면 GTID도 함께 덤프되는데, 이는 복원 시 GTID 충돌을 야기할 수 있으므로 --set-gtid-purged=OFF 옵션을 추가해준다.
2. 일관성 관련 경고
   • --single-transaction 이 옵션은 InnoDB 테이블에서만 동작하며, mysqldump로 백업을 시작할 때 트랜잭션을 시작해서 특정 시점의 스냅샷을 기준으로 데이터를 덤프한다. 백업 도중 데이터가 변경되어 불완전하거나 불일치한 덤프가 되는 것을 방지한다.

이를 반영하여 아래와 같이 덤프를 진행한다.

mysqldump -h 127.0.0.1 -P 3307 -u admin -p --single-transaction --set-gtid-purged=OFF --databases example > backup.sql

1.2. 추출한 데이터 옮기기

아래 명령어를 통해 추출한 데이터를 옮기고자 하는 데이터베이스에 이관할 수 있다. 이때, 동일한 이름의 테이블이 있을 경우 덮어쓰기가 되고, 없는 경우는 새로 생성된다.

mysql -h 127.0.0.1 -P 3307 -u admin -p < backup.sql

2. uuid 매핑

유저 아이디의 경우, 식별자로 숫자 인덱스를 사용하는 것보다 UUID를 사용할 때 다양한 장점을 확보할 수 있다. 무작위 문자열은 유저의 아이디를 추측할 수 없기 때문에 보안성을 확보할 수 있고, 전역적으로 고유하므로 마이크로 서비스에 적합하며, 데이터 이전 및 통합 시 충돌을 방지할 수 있다.

2.1. 매핑 테이블 생성

기존 유저 아이디와 UUID가 매핑될 테이블을 생성한다. id만 입력하면 UUID가 생성되도록 디폴트를 설정했다.

CREATE TABLE tb_user_uuid_mapping (
    user_id bigint PRIMARY KEY, 
    uuid varchar(255) NOT NULL default(uuid())
);

2.2. 매핑 테이블 데이터 입력

기존 T_USER 테이블에서 식별자로 사용되었던 id 값을 가져와서 매핑한다.

INSERT INTO tb_user_uuid_mapping (user_id) 
SELECT id FROM T_USER;

3. DDL 입력

테이블을 생성한다. tb_user를 살펴보면, idx를 인덱스로 설정하고, user_id에 UUID를 입력할 것이다. 변경된 다른 테이블들도 생성해준다.

CREATE TABLE `tb_user` (
  `idx` bigint NOT NULL AUTO_INCREMENT,
  `user_id` varchar(255) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) DEFAULT NULL,
  `created_at` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `deleted_at` timestamp(3) DEFAULT NULL,
  PRIMARY KEY (`idx`)
);

4. 데이터 매핑

4.1. 데이터 정리

새로 생성할 테이블에 기존 테이블의 컬럼들 중 어떤 것을 연결해야 하는지 정리한다.

4.2. Select 구성

먼저 select를 하여, 목표한 데이터가 제대로 추출되는지 살펴본 뒤 insert를 진행하여 실수를 방지하자.

4.2.1. CASE 구문

기존 값에 따라 다른 값을 입력하고 싶을 때 사용한다. 예를 들어, 기존 값이 A, B, C로 표현된 유저의 티어를 gold, silver, bronze로 표현한다고 했을 때, 다음과 같이 사용할 수 있다.

SELECT
  CASE
    WHEN u.tier = "A" THEN 'gold',
    WHEN u.tier = "B" THEN 'silver',
    WHEN u.tier = "C" THEN 'bronze',
    ELSE 'iron'
  END,
...

 

boolean을 표현하고 싶다면 아래처럼 when then을 한번만 사용해서 나타낼 수도 있다.

SELECT
  CASE WHEN u.tier = 'A' THEN true ELSE false END,
...

4.2.2. IFNULL

값이 null 일 때 디폴트를 입력하고 싶다면 ifnull을 사용할 수 있다.

SELECT
    IFNULL(u.point, 0),
...

4.2.3. CAST

타입이 다르다면 CAST를 사용할 수 있다. 아래는 string인 u.point가 빈문자열일 경우, null을 반환하고, 이를 int로 변환한다는 말이다. 즉, “2000”은 2000 으로 저장되고, 빈문자열인 경우는 null이 된다.

SELECT
  CAST(NULLIF(u.point, '') AS SIGNED),
...

4.2.4. DATE_ADD

특정 날짜를 수정해서 저장하고 싶다면, DATE_ADD를 사용할 수 있다. 그냥 숫자를 쓰면 증가하고, 음수 기호와 함께 사용하면 감소된 날짜가 입력된다.

SELECT
	DATE_ADD(u.approved_at, INTERVAL 1 DAY),
...

4.3. JOIN 구성

4.3.1. 일대다 관계에서 LEFT JOIN & CASE 사용

CASE 문을 사용할 때, 일대다 관계라면 데이터가 의도와 다르게 중복된 것처럼 보일 수 있다. 예를 들어, 유저가 주문을 한 경우를 생각해보자. 유저 A는 완료, 대기 상태인 2개의 주문, 유저 B는 완료 상태인 1개의 주문을 했다고 가정하자.

만약 원하는 것이 유저 옆에 주문 여부를 나타내는 것이라고 하면, 아래 쿼리를 실행하면 아래 표와 같은 결과를 얻는다.

SELECT
  u.user_id,
  u.name,
  CASE WHEN o.order_id IS NOT NULL THEN true ELSE false END AS has_order
FROM user u
LEFT JOIN orders o ON u.user_id = o.user_id;

 

user_id	name	has_order
1	A	true
1	A	true
2	B	false

이때는 MAX, GROUP BY 등을 활용하여 1개만 출력되도록 해야 한다. 최근 주문의 주문번호 저장을 위해 서브 쿼리를 조인하는 경우, 아래와 같이 구현할 수 있다.

SELECT
    u.user_id,
    latest_order.status
FROM user u
LEFT JOIN (
    SELECT o1.user_id, o1.status
    FROM orders o1
    JOIN (
        SELECT user_id, MAX(id) AS max_id
        FROM orders
        GROUP BY user_id
    ) o2 ON o1.user_id = o2.user_id AND o1.id = o2.max_id
) latest_order ON latest_order.user_id = u.user_id

5. 스냅샷 생성 후 삭제

모든 과정을 거쳤다면 기존 테이블을 삭제하기 전에 스냅샷으로 데이터를 저장해놓자.