# Migrations w/MySQLdump file (.sql?)

**URL:** <https://www.ruby-forum.com/t/migrations-w-mysqldump-file-sql/178945>\
**Category:** Rails\
**Created:** [November 13, 2009, 10:41pm UTC](https://www.ruby-forum.com/t/migrations-w-mysqldump-file-sql/178945 "2009-11-13T22:41:54Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jrgoodner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/53a042/32.png) [@jrgoodner](https://www.ruby-forum.com/u/jrgoodner)\
**Post date:** [November 13, 2009, 10:41pm UTC](https://www.ruby-forum.com/t/migrations-w-mysqldump-file-sql/178945/1 "2009-11-13T22:41:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jrgoodner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/53a042/32.png) [@jrgoodner](https://www.ruby-forum.com/u/jrgoodner)\
**Post date:** [November 13, 2009, 10:52pm UTC](https://www.ruby-forum.com/t/migrations-w-mysqldump-file-sql/178945/2 "2009-11-13T22:52:26Z")

</div>

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.

Darian Shimy

[http://www.darianshimy.com](http://www.darianshimy.com)  
[http://twitter.com/dshimy](http://twitter.com/dshimy)

On Fri, Nov 13, 2009 at 1:40 PM, jrgoodner [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> I’d like to dump the table with MySQLdump. Then, when migrating,
> 
> –Jared
> 
> >

–  
Darian Shimy

---

<div class="post-metadata">

**Author:** ![jrgoodner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/53a042/32.png) [@jrgoodner](https://www.ruby-forum.com/u/jrgoodner)\
**Post date:** [November 14, 2009, 12:11am UTC](https://www.ruby-forum.com/t/migrations-w-mysqldump-file-sql/178945/3 "2009-11-14T00:11:28Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![jrgoodner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/53a042/32.png) [@jrgoodner](https://www.ruby-forum.com/u/jrgoodner)\
**Post date:** [November 14, 2009, 12:41am UTC](https://www.ruby-forum.com/t/migrations-w-mysqldump-file-sql/178945/4 "2009-11-14T00:41:18Z")

</div>

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.

No you wouldn’t. 🙂 There are many good reasons not to put seed data in  
migrations. What I recommend doing is using seed\_fu or similar, but use  
ar\_extensions or even an execute statement so you’re not doing a  
separate query for each record.

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![jrgoodner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/53a042/32.png) [@jrgoodner](https://www.ruby-forum.com/u/jrgoodner)\
**Post date:** [November 14, 2009, 12:40am UTC](https://www.ruby-forum.com/t/migrations-w-mysqldump-file-sql/178945/5 "2009-11-14T00:40:09Z")

</div>

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.

On Fri, Nov 13, 2009 at 3:09 PM, jrgoodner [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> > That way you will only be recreating the view and not the whole table.  
> > On Fri, Nov 13, 2009 at 1:40 PM, jrgoodner [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:
> > 
> > > I’d like to dump the table with MySQLdump. Then, when migrating,
> > 
> > > –Jared
> > 
> > –  
> > Darian Shimy

–  
Darian Shimy