Welcome to Working With Rails


Discussion Forums

Discuss all things Ruby on Rails with perhaps the web's most vibrant group of Ruby on Rails enthusiasts.
copy development db to production (both mysql)
11 Posts
copy development db to production (both mysql)


I'm testing deployment of a rails app with some dummy mysql db content. Is there an easy way to copy development to the production db?

When I use export/import from phpmyadmin (with "structure" unchecked), it wants to recreate the development db (_development), rather than just import the data into the _production db.


edit:to partially answer my own question, I was able to do it table by table...

David, I'm usually using a migration for that. The way I'm doing it is by having two migrations, one for the actual model, and the second for loading up the data that I would use in both the development and in the production databases. For example:

20090716233745_create_model (which contains the model/table creation), and

20090716233756_populate_model (that containts the actual data to be used) as in :

class PopulateModel < ActiveRecord::Migration Model.new({:field_a => 'field_content1', :field_b => 'more_content1'}).save! Model.new({:field_a => 'field_content2', :field_b => 'more_content2'}).save! Model.new({:field_a => 'field_content3', :field_b => 'more_content3'}).save! end

You can then use the migrations in the environment you want.

Here are the basic commands for dumping/loading DBs in mysql:

Dumping the database:

$ mysqldump your_dev_db_name > your_db_dump.sql

Loading the dump:

$ mysql your_production_db_name < your_db_dump.sql

Can rake db:migrate RAILS_ENV=production works?and also the total database data?I think mysqldump is effective,but only for mysql.

Pat's suggestion is the correct one. In response to Kosmas's suggestion to bootstrap data into your database using migrations: don't do this. Here's why:

1) Rails will not migrate your test database. The db:test:prepare rake task simply slams the schema of your migrated development database into tests database, but no data. If you depend on data bootstrapped by migrations for your models to be valid then your tests will not work. You can fake it using fixture data, but then you've duplicated everything.

2) You can potentially get away with using migrations to bootstrap data using migrations if you're careful and you carefully manage your test fixtures, but only if you never (never EVER) use ActiveRecord models in your migrations. If you insert data you must do it using raw SQL. Every time that migration runs it will expect the database to be in the state created by running all previous migrations. However, ActiveRecord objects have no such historical knowledge; they always expect the database to be in the state created by running ALL migrations, including migrations that come after your data import migration. If the structure of that model's table has changed since you wrote the data import migration then your ActiveRecord will likely try to make method calls based on a schema that doesn't exist yet. Don't do it.


The suggestion about using migrations for populating data was taken from the book : 'Packt-Ruby on Rails Web Mashup Projects' on the first project and under the paragraph 'populating kiosk locations with longitude and latitude information' page 38.

As far as I can remember as long as the data migration comes immediately after the table creation migration, there is no problem using ActiveRecord. Will have to look again in the tests but cannot remember any problems there.

There is also the benefit that you can have a different (ie sqlite) development database and production (MySQL) database that way.

As I said in my edit, export/import table by table with phpmyadmin appears to have worked. A trifle tedious, but better than rebuilding from scratch.

One odd thing that happened is that when I ran the migration on the production server (rake db:schema:load RAILS_ENV=production), I noticed in phpmyadmin that many of the fields were assigned "latin1_swedish_ci" collation, and had to be manually reset to "utf8_general_ci". This is on a bog-standard apache2-mysql-rails installation on ubuntu 8.04 server, and did not occur in my previous deployment test to the same server.

Hi there I've been using a plugin from heroku http://github.com/adamwiggins/yaml_db/tree/master that helps you to copy you database data from sqlite, MySQL PostgreSQL, just use the command rake db:data:dump for exporting, this exports to yaml rake db:data:load for loading the exported data

I second the recommendation for yaml_db from the guys at heroku. It is dead simple and works without configuration. Just rake db:data:dump, git add data.yml, commit, and then git pull and rake db:data:load on your production machine. Awesome.

rake db:test:clone will clone form current env database

11 Posts
Login to add your message