製品＆サポートサービス

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.

MySQLサポートサービス

Oracle Cloudトータルサポート