製品&サポートサービス

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サポートサービス
MySQL Editions
Oracle Cloudトータルサポート
Percona
MariaDBプロダクト・サポート・サービス
エンタープライズOSSサポート
高可用性ソリューション
OSSシステムソリューション
Navicat
SQL Diagnostic Manager for MySQL
SYNCNEL
OSS保守サポートサービス
Microsoft Azure
お問い合わせ
各MySQL保守サービス見積依頼
スマートスタイルOSSストア
Oracle Gold Partner

ページトップへ