Kartar.Net

Migrating a Rails database from Sqlite3 to MySQL

• Sunday August 24th, 2008

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 git://github.com/adamwiggins/yaml_db.git ` .. for Rails versions less than 2.1 use ... ` $ sudo script/plugin install http://github.com/adamwiggins/yaml_db.git ` … 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!