Set Up a Remote Database to Optimize Performance with MySQL on Ubuntu 16.04

Introduction

As your application or website grows, you may come to a point where you’ve outgrown your current server setup. If you are hosting your web server and database backend on the same machine, it may be a good idea to separate these two functions so that each can operate on its own hardware and share the load of responding to your visitors’ requests.

In this guide, we’ll discuss how to configure a remote MySQL database server that your web application can connect to. We will be using WordPress as an example so that we have something to work with, but the technique is widely applicable to any MySQL-backed application.

Prerequisites

Before beginning this tutorial, you will need:

• Two Ubuntu 16.04 servers, with a non-root sudo-enabled user, and UFW firewall enabled
• On one of the servers you’ll need the LEMP (Linux, Nginx, MySQL, PHP) stack installed. Our tutorial
• Optionally (but strongly recommended), you can secure your LEMP web server with SSL certificates.

Step 1 — Installing MySQL on the Database Server

Having our data stored on a separate server is a good way to expand gracefully when we hit the performance ceiling of a one-machine configuration. It also provides the basic structure necessary to load balance and expand our infrastructure even more at a later time.

To get started, we’ll install MySQL on the server we did not install the LEMP stack on. Log into this server, then update your package cache and install the MySQL server software:

 	sudo apt-get update
 	sudo apt-get install mysql-server

You will be asked to set and confirm a root password for MySQL during the installation procedure. Choose a strong password and take note of it, as we’ll need it later.

MySQL should be installed and running now. Let’s check using systemctl:

• • systemctl status mysql

● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Tue 2017-05-23 14:54:04 UTC; 12s ago
 Main PID: 27179 (mysqld)
   CGroup: /system.slice/mysql.service
           └─27179 /usr/sbin/mysqld

The Active: active (running) line means MySQL is installed and running. Now we’ll make the installation a little more secure. MySQL comes with a script that walks you through locking down the system:

• • mysql_secure_installation

This will ask you for the MySQL root password that we just set. Type it in and press ENTER. Now we’ll answer a series of yes or no prompts. Let’s go through them:

First, we are asked about the validate password plugin, a plugin that can automatically enforce certain password strength rules for your MySQL users. Enabling this is a decision you’ll need to make based on your individual security needs. Type y and ENTER to enable it, or just hit ENTER to skip it. If enabled, you will also be prompted to choose a level from 0–2 for how strict the password validation will be. Choose a number and hit ENTER to continue.

Next you’ll be asked if you want to change the root password. Since we just created the password when we installed MySQL, we can safely skip this. Hit ENTER to continue without updating the password.

The rest of the prompts can be answered yes. You will be asked about removing the anonymous MySQL user, disallowing remote root login, removing the test database, and reloading privilege tables to ensure the previous changes take effect properly. These are all a good idea. Type y and hit ENTER for each.

The script will exit after all the prompts are answered. Now our MySQL installation is reasonably secured. In the next step, we’ll configure MySQL to allow access from remote connections.

Step 2 — Configuring MySQL to Listen for Remote Connections

Now that you have your database up and running, we need to change some configuration values to allow connections from other computers.

Open up the mysqld configuration file with root privileges in your editor:

• • sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

This file is divided into sections denoted by words in brackets ([ and ]). Find the section labeled mysqld:

. . .
[mysqld]

Within this section you’ll need to find a parameter called bind-address. This tells the database software which network address to listen for connections on.

Currently, MySQL is configured to only look for local connections. We need to change that to reference an external IP address that your server can be reached at.

If both of your servers are in a datacenter with private networking capabilities, use your server’s private network IP. Otherwise, you can use the public IP address:

[mysqld]
. . .
bind-address = db_server_ip

Since we’ll be connecting to the database over the internet, we will require encrypted connections to keep our data secure. If you don’t encrypt your MySQL connection, anybody on the network could sniff sensitive information between your web and database server. Add the following line after the bind-address line we just updated:

. . .
require_secure_transport = on

Save and close the file when you are finished.

For SSL connections to work, we need to create some keys and certificates. MySQL comes with a command that automatically sets up everything we need:

• • sudo mysql_ssl_rsa_setup –uid=mysql</li

This will create the necessary files and make them readable by the MySQL server (--uid=mysql).

To force MySQL to update its configuration and read in the new SSL information, restart the database:

• • sudo systemctl restart mysql

To confirm that the server is now listening on the external interface, check with netstat:

• • sudo netstat -plunt | grep mysqld

tcp        0      0 db_server_ip:3306     0.0.0.0:*               LISTEN      27328/mysqld

netstat prints statistics about our server’s networking system. This output shows us that a process called mysqld is attached to the db_server_ip at port 3306, the standard MySQL port.

