mysql-copydb.rb

#!/usr/bin/env ruby

require 'rubygems'
require 'mysql'

from_database = 'something_production'
to_database = 'something_development'

connection = Mysql.connect('localhost', 'username', 'password', from_database)
connection.query_with_result = false
tables = connection.list_tables

exit if tables.empty?

# copy structure first so we can establish locks on the entire database
tables.each do |table|
  connection.query("DROP TABLE IF EXISTS `#{to_database}`.`#{table}`")
  connection.query("CREATE TABLE `#{to_database}`.`#{table}` LIKE `#{from_database}`.`#{table}`")
end

# establish locks
locks = tables.collect { |table| "`#{to_database}`.`#{table}` WRITE, `#{from_database}`.`#{table}` READ" }.join(', ')
connection.query("LOCK TABLES " + locks)

# copy data, disabling keys as we go (this is so we dont run into foreign key constraints)
tables.each do |table|
  connection.query("ALTER TABLE `#{to_database}`.`#{table}` DISABLE KEYS")
  connection.query("INSERT INTO `#{to_database}`.`#{table}` SELECT * FROM `#{from_database}`.`#{table}`")
  connection.query("ALTER TABLE `#{to_database}`.`#{table}` ENABLE KEYS")
end

# release locks and close connection
connection.query('UNLOCK TABLES')
connection.close