Convert a Ruby on Rails app from sqlite to MySQL?

Question

I made an app in Ruby on Rails and now I want to get it hosted. However, they require that I use MySQL and I set it up using sqLite3. Is there any way to convert it to use MySQL?

Answer 1

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: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   reconnect: false   database: **myapp_development**   pool: 5   username: **root**   password: **supersecretpassword**   **socket: /opt/local/var/run/mysql5/mysqld.sock** test:   adapter: mysql   encoding: utf8   reconnect: false   database: **myapp_test**   pool: 5   username: **root**   password: **supersecretpassword**   socket: **/opt/local/var/run/mysql5/mysqld.sock**  production:   adapter: mysql   encoding: utf8   reconnect: false   database: **myapp_production**   pool: 5   username: **root**   password: **supersecretpassword**   socket: **/opt/local/var/run/mysql5/mysqld.sock** 

Be sure to update the values surrounded by asterix as appropriate for your platform! The socket value is only good for Mac OSX using MacPorts. Most flavors of linux do not require this value.

Step 5

If you have some errors in the following step, you might have to install the mysql gem:

sudo gem install mysql 

Have rake create your database

rake db:create rake db:schema:load 

Step 6

Use YamlDb to reload your data into MySql

rake db:load