How To Set Up a Remote Database to Optimize Site Performance with MySQL on Ubuntu

Introduction

As your application or website grows, there may come 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, you will configure a remote MySQL database server that your web application can connect to. WordPress is used as an example in order to have something to work with, but the technique is widely applicable to any application backed by MySQL.

Prerequisites

Before beginning this tutorial, you will need:

• Two Ubuntu servers. Each should have a non-root user with sudo privileges and UFW firewall enabled, as described in our Initial Server Setup with Ubuntu tutorial. One of these servers will host your MySQL backend, and will be referred to as the database server throughout this guide. The other will connect to your database server remotely and act as your web server; likewise, it will be referred to as the web server over the course of this guide.
• On your database server: MySQL installed. Follow How To Install MySQL on Ubuntu to set this up.
• On your web server: Nginx and PHP installed. Our tutorial How To Install Linux, Nginx, MySQL, PHP (LEMP stack) in Ubuntu will guide you through the process, but note that you should skip Step 2 of this tutorial, which focuses on installing MySQL, as you will install MySQL on your database server.
  • Optionally (but strongly recommended), TLS/SSL certificates from Let’s Encrypt installed on your web server. You’ll need to purchase a domain name and have DNS records set up for your server, but the certificates themselves are free. Our guide How To Secure Nginx with Let’s Encrypt on Ubuntu will show you how to obtain these certificates.

Step 1 — Configuring MySQL to Listen for Remote Connections on the Database Server

Having one’s data stored on a separate server is a good way to expand gracefully after hitting the performance ceiling of a one-machine configuration. It also provides the basic structure necessary to load balance and expand your infrastructure even more at a later time. After installing MySQL by following the prerequisite tutorial, you’ll need to change some configuration values to allow connections from other computers.

Most of the MySQL server’s configuration changes can be made in the mysqld.cnf file, which is stored in the /etc/mysql/mysql.conf.d/ directory by default. Open up this file on your database server with root privileges in your preferred editor. This example uses nano:

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

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

. . .
[mysqld]
. . .

Within this section, look for a parameter called bind-address. This tells the database software which network address to listen for connections on.

By default, this is set to 127.0.0.1, meaning that MySQL is configured to only look for local connections. You need to change this to reference an external IP address where your server can be reached.

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

[mysqld]
. . .
bind-address = db_server_ip

Because you’ll connect to your database over the internet, it’s recommended that you require encrypted connections to keep your data secure. If you don’t encrypt your MySQL connection, anybody on the network could sniff sensitive information between your web and database servers. To encrypt MySQL connections, add the following line after the bind-address line you just updated:

[mysqld]
. . .
require_secure_transport = on
. . .

Save and close the file when you are finished. If you’re using nano, do this by pressing CTRL + X, Y, and then ENTER.

For SSL connections to work, you will need to create some keys and certificates. MySQL comes with a command that will automatically set these up. Run the following command, which creates the necessary files. It also makes them readable by the MySQL server by specifying the UID of the mysql user:

sudo mysql_ssl_rsa_setup --uid=mysql

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

sudo systemctl restart mysql

To confirm that the server is now listening on the external interface, run the following ss command:

sudo ss -plunt | grep mysqld

Output
tcp     LISTEN   0        70             127.0.0.1:33060          0.0.0.0:*      users:(("mysqld",pid=4053,fd=22))                                              
tcp     LISTEN   0        151        db_server_ip:3306           0.0.0.0:*      users:(("mysqld",pid=4053,fd=24))

ss prints statistics about your server’s networking system. This output shows that a process called mysqld is attached to the db_server_ip at port 3306, the standard MySQL port, confirming that the server is listening on the appropriate interface.

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

sudo ufw allow mysql

Those are all the configuration changes you need to make to MySQL. Next, you will set up a database and some user profiles, one of which you will use to access the server remotely.

Step 2 — Setting Up a WordPress Database and Remote Credentials on the Database Server

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

Begin by connecting to MySQL as the root MySQL user:

sudo mysql

mysql -u root -p

From the MySQL prompt, create a database that WordPress will use. It may be helpful to give this database a recognizable name so that you can easily identify it later on. Here, it’s named wordpress:

CREATE DATABASE wordpress DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Now that you’ve created your database, you next need to create a pair of users. Create a local-only user as well as a remote user tied to the web server’s IP address.

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

