MySQL rake task for rails to copy the content of the production database to the development database. Please note that the current development database will be DROPPED!

mysql_DB_clone.rake

# it's not working

namespace :db do
  namespace :mysql do
    desc "Overwrite the whole development db with a copy of the production db"
    task :init_dev do
      
      config = YAML.load(IO.read("config/database.yml"))
      d = config["development"]
      p = config["production"]
      
      unless d["adapter"] == "mysql" and
              p["adapter"] == "mysql"
        raise "This task works only on mysql"
      end
      
      drop_dev_db = "mysqladmin \
                    drop #{d["database"]} \
                    -h #{d["host"]} \
                    -u #{d["username"]} \
                    -p#{d["password"]}"
      
      create_dev_db = "mysqladmin \
                      create #{d["database"]} \
                      -h #{d["host"]} \
                      -u #{d["username"]} \
                      -p#{d["password"]}"
      
      dump_prod_db = "mysqldump \
                      -h #{p["host"]} \
                      -u #{p["username"]} \
                      -p#{p["password"]} \
                      #{p["database"]} \
                      > /tmp/db_mysql_init_dev.sql"
      
      populate_dev_db = "mysql \
                        -h #{d["host"]} \
                        -u #{d["username"]} \
                        -p#{d["password"]} \
                        #{d["database"]} \
                        < /tmp/db_mysql_init_dev.sql "
    
      puts drop_dev_db
      print "(drop the development database) ... "
      STDOUT.flush
      `#{drop_dev_db}`
      puts "done."
      
      puts create_dev_db
      print "(create a new development database) ..."
      STDOUT.flush
      `#{create_dev_db}`
      puts "done."
      
      puts dump_prod_db
      print "(dump the production database) ..."
      STDOUT.flush
      `#{dump_prod_db}`
      puts populate_dev_db
      puts "done."
      
      print "(populate the development database) ..."
      STDOUT.flush
      `#{populate_dev_db}`
      puts "done."
      
      `rm /tmp/db_mysql_init_dev.sql`
      
    end
  end
end

I'm so glad you wrote this! Any particular reason you decided to dump to a file, instead of piping from one to the other?

I did it to keep it simple... On the system I wrote it, this was OK, since the DB is small and writing to a file did not impact the performance... if you write the pipe version, maybe you can gist it too...

Good stuff! Thanks

Very helpful. I wasn't aware of how rake tasks work, so for newbies, save this file in lib/tasks and simply call

rake db:mysql:init_dev

I also needed to change 'mysql' to 'mysql2' in two instances (the unless block at the top)

Note that if the database contains sensitive user information (even emails or encrypted passwords), this can be a security risk. Consider sanitizing all but a few accounts needed for testing as part of the process.

Also it looks like you left the original database name in the drop db line. Other than that it works like a charm.