pt-online-schema-change

pt-online-schema-change can alter a table's structure without blocking reads or writes.

pt-online-schema-change works by creating an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it moves away the original table and replaces it with the new one. Any modifications to data in the original tables during the copy will be reflected in the new table, because the tool creates triggers on the original table to update the corresponding rows in the new table.

Install

$ sudo apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

# add this to `/etc/apt/sources.list`
# remember to replace `precise` with the name of your distribution
deb http://repo.percona.com/apt precise main
deb-src http://repo.percona.com/apt precise main

$ sudo apt-get update
$ sudo aptitude -V install libio-socket-ssl-perl libnet-ssleay-perl percona-toolkit

ref:
http://www.percona.com/doc/percona-server/5.6/installation/apt_repo.html

Usage

$ time pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--dry-run \
--alter "ADD COLUMN extra_data TEXT NULL" \
D=streetvoice,t=member_userprofile

# you must specify `execute` option explicitly to alter tables
$ time pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--execute \
--alter "ADD COLUMN extra_data TEXT NULL" \
D=streetvoice,t=member_userprofile

$ pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--execute \
--alter " \
DROP COLUMN get_sms, \
DROP COLUMN job_title, \
DROP COLUMN exp, \
DROP COLUMN non_member_message, \
DROP COLUMN fans_amount, \
DROP COLUMN new_fans, \
DROP COLUMN last_time_in_giftbox \
" \
D=streetvoice,t=member_userprofile

$ pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--execute \
--alter " \
ADD COLUMN playlist_id integer NULL AFTER user_id, \
ADD CONSTRAINT playlist_id_refs_id_1d572331 FOREIGN KEY (playlist_id) REFERENCES music_playlist (id), \
ADD INDEX music_playrecord_97bd5154 (playlist_id) \
" \
D=streetvoice,t=music_playrecord

# 2 cores, 2GB RAM
real 105m27.484s
user 0m8.541s
sys 0m1.414s

$ pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--execute \
--alter-foreign-keys-method=rebuild_constraints \
--alter " \
ADD COLUMN play_count integer UNSIGNED NOT NULL DEFAULT 0 AFTER buy_link \
" \
D=streetvoice,t=music_musicalbum

$ pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--execute \
--alter " \
DROP COLUMN starsigns \
" \
D=streetvoice,t=member_usertemp

$ pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--dry-run \
--alter " \
DROP COLUMN is_cc, \
DROP COLUMN cc_license \
" \
D=streetvoice,t=music_song

ref:
http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html
http://www.percona.com/blog/2014/11/18/avoiding-mysql-alter-table-downtime/
http://www.cnblogs.com/zhoujinyi/p/3491059.html
http://huanghualiang.blog.51cto.com/6782683/1360873

alter-foreign-keys-method

如果你要修改的 table 有欄位是其他 table 的 foreign key
你必須明確地指定 alter-foreign-keys-method 參數
否則 pt-online-schema-change 會不讓你執行
因為會有 constraint 的問題()

官方文件說 rebuild_constraints 是比較建議的作法

$ time pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--execute \
--alter-foreign-keys-method=rebuild_constraints \
--alter "ADD COLUMN length int(10) UNSIGNED NOT NULL DEFAULT 0 AFTER file_size" \
D=streetvoice,t=music_song

# 2 cores, 2G RAM
real 2m57.298s
user 0m0.338s
sys 0m0.043s

ref:
http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html#cmdoption-pt-online-schema-change--alter-foreign-keys-method

MariaDB Galera Cluster

This tool works fine with MariaDB Galera Cluster, only makes sure wsrep_OSU_method value is TOI.

ref:
http://www.severalnines.com/blog/online-schema-upgrade-mysql-galera-cluster-using-toi-method
http://galeracluster.com/documentation-webpages/schemaupgrades.html