Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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)
Add Comment
Please, Sign In to add comment