copy production database to staging capistrano

I am using rails and capistrano with a staging and production server. I need to be开发者_如何学Go able to copy the production database to the staging database when I deploy to staging. Is there an easy way to accomplish this?

I thought about doing this with mysql and something like:

before "deploy:migrate" do
  run "mysqldump -u root #{application}_production > output.sql"
  run "mysql -u root #{application}_staging < output.sql"
end

(I have not tested this btw, so not sure it would even work) but it would be easier / better if there was another way.

Thanks for any help

This is a quick way to do it also. This uses SSH remote commands and pipes to avoid temp files.

mysql -e 'DROP DATABASE stag_dbname;'
ssh prod.foo.com mysqldump -uprodsqluser -pprodsqlpw prod_dbname | gzip -c | gunzip -c | mysql stag_dbname

Here's my deployment snippet:

namespace :deploy do
  task :clone_production_database, :except => { :no_release => true } do
    mysql_user = "username"
    mysql_password = "s3C_re"
    production_database = "production"
    preview_database = "preview"
    run "mysql -u#{mysql_user} -p#{mysql_password} --execute='CREATE DATABASE IF NOT EXISTS #{preview_database}';"
    run "mysqldump -u#{mysql_user} -p#{mysql_password} #{production_database} | mysql -u#{mysql_user} -p#{mysql_password} #{preview_database}"
  end
end
before "deploy:migrate", "deploy:clone_production_database"

I do this -- it is really useful. Here are links explaining how ...

http://c.kat.pe/post/capistrano-task-for-loading-production-data-into-your-development-database/

or

http://blog.robseaman.com/2008/12/2/production-data-to-development

or

https://web.archive.org/web/20160404204752/http://blog.robseaman.com/2008/12/2/production-data-to-development

mysql -e 'DROP DATABASE stag_dbname;' 
ssh prod.foo.com mysqldump -u prodsqluser

This may not works. At least it does not work with the PostgreSQL.

1. You have your staging application locked the database so you cannot drop it
2. While some tables are locked you will still overwrite rest tables. So you got an corrupted database

working link for the post above

https://web.archive.org/web/20160404204752/http://blog.robseaman.com/2008/12/2/production-data-to-development