Scaling Ruby on Rails: Setting Up A Dedicated MySQL Server (part 2)

1. Choosing A Database

2. Server Set-Up Structure

1. Load-Balancing Multiple Application Servers
2. The Database Server Layer

3. Adding The Database Server To The Deployment Set-Up

1. Preparing The Server

4. Installing MySQL

1. Downloading The Database Server
2. Performing The Initial Set-Up
3. Connect To The Database Server
4. Create A New Database
5. Create A New Database User
6. Granting Privileges
7. Enabling Remote Connections

5. Configuring Rails Applications

1. Installing Database Server Libraries
2. Configuring database.yml For Rails
3. Getting The mysql Gem
4. Migrating Data Between Servers

Ruby on Rails application development framework provides a large array of support for database servers. For a majority of applications, a relational database management system is the way to go. However, some might require a non-relational, schema-less NoSQL database server – either instead of the relational one or both running together.

When you begin working with Rails on your own development computer, the simplest and probably the most logical way is to start with using a capable but basic database implementation, such as the SQLite library. However, for real-world deployments, chances are SQLite would be insufficient to handle your application load, thus requiring a full-fledged RDBMS.

Depending on your needs and application type, you need to decide on a database management system (i.e. a database server) to create the database layer of your application deployment set-up.

For relational databases some of the more popular choices are:

• MySQL and derivatives:

The most popular and commonly used RDBMS and related, forked projects.

• PostgreSQL:

The most advanced, SQL-compliant and open-source objective-RDBMS.

For non-relational database servers:

• Column based:

Cassandra, HBase, etc.

• Document:

MongoDB, Couchbase, etc

• Graph:

OrientDB, Neo4J, etc.

In order to make a clear and long-term decision before continuing with deploying a database server, you might be interested in reading our articles on the subject:

• Introduction To Databases:

Understanding SQL And NoSQL Databases And Different Database Models

• Relational:

A Comparison Of Relational Database Management Systems

• NoSQL:

A Comparison Of NoSQL Database Management Systems And Models

Before we begin with building the database layer, let’s see what our final deployment set up will look like.

Load-Balancing Multiple Application Servers

Previously, after creating a load-balancer / reverse-proxy with multiple application servers, this is what we had in the end:

Three droplets with each having a distinct role:
------------------------------------------------
1 x Load-Balancer / Reverse-Proxy
2 x Application Servers Running Your Rails Web-Application / API

                             ---

                    DEPLOYMENT STRUCTURE

             +-------------------------------+
             |                               |
             | LOAD-BALANCER / REVERSE PROXY |
             |                               |
             +-------------------------------+
                             +
                             |
                             |
        +---------------+    |    +---------------+
        |  APP  SERVER  |    |    |  APP  SERVER  |
        |---------------|    |    |---------------|
        |               |    |    |               |
        |     RAILS     |<---+--->|     RAILS     |
        |               |         |               |
        +---------------+         +---------------+

The Database Server Layer

In order to have a centrally accessible database server (e.g. a RDBMS and/or NoSQL database), we will add a 4th element to our server set-up:

Four droplets:
------------------------------------------------
1 x Load-Balancer / Reverse-Proxy
2 x Application Servers Running Your Rails Web-Application / API
1 x Database Server (e.g. MySQL, PostgreSQL, MongoDB etc.)

             +-------------------------------+
             |                               |
             | LOAD-BALANCER / REVERSE PROXY |
             |                               |
             +-------------------------------+
                             +
                             |
                             |
        +---------------+    |    +---------------+
        |  APP  SERVER  |    |    |  APP  SERVER  |
        |---------------|    |    |---------------|
        |               |    |    |               |
        |     RAILS     |<---+--->|     RAILS     |
        |               |         |               |
        +---------------+         +---------------+
                +                         +
                |                         |
                |  +-------------------+  |
                |  |  DATABASE SERVER  |  |
                |  |-------------------|  |
                |  |                   |  |
                |  |       MySQL,      |  |
                +->|     PostgreSQL,   |<-+
                   |        etc.       |
                   |                   |
                   +-------------------+

In this article, for the purposes of demonstration, we are going to create and configure a MySQL database.

Let’s begin!

Preparing The Server

Note: This part is a summary of the server preparation section from our Scaling-Rails tutorial. It explains how to get started with a CentOS VPS. If you would like to deploy your MySQL instance on an Ubuntu machine, check out Deploying Sinatra tutorial to see how to prepare an Ubuntu server before continuing with installing MySQL, or any other database server.

Run the following command to update the default tools of your CentOS based virtual server:

yum -y update

Install the application bundle containing several development tools by executing the following command:

yum groupinstall -y 'development tools'

Add the EPEL software repository for YUM package manager to use.

# Enable EPEL Repository
sudo su -c 'rpm -Uvh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm'

# Update everything, once more.
yum -y update

Install some additional libraries:

yum install -y curl-devel nano sqlite-devel libyaml-devel

Downloading The Database Server

In order to install MySQL, execute the following command:

yum install mysql-server mysql-devel

Start the MySQL server daemon:

service mysqld start

Note: If you are working with Ubuntu, instead of mysql-devel, you need to install mysql-client and libmysqlclient-dev packages using aptitude (or apt-get) on your application servers for Rails to be able to work with MySQL.

Performing The Initial Set-Up

Run the following command to start the initial MySQL set-up process:

/usr/bin/mysql_secure_installation

Once you run the above command, you will see a welcome screen similar to below:

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 

Unless have already created a password using the:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h myt password 'new-password'

commands, press enter and move on with the next steps, answering the questions similarly to this:

# Set root password?                     --> Y
# Remove anonymous users?                --> Y
# Disallow root login remotely?          --> Y
# Remove test database and access to it? --> Y
# Reload privilege tables now?           --> Y

Connect To The Database Server

Connect to the database using the MySQL client:

mysql -u root -p

Enter your root password set at the previous step:

# Enter password:
# ..
# .
mysql>

Create A New Database

Let’s begin with creating a default database for our Rails application.

Run the following command to create a new MySQL database:

# Usage: create database [database_name];
# Example:
create database rails_myapp;

Create A New Database User

For reasons of security, let’s now create a database user for Rails application to use that will have remote access.

Add the new user with both local and remote access:

# Usage:
# CREATE USER '[user name]'@'localhost' IDENTIFIED BY '[password]';
# CREATE USER '[user name]'@'%' IDENTIFIED BY '[password]'; 
# Example:
CREATE USER 'rails_myapp_user'@'localhost' IDENTIFIED BY 'pwd';
CREATE USER 'rails_myapp_user'@'%' IDENTIFIED BY 'pwd';

To verify that the users have been created, run the following:

SELECT User,host FROM mysql.user;

# Example:
# +------------------+-----------+
# | User             | host      |
# +------------------+-----------+
# | rails_myapp_user | %         |
# | root             | 127.0.0.1 |
# | rails_myapp_user | localhost |
# | root             | localhost |
# +------------------+-----------+ 

Granting Privileges

Run the following commands to grant privileges to a specific user:

# Usage:
# GRANT ALL ON [database name].* TO '[user name]'@'localhost';
# GRANT ALL ON [database name].* TO '[user name]'@'%';
# Example:
GRANT ALL ON rails_myapp.* TO 'rails_myapp_user'@'localhost';
GRANT ALL ON rails_myapp.* TO 'rails_myapp_user'@'%';

And flush privileges:

FLUSH PRIVILEGES;

Note: To fine-tune the privileges according to your needs, check out the official MySQL documentation on the subject: Privileges Provided by MySQL

Exist the client:

exit
# Bye

Enabling Remote Connections

Since we need MySQL server to be accessible from remote computers running the Rails application, the configuration file must be modified.

Run the following command to edit the MySQL configuration my.cnf using the nano text editor:

nano /etc/my.cnf

We would like to tell MySQL to listen to connections from the IP address assigned to our droplet, so let’s add the following line:

bind-address   =  0.0.0.0

At the end of the [mysqld] block:

[mysqld]
..
.
bind-address   =  0.0.0.0

Save and exit by pressing CTRL+X and confirming with Y.

Restart the MySQL daemon with the following command:

service mysqld restart

# Stopping mysqld:                               [  OK  ]
# Starting mysqld:                               [  OK  ]

In this section, we will modify the Rails application servers so that they start working with the database server we have just set up.

Installing Database Server Libraries

The first thing to do is installing the necessary database libraries. In our case, it is MySQL’s development package.

Run the following to install MySQL development package mysql-devel:

yum install -y mysql-devel

Configuring database.yml For Rails

Database settings for Rails applications are kept inside the database.yml file in /config directory.

Run the following command to edit the database.yml file using the nano text editor:

# Make sure to enter your application deployment directory
# Example:
# cd /var/www/my_app

nano config/database.yml

Once you open up this file, you will see database settings, divided by environment names. Since an application needs to run using the production environment, let’s edit the configuration for that.

Replace the production: YML code block with the following, changing the necessary bits to suit your own set-up configuration, e.g. the IP address etc.

# Example:
# production:
#   adapter: mysql
#   encoding: utf8
#   database: [database name]
#   username: [user name]
#   password: [password]
#   host: [server IP address]
#   port: [port number]
#   protocol: [protocol]
#   pool: [connection pool]

production:
  adapter: mysql
  encoding: utf8
  database: rails_myapp
  username: rails_myapp_user
  password: pwd
  host: 128.199.233.36
  port: 3306
  pool: 10

Note: As provided in the example above, you might need to specify the protocol.

Note: The pool argument contains the number of maximum simultaneous database connection slots (i.e. pool) available. You need to assess your needs and set a number accordingly.

Save and exit by pressing CTRL+X and confirming with Y.

Getting The mysql Gem

Start editing the Gemfile using nano using the following:

nano Gemfile

Add the following line to the file:

gem 'mysql'

Save and exit by pressing CTRL+X and confirming with Y.

Install the new gem using bundle:

bundle install

And that’s it! From now on, your Rails application servers will be using your brand new database server for all operations.

Migrating Data Between Servers

If you already have data on your development machine which you would like to migrate to your VPS, check out the DigitalOcean community article on the subject: How To Migrate a MySQL Database Between Two Servers.

<div class=“author”>Submitted by: <a href=“https://twitter.com/ostezer”>O.S. Tezer</a></div>