Mega DB Comparison Script for MySQLCompares two databases by doing a checksum on every table.For any tables that differ, a row by row comparison is done.All differences are then HashDiffed to see the actual changes

mega_compare.rb

require 'mysql2'
require 'awesome_print'
require 'hashdiff'

BEFORE_DB = 'alaina_before_upgrade'
AFTER_DB = 'alaina_after_upgrade'

BEFORE = Mysql2::Client.new(host:"127.0.0.1", port: 3307, username: "root", password: '', database: BEFORE_DB, symbolize_keys: true)
AFTER  = Mysql2::Client.new(host:"127.0.0.1", port: 3307, username: "root", password: '', database: AFTER_DB, symbolize_keys: true)

def diff_table(table)
  fields = BEFORE.query("SHOW COLUMNS FROM #{table}", :as => :hash)
  key = fields.find { |f| f[:Key] == 'PRI' }
  unless key
    puts "!!!!!! CANNOT DIFF TABLE #{table} AS IT HAS NO PRIMARY KEY !!!!!!!!!"
    return
  end
  key = key[:Field]
  lowercased_key = key.to_s.downcase.to_sym
  field_names = fields.map { |f| f[:Field] }
  field_list = field_names.join(", ")

  # puts "Primary Key: #{key}"
  # puts "Fields: #{field_names}"
  puts "-------- Comparing #{table} --------"

  comparison_query = <<-SQL
  SELECT MIN(tbl_name) AS tbl_name, #{field_list}
  FROM
   (
    SELECT 'source_table' as tbl_name, #{field_list}
    FROM #{BEFORE_DB}.#{table} AS S
    UNION ALL
    SELECT 'destination_table' as tbl_name, #{field_list}
    FROM #{AFTER_DB}.#{table} AS D 
  )  AS alias_table
  GROUP BY #{field_list}
  HAVING COUNT(1) = 1
  ORDER BY #{key};
  SQL

  differences = BEFORE.query(comparison_query)

  grouped_diffs = differences.group_by { |r| r[lowercased_key] }

  grouped_diffs.each do |k, rows|
    if rows.length == 2        
      puts "Diff for #{table} with #{key} = #{rows[0][lowercased_key]}"
      rows[0].delete(:tbl_name)
      rows[1].delete(:tbl_name)
      ap HashDiff.diff(rows[0], rows[1])
    else
      puts "No matching row for:"
      ap rows[0]
    end
    puts "*****"
  end

  puts "--End diff for #{table}--\n"
end

def checksum(connection, table)
  begin 
    connection.query("CHECKSUM TABLE #{table}", :as => :array).to_a[0][1]
  rescue
    puts $!
    '?'
  end
end

def failed_checksum_tables
  failed_checksums = []

  BEFORE.query("SHOW TABLES", :as => :array).each do |row|
    table = row.first
    print "#{table}: "
    
    before_checksum = checksum(BEFORE, table)
    print "#{before_checksum} -> "

    after_checksum  = checksum(AFTER, table)
    print "#{after_checksum} "

    if before_checksum != after_checksum
      failed_checksums << table
      puts "\u2717"
    else 
      puts "\u2713"
    end
  end

  puts "---------------"
  puts "Failed Checksums"
  ap failed_checksums
  failed_checksums
end

failed_checksum_tables().each do |table|
 diff_table table
end