Migrating a Rails database from Sqlite3 to MySQL

August 25th, 2008 by kartar Leave a reply »

So when I first looked at Redmine I ran it up and used Sqlite3 as the database back-end. Then when I migrated our Trac data I just left Sqlite3 as the back-end database and migrated our data to that. With that startling lack of forethought aside, I always had the view the database should be MySQL because well:

a) I know it
b) I like it
c) It’s probably more scalable (IMHO)

So today I actually sat down to do the migration piece. I dumped out the sqlite3 database and tried to do some manual/scripted edits to convert it to something MySQL would import. Epic Fail.

So I tried the YAMLdb that abstracts database exports using YAML. A quick installation, some edits to config/database.yml, a rake db:dump and rake db:load and the data was moved:

… Create our database …

$ sudo mysql -p
mysql> create database redmine character set utf8;

… Grant privs to your chosen user …

mysql> GRANT ...

… Configure a test database for our new MySQL database …

$ vim config/database.yml

.. for Rails version 2.1 and later install the plugin …

$ sudo script/plugin install
://.com/adamwiggins/yaml_db.

.. for Rails versions less than 2.1 use …

$ sudo script/plugin install http://.com/adamwiggins/yaml_db.

… Dump out the current production database …

$ sudo rake db:dump RAILS_ENV=production

… Load the freshly created db/data.yml file into our test database …

$ sudo rake db:load RAILS_ENV=test

… Reconfigure the application to point to the new MySQL database as production …

$ vim config/database.yml

… Start Redmine …

$ sudo /etc/init.d/mongrel_cluster start

Had one bad field I had to do some manual editing too – still not quite sure what was wrong with the field but whatever I did fixed it – but otherwise very smooth.

Started up and now Redmine runs perfectly with MySQL as the back-end!

2 comments

  1. This plugin is amazing but I think it lacks of a command-line utility, installing the plugin to every application I need to dump data is something weird, here you can find my fork to this project which already have a command-line utility > http://github.com/rafmagana/yaml_db

  2. Anonymous says:

    The current URL for yaml_db is:

    git://github.com/adamwiggins/yaml_db.git

    Or for Rails 2.0 and below:

    http://github.com/adamwiggins/yaml_db.git

Leave a Reply