Compare MySQL variables between servers

compare_mysql_vars.rb

#!/usr/bin/env ruby -wKU

require 'csv'

$: << File.expand_path(__FILE__)

# Compares the output of `show variables;` run in the CLI mysql client. The
# script expects the output of this query executed with the `--tables` flag.
# Output is written to the STDOUT, so you'll need to use standard shell
# redirection. Output format is CSV. Example usage:
#
#
#     ./compare_mysql_vars.rb settings_file1.txt settings_file2.txt > comparison.csv
#
# The easiest way to generate output for analysis is by executing:
# 
# mysql --table -u root -p -e"show variables;" > ~/mysql_variables.txt
#
# Be sure to customize the output filename for each server you want to compare.

class MySQLCompareSettings

  def initialize #:nodoc:
    # Init the data containers
    @files = []   # list of files to be processed (passed as args)
    @data = {}    # raw data for each file in nested hash { :base_filename => { :setting => value, ... } }
    @results = [] # 2D array of rows that will be written as CSV

    # We must have at least two files to process
    if ARGV.length < 2
      STDERR.puts "This script expects at least two arguments"
      exit 1
    end
    # Load files from arguments
    ARGV.each do |arg|
      @files << File.expand_path(arg)
    end
  end

  def start #:nodoc:
    # Process the files
    @files.each do |filename|
      key = File.basename(filename, '.txt').to_sym
      # Each file gets a key, inside which parsed settings will be stored
      @data[key] ||= {}
      filtered_file(filename).each do |line|
        pair = parsed_line(line)
        # Parsed lines only contain non-empty values, but we're interested in
        # knowing if an option is present, but unset, so we specify unassigned
        case pair.length
        when 2
          @data[key][pair[0].to_sym] = pair[1]
        when 1
          @data[key][pair[0].to_sym] = "UNASSIGNED"
        else
          # We should never have more than two columns; this is possible if a
          # line has a pipe in the setting value; not much we can do
          puts "Unable to parse row; row data:\n#{line}"
          exit 1
        end
      end
    end

    # Load results
    mysql_variables.each do |setting|
      @results << [format_setting(setting), setting].push(fetch_settings(setting), compare_settings(setting)).flatten
    end
    @results.shift # The first row contains garbage records
    @results.unshift(["Setting", "Raw Setting", @data.keys.collect{ |i| i.to_s }, "Match"].flatten)

    # Output csv
    @results.each do |result|
      puts result.to_csv
    end
  end

  private

  # Collects an array of mysql variables common to all processed files
  def mysql_variables #:doc:
    @data.values.collect { |value| value.keys }.flatten.uniq!
  end

  # Looks up a setting in all files and compares the values for a match
  def compare_settings(setting) #:doc:
    @data.values.collect { |value| value[setting] }.uniq.length == 1 ? "TRUE" : "FALSE"
  end

  # Collects the specified setting for all files
  def fetch_settings(setting) #:doc:
    @data.values.collect { |value| value[setting] }
  end

  # Filters the file, returning only rows that actually contain data; necessary
  # because we're processing the output of a MySQL ASCII table
  def filtered_file(filename) #:doc:
    File.open(filename).readlines.grep(/^\|/)
  end

  # Reformat the setting symbol to something more readable; trying to avoid
  # active_support dependency
  def format_setting(setting) #:doc:
    setting.to_s.gsub(/_/, " ").capitalize.gsub(/Sql/, "SQL").gsub(/Ssl/, "SSL")
  end

  # Splits the line up in to a setting:value pair; fails if either contains a pipe
  # but have no work around
  def parsed_line(line) #:doc:
    line.split('|').map!{ |item| item.strip }.reject{ |item| item.empty? }
  end
end

MySQLCompareSettings.new.start