Migrating Rails Database From SQLite To (Remote) MySQL

I was creating a simple Rails web application and it was predicted and agreed that the traffic won’t be too high, let’s say that the maximum simultaneous access to the application will be below 50 at a time. But, things went better (or worse) that it should handle more than 100 simultaneous access at a time after a month of launching, and predictively will continue to increase in the future.

SQLite is the default database engine for Rails in the development mode, and based on my benchmark it still pretty good to use it than to put MySQL to manage the store and retrieve the data for the application in production environment. SQLite only consumes below 10% in average of time within the requests even in busiest query, while the most time consuming is the Ruby itself, also the network resolve and dispatching on web server including load balancing only consumes around 10% load time on around 100 ms response time.

When it reaches 100 access simultaneously to the application, a busiest table crashed, Rails complaining about a table not found error. I restored from the backup, but, a few moments later the table crashed again and followed by another table. I think that the table read-write high activity made the tables corrupted, and because it just can’t handle too much requests, and it won’t scale because the database itself is not designed to scale. That’s the case i’m experiencing, and i immediately start to think moving the database to a more reliable relational database. I’m considering of PostgreSQL or MySQL as the option, but i choose MySQL because the application activities is much on reading than writing to the database.

For the future anticipation, i also planned to separate the server for the database from the application itself, so when it required to be scaled on the database, it will be easier and won’t distract the application. By the way, let’s back to the main topic, that migrating database could be a painful and hard job if you don’t think it in a simple and tricky way. What you need to do is setup the remote MySQL database server, grant the access to the application server, dump the SQLite application production database, load them in MySQL, change the application database config, then restart the application.

Setup the MySQL remote server
On a newly fresh installed server, you need to install the MySQL distribution. This article will only cover the installation and setup on latest Ubuntu 14.04 or it near versions, and the first thing you need to do is to perform this command:

$ sudo apt-get update
$ sudo apt-get install mysql-server mysql-client libmysqlclient-dev

You will be prompted to enter your root password at the end of the installation step. Don’t forget to remember or take a note of it. Also, the MySQL server will be automatically started, but if you want to make sure you can run this command:

$ sudo service mysql restart

We need to adjust so that MySQL will be available remotely, open the config file:

$ sudo nano /etc/mysql/my.cnf

Remove or comment skip-networking config line to make the networking available, and bind your address to the server public IP Address, then reload or restart the MySQL.

// #skip-networking
// bind-address=YOUR-MYSQL-SERVER-IP

$ sudo service mysql restart

Then we need to create the database for the application and grant the access to your application server. It still use root user anyway, but you can always use the different user. Issue this command, enter the password, then you need to tell MySQL to grant the access:

$ mysql -u root -p

> create database YOUR_APP_production;
> GRANT ALL ON YOUR_APP_production.* TO root@’YOUR_APP_SERVER_IP’ IDENTIFIED BY ‘YOUR_ROOT_PASSWORD’;

Now aal iz well (everything is set with Slumdog Millionaire accent), we can continue to dump the current SQLite database on the application.

Dump the current database
Actually, there’s a built-in command on SQLite to dump the record of the tables within the database to a .sql file. But, there’s a few possibilites that it will return an error on discompatibility of the SQL command or encoding. I played it save and my favorite tool to do it is the yaml_db gem.

Use the gem and also the mysql2 gem to your application by add it to your Gemfile:

gem ‘yaml_db’
gem ‘mysql2′

Install it using bundle:

$ bundle install

Perform the command to dump the database records to db/data.yml and you will get your converted database in YAML format within the db directory.

$ rake RAILS_ENV=production db:data:dump
$ ls db/dump.yml

Load the data to MySQL
The result of the dump is already exist in db directory as you perform the ls command previously. Now, you need to change the production database config on config/database.yml to point to MySQL database we created before.

production:
  adapter: mysql2
  encoding: utf8
  database: YOUR_APP_production
  username: root
  password: YOUR_ROOT_PASSWORD
  host: YOUR_MYSQL_SERVER_IP
  port: 3306

Migrate your database so it will be identical to the structure of the SQLite version, then load the dumped YAML data.

$ rake RAILS_ENV=production db:migrate
$ rake RAILS_ENV=production db:data:load

You can see the changes on the database by using ActiveRecord query within production environment of the rails console.

Restart the application
Now MySQL is taking place, you can restart the application and see the application running on the remote MySQL server.

blog comments powered by Disqus