Convert a MySQL database from latin1 to utf8 charset.

latin1-to-utf8.rb

#! /usr/bin/env ruby

#
# Convert a MySQL database from latin1 to utf8 charset.
#
# Based on the short but slightly limited shell script in this article:
# http://yoonkit.blogspot.com/2006/03/mysql-charset-from-latin1-to-utf8.html
#
# Use at your own risk! I take not responsiblity for anything or
# anyone that might be damaged, lost, or killed from using this
# script. ~ Jim Myhrberg ([email protected]).
#

require 'optparse'
require 'optparse/time'
require 'ostruct'

class Latin1ToUtf8
  
  class << self
    attr_accessor :options
    attr_accessor :opts
  end
  
  def self.run
    if ARGV.size == 0
      ARGV << "--help"
    end
    self.parse_options(ARGV)
    user_switch = ([email protected]?) ? "-u #{@options.username}" : ""
    pass_switch = (@options.password) ? "-p" : ""
    database = ARGV[0]
    
    puts "Dumping #{database} to #{database}.sql..."
    system "mysqldump --add-drop-table #{user_switch} #{pass_switch} #{database} > #{database}.sql"
    time = Time.now.strftime("%Y%m%d%H%M%S")
    
    puts "Backing up backups/#{database}-#{time}.sql..."
    system "mkdir -p backups"
    system "cp #{database}.sql backups/#{database}-#{time}.sql"
    
    puts "String replacing latin1 with utf8..."
    system "cat #{database}.sql | replace \"charset=latin1;\" \"charset=utf8;\" > #{database}_tmp.sql"
    system "cat #{database}_tmp.sql | replace \"CHARSET=latin1;\" \"CHARSET=utf8;\" > #{database}_fixed.sql"
    system "rm #{database}_tmp.sql"
    
    puts "Importing altered SQL file back to MySQL..."
    system "mysql  #{user_switch} #{pass_switch} #{database} < #{database}_fixed.sql"
    
    puts "Changing db charset to utf8..."
    system "mysql  #{user_switch} #{pass_switch} #{database} -e \"alter database #{database} charset=utf8;\""
    
  end

  def self.parse_options(args)
    @options = OpenStruct.new
    @options.username = nil
    @options.password = false

    @opts = OptionParser.new do |opts|
      opts.banner = "Usage: latin1-to-utf8.rb [options] database_name"

      opts.separator ""
      opts.separator "Optional:"

      opts.on("-u", "--username MYSQL_USER", "Specify MySQL user") do |u|
        @options.username = u
      end

      opts.on("-p", "--password", "Make MySQL request your password") do
        @options.password = true
      end
      
      opts.separator ""
      opts.separator "Help:"

      opts.on_tail("-h", "--help", "Show this message") do
        puts opts
        exit
      end

    end

    @opts.parse!(args)
  end

end

Latin1ToUtf8.run