[Percona Toolkit] pt-table-sync

사용법 

pt-table-sync는 MySQL 테이블 간에 데이터를 효과적으로 맞춰주는 프로그램으로 다음과 같이 사용한다. 

pt-table-sync [OPTIONS] DSN [DSN]

이 툴은 최대한 안전하게 데이터를 수정하는 프로그램이지만, 가능하면 사용 전에 데이터 백업을 하기를 권장한다. 만약,  --replicate 또는 --sync-to-master라는 옵션을 사용하여 데이터를 싱크 하는 작업을 하려고 한다면, 항상 마스터가 직접 데이터 변경 내역을 처리해야 하고, 슬레이브 서버로는 절대 직접 DML 쿼리를 받아서는 안된다. 즉, 슬레이브 서버는 읽기 모드로 동작해야 하는 상태이어야만 한다. 그렇지 않으면, 데이터에 문제가 발생할 수 있다. 

host1에서 host2로 db.tbl을 맟추려는 경우에는 다음과 같이 실행한다. 

 

 

host1,2,3 서버의 모든 테이블을 맞추고자 하는 경우 다음과 같이 실행한다. 

 

 

마스터 서버에서 슬레이브 서버로 맞추고자 하는 경우 다음과 같이 실행한다. 

 

 

pt-table-checksum을 통해 확인한 테이블을 마스터 서버의 데이터로 모든 슬레이브 서버에 맞추려고 하는 경우 다음과 같이 실행한다. 

 

 

데이터가 다른 슬레이브 서버 한대에 대해서만 맟추려고 하는경우 다음과 같이 실행한다. 

 

 

multi-master 구성의 리플리케이션에서 master2의 db.tbl 테이블의 데이터를 master1의 것으로 맞추려고 하는 경우 다음과 같이 실행한다. 

 

 

multi-master 구성의 리플리케이션에서 다음과 같이 실행하면 안된다. 

 

 

사용 설명 

pt-table-sync는 단방향과 양방향의 데이터 동기화를 제공하는 프로그램이다. 하지만 테이블의 구조나 인덱스, 다른 스키마적인 내용을 맞춰주지는 않는다. 

먼저 단방향 동기화 작업은 다음과 같이 진행한다. 

이 프로그램은 복잡다단하게 만들어졌기 때문에 안전하게 사용하기 위해 다음의 3가지를 이해해야 한다. 바로 --replicate 옵션의 목적과, 다른 점을 찾는 것, 그리고 호스트를 명시하는 방법이다. 이 세가지 개념을 정확히 이해하고 있어야만 제대로 프로그램을 구동시킬 수 있다. 

논리 로직은 다음과 같다. 

 

 

pt-table-sync는 2가지 방법을 선택할 수 있다. --replicate 옵션을 사용하는 방법과 사용하지 않는 방법이 그것이다. 기본으로 실행하면 --replicate옵션이 없는 방식으로 구동되는데, 이것은 자동적으로 다른 부분을 찾아서 처리하게 한다. 알고리즘은 구현되어있는 것 중 한가지를 선택하게 한다. --replicate 옵션을 사용하게 된다면, pt-table-sync는 --replicate옵션으로 실행한 pt-table-checksum에서 추출한 정보를 기반으로 작업을 진행하게 된다. 좀 더 업격하게 이야기 해서, pt-table-sync가 다른 점을 찾을 수 있기 때문에 --replicate 옵션을 사용할 필요가 없다. 하지만, 많은 사람들이 --replicated 옵션을 사용하는데, 그 이유는 일반적으로 pt-table-checksum을 통해 다른 점을 먼저 찾기 때문이다. 

--replicate 사용과 상관없이, 사용자는 동기화 하는데 사용할 서버에 대한 정보를 명시해야 한다. 이것은 두가지 방법이 있다. --sync-to-master 옵션을 사용하는 방법과 사용하지 않는 방법이 있다. --sync-to-master 옵션을 사용하면, 하나의 서버에 대한 작업만 기대하고, 실제 DSN 정보도 슬레이브 서버 한대에 대한 정보만 기입한다. 그러면 자동적으로 슬레이브 서버의 마스터 서버를 찾아서 데이터를 맞추는 작업을 진행한다. 만약, 해당 마스터 서버에 한대 이상의 슬레이브 서버가 있는 경우, 다른 슬레이브 서버가 데이터 변경 작업에 대한 영향을 받게 됨으로 주의해야 한다. 

--sync-to-master를 지정하지 않고 사용하는 경우, 첫번째 DSN 정보는 소스 서버가 된다. 그리고, --replicate 옵션도 사용하지 않으면, 적어도 한개 이상의 다른 DSN 정보를 기입해야 한다. 즉, destination 서버는 한대 이상이 되어야 한다. 각 서버들은 독립적이어야 하며, 리플리케이션으로 연결되어있으면 안된다. 만약, 리플리케이션 연결이 되어있다면, 동작에 오류가 발생하여 데이터에 문제가 발생하게 된다. 즉, --replicate 옵션을 사용하고, --sync-to-master를 사용하지 않는 경우 명령행에 기입되는 서버의 종류는 하나이어야 한다는 것을 의미하고, 이와 같이 작성되면, pt-table-sync는 자동으로 모든 슬레이브 서버들을 감지하여 모두 다 같이 데이터를 동기화 한다. 

즉, --replicate옵션을 사용하면 가장 첫번째에 마스터 서버를 지정해야 하고, --sync-to-master 옵션을 사용하면 슬레이브 서버를 지정해야 한다. 

만약 DSN을 사용하는 경우 다음과 같이 지정하여 사용하는 것이 가능하다. 

 

 

이렇게 사용하는 경우 host1의 설정 정보를 host2도 동일하게 사용하게 된다. 

OUTPUT

--verbose옵션을 사용하게 되면 다음과 같이 테이블의 데이터 다름 정보를 보여주게 된다. 

 

 

위 예제는 host2의 test.test1이 3개의 데이터를 입력했다는 것을 의미하고, 이걸 분석한 알고리즘은 Chunk 라는 것을 의미한다. 동기화 작업은 1시 부터 17분 동안 진행되었다는 것을 의미한다. 시간은 소스 서버의 NOW()함수 호출로 결정된다. 데이터에 대한 다름이 발견되면 EXIT STATUS는 2 가 된다. 

--print 옵션을 명시하게 되면, 사용자는 동기화 작업에 사용된 쿼리도 확인할 수 있다. 만약, 모든 쿼리 정보를 보고 싶으면, --print와 --verbose 옵션을 같이 사용하면 된다. 하지만, 많은 양이 출력될 것이기 때문에 그 부분은 유의해야 한다. 

다음의 예제와 같이 UK나 PK가 충돌되는 현상이 발생한 테이블도 있을 수 있다. 

 

 

이런 경우에는 간단한 UPDATE 문 가지고 해결할 수는 없다. 이런 상황이 발생되면 pt-table-sync는 DELETE 와 REPLACE 구문을 호출하여 작업을ㄹ 진행한다. 첫번째 인덱스에서 충돌이 발생하면 자동적으로 호출된다. 

multi-master 구성에서 pt-table-sync는 주의해야 한다. 두 마스터 서버 사이에서 리플리케이션은 근본적으로 까다롭게 실수하기 쉽기 때문이다. 그래서, 사용자가 기본 multi-master 구성에서 올바르게 사용하기 쉽지 않다. 이것이 문제이다. 

만약, 작업을 진행하려는 테이블에 FK가 걸려있고, ON DELETE, ON UPDATE 가 설정되어있다면 의도치 않게 문제가 될 수 있기 때문에 삭제하고 진행하도록 해야 한다. 

즉, 이 프로그램은 기본키와 인덱스 UK가 있을때 사용하기 좋은 방법이다. 

REPLICATION SAFETY

일반적으로 구성하는 리프리케이션에서 데이터 동기화 작업은 그리 큰 문제는 아니다. 하지만 슬레이브 서버에서 DML이 발생하게 되면 그것은 문제를 발생하게 된다. 이런 경우에 데이터를 맞추기 위해, 가장 안전하게 사용하는 방법은 마스터 서버에서 REPLACE 구문을 실행하는 것이다. 이렇게 사용하려면 UK가 있어야 한다. 그렇지 않으면 일반적인 INSERT로 동작하게 된다. 

테이블에 UK가 있는 경우, --sync-to-master나 --replicate 옵션을 사용하여 동기화 작업을 진행하게 된다. 이것이 가장 일반적인 방법이다. 만약, UK가 없다면, 슬레이브 서버에 데이터를 변경할 방법이 없다. 

ALGORITHMS

pt-table-sync 프로그램은 다음과 같은 알고리즘을 사용하여 데이터의 다름을 찾아낸다. 

Chunk: 첫번째 컬럼이 숫자형인 경우 그리고 그 컬럼으로 인덱스가 있는 경우에 사용하는 방식, 데이터를 chunk로 분할하여 작업을 진행한다. 

Nibble: Chunk와 유사한 방식, 인덱스를 찾아서 결정된 사이즈의 nubble로 인덱스 정보를 나눠서 처리. 

GroupBy: 모든 컬럼을 group by 하여 count(*) 값을 보고 비교하는 방식 PK, UK가 있어야 한다. 

Stream: 스트림방식으로 모든 컬럼을 비교 인덱스가 없는 경우 사용하는 방식. 

Future Plans: 미래에 가능한 방식으로 TempTable과 DrillDown, GroupByPrefix 방식이 있다. 각 방식은 장단점이 있다. 아직 구현 전이다. 

BIDIRECTIONAL SYNCING

양뱡향 방식 은 새로운 방식으로 새롭게 추가된 기능이다. 안정적이게 동작하게 하기 위해 제약사항이 존재한다. 

-- 작업에 들어가는 서버들이 모두 독립적이어야 한다. 

-- 리플리케이션이 되지 않아야 한다. 

-- Chunk 알고리즘을 사용할 수 있어야 한다. 

-- 한번에 2대의 서버에 대해서만 작업이 일어난다. 

-- DELETE 쿼리는 처리되지 않는다. 

OPTIONS

--alorithms

--ask-pass

--bidirectional

--[no]bin-log

--buffer-in-mysql

--[no]buffer-to-client

--charset

--[no]check-child-tables

--[no]check-master

--[no]check-slave

--[no]check-triggers

--chunk-column

--chunk-index

--chunk-size

--columns

--config

--confilict-column

--conflict-comparison

--conflict-error

--conflict-threshold

--conflict-value

--databases

--defaults-file

--dry-run

--engines

--execute

--explain-hosts

--float-precision

--[no]foreign-key-checks

--function

--help

--[no]hex-blob

--host

--ignore-columns

--ignore-databases

--ignore-engines

--ignore-tables

--ignore-tables-regex

--[no]index-hint

--lock

--lock-and-rename

--password

--pid

--port

--print

--recursion-method

--replace

--replicate

--set-vars

--socket

--sync-to-master

-tables

--timeout-ok

--[no]ransaction

--trim

--[no]unique-checks

--user

--verbose

--version

--[no]version-check

--wait

--where

--[no]zero-chunk

 

테스트~~~

[mysql@oraclelinux6 bin]$ pt-table-sync --execute --verbose --print --sync-to-master --tables=test.test h=10.0.0.104,u=root,p=test,P=3306

# Syncing P=3306,h=10.0.0.104,p=...,u=root

# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE

[mysql@oraclelinux6 bin]$ pt-table-sync --execute --verbose --print --sync-to-master --databases=test h=10.0.0.104,u=root,p=test,P=3306

# Syncing P=3306,h=10.0.0.104,p=...,u=root

# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE

DELETE FROM `test`.`test` WHERE `i`='2' LIMIT 1 /*percona-toolkit src_db:test src_tbl:test src_dsn:P=3306,h=10.0.0.103,p=...,u=root dst_db:test dst_tbl:test dst_dsn:P=3306,h=10.0.0.104,p=...,u=root lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:31561 user:mysql host:oraclelinux6.localdomain*/;

#      1       0      0      0 Chunk     02:58:22 02:58:22 2    test.test

[mysql@oraclelinux6 bin]$

데이터 변경 확인

[root@oraclelinux6.localdomain][test]> select * from test;

+---+-------+

| i | str   |

+---+-------+

| 1 | data  |

| 2 | data2 |

+---+-------+

2 rows in set (0.00 sec)

[root@oraclelinux6.localdomain][test]> select * from test;

+---+------+

| i | str  |

+---+------+

| 1 | data |

+---+------+

1 row in set (0.00 sec)