Migrations w/MySQLdump file (.sql?)

rubyonrails-talk
1

Hey there, I'm not super familiar with MySQL, so please forgive me if I say something stupid.

I have a large database table (it includes every zipcode in the U.S., along with its city,state,latitude,longitude), and it seems that we are frequently dropping/creating/migrating our database. It takes a very long time to migrate the zipcodes table (I do so from a .csv, creating a Zipcode object row by row).

I'd like to dump the table with MySQLdump. Then, when migrating, rather than run the ruby code that creates a bunch of objects, instead load the resulting dump file. [With the hopes that that will be a quicker process...think that's the case?]

I don't know how to load the dump file using Rails migrations, and I'd like to. Any thoughts? Re: any part of this process? Is this a silly thing to do? How would you guys suggest I go about migrating tables w/large amounts of data?

Thanks in advance, any advice is MUCH APPRECIATED!!

--Jared

2

I would create a new database, load that table in the new database, and create a view from your database used by rails to the new db you created. That way you will only be recreating the view and not the whole table.

If you are dealing with indexes on the table, be sure to add the indexes before you load the data. Faster.

3

Thanks for the swift reply!

So the application will utilize two databases, one just for the zipcodes table? How would I go about that? Know of any decent references for something like this?

4

See if the following will help:

Create two databases in the same server

create database zipcode; create database rails;

Create the zipcode table in the zipcode database

use zipcode; create table zipdata…;

Switch over to the rails app database and create the view

use rails; create or replace view zipdata as select * from zipcode.zipdata.

query the view like you would any other table (you can even do inserts)

select * from zipdata;

Let me know if that helps.

5

jrgoodner wrote:


Hey there, I'm not super familiar with MySQL, so please forgive me if
I say something stupid.
I have a large database table (it includes every zipcode in the U.S.,
along with its city,state,latitude,longitude), and it seems that we
are frequently dropping/creating/migrating our database. It takes a
very long time to migrate the zipcodes table (I do so from a .csv,
creating a Zipcode object row by row).
I'd like to dump the table with MySQLdump. Then, when migrating,
rather than run the ruby code that creates a bunch of objects, instead
load the resulting dump file. [With the hopes that that will be a
quicker process...think that's the case?]
I don't know how to load the dump file using Rails migrations, and I'd
like to. Any thoughts? Re: any part of this process? Is this a
silly thing to do? How would you guys suggest I go about migrating
tables w/large amounts of data?
Thanks in advance, any advice is MUCH APPRECIATED!!
--Jared

I load a MySQLdump file with a simple:

result = system “mysql -u #{username} -p#{password} -q -e "source #{backupfile}" #{database}”