> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL의 무중단 스키마 변경 > 읽기나 쓰기를 차단하지 않고 라이브 MySQL 테이블에 ALTER TABLE을 실행하는 방법, 온라인 DDL이 도울 수 없는 경우, 그리고 gh-ost가 그 틈을 메우는 방법입니다. > [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md) > > All examples on this page work on VillageSQL. Install Now → 라이브 트래픽이 있는 테이블에 `ALTER TABLE`을 실행하면 질문이 하나 생깁니다: 이 문이 실행되는 동안 애플리케이션을 차단할까? 이 가이드는 실제 테이블에서, 동시 연결이 내내 지켜보는 가운데, 그것을 직접 측정합니다. [MySQL의 스키마 마이그레이션](https://villagesql.com/docs/ko/guides/schema-migrations.md)은 마이그레이션 파일을 구성하고 버전 관리하는 방법을 다룹니다. 이 가이드는 그런 마이그레이션 파일 중 하나가 실행되는 동안 라이브 테이블이 실제로 무엇을 하는지 다룹니다. ## 테스트 설정 이 가이드의 모든 숫자는 같은 테이블에서 나왔습니다: 로컬 VillageSQL 서버에 3,276,802개 행(데이터 168.7 MB)을 적재한 `orders` 테이블입니다. ```sql theme={null} CREATE TABLE orders ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, status VARCHAR(20) NOT NULL, amount_cents INT NOT NULL, created_at DATETIME NOT NULL ) ENGINE=InnoDB; ``` 행은 숫자 테이블 교차 조인으로 생성한 다음, 자기 자신에서 `INSERT ... SELECT`로 몇 번 두 배로 늘려 수백만 행에 도달했습니다. 정확한 크기는 아래 결론에 중요하지 않지만, 전체 테이블 재구축이 1초의 일부보다 오래 걸릴 만큼 테이블이 커야 합니다. 그렇지 않으면 그것과 동시에 실행되는 것을 아무것도 관찰할 수 없습니다. ## 온라인 ALTER는 트래픽을 차단하지 않습니다 위의 327만 행 테이블에 `ALGORITHM=INPLACE, LOCK=NONE`으로 보조 인덱스를 추가하면: ```sql theme={null} ALTER TABLE orders ADD INDEX idx_customer_id (customer_id), ALGORITHM=INPLACE, LOCK=NONE; ``` 처음부터 끝까지 1.003초가 걸렸습니다. 실행 중에 두 번째 연결의 `SHOW PROCESSLIST`는 그것을 실제로 진행 중인 작업으로 보여주었습니다: ``` Id User Host db Command Time State Info 325 root localhost zdt_demo Query 0 altering table ALTER TABLE orders ADD INDEX idx_customer_id (customer_id), ALGORITHM=INPLACE, LOCK=NONE ``` `information_schema.INNODB_TRX`는 같은 테이블에 대한 별도의 `ADD INDEX`에서 같은 문을 실행 중인 트랜잭션으로 보여줍니다: ``` trx_id: 6472 trx_state: RUNNING trx_query: ALTER TABLE orders ADD INDEX idx_status (status), ALGORITHM=INPLACE, LOCK=NONE ``` 위의 `ALTER`가 아직 `altering table` 상태인 동안 열어서 실행한 세 번째 연결에서, 같은 테이블에 대한 `INSERT`와 `SELECT`가 모두 즉시 완료되었습니다: ```sql theme={null} INSERT INTO orders (customer_id, status, amount_cents, created_at, priority, priority2) VALUES (999999, 'pending', 500, NOW(), 0, 0); -- 0.009s SELECT COUNT(*) FROM orders WHERE status='paid'; -- 0.271s ``` 어느 문도 `ALTER`가 끝나기를 기다리지 않았습니다. 이것이 `LOCK=NONE`의 핵심입니다: 테이블 재구축은 InnoDB의 온라인 DDL 로그로 동시 변경을 기록하면서 백그라운드에서 일어나고, 테이블에 대한 DML은 재구축이 따라잡아 교체될 때까지 원래의 데이터 구조에 대해 진행됩니다. ## ALGORITHM=INSTANT 대 ALGORITHM=INPLACE 모든 온라인 작업의 비용이 같지는 않습니다. `ALGORITHM=INSTANT`로 테이블 끝에 열을 추가하는 것은 테이블 메타데이터만 바꿉니다. 행을 하나도 읽거나 다시 쓰지 않습니다: ```sql theme={null} ALTER TABLE orders ADD COLUMN priority TINYINT NOT NULL DEFAULT 0, ALGORITHM=INSTANT; -- 0.011s ``` 같은 형태의 변경, 즉 기본값이 있는 열 추가를 대신 `ALGORITHM=INPLACE`로 강제하면: ```sql theme={null} ALTER TABLE orders ADD COLUMN priority2 TINYINT NOT NULL DEFAULT 0, ALGORITHM=INPLACE, LOCK=NONE; -- 0.244s ``` 이 327만 행 테이블에서, 기능적으로 같은 열 추가에 대해 `INSTANT`가 `INPLACE`보다 대략 20배 빨랐습니다. `INPLACE`는 여전히 테이블의 데이터 딕셔너리 항목을 건드리고 동시 DML과 조정해야 합니다. `INSTANT`는 둘 다 하지 않습니다. 차이는 테이블 크기와 함께 커집니다: `INSTANT`는 행 수와 관계없이 일정하지만, `INPLACE`는 행 수에 비례합니다. `INSTANT`를 모든 작업에 쓸 수 있는 것은 아닙니다. `BLOB`/`TEXT` 기본값이 있는 열을 추가하거나 기존 열의 유형을 바꾸는 것은 `INPLACE`나 `COPY`로 돌아갑니다. 테이블 중간을 포함해 어느 위치에든 열을 추가하는 것도 `INSTANT`를 사용합니다(MySQL 8.0.29+). ## 온라인 DDL이 도울 수 없는 경우: 열 유형 변경 모든 `ALTER TABLE`이 잠금을 피할 수 있는 것은 아닙니다. 열의 데이터 유형을 바꾸려면 MySQL이 모든 행을 다시 써야 하고, `INPLACE`는 그것을 할 수 없습니다: ```sql theme={null} ALTER TABLE orders MODIFY COLUMN amount_cents BIGINT NOT NULL, ALGORITHM=INPLACE, LOCK=NONE; ``` ``` ERROR 1846 (0A000) at line 2: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. ``` 같은 변경에 `ALGORITHM=INSTANT`를 요청하면 다른 이유로 같은 방식으로 실패합니다: ``` ERROR 1846 (0A000) at line 2: ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE. ``` MySQL이 이 문에 받아들이는 유일한 알고리즘은 `COPY`이며, 테이블 전체를 재구축하고 그동안 배타적 잠금을 잡습니다. 같은 테이블에 대해 실행하고 재구축 중에 두 번째 연결에서 행을 삽입해 보면 차이가 직접 드러납니다: ```sql theme={null} ALTER TABLE orders MODIFY COLUMN amount_cents BIGINT NOT NULL, ALGORITHM=COPY; -- 6.776s ``` `ALTER`가 시작하고 0.3초 뒤에 보낸 동시 `INSERT`는 `ALTER`가 거의 끝날 때까지 돌아오지 않았습니다: ```sql theme={null} INSERT INTO orders (customer_id, status, amount_cents, created_at, priority, priority2) VALUES (1, 'pending', 1, NOW(), 0, 0); -- 6.466s ``` 이 동안의 `SHOW PROCESSLIST`는 `ALTER`를 `copy to tmp table` 상태로, 그 옆의 차단된 `INSERT`를 `Waiting for table metadata lock` 상태로 보여주었습니다. 그 상태가 진단입니다: 어느 문이 멈춰 있는지 짚어 주고, 느리게 실행되는 것이 아니라 잠금 뒤에 줄 서 있다고 알려줍니다. 이 같은 문에 `LOCK=NONE`으로 빨리 실패하게 해 보는 것이 바로, 프로덕션 테이블에 실행하기 전에, 다시 쓰기가 걸리는 시간만큼 쓰기 작업을 차단할 것임을 알아내는 방법입니다. ## 외래 키와 온라인 DDL 외래 키는 `ALGORITHM`과 `LOCK`과 독립적인 제한을 더합니다. `FOREIGN KEY (order_id) REFERENCES orders(id)`가 있는 자식 테이블 `order_items`에서, 평범한 `ADD COLUMN`은 외래 키가 있어도 여전히 `ALGORITHM=INSTANT`로 밀리초 안에 완료됩니다. 하지만 2가지 작업은 알고리즘과 관계없이 바로 실패합니다: 외래 키가 의존하는 부모의 기본 키 삭제: ```sql theme={null} ALTER TABLE orders DROP PRIMARY KEY, ADD PRIMARY KEY (id, customer_id), ALGORITHM=INPLACE, LOCK=NONE; ``` ``` ERROR 1553 (HY000) at line 2: Cannot drop index 'PRIMARY': needed in a foreign key constraint ``` 참조하는 부모 열과 독립적으로 자식 열의 유형 변경: ```sql theme={null} ALTER TABLE order_items MODIFY COLUMN order_id INT NOT NULL, ALGORITHM=COPY; ``` ``` ERROR 3780 (HY000) at line 2: Referencing column 'order_id' and referenced column 'id' in foreign key constraint 'fk_order' are incompatible. ``` 두 오류 모두 MySQL이 알고리즘을 고르기 전인 구문 분석과 검증 시점에 일어납니다. 마이그레이션이 외래 키에 관련된 열을 바꿔야 한다면, 외래 키를 같은 마이그레이션의 일부로 삭제하고 다시 만들어야 하며, 부모와 자식 열의 유형은 모든 단계에서 양쪽이 호환되어야 합니다. ## 온라인 DDL이 안전하게 할 수 없는 변경을 위한 gh-ost 위의 `MODIFY COLUMN ... ALGORITHM=COPY` 경우가 온라인 DDL이 도울 수 없는 경우입니다: 전체 행 다시 쓰기가 필요하지만, 라이브 테이블에서 그동안 잠금을 잡고 싶지는 않습니다. `gh-ost`는 새 스키마로 테이블의 복사본("고스트" 테이블)을 만든 다음, 백그라운드에서 기존 행을 거기에 복사하면서 복사 중에 원래 테이블에 이루어진 모든 쓰기를 트리거 대신 바이너리 로그를 스트리밍해 재생하는 방식으로 이것을 처리합니다. 짧은 원자적 이름 바꾸기로 전환하며 끝납니다. `gh-ost`는 다른 테이블이 외래 키로 참조하는 테이블의 마이그레이션을 거부합니다: 앞 섹션의 `order_items` 테이블에는 `FOREIGN KEY (order_id) REFERENCES orders(id)`가 있고, 그 제약 조건이 그대로 있는 채로 `orders`에 `gh-ost`를 실행하면 `FATAL found 1 parent-side foreign keys on ... orders. Parent-side foreign keys are not supported. Bailing out`으로 즉시 실패합니다. 다른 테이블이 참조하는 테이블에 `gh-ost`를 실행하기 전에 그 외래 키를 먼저 삭제하세요(또는 `--discard-foreign-keys`를 전달하세요. 이 옵션은 외래 키를 대신 삭제하고 다시 만들지 않습니다). 바로 이런 종류의 변경을 위해, `order_items`의 외래 키를 삭제한 채로, 같은 3,276,802행 테이블에 대해 이것을 설치하고 실행했습니다: ```bash theme={null} # macOS brew install gh-ost # Linux (Debian/Ubuntu) curl -LO https://github.com/github/gh-ost/releases/latest/download/gh-ost.amd64.deb sudo dpkg -i gh-ost.amd64.deb ``` ```bash theme={null} gh-ost \ --user=root --password= \ --host=127.0.0.1 --port=3306 \ --database=zdt_demo --table=orders \ --alter="MODIFY COLUMN customer_id BIGINT NOT NULL" \ --allow-on-master \ --initially-drop-ghost-table \ --exact-rowcount \ --chunk-size=2000 \ --default-retries=5 \ --execute ``` 실제 비밀번호가 있다면, 명령줄의 `--password` 대신 `-conf` 파일로 전달하세요. 명령줄 비밀번호는 호스트의 다른 모든 사용자에게 `ps`로 보이고 셸 기록에 남습니다. `--execute`가 없으면 `gh-ost`는 드라이런 모드로 실행됩니다: 연결하고, binlog를 스트리밍하고, 고스트 테이블에 사용할 `CREATE TABLE` 문을 출력하지만, 아무것도 바꾸지 않습니다. `--execute`를 주면 전체 마이그레이션을 13.112초에 실행했고, 진행하면서 진행 상황을 출력했습니다: ``` Copy: 596000/3276802 18.2%; Applied: 0; Backlog: 1/1000; Time: 3s(total), 3s(copy); ...; ETA: 8s Copy: 1762000/3276802 53.8%; Applied: 0; Backlog: 0/1000; Time: 7s(total), 7s(copy); ...; ETA: 5s Copy: 3276802/3276802 100.0%; Applied: 0; Backlog: 0/1000; Time: 12s(total), 12s(copy); ...; ETA: due ``` 같은 마이그레이션의 두 번째 실행에서 3초가 지났을 때, 라이브 `orders` 테이블에 대한 동시 `INSERT`는 0.007초에 돌아왔고, `gh-ost` 자신의 진행 줄이 그 쓰기를 받아 재생했음을 확인해 주었습니다(`Applied: 1`): ``` Copy: 1812000/3276803 55.3%; Applied: 1; Backlog: 1/1000; Time: 7s(total), 7s(copy); ... ``` 전환 후 `SHOW CREATE TABLE orders`는 `customer_id`를 `bigint`로 보여주었고, 행 수는 정확히 일치했으며(3,276,803, 원래 수에 마이그레이션 중 삽입된 행 1개를 더한 값), 마이그레이션 중에 삽입된 행은 올바른 값으로 존재했습니다. `gh-ost`는 원래 테이블을 삭제하지 않고 `_orders_del`로 이름을 바꾸었으므로, 전환을 확인한 뒤 비교하거나 수동으로 삭제할 수 있도록 마이그레이션 전 테이블이 여전히 남아 있습니다. 이것은 `binlog_format=ROW`와 `log_bin=ON`이 이미 설정된 독립 실행형 서버에 대해 실행했습니다. `gh-ost`는 변경을 스트리밍하기 위해 이것을 요구하며, 문 기반 복제에서는 동작하지 않습니다. 또한 테이블에 기본 키나 고유 키가 있어야 합니다. 둘 다 흔한 기본값이라서 여기서는 추가 설정이 필요 없었지만, 확인하지 않은 테이블에 `gh-ost`를 믿고 쓰기 전에 둘 다 확인하세요. ## 자주 묻는 질문 #### ALTER TABLE이 테이블을 차단할지 어떻게 미리 알 수 있나요? 실행하기 전에 문에 `ALGORITHM=INPLACE, LOCK=NONE`(또는 `LOCK=NONE`만)을 추가하세요. MySQL이 차단 없이 변경할 수 있으면 실행됩니다. 할 수 없으면, 실행하고 차단하는 대신 즉시 오류를 반환합니다. 어느 쪽이든, 추측하거나 소스 코드를 읽지 않고 확정적인 답을 얻습니다. #### 열 유형 변경에서 ALGORITHM=INSTANT가 왜 실패했나요? `INSTANT`는 MySQL이 변경을 메타데이터 업데이트만으로 표현할 수 있을 때만 동작합니다. 열의 저장 유형을 바꾸면 기존 모든 행의 디스크 표현이 새 유형에 맞지 않으므로, 행을 읽고 다시 써야 합니다. 그것이 `INSTANT`를 배제하고, 유형 변경에서는 특히 `INPLACE`도 배제합니다. `COPY`만 할 수 있습니다. #### gh-ost는 복제 설정이 필요한가요? 아니요. 마이그레이션하는 서버의 바이너리 로그를 읽는데, `binlog_format=ROW`인 서버라면 복제본이 있든 없든 이미 그것을 만들어 냅니다. `gh-ost`는 복제본에 대해 실행하고 소스에서 전환하는 것도 지원하며, 매우 크거나 매우 바쁜 테이블에는 그쪽이 더 안전한 패턴이지만, 그것은 배포 선택이지 동작시키기 위한 요구 사항은 아닙니다. #### gh-ost가 끝난 뒤 옛 테이블은 어떻게 되나요? 기본적으로 `gh-ost`는 원래 테이블을 삭제하는 대신 접미사(위에서 기록한 실행에서는 `_del`)를 붙여 이름을 바꿉니다. 직접 삭제할 때까지 스키마에 사용되지 않은 채 남아 있습니다. ## 문제 해결 | 문제 | 해결 방법 | | :- | :- | | `ERROR 1846: ALGORITHM=INPLACE is not supported` | 작업에 전체 다시 쓰기가 필요함. 점검 시간에 `ALGORITHM=COPY`를 쓰거나, 라이브 테이블에는 `gh-ost`/`pt-online-schema-change`를 사용. | | `ERROR 1846: ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table...` | 변경이 메타데이터만으로 끝나지 않음. 먼저 `ALGORITHM=INPLACE, LOCK=NONE`을 시도하고, 그것도 실패하면 `COPY`로 돌아가기. | | `ERROR 1553: Cannot drop index 'PRIMARY': needed in a foreign key constraint` | 참조하는 외래 키를 먼저 삭제하고, 인덱스를 변경한 다음, 외래 키를 다시 만들기. | | `ERROR 3780: Referencing column ... and referenced column ... are incompatible` | 외래 키의 자식 열과 부모 열은 유형이 호환되어야 함. 양쪽을 함께 바꾸거나, 마이그레이션 동안 외래 키를 삭제. | | `COPY` 알고리즘 `ALTER`가 끝이 보이지 않게 쓰기 작업을 막고 있음 | `SHOW PROCESSLIST`에서 상태를, `information_schema.INNODB_TRX`에서 얼마나 오래 실행되었는지 확인. 기다릴 수 없다면, 중단하고 `gh-ost`로 전환. | | `gh-ost`가 시작을 거부 | `binlog_format=ROW`인지, 대상 테이블에 기본 키나 고유 키가 있는지 확인. 둘 다 필수. | ## 함께 보기 * [MySQL의 스키마 마이그레이션](https://villagesql.com/docs/ko/guides/schema-migrations.md) — 이 가이드가 실행하는 `ALTER TABLE` 문을 만들어 내는 마이그레이션 파일을 구성하고 버전 관리하기 * [MySQL 인덱스의 동작 방식](https://villagesql.com/docs/ko/guides/mysql-indexes.md) — 온라인 `ADD INDEX` 중에 MySQL이 만드는 인덱스가 이후 쿼리 성능에 미치는 영향 * [MySQL의 EXPLAIN 읽기](https://villagesql.com/docs/ko/guides/reading-explain.md) — 마이그레이션이 끝난 뒤 새로 추가한 인덱스가 실제로 사용되는지 확인하기 This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.