Convert a Ruby on Rails app from sqlite to MySQL?

by

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

More Related Contents:

  1. SSL_connect returned=1 errno=0 state=SSLv3 read server certificate B: certificate verify failed
  2. How to understand nil vs. empty vs. blank in Ruby
  3. Best way to load module/class from lib folder in Rails 3?
  4. Ruby on Rails Server options [closed]
  5. Rails – Strong Parameters – Nested Objects
  6. Rails 4: List of available datatypes
  7. ActiveRecord.find(array_of_ids), preserving order
  8. How to sort text in sqlite3 with specified locale?
  9. How to redirect to previous page in Ruby On Rails?
  10. Why does String#gsub double content?
  11. What is Rack middleware?
  12. rescue_from ActionController::RoutingError in Rails 4
  13. Rails 4: before_filter vs. before_action
  14. Add a reference column migration in Rails 4
  15. How do I render a partial of a different format in Rails?
  16. Why are all Rails helpers available to all views, all the time? Is there a way to disable this?
  17. Uploading multiple files with paperclip
  18. Rails 3.1 and Ruby 1.9.3p125: ruby-debug19 still crashes with “Symbol not found: _ruby_threadptr_data_type” [duplicate]
  19. How to change the default path of view files in a Rails 3 controller?
  20. Rails validation over redirect
  21. How to store data in S3 and allow user access in a secure way with rails API / iOS client?
  22. Rails has_many through form with checkboxes and extra field in the join model
  23. Changing the base URL for Rails 3 development
  24. Rails segmentation fault when starting server?
  25. PGError: ERROR: aggregates not allowed in WHERE clause on a AR query of an object and its has_many objects
  26. What is the difference between pluck and collect in Rails?
  27. Looking for suggestions for building a secure REST API within Ruby on Rails
  28. How can I disable logging in Ruby on Rails on a per-action basis?
  29. How to integrate a Wrap Bootstrap theme into an Rails application?
  30. Is the server running on host “localhost” (::1) and accepting TCP/IP connections on port 5432?

Leave a Comment