Create Rails App With Existing MySQL Database Without Losing Data

Question

I have a MySQL Database named Foo_development. It has one table, named bars.

$ mysql -e 'select * from Foo_development.bars'

+----+-------+---------------+---------------------+---------------------+
| id | name  | email         | created_at          | updated_at          |
+----+-------+---------------+---------------------+---------------------+
|  1 | Alice | Alice@bob.com | 2015-08-24 13:45:11 | 2015-08-24 13:45:11 |
+----+-------+---------------+---------------------+---------------------+

I'd like to create a Rails app that uses this database and this table. So I created an app and a model:

$ rails new Foo -d mysql
$ cd Foo
$ rails generate model Bar name:string email:string

That all worked just fine, although I had to edit config/database.yml to connect to the appropriate MySQL host before generating the model.

I tried running my app and got an error:

$ rails server

ActiveRecord::PendingMigrationError
Migrations are pending. To resolve this issue, run:
    bin/rake db:migrate RAILS_ENV=development

I tried running the migrations and got another error:

$ rake db:migrate

== 20150824204923 CreateBars: migrating ===
StandardError: An error has occurred, all later migrations canceled:
Mysql2::Error: Table 'bars' already exists

Didn't know what to do here, so I tried something I found on Google:

$ rake db:migrate:reset

== 20150824204923 CreateBars: migrated (0.0124s) ===

But now all my database data is gone :(

$ mysql -e 'select * from Foo_development.bars'

Empty set (0.00 sec)

What should I have done instead to avoid losing all of my data?

I'm assuming there must be some command besides rake db:migrate:reset that would allow me to run the migrations without truncating the tables in the database, but I haven't found any.

I did look at these questions but they don't talk about preventing data loss, so this is not a duplicate.

If it matters, the Rails app is running from a different computer than the one which hosts the MySQL database. I'm trying to avoid import/export operations, because the databases that I'm working with (outside of this trivial example) have a million+ rows, but I suppose I'm willing to do that if that's the fastest method.

Edit: This question is similar, but it asks about sqlite3 and the answer involves importing the database from an external file (and I'm guessing there's a faster/more efficient way than that.)

Answer 1

In your first Rails app (the old one) you can add the gem seed_dump https://github.com/rroblak/seed_dump in your Gemfile then bundle install

then run in rake db:seed:dump db:drop that will dump the content of your database in your db/seed.rb file and that will also drop your db. Then you can copy and paste this file into your new Rails app under db/seed.rb.

`rake db:create`#create the db (database.yml)
`rake db:migrate db:seed`

So at the end you recreate a db with the same content. Notes if you want only one table you can use rake db:seed:dump MODELS=Bar

edit

First check to see if you have the program mysqldump installed on your machine

which mysqldump

If so then

mysqldump your_database_name > output.sql

In your rails app

rake db:drop db:create db:migrate

Make sure that you drop the db either by rake or by sql syntax.

open the file output.sql and add the line that start with INSERT INTO bars.... in your `db/seeds.rb``with this following syntax.

sql = "INSERT INTO `friendly_id_slugs` VALUES (3,'abram-s-commitment-to-a-life-of-faith',1,'Book',NULL,'2015-08-27 01:50:39'),(4,'what-aileth-thee',2,'Book',NULL,'2015-08-27 01:50:39');"

ActiveRecord::Base.connection.execute(sql)

Notes you might have to edit your sql variables to use proper apostrophe ' or ". You could also rails c -s and execute the above code manually to see if it works on the sandbox console

Then finally

rake db:seed

Answer 2

A simple option would have been to put your CreateBars migration version number into the schema_migrations table.

It appears that the migration file that was causing trouble is probably named db/migrate/20150824204923_create_bars.rb, right?

So you could have done this in your database since the table was already there:

INSERT INTO schema_migrations(version) VALUES('20150824204923');

Oh, and I suppose you'd probably need to create the schema_migrations table too since Rails probably hadn't done that either:

CREATE TABLE IF NOT EXISTS schema_migrations(
  version VARCHAR NOT NULL
);

Answer 3

I would export the tables in the database as a .csv file, depending on the language, framework, etc. you are using it is done in different ways.

Then you could fill your database seeds in your rails application from that csv file.

There are several gems that do that, please find a great one below:

https://github.com/tilo/smarter_csv

Answer 4

As there is only one table, the easiest way would be to take a backup of the database. Create the app, create new migrations, then restore the table from the backup.