Easily dump or load your entire MySql database using text files

One of my apps currently runs in several different environments: development, test, staging and production. Whenever I deploy to my staging environment, I like make sure that the deployment will be successful on production data. I don’t currently have any synchronisation strategy between staging and production, so I needed a way to quickly and easily replicate the entire production database into my staging database.

The reasoning behind this is that the deployment itself should be a test. Deployment to staging should effectively be a test for a production deployment. If my staging deployment succeeds, then it’s pretty safe to assume that the production deployment will also succeed.

MySql gives us a very handy command to dump the database into a text file: mysqldump. Similarly, it’s just as easy to load a database from a text file.

Dumping data

mysqldump -uEd -pSecret foo_db --skip-triggers --compact 
    --ignore-table=foo_db.schema_migrations
    --no-create-info > data_file

In this command, our user is Ed with a password of Secret. The lack of spaces between the -u and -p switches and their values is intentional. We are targeting the foo_db database. The contents of all tables except the schema_migrations table will be dumped into the data_file text file.
We are also providing a couple of other options to exclude any triggers and to exclude SQL statement that will create the tables.

Loading data

mysql -uEd -pSecret foo_db < data_file

Not much explanation needed here. Load the contents of the data_file into the foo_db database.

Ok, how the hell do I use this…?

Well, I have a rake task for each of the above operations. When I deploy to my staging environment, I dump the entire production database into a text file, and then load that file into the staging database. Then the deployment itself begins, part of which is running migrations.

Here’s what my rake tasks look like:

namespace :db do
  namespace :data do
    desc "Dump data into sql script file: filename=[target filename]"
    task :dump => 'environment' do
      environment = (ENV.include?("RAILS_ENV")) ? (ENV["RAILS_ENV"]) : 'development'
      ENV["RAILS_ENV"] = RAILS_ENV = environment

      database = get_database(environment)
      user = ENV['USER'] || 'root'
      password = ENV['PASSWORD']
      filename = ENV['FILENAME'] || environment
      timestamp = ENV['TIMESTAMP'] ? bool(ENV['TIMESTAMP']) : true
      file_suffix = DateTime.now.strftime('%Y-%m-%d-%H-%M-%S')

      full_filename = filename + (timestamp ? "_#{file_suffix}" : '') + '.sql'

      puts "Connecting to #{environment} environment..."
      sh "mysqldump -u#{user} -p#{password} #{database} --skip-triggers --compact " +
        "--ignore-table=#{database}.schema_migrations " +
        "--no-create-info > #{RAILS_ROOT}/db/data/#{full_filename}"

      puts "Successfully created #{full_filename} from #{environment} environment."
    end

    desc "Load data from sql script file: filename=[data file]"
    task :load => 'environment' do
      environment = (ENV.include?("RAILS_ENV")) ? (ENV["RAILS_ENV"]) : 'development'
      ENV["RAILS_ENV"] = RAILS_ENV = environment

      database = get_database(environment)
      user = ENV['USER'] || 'root'
      password = ENV['PASSWORD']
      filename = ENV['FILENAME']
      raise "Please specify a source file (FILENAME=1)" if filename.blank?

      puts "Connecting to #{environment}..."
      ActiveRecord::Base.establish_connection(RAILS_ENV.to_sym)

      puts "Truncating tables..."
      ActiveRecord::Base.connection.execute('show tables').each do |table|
        unless table.to_s == 'schema_migrations'
          puts "   Truncating #{table}"
          ActiveRecord::Base.connection.execute("truncate table #{table.to_s}")
        end
      end

      puts "Importing data from #{filename}..."
      sh "mysql -u#{user} -p#{password} #{database} < #{RAILS_ROOT}/#{filename}"
      puts "Completed loading #{filename} into #{environment} environment."
    end
  end
end

private
def get_database(environment)
  case environment
    when 'test'
      'db_test'
    when 'staging'
      'db_staging'
    when 'production'
      'db'
    else
      'db_development'
  end
end

def bool(s)
  s.match(/(true|t|yes|y|1)$/i) != nil
end

Gotchas!

Obviously this strategy will not work with very large, high-traffic databases. It also make various assumptions about access right and privileges, but for my purposes it works. Be very careful with these commands though – there is no “Are you sure?” safety net when you load a data file. Use with care!

About these ads