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

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

1. Preparing The Server

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

1. Installing Database Server Libraries
2. Configuring database.yml For Rails
3. Getting The mysql Gem
4. Migrating Data Between 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     |
        |               |         |               |
        +---------------+         +---------------+

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.       |
                   |                   |
                   +-------------------+

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

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.

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 using the MySQL client:

mysql -u root -p

Enter your root password set at the previous step:

# Enter password:
# ..
# .
mysql>

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;

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 |
# +------------------+-----------+ 

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

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  ]

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

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.

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.

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>