Topic: Populate Production DB

I am so happy to report that my first Rails app is online and popping big_smile

Of course (as always) there is one more step to complete. My production DB has been built and migrated nicely..now I just need to actually transfer the data from my dev DB.

What's the most painless way to do this?

Re: Populate Production DB

I found an interesting rake task for backing up data FROM production DB, but I'm not sure how to rework it do my needs. Any ideas  are welcome

namespace :db do
  desc "Update the development database with a fresh dump of production"
  task :update_dev do
    host = "deploy@marklunds.com"
    db = "marklunds_production"
    system("ssh #{host} \"mysqldump -u root --set-charset #{db} > /tmp/#{db}.dmp\"")
    system("scp #{host}:/tmp/#{db}.dmp ~/tmp")
    system("mysql -u root marklunds_development > ~/tmp/#{db}.dmp")
  end
end

Re: Populate Production DB

That uses mysqldump which should do what you want. You can just run the commands manually on your end - no need to create a ruby script.

If you just want to copy over a table or two, sometimes it's easiest to do this:

/* local */
SELECT * FROM foo INTO OUTFILE '/path/to/new/file';

/* server */
LOAD DATA INFILE '/path/to/file' INTO TABLE foo;


Just make sure the table structure matches.

Railscasts - Free Ruby on Rails Screencasts

Re: Populate Production DB

How do I make sure I select from the right DB though?

Edit: Do I need to save the files with a particular extension?

Last edited by pimpmaster (2007-05-14 21:27:28)

Re: Populate Production DB

for the second command? You need to specify it:

USE foo_development;

Railscasts - Free Ruby on Rails Screencasts

Re: Populate Production DB

See above edit smile

Re: Populate Production DB

Just tried it out and get this error:

"Cant create/write to file" (Errcode: 13)

weird

Re: Populate Production DB

pimpmaster wrote:

Edit: Do I need to save the files with a particular extension?

I don't think it matters, but I normally save the files as ".txt".

Your next question will probably be about permissions. MySQL doesn't have access to much so you need to create a special folder that either he owns or is read/write accessible to everyone so MySQL can export to it.

Edit: LOL, I knew it.

Last edited by ryanb (2007-05-14 21:30:58)

Railscasts - Free Ruby on Rails Screencasts

Re: Populate Production DB

LOL..u got me. Gonna chmod 777 and see whats good

Re: Populate Production DB

argh.. that didnt work at all. Perhaps I need to find out which folder is "owned" by MySQL

Re: Populate Production DB

I sorted this out by saving text files to my tmp directory