home » work in progress » rails 3 migrating data from sqlite3 to mysql

Rails 3 migrating data from sqlite3 to mysql

Rails 3 migrating data from sqlite3 to mysql

Warning
Example 1
Example 2
Migrating Schema
References

Warning

This is a notes page, please do not following any information contained therein.

Example 1

Probably the quick easiest way is using the sqlite .dump command, in this case create a dump of the sample database.

sqlite3 sample.db .dump > dump.sql

You can then (in theory) import this into the mysql database, in this case the test database on the database server 127.0.0.1, using user root.

mysql -p -u root -h 127.0.0.1 test < dump.sql

I say in theory as there are a few differences between grammars.

In sqlite transactions begin

BEGIN TRANSACTION;
…
COMMIT;

MySQL uses just

BEGIN;
…
COMMIT;

There are other similar problems (varchars and double quotes spring back to mind) but nothing find and replace couldn't fix.

Perhaps you should ask why you are migrating, if performance/ database size is the issue perhaps look at reoginising the schema, if the system is moving to a more powerful product this might be the ideal time to plan for the future of your data.

top

Example 2

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

top

Migrating Schema

Migrate schema from current db to production:

rake:db:schema:dump
RAILS_ENV=production rake db:schema:load

top

References

top