製品＆サポートサービス
pt-online-schema-change
コマンド
pt-online-schema-change [ オプション ] [ --execute または --dry-run ] [ DSN ]
【必須項目】
- ・ [ オプション ] ： -p パスワード
- ・ [ DSN ] ： h=ホスト名, D=データベース名, t=テーブル名
- ・ --execute or --dry-run ： --executeを指定すると変更が実行され、--dry-runは変更を実行しません
- ・ --alter "alter文" ： 実行したいALTER文を入力します（ただし"ALTER TABLE"という記述は省きます）
【主なオプション】
- ・ --max-load : 大きなデータベースのスキーマ変更の時に、スレッドの閾値を変更します（デフォルトは25です）
目的
テーブルをロックをせずに、指定のテーブルのスキーマ（構造）を変更する事ができます
設定ファイル
上記の必須項目を、設定ファイルにまとめておきます
ただし、[ DSN ], --execute(--dry-run), --alter はスクリプト上で直接指定する必要があるため、ここでは書きません
# touch /etc/percona-toolkit/pt-online-schema-change.conf # vi /etc/percona-toolkit/pt-online-schema-change.conf
・設定ファイルの作成
# touch /etc/percona-toolkit/pt-online-schema-change.conf
・設定ファイルの編集
# vi /etc/percona-toolkit/pt-online-schema-change.conf
# config for pt-online-schema-change user=root password=パスワード
・MySQL のユーザ名
user=root
・MySQL のパスワードを記載
password=パスワード
シナリオ
以下のようなテーブルを用意します
mysql> desc departments; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | dept_no | char(4) | NO | PRI | NULL | | | dept_name | varchar(40) | NO | UNI | NULL | | +-----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql>
テーブルに "test" というカラムを追加するために、以下のpt-online-schema-changeコマンドを実行します
# pt-online-schema-change --execute --alter-foreign-keys-method auto \ > --alter "add column TEST int" h=localhost,D=employees,t=employees
pt-online-schema-changeコマンド実行中に、MySQLで以下のINSERT文・UPDATE文を実行します
通常であれば「カラムの追加」のようなテーブルのスキーマ情報を書き換える動作の場合、テーブルのロックが発生し、その間のINSERT文・UPDATE文はロック待ちとなるため実行に時間がかかります
mysql>INSERT INTO employees VALUE (500000,"1958-05-05","hoge","hogehoge","M","1997-05-31"); mysql> UPDATE employees SET gender="F" WHERE emp_no=500000;
結果
以下の画面が表示されTESTカラムの追加が行われます
以下のコマンドでINSERT文・UPDATE文も問題なく実行されていることが確認できます
mysql> SELECT * FROM employees WHERE emp_no="500000";
mysql> INSERT INTO employees VALUE (500000,"1958-05-05","hoge","hogehoge","M","1997-05-31"); Query OK, 1 row affected (0.10 sec) mysql> UPDATE employees SET gender="F" WHERE emp_no=500000; Query OK, 1 row affected (0.17 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM employees WHERE emp_no="500000"; +--------+------------+------------+-----------+--------+------------+------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | TEST | +--------+------------+------------+-----------+--------+------------+------+ | 500000 | 1958-05-05 | hoge | hogehoge | F | 1997-05-31 | NULL | +--------+------------+------------+-----------+--------+------------+------+ 1 row in set (0.01 sec)
[root@localhost percona-toolkit]# pt-online-schema-change --execute --alter-foreign-keys-method auto \ > --alter ""add column TEST int"" h=localhost,D=employees,t=employees No slaves found. See --recursion-method if host localhost.localdomain has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Child tables: `employees`.`dept_emp` (approx. 331143 rows) (省略) Determining the method to update foreign keys... 2017-04-24T15:49:40 `employees`.`dept_emp`: too many rows: 331143; must use drop_swap 2017-04-24T15:49:40 Drop-swapping tables... 2017-04-24T15:49:40 Analyzing new table... 2017-04-24T15:49:40 Dropped and swapped tables OK. Not dropping old table because --no-drop-old-table was specified. 2017-04-24T15:49:40 Dropping triggers... 2017-04-24T15:49:40 Dropped triggers OK. Successfully altered `employees`.`employees`.
コマンドの実行後、employeesテーブルではTESTカラムが追加されたことが確認できます
mysql> DESC employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
| TEST | int(11) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
良い点
通常のALTER文と異なり、サービスを止める事無く、テーブルへの列追加等が可能となります
その他
--dry-runオプションを指定した場合は以下のようになり、実際にスキーマ変更は実行されません
[root@localhost percona-toolkit]# pt-online-schema-change --dry-run --alter-foreign-keys-method auto \ > --alter "add column TEST int" h=localhost,D=employees,t=employees Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Child tables: (省略) Will automatically choose the method to update foreign keys. Starting a dry run. `employees`.`employees` will not be altered. Specify --execute instead of --dry-run to alter th e table. Creating new table... Created new table employees._employees_new OK. Altering new table... Altered `employees`.`_employees_new` OK. Not creating triggers because this is a dry run. Not copying rows because this is a dry run. Not determining the method to update foreign keys because this is a dry run. Not swapping tables because this is a dry run. Not updating foreign key constraints because this is a dry run. Not dropping old table because this is a dry run. Not dropping triggers because this is a dry run. 2017-04-24T16:01:27 Dropping new table... 2017-04-24T16:01:27 Dropped new table OK. Dry run complete. `employees`.`employees` was not altered.