Now open up that port on the firewall to allow traffic through:

• • sudo ufw allow mysql

Next we’ll set up the users and database we’ll need to access the server remotely.

Step 3 — Setting Up a WordPress Database and Remote Credentials

Even though MySQL itself is now listening on an external IP address, there are currently no remote-enabled users or databases configured. Let’s create a database for WordPress, and a user that can access it.

Begin by connecting to MySQL using the MySQL root account:

• • mysql -u root -p

You will be asked for your MySQL root password and then you’ll be given a new mysql> prompt.

Now we can create the database that WordPress will use. We will just call this wordpress so that we can easily identify it later:

• • CREATE DATABASE wordpress;

Note: All SQL statements must end in a semicolon (;). If you hit ENTER on a MySQL command and only see a new line with a -> prompt, you likely forgot the semicolon. Just type it on the new line and press ENTER again to continue.

Now that we have a database, we need to create our user. One twist in creating our user is that we need to define two different profiles based on where the user is connecting from. We will create a local-only user, and a remote user tied to our web server’s IP address.

First, we create our local user wordpressuser and make this account only match local connection attempts by using localhost in the declaration:

• • CREATE USER ‘wordpressuser’@’localhost’ IDENTIFIED BY ‘password‘;

Let’s go ahead and grant this account full access to our database:

• • GRANT ALL PRIVILEGES ON wordpress.* TO ‘wordpressuser’@’localhost’;

This user can now do any operation on the database for WordPress, but this account cannot be used remotely, as it only matches connections from the local machine.

Now create a companion account that will match connections exclusively from our web server. For this, you’ll need your web server’s IP address. We could name this account anything, but for a more consistent experience, we’re going to use the exact same username as we did above, with only the host portion modified.

Keep in mind that you must use an IP address that utilizes the same network that you configured in your mysqld.cnf file. This means that if you used a private networking IP, you’ll want to create the rule below to use the private IP of your web server. If you configured MySQL to use the public internet, you should match that with the web server’s public IP address.

• • CREATE USER ‘wordpressuser’@’web-server_ip‘ IDENTIFIED BY ‘password‘;

Now that we have our remote account, we can give it the same privileges as the local user:

• • GRANT ALL PRIVILEGES ON wordpress.* TO ‘wordpressuser’@’web_server_ip‘;

Flush the privileges to write them to disk and begin using them:

• • FLUSH PRIVILEGES;

Then exit the MySQL prompt by typing:

• • exit

Now that we’ve set up a new database and remote-enabled user, let’s test the database and connections.

Step 4 — Testing Remote and Local Connections

Before we continue, it’s best to verify that you can connect to your database from both the local machine and from your web server using the wordpressuser accounts.

First, test the local connection from your database machine by attempting to log in with our new account:

mysql -u wordpressuser -p

Type in the password that you set up for this account when prompted.

If you are given a MySQL prompt, then the local connection was successful. You can exit out again by typing:

• • exit

Log into your web server to test remote connections.

On your web server, you’ll need to install some client tools for MySQL in order to access the remote database. Update your local package cache, and then install the client utilities:

• • sudo apt-get update

• • sudo apt-get install mysql-client

Now, we can connect to our database server using the following syntax:

• • mysql -u wordpressuser -h db_server_ip -p

Again, you must make sure that you are using the correct IP address for the database server. If you configured MySQL to listen on the private network, enter your database’s private network IP, otherwise enter your database server’s public IP address.

You will be asked for the password for your wordpressuser account, and if all goes well you will be given a MySQL prompt. We can verify that the connection is using SSL with the following command:

• • status

--------------
mysql  Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using  EditLine wrapper

Connection id:      52
Current database:
Current user:       [email protected]
SSL:         Cipher in use is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.18-0ubuntu0.16.04.1 (Ubuntu)
Protocol version:   10
Connection:     203.0.113.111 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         3 hours 43 min 40 sec

Threads: 1  Questions: 1858  Slow queries: 0  Opens: 276  Flush tables: 1  Open tables: 184  Queries per second avg: 0.138
--------------

The SSL: line will indicate if an SSL cipher is in use. You can go ahead and exit out of the prompt now, as you’ve verified that you can connect remotely:

• • exit

For an additional check, you can try doing the same thing from a third server to make sure that this other server is notgranted access. You have verified local access and access from the web server, but you have not verified that other connections will be refused.

Go ahead and try that same procedure on a server that you did not configure a specific user account for. You may have to install the client utilities as you did above:

• • mysql -u wordpressuser -h db_server_ip -p

This should not complete successfully. It should throw back an error that looks something like:

ERROR 1130 (HY000): Host '203.0.113.12' is not allowed to connect to this MySQL server

This is what we expect and what we want.