ntulip · November 16, 2010 00:53 · Nov 11, 2010
diff --git a/move_to_rds.rb b/move_to_rds.rb
@@ -0,0 +1,129 @@
+require 'fileutils'
+
+start_time = Time.now
+
+
+SOURCE_DB = {
+  :name => 'db_name',
+  :user => 'db_user',
+  :password => 'db_pass',
+  :host => 'localhost'
+}
+TARGET_DB = {
+  :name => 'db_name',
+  :user => 'db_user',
+  :password => 'db_pass',
+  :host => 'foo.abcdef.us-east-1.rds.amazonaws.com'
+}
+
+DUMP_DIR  = '/mnt/db_dump'
+
+
+# These tables can be moved beforehand, and the diff later
+INSERT_ONLY_TABLES = %w[click_tracking logs]
+
+
+def benchmark(label = nil)
+  puts label unless label.nil?
+  before = Time.now
+  yield
+  after = Time.now
+  puts "Took %.3fs" % (after - before)
+end
+
+def host_user_password(db)
+  "--host='#{db[:host]}' --user='#{db[:user]}' --password='#{db[:password]}'"
+end
+
+def show_tables(db)
+  `mysql --execute='SHOW TABLES' --silent #{host_user_password(db)} #{db[:name]}`.split("\n")
+end
+
+def dump_db_tables(db, tables, dir, where = nil)
+  FileUtils.mkdir_p(dir, :mode => 0777)
+  where_arg = where.nil? ? '' : %(--where="#{where}")
+
+  benchmark("Dumping tables [#{tables.join(', ')}] to #{dir} #{where_arg}") do
+    system "mysqldump --verbose #{host_user_password(db)} --tab=#{dir} #{where_arg} #{LOCK_ARG} #{db[:name]} #{tables.join(' ')}"
+  end
+end
+
+def dump_db_before(db, migrate_before = [])
+  dir = "#{DUMP_DIR}/#{db[:name]}/before"
+  dump_db_tables(db, migrate_before, dir)
+end
+
+def dump_db_hot(db, migrate_before = [])
+  dir = "#{DUMP_DIR}/#{db[:name]}/hot"
+  tables = show_tables(db) - migrate_before
+  dump_db_tables(db, tables, dir)
+end
+
+def dump_db_diff(source, target, migrate_before = [])
+  dir = "#{DUMP_DIR}/#{source[:name]}/diff"
+
+  migrate_before.each do |table|
+    last_max_id = `mysql --execute='SELECT MAX(id) FROM #{table}' --silent #{host_user_password(target)} #{target[:name]}`.strip
+    dump_db_tables(source, [table], dir, "id > #{last_max_id}")
+  end
+end
+
+def dump_db_structure(db)
+  dump_file = "#{DUMP_DIR}/#{db[:name]}_structure.sql"
+  FileUtils.mkdir_p(DUMP_DIR, :mode => 0777)
+
+  benchmark("Dumping structure of #{db[:name]} to #{dump_file}") do
+    system "mysqldump --verbose #{host_user_password(db)} --no-data #{db[:name]} > #{dump_file}"
+  end
+end
+
+def import_db_structure(source, target)
+  dump_file = "#{DUMP_DIR}/#{source[:name]}_structure.sql"
+
+  benchmark("Importing structure of #{source[:name]} from #{dump_file}") do
+    system "mysql #{host_user_password(target)} #{target[:name]} < #{dump_file}"
+  end
+end
+
+def import_db_tables(target, tables, dir)
+  benchmark("Importing tables from #{dir}") do
+    dump_files = tables.map { |table| File.join(dir, "#{table}.txt") }
+    system "mysqlimport --local --compress --verbose #{host_user_password(target)} #{target[:name]} #{dump_files.join(' ')}"
+  end
+end
+
+def import_db_before(source, target, migrate_before = [])
+  dir = "#{DUMP_DIR}/#{source[:name]}/before"
+  import_db_tables(target, migrate_before, dir)
+end
+
+def import_db_hot(source, target, migrate_before = [])
+  dir = "#{DUMP_DIR}/#{source[:name]}/hot"
+  tables = show_tables(source) - migrate_before
+  import_db_tables(target, tables, dir)
+end
+
+def import_db_diff(source, target, migrate_before = [])
+  dir = "#{DUMP_DIR}/#{source[:name]}/diff"
+  import_db_tables(target, migrate_before, dir)
+end
+
+
+if ARGV.include?('--before')
+  # Don't lock tables while the site is still up
+  LOCK_ARG = "--skip-lock-tables"
+  # Run these before
+  dump_db_structure(SOURCE_DB)
+  dump_db_before(SOURCE_DB, INSERT_ONLY_TABLES)
+  import_db_structure(SOURCE_DB, TARGET_DB)
+  import_db_before(SOURCE_DB, TARGET_DB, INSERT_ONLY_TABLES)
+else
+  LOCK_ARG = ""
+  # Run these when the site is offline
+  dump_db_hot(SOURCE_DB, INSERT_ONLY_TABLES)
+  dump_db_diff(SOURCE_DB, TARGET_DB, INSERT_ONLY_TABLES)
+  import_db_hot(SOURCE_DB, TARGET_DB, INSERT_ONLY_TABLES)
+  import_db_diff(SOURCE_DB, TARGET_DB, INSERT_ONLY_TABLES)
+end
+
+puts "Script ran for: %.3fs" % (Time.now - start_time)