Ridgepoleに--external-scriptというオプションを付けました。
更新SQLの実行を外部スクリプトで行うオプションです。
これで、一応pt-oscを使って、DDLを実行できるようにはなりました。あとは、MySQLのALTER文で末尾にLOCK=NONEを付けるとか。
ただ、MySQLのインデックス追加・削除に関してはCREATE INDEX、DROP INDEXが渡されるため、ALTER文への書き換えが必要です。
また、ALTER文についても、pt-oscで実行する場合はテーブル名と以降のSQLへの分解が必要です。
Example
pt-osc.rb
#!/usr/bin/env ruby
require 'open3'
require 'shellwords'
def parse_sql(sql)
case sql
when /\AALTER +TABLE +`([^`]+)`(.+)\z/i
[$1, $2]
when /\ACREATE +INDEX +`([^`]+)`(.+)ON +`([^`]+)`(.+)\z/
[$3, "ADD INDEX `#{$1}` #{$2} #{$4}"]
when /\ADROP +INDEX +`([^`]+)` +ON +`([^`]+)`\z/i
[$2, "DROP INDEX `#{$1}`"]
else
raise 'Unsuppored SQL'
end
end
table, alter = parse_sql(ARGV[0])
dsn = "h=localhost,u=root,p=root,D=employees,t=#{table},A=utf8"
cmd = ['pt-online-schema-change']
cmd << '--alter' << alter
cmd << '--execute'
cmd << dsn
cmd = Shellwords.join(cmd)
out, err, status = Open3.capture3(cmd)
puts out
$stderr.puts err
exit status.to_i
実行してみる
まずはdry-run。
$ ridgepole -c database.yml -a --external-script ./pt-osc.rb --dry-run
pply `Schemafile` (dry-run)
add_column("titles", "to_date2", :date, {:after=>"from_date"})
remove_column("titles", "to_date")
remove_index("titles", {:name=>"emp_no"})
add_index("titles", ["emp_no"], {:name=>"emp_no2", :using=>:btree})
問題なさそうなので適用します。
$ ridgepole -c database.yml -a --external-script ./pt-osc.rb
Apply `Schemafile`
-- add_column("titles", "to_date2", :date, {:after=>"from_date"})
Execute ./pt-osc.rb ALTER\ TABLE\ \`titles\`\ ADD\ \`to_date2\`\ date\ AFTER\ \`from_date\`
./pt-osc.rb: pt-online-schema-change --alter \ ADD\ \`to_date2\`\ date\ AFTER\ \`from_date\` --execute h\=localhost,u\=root,p\=root,D\=employees,t\=titles,A\=utf8
No slaves found. See --recursion-method if host vagrant-ubuntu-trusty-64 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `employees`.`titles`...
Creating new table...
Created new table employees._titles_new OK.
Altering new table...
Altered `employees`.`_titles_new` OK.
2015-11-20T16:09:42 Creating triggers...
2015-11-20T16:09:42 Created triggers OK.
2015-11-20T16:09:42 Copying approximately 442453 rows...
2015-11-20T16:09:45 Copied rows OK.
2015-11-20T16:09:45 Swapping tables...
2015-11-20T16:09:45 Swapped original and new tables OK.
2015-11-20T16:09:45 Dropping old table...
2015-11-20T16:09:45 Dropped old table `employees`.`_titles_old` OK.
2015-11-20T16:09:45 Dropping triggers...
2015-11-20T16:09:45 Dropped triggers OK.
Successfully altered `employees`.`titles`.
-> 3.6346s
-- remove_column("titles", "to_date")
Execute ./pt-osc.rb ALTER\ TABLE\ \`titles\`\ DROP\ \`to_date\`
./pt-osc.rb: pt-online-schema-change --alter \ DROP\ \`to_date\` --execute h\=localhost,u\=root,p\=root,D\=employees,t\=titles,A\=utf8
No slaves found. See --recursion-method if host vagrant-ubuntu-trusty-64 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `employees`.`titles`...
Creating new table...
Created new table employees._titles_new OK.
Altering new table...
Altered `employees`.`_titles_new` OK.
2015-11-20T16:09:46 Creating triggers...
2015-11-20T16:09:46 Created triggers OK.
2015-11-20T16:09:46 Copying approximately 1 rows...
2015-11-20T16:09:49 Copied rows OK.
2015-11-20T16:09:49 Swapping tables...
2015-11-20T16:09:49 Swapped original and new tables OK.
2015-11-20T16:09:49 Dropping old table...
2015-11-20T16:09:49 Dropped old table `employees`.`_titles_old` OK.
2015-11-20T16:09:49 Dropping triggers...
2015-11-20T16:09:49 Dropped triggers OK.
Successfully altered `employees`.`titles`.
-> 3.1043s
-- remove_index("titles", {:name=>"emp_no"})
Execute ./pt-osc.rb DROP\ INDEX\ \`emp_no\`\ ON\ \`titles\`
./pt-osc.rb: pt-online-schema-change --alter DROP\ INDEX\ \`emp_no\` --execute h\=localhost,u\=root,p\=root,D\=employees,t\=titles,A\=utf8
No slaves found. See --recursion-method if host vagrant-ubuntu-trusty-64 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `employees`.`titles`...
Creating new table...
Created new table employees._titles_new OK.
Altering new table...
Altered `employees`.`_titles_new` OK.
2015-11-20T16:09:49 Creating triggers...
2015-11-20T16:09:49 Created triggers OK.
2015-11-20T16:09:49 Copying approximately 1 rows...
2015-11-20T16:09:51 Copied rows OK.
2015-11-20T16:09:51 Swapping tables...
2015-11-20T16:09:51 Swapped original and new tables OK.
2015-11-20T16:09:51 Dropping old table...
2015-11-20T16:09:51 Dropped old table `employees`.`_titles_old` OK.
2015-11-20T16:09:51 Dropping triggers...
2015-11-20T16:09:51 Dropped triggers OK.
Successfully altered `employees`.`titles`.
-> 2.3925s
-- add_index("titles", ["emp_no"], {:name=>"emp_no2", :using=>:btree})
Execute ./pt-osc.rb CREATE\ \ INDEX\ \`emp_no2\`\ USING\ btree\ ON\ \`titles\`\ \(\`emp_no\`\)\
./pt-osc.rb: pt-online-schema-change --alter ADD\ INDEX\ \`emp_no2\`\ \ USING\ btree\ \ \ \(\`emp_no\`\)\ --execute h\=localhost,u\=root,p\=root,D\=employees,t\=titles,A\=utf8
No slaves found. See --recursion-method if host vagrant-ubuntu-trusty-64 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `employees`.`titles`...
Creating new table...
Created new table employees._titles_new OK.
Altering new table...
Altered `employees`.`_titles_new` OK.
2015-11-20T16:09:51 Creating triggers...
2015-11-20T16:09:51 Created triggers OK.
2015-11-20T16:09:51 Copying approximately 1 rows...
2015-11-20T16:09:54 Copied rows OK.
2015-11-20T16:09:54 Swapping tables...
2015-11-20T16:09:54 Swapped original and new tables OK.
2015-11-20T16:09:54 Dropping old table...
2015-11-20T16:09:54 Dropped old table `employees`.`_titles_old` OK.
2015-11-20T16:09:54 Dropping triggers...
2015-11-20T16:09:54 Dropped triggers OK.
Successfully altered `employees`.`titles`.
-> 3.1088s
正常にDBの更新が行われました。再度、適用しても変更はありません。
$ ridgepole -c database.yml -a --external-script ./pt-osc.rb
Apply `Schemafile`
No change
その他
pt-online-schema-changeがSQLの間違いなどで失敗しても、終了コードが0になって胃るっぽいんですが、どうしたもんでしょうね…
