Convert a Ruby on Rails app from sqlite to MySQL?

Step 0

To be safe, I recommend experimenting a bit with this technique in a virtual machine. Save yourself a bunch of heartache and build a virtual machine, check out your code, and have a safe playground that you can throw away if tragedy strikes.

Step 1

Make a backup copy of your database.yml file.

(from your application root)

cp config/database.yml config.database.yml.sqlite3

Step 2

Make a backup copy of your data

For Rails 3, install the YAML DB gem: https://github.com/ludicast/yaml_db

For Rails 2.x install the YAML DB plugin:

script/plugin install git://github.com/adamwiggins/yaml_db.git

Run the dump task

rake db:data:dump

Step 3

Update your config/database.yml file. You will find entries like

development:
  adapter: sqlite3
  database: db/development.sqlite3
  timeout: 5000
test:
  adapter: sqlite3
  database: db/test.sqlite3
  timeout: 5000
production:
  adapter: sqlite3
  database: db/production.sqlite3
  timeout: 5000

Change them to

development:
  adapter: mysql
  encoding: utf8
  database: **myapp_development**
  pool: 5
  username: **root**
  password: **supersecretpassword**
test:
  adapter: mysql
  encoding: utf8
  database: **myapp_test**
  pool: 5
  username: **root**
  password: **supersecretpassword**
production:
  adapter: mysql
  encoding: utf8
  database: **myapp_production**
  pool: 5
  username: **root**
  password: **supersecretpassword**

Be sure to update the values surrounded by asterix as appropriate for your platform!

Step 4

If you have some errors in the following step, you might have to install the mysql (or mysql2 for Rails 3.x) gem:

sudo gem install mysql

Have rake create your database

rake db:create
rake db:schema:load

Step 5

Use YamlDB to reload your data into MySQL

rake db:data:load