MySQL convert to character set

mysql 전체 테이블의 character set을 일괄로 변경하고자 할때 온라인으로 변경하는 방법을 설명한다.

ALTER TABLE .. convert to character set

  • Converting a character set
    • inplace : NO
    • Rebuild Table : YES
    • Permits DDL : No
  • online ddl이 지원되지 않는다.
  • online으로 작업하고 싶다면? pt-online-schema-change 를 사용한다.
    • pt-online-schema-change는 데이터를 chunk size 단위로 복사하면서, trigger로 변경분을 current, new table에 동시에 반영한다. 즉, 전체 데이터를 새 테이블에 새로운 character set으로 깔끔하게 쓰게 된다.

Pre-requites

  • character set변경이 가능한지 확인한다.
  • 특히 character set 변경으로 character size가 변경되는 경우, (utf8 -> utf8mb4)라면 (3bytes->4bytes)로 크기가 변경되므로, mysql의 max row size, max index size를 초과하게 되지는 않는지 반드시 확인해야한다.

Check table with no-pk

  • pk가 없는 테이블은 pt-online-schema작업이 불가능하다.
SELECT
    t.table_schema, t.table_name
FROM information_schema.TABLES t
LEFT JOIN information_schema.KEY_COLUMN_USAGE AS c
ON (
       t.TABLE_NAME = c.TABLE_NAME
   AND c.CONSTRAINT_SCHEMA = t.TABLE_SCHEMA
   AND c.constraint_name = 'PRIMARY'
)
WHERE
    t.table_schema ='your_database_name'
AND c.constraint_name IS NULL;

-> pk를 생성해주거나, 그냥 DDL로 작업한다.

Check table max size over 65536

  • mysql table row length limitation : 65536
SELECT table_schema, table_name, sum(CHARACTER_MAXIMUM_LENGTH)*4
FROM information_schema.COLUMNS
WHERE  table_schema ='your_database_name'
GROUP BY 1,2
having sum(CHARACTER_MAXIMUM_LENGTH)*4 > 65535

-> 테이블 character set변경이 불가능하다. 정말 변환이 필요한 컬럼만 변경하도록 한다.

Check index max size over 3072

  • mysql index key length limitation : 3072 (innodb_large_prefix=ON)
  • mysql index key length limitation : 767 (innodb_large_prefix=OFF)
  • innodb_large_prefix에 따라서 key length 제한이 다르다. innodb_large_prefix를 사용하도록 미리 설정한다.
set global innodb_large_prefix=1;
  • 그리고 large prefix의 제한도 넘기는 index가 있다면, prefix index가 되므로 constraints에 문제가 없는지 확인한다.
select s.table_name, s.index_name,sum(c.CHARACTER_MAXIMUM_LENGTH)*4
from information_schema.statistics s left join information_schema.COLUMNS c on (c.table_schema=s.table_schema and c.table_name=s.table_name and c.column_name=s.column_name)
WHERE  s.table_schema ='pinfo'
GROUP BY 1,2
having sum(c.CHARACTER_MAXIMUM_LENGTH)*4 > 3072

pt-online-schema-change를 사용한 변경

  • options들은 적당히 설정한다.
  • 아래는 예제 스크립트이니 참조해서 알맞게 작성한다.
    • 다음은 테이블이 여러개일때, 로그와 수행시간 로그를 append하여 기록한다.
    • pt-online-schema-change output : ptosc_convert_charset.out
    • time output : time_ptosc.out
TBL_LIST="table1
table2"

for tn in $TBL_LIST;do
        echo $tn;
        \time -ao time_ptosc.out  pt-online-schema-change --alter "convert to character set utf8mb4 collate utf8mb4_general_ci, ROW_FORMAT=DYNAMIC"  D=your_database_name,t=$tn \
        --no-drop-old-table \
        --no-drop-new-table \
        --chunk-size=1000 \
        --sleep=0.01 \
        --defaults-file=/mysql/MyHome/my.cnf \
        --host=your_host \
        --port=your_port \
        --user=your_user \
        --password=your_password \
        --progress=time,30 \
        --max-load="Threads_running=100" \
        --critical-load="Threads_running=200" \
        --chunk-index=PRIMARY \
        --charset=utf8mb4 \
        --set-vars="tx_isolation='repeatable-read',binlog_format='statement'" \
        --no-check-alter \
        --execute >> ptosc_convert_charset.out 2>&1
done

MySQL DDL로 변경

  • pk없는 테이블만 대상
alter table your_no_pk_table convert to character set utf8mb4 collate utf8mb4_general_ci, ROW_FORMAT=DYNAMIC;