pt-online-schema-change

설명

Percona Toolkit의 온라인 스키마 변경 도구. 테이블 락 없이 ALTER를 수행할 수 있다.

사용 예시

로컬에서

pt-online-schema-change --alter "ENGINE=InnoDB" D=<DB명>,t=<테이블명> \
    --no-drop-old-table \
    --no-drop-new-table \
    --chunk-size=3000 \
    --defaults-file=<MySQL설치경로>/my.cnf \
    --port=<포트> \
    --user=<아이디> \
    --password=<패스워드> \
    --progress=time,30 \
    --max-load="Threads_running=100" \
    --critical-load="Threads_running=200" \
    --chunk-index=PRIMARY \
    --charset=utf8mb4 \
    --set-vars="innodb_lock_wait_timeout=3,lock_wait_timeout=3" \
    --no-check-alter \
    --prompt-before-copy \
    --sleep=0.3 \
    --execute
 
# 슬레이브 체크 스킵 시 추가
# --recursion-method=none

원격에서

pt-online-schema-change --alter "ENGINE=InnoDB" h=<호스트명>,P=<포트>,D=<DB명>,t=<테이블명> \
  --no-drop-old-table \
  --chunk-size=3000 \
  --user=<아이디> \
  --password='<패스워드>' \
  --progress=time,30 \
  --max-load="Threads_running=100" \
  --critical-load="Threads_running=200" \
  --chunk-index=PRIMARY \
  --charset=utf8mb4 \
  --set-vars="innodb_lock_wait_timeout=1,lock_wait_timeout=1" \
  --no-check-alter \
  --sleep=0.5 \
  --recursion-method=none \
  --prompt-before-copy \
  --execute >> result2.txt 2>&1

사용법

사전작업

cd <MySQL설치경로>
wget "<내부망IP>:<포트>/installer/util.tar.gz" > /dev/null 2>&1
tar xvfz util.tar.gz
rm -fr util.tar.gz
 
<MySQL설치경로>/util/add_bash_profile.sh

pt-osc 패치파일 준비

사전작업을 진행했다면 추가 다운로드 불필요. ptosc_custom.patch 만 적용하면 된다.

wget percona.com/get/percona-toolkit.tar.gz
 
cd ~/db/mysql/util/ptkit/bin
vi ptosc_custom.patch

패치 내용:

@@ -8464,6 +8464,14 @@
          or warn "Cannot reopen STDOUT to /dev/null: $OS_ERROR";
    }
 
+  ## Added
+
+   if($o->has('prompt-before-copy') && defined $o->get('prompt-before-copy')){
+       print "  >> prompting user operation : Yes\n";
+   }else{
+       print "  >> prompting user operation : No\n";
+   }
+
    # ########################################################################
    # Connect to MySQL.
    # ########################################################################
@@ -11159,3 +11163,18 @@
 
+   ## Added - Prompt and Waiting user custom operation
+   if($o->has('prompt-before-copy') && defined $o->get('prompt-before-copy')){
+      my $tmp_user_input = "";
+      print "\n";
+      print "Table copy operation is paused temporarily by user request '--prompt-before-copy'.\n";
+      print "pt-online-schema-change utility created new table, but not triggers.\n";
+      print "   ==> new table name : $new_tbl->{name} \n\n";
+      print "So if you have any custom operation on new table, do it now.\n";
+      print "Type 'yes', when you ready to go.\n";
+      do{
+         print "Should I continue to copy [Yes] ? : ";
+         chomp ($tmp_user_input = <STDIN>);
+      }while( !($tmp_user_input =~ m/^yes$/i) );
+   }
+
   my @trigger_names;
   @drop_trigger_sqls = ();
@@ -11948,6 +11971,10 @@
 
 Prompt for a password when connecting to MySQL.
 
+=item --prompt-before-copy
+
+Prompt before data copy to new temporary table for user custom operation
+
 =item --charset
 
 short form: -A; type: string

패치파일 적용

patch ./pt-online-schema-change < ptosc_custom.patch
# patching file pt-online-schema-change
# Hunk #2 succeeded at 11167 with fuzz 2.
 
patch /usr/local/bin/pt-online-schema-change < ptosc_custom.patch

추가된 —prompt-before-copy 옵션 확인

./pt-online-schema-change --help | grep prompt
#  --prompt-before-copy             Prompt before data copy to new temporary
#  --prompt-before-copy             FALSE

에러 발생 시

utf8mb4 charset 오류

Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file
Cannot connect to MySQL: ... Can't initialize character set utf8mb4 (path: /usr/share/mysql/charsets/)

/usr/share/mysql/charsets/Index.xml 맨 아래에 추가:

<charset name="utf8mb4">
  <family>Unicode</family>
  <description>UTF-8 Unicode</description>
  <alias>utf-8</alias>
  <collation name="utf8_general_ci" id="33">
    <flag>primary</flag>
    <flag>compiled</flag>
  </collation>
  <collation name="utf8_bin" id="83">
    <flag>binary</flag>
    <flag>compiled</flag>
  </collation>
</charset>

참고

• Percona Toolkit - pt-online-schema-change