Scaling Ruby on Rails: Setting Up A Dedicated MySQL Server (part 2)
Introduction
Each server that is part of a multi-machine application deployment stack should be like a good Italian pizza: a solid base needs to be garnished only with the necessary ingredients, without over-bloating or heavily loading, in order to keep everything easy to handle (and manage).
In this second part of our Scaling-Rails DigitalOcean article series, we are going to see how to create a droplet to host the database layer, for application servers to connect-to and work-with. Our aim here is to minimize the chances of letting a Single Point of Failure (SPoF) emerge as a possible culprit of downtime (or loss), by distinctively delegating one single task per server.
Glossary
1. Choosing A Database
2. Server Set-Up Structure
- Load-Balancing Multiple Application Servers
- The Database Server Layer
3. Adding The Database Server To The Deployment Set-Up
- Preparing The Server
4. Installing MySQL
- Downloading The Database Server
- Performing The Initial Set-Up
- Connect To The Database Server
- Create A New Database
- Create A New Database User
- Granting Privileges
- Enabling Remote Connections
5. Configuring Rails Applications
- Installing Database Server Libraries
- Configuring
database.ymlFor Rails - Getting The
mysqlGem - Migrating Data Between Servers
Choosing A Database
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
Server Set-Up Structure
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. |
| |
+-------------------+
Adding The Database Server To The Deployment Set-Up
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
Installing MySQL
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 ]
Configuring Rails Applications
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.
3 Comments