Migrating a Rails App from MySQL to SQLite database

This article shows the steps to migrate your database from MySQL to SQLite.

Pre-requites

• You need to have administrative access to the target MySQL database.
• You have SQLite3 installed on the target machine.

Steps

1. Create a dump of your MySQL database.

   $ mysqldump -u USER -p -h HOST > ~/dump.sql
   

2. Clone the mysql2sqlite tool from Github.

   $ git clone https://github.com/mysql2sqlite/mysql2sqlite.git
   

3. Use the mysql2sqlite tool to import data from your dump file into a fresh SQLite database file.

   $ ./mysql2sqlite ./dump.sql | sqlite3 mydb.sqlite3
   

4. Update the Gemfile in your Rails App.

   # gem 'mysql2', '~> 0.5'
   gem 'sqlite3', '>= 2.1'
   

5. Run bundle install.

6. Update the database configuration.

   default: &default
     adapter: sqlite3
     pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
     timeout: 5000
   
   development:
     <<: *default
     database: storage/mydb.sqlite3
   

7. Copy the SQLite file into the storage directory.

8. That's all. You can now test your app.