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 << '--dry-run'
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})

# ALTER TABLE `titles` ADD `to_date2` date AFTER `from_date`
# ALTER TABLE `titles` DROP `to_date`
# DROP INDEX `emp_no` ON `titles`
# CREATE  INDEX `emp_no2` USING btree ON `titles` (
# `emp_no`)

問題なさそうなので適用します。

$ 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になって胃るっぽいんですが、どうしたもんでしょうね…

so what

ridgepole+pt-osc

Example

pt-osc.rb

実行してみる

その他