CREATE USER 'local_db_user'@'localhost' IDENTIFIED BY 'password';

Then grant this account full access to the wordpress database:

GRANT ALL PRIVILEGES ON wordpress.* TO 'local_db_user'@'localhost';

This user can now perform any operation on the database for WordPress, but this account cannot be used remotely, as it only matches connections from the local machine. With this in mind, create a companion account that will match connections exclusively from your web server. For this, you’ll need your web server’s IP address.

Please note 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 specified a private networking IP in the mysqld.cnf file, you’ll need to include the private IP of your web server in the following two commands. If you configured MySQL to use the public internet, you should match that with the web server’s public IP address.

CREATE USER 'remote_user'@'web_server_ip' IDENTIFIED BY 'password';

After creating your remote account, give it the same privileges as your local user:

GRANT ALL PRIVILEGES ON wordpress.* TO 'remote_user'@'web_server_ip';

Lastly, flush the privileges so MySQL knows to begin using them:

FLUSH PRIVILEGES;

Then exit the MySQL prompt by typing:

exit

Now that you’ve set up a new database and a remote-enabled user, you can move on to testing whether you’re able to connect to the database from your web server.

Step 3 — Testing Remote and Local Connections

Before continuing, it’s best to verify that you can connect to your database from both the local machine — your database server — and from your web server.

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

mysql -u local_db_user -p

When prompted, enter the password that you set up for this account.

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

exit

Next, log into your web server to test remote connections:

ssh remote_user@web_server_ip

You’ll need to install some client tools for MySQL on your web server in order to access the remote database. First, update your local package cache if you haven’t done so recently:

sudo apt update

Then install the MySQL client utilities:

sudo apt install mysql-client

Following this, connect to your database server using the following syntax:

mysql -u remote_user -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 remote_user account. After entering it, and if everything is working as expected, you will be taken to the MySQL prompt. Verify that the connection is using SSL with the following command:

status

If the connection is indeed using SSL, the SSL: line will indicate this, as shown here:

Output
--------------
mysql  Ver 8.0.29-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

Connection id:          14
Current database:
Current user:           remote_user@web_server_ip
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.29-0ubuntu0.20.04.2 (Ubuntu)
Protocol version:       10
Connection:             db_server_ip via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Binary data as:         Hexadecimal
Uptime:                 1 hour 8 min 28 sec

Threads: 2  Questions: 69  Slow queries: 0  Opens: 206  Flush tables: 3  Open tables: 125  Queries per second avg: 0.016
--------------

After verifying that you can connect remotely, exit the prompt:

exit

You’ve verified local access and access from the web server, but you have not verified that other connections will be refused. For an additional check, try doing the same thing from a third server for which you did not configure a specific user account in order to make sure that this other server is not granted access.

Note that before running the following command to attempt the connection, you may have to install the MySQL client utilities as you did previously:

mysql -u wordpressuser -h db_server_ip -p

This should not complete successfully, and should throw back an error that is similar to this:

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

This is expected, since you haven’t created a MySQL user that’s allowed to connect from this server, and also desired, since you want to be sure that your database server will deny unauthorized users access to your MySQL server.

After successfully testing your remote connection, you can proceed to installing WordPress on your web server.

Step 4 — Preparing the Web Server for Wordpress Installation

If you followed the prerequisite tutorials, you should have both Nginx and PHP installed. WordPress, and many of its plugins, leverage many additional PHP extensions. Before installing Wordpress on your web server, prep your server by installing some of these PHP extensions for use with Wordpress:

sudo apt install php-curl php-gd php-intl php-mbstring php-soap php-xml php-xmlrpc php-zip

When you are finished installing the extensions, restart the PHP-FPM process so that the running PHP processor can leverage the newly installed features:

sudo systemctl restart php7.4-fpm

Next, make a few adjustments to your Nginx server block files. Based on the prerequisite tutorials, you should have a configuration file for your site in the /etc/nginx/sites-available/ directory. It should also be configured to respond to your server’s domain name or IP address and protected by a TLS/SSL certificate. /etc/nginx/sites-available/your_domain is used an example directory here. Substitute the path to your configuration file where appropriate.

Additionally, /var/www/your_domain will be used as the root directory in this example WordPress install. Again, use the document root specified in your own configuration.

Open your site’s server block file with your preferred editor. This example uses nano:

sudo nano /etc/nginx/sites-available/your_domain

Within this server block, add a few location blocks. Start by creating exact-matching location blocks for requests to /favicon.ico and /robots.txt, both of which you do not want to log requests for.

You’ll do this by using a regular expression location to match any requests for static files, turn off the logging for these requests, and will mark them as highly cacheable since these are typically expensive resources to serve. You can adjust this static files list to contain any other file extensions your site may use:

server {
    server_name your_domain www.your_domain;
    root /var/www/your_domain;

    index index.html index.htm index.php;

    location = /favicon.ico { log_not_found off; access_log off; }
    location = /robots.txt { log_not_found off; access_log off; allow all; }
    location ~* \.(css|gif|ico|jpeg|jpg|js|png)$ {
        expires max;
        log_not_found off;
    }
    . . .
}

Inside of the existing location / block, you can adjust the try_files list. First, comment out the default setting by prepending the first line with a pound sign (#). Then add the following lines:

server {
    . . .
    location / {
        #try_files $uri $uri/ =404;
        try_files $uri $uri/ /index.php$is_args$args;
    }
    . . .
}

With this new line in your configuration, instead of returning a 404 error as the default option, control is passed to the index.php file with the request arguments.

Altogether, the top portion of your file should be similar to this:

server {
    server_name your_domain www.your_domain;
    root /var/www/your_domain;

    index index.html index.htm index.php;

    location = /favicon.ico { log_not_found off; access_log off; }
    location = /robots.txt { log_not_found off; access_log off; allow all; }
    location ~* \.(css|gif|ico|jpeg|jpg|js|png)$ {
        expires max;
        log_not_found off;
    }

    location / {
       # try_files $uri $uri/ =404;
        try_files $uri $uri/ /index.php$is_args$args;
    }

  ...
}

When you are finished, save and close the file.

Check your configuration for syntax errors by typing:

sudo nginx -t

If there are no errors reported, reload Nginx by typing:

sudo systemctl reload nginx

After prepping your web server, you are now ready to install Wordpress.

Step 5 – Installing WordPress on the Web Server

To demonstrate the capabilities of your new remote-capable MySQL server, you will go through the process of installing and configuring WordPress — the popular content management system — on your web server. With your server software configured, you can download Wordpress. For security reasons, it is always recommended to get the latest version of WordPress directly from the project’s website.

First, change into a writable directory (a temporary one like /tmp is recommended):

cd /tmp

This changes your working directory to the temporary folder. Then, enter the following command to download the latest version of WordPress as a compressed file:

curl -LO https://wordpress.org/latest.tar.gz

Extract the compressed file to create the WordPress directory structure:

tar xzvf latest.tar.gz

You will be moving these files into your document root momentarily, but before you do, copy over the sample configuration file to the filename that WordPress reads:

cp /tmp/wordpress/wp-config-sample.php /tmp/wordpress/wp-config.php

Copy the entire contents of the directory into your document root. The -a flag will ensure your permissions are maintained, and a dot at the end of your source directory to indicate that everything within the directory should be copied (including hidden files):

sudo cp -a /tmp/wordpress/. /var/www/your_domain

With your files in place, assign ownership to the www-data user and group. This is the user and group that Nginx runs as, and Nginx will need to be able to read and write WordPress files in order to serve the website and perform automatic updates:

sudo chown -R www-data:www-data /var/www/your_domain

Your WordPress files are now in the server’s document root and have the correct ownership. Now, you’re ready to configure Wordpress.

Step 6 — Setting up the WordPress Configuration File

When you open the Wordpress configuration file, you’ll start by adjusting some secret keys to provide some security for your installation. WordPress provides a secure generator for these values so that you don’t have to come up with values on your own. These are only used internally, so it won’t hurt usability to have complex, secure values here.

To grab secure values from the WordPress secret key generator, type:

curl -s https://api.wordpress.org/secret-key/1.1/salt/

You will receive unique values that resemble output similar to the following:

Output
define('AUTH_KEY',         'L4|2Yh(giOtMLHg3#] DO NOT COPY THESE VALUES %G00o|te^5YG@)');
define('SECURE_AUTH_KEY',  'DCs-k+MwB90/-E(=!/ DO NOT COPY THESE VALUES +WBzDq:7U[#Wn9');
define('LOGGED_IN_KEY',    '*0kP!|VS.K=;#fPMlO DO NOT COPY THESE VALUES +&[%8xF*,18c @');
define('NONCE_KEY',        'fmFPF?UJi&(j-{8=$- DO NOT COPY THESE VALUES CCZ?Q+_~1ZU~;G');
define('AUTH_SALT',        '@qA7f}2utTEFNdnbEa DO NOT COPY THESE VALUES t}Vw+8=K%20s=a');
define('SECURE_AUTH_SALT', '%BW6s+d:7K?-`C%zw4 DO NOT COPY THESE VALUES 70U}PO1ejW+7|8');
define('LOGGED_IN_SALT',   '-l>F:-dbcWof%4kKmj DO NOT COPY THESE VALUES 8Ypslin3~d|wLD');
define('NONCE_SALT',       '4J(<`4&&F (WiK9K#] DO NOT COPY THESE VALUES ^ZikS`es#Fo:V6');

These are configuration lines that you can paste directly in your configuration file to set secure keys. Copy the output you received now.

Open the WordPress configuration file:

sudo nano /var/www/your_domain/wp-config.php

Find the section that contains the example values for those settings:

. . .
define('AUTH_KEY',         'put your unique phrase here');
define('SECURE_AUTH_KEY',  'put your unique phrase here');
define('LOGGED_IN_KEY',    'put your unique phrase here');
define('NONCE_KEY',        'put your unique phrase here');
define('AUTH_SALT',        'put your unique phrase here');
define('SECURE_AUTH_SALT', 'put your unique phrase here');
define('LOGGED_IN_SALT',   'put your unique phrase here');
define('NONCE_SALT',       'put your unique phrase here');
. . .

Delete those lines and paste in the values you copied from the command line:

. . .
define('AUTH_KEY',         'VALUES COPIED FROM THE COMMAND LINE');
define('SECURE_AUTH_KEY',  'VALUES COPIED FROM THE COMMAND LINE');
define('LOGGED_IN_KEY',    'VALUES COPIED FROM THE COMMAND LINE');
define('NONCE_KEY',        'VALUES COPIED FROM THE COMMAND LINE');
define('AUTH_SALT',        'VALUES COPIED FROM THE COMMAND LINE');
define('SECURE_AUTH_SALT', 'VALUES COPIED FROM THE COMMAND LINE');
define('LOGGED_IN_SALT',   'VALUES COPIED FROM THE COMMAND LINE');
define('NONCE_SALT',       'VALUES COPIED FROM THE COMMAND LINE');
. . .

Next, modify some of the database connection settings at the beginning of the file. You’ll have to adjust the database name, the database user, and the associated password that was configured within MySQL.

Enter the connection information for your remote database. Remember to use the same IP address and credentials you used in your remote database test earlier:

. . .
/** The name of the database for WordPress */
define('DB_NAME', 'wordpress');

/** MySQL database username */
define('DB_USER', 'remote_user');

/** MySQL database password */
define('DB_PASSWORD', 'remote_user_password');

/** MySQL hostname */
define('DB_HOST', 'db_server_ip');
. . .

And finally, following the define( 'DB_COLLATE', '' ); line, add this highlighted line which tells WordPress to use an SSL connection to your MySQL database:

...
define( 'DB_COLLATE', '' );

define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);
...

Save and close the file.

With your configuration set up, WordPress is installed and you’re ready to run through its web-based setup routine.

Step 7 — Setting Up Wordpress Through the Web Interface

WordPress has a web-based setup process. As you go through it, it will ask a few questions and install all the tables it needs in your database. Here, you will perform the initial steps of setting up WordPress, which you can use as a starting point for building your own custom website that uses a remote database backend.

Navigate to the domain name (or public IP address) associated with your web server along with a trailing /wp-admin:

http://your_domain.com/wp-admin

You will be prompted to select the language you would like to use:

Next, you will come to the main setup page.

Select a name for your WordPress site and choose a username. It is recommended to choose something unique and avoid common usernames like “admin” for security purposes. A strong password is generated automatically. Save this password or select an alternative strong password.

Enter your email address and select whether you want to discourage search engines from indexing your site:

Once you have submitted your information, you will need to log into the WordPress admin interface using the account you just created. You will then be taken to a dashboard where you can customize your new WordPress site.

Conclusion

By following this tutorial, you’ve set up a MySQL database to accept SSL-protected connections from a remote Wordpress installation. The commands and techniques used in this guide are applicable to any web application written in any programming language, but the specific implementation details will differ. Refer to your application or language’s database documentation for more information.