How to Use a Remote Database with WordOps

Introduction

WordOps is a command-line WordPress management tool for Linux servers with Nginx, MySQL, and PHP (LEMP). WordOps usually configures WordPress on a single server, but you can also use it to install the website and database on different servers when you need to scale your WordPress site. This article explains how to install WordPress on one server and MySQL on a different server. The servers communicate over a private Vultr VPC. You could also use this same approach to scale further with Vultr Load Balancers.

1. Create Vultr Instances

Deploy two Ubuntu 20.04 cloud servers in the same location.
- Give them hostnames web-server and db-server.
- Connect them to the same VPC.
- Create a sudo user on both servers.
- Update the servers.
Make note of the private IP addresses for each server. This article assumes those are:
- web-server: 10.0.0.1
- db-server: 10.0.0.2

2. Install MySQL on db-server

Connect to db-server as your sudo user through SSH.
Install WordOps.
```
$ wget -qO wo wops.cc && sudo bash wo
```
Install MySQL.
```
$ wo stack install --mysql
```
Verify MySQL is running.
```
$ service mysql status
```

3. Configure db-server

Configure the UFW firewall with the commands below. Replace 10.0.0.1 with the web-server's IP address. This allows SSH from anywhere and access to MySQL only from web-server.

$ sudo ufw default allow outgoing
$ sudo ufw default deny incoming
$ sudo ufw allow 22 comment 'SSH'
$ sudo ufw allow from 10.0.0.1 to any port 3306 comment 'MySQL'
$ sudo ufw enable

Secure db-server with fail2ban.
```
$ wo stack install --fail2ban
```
Configure MySQL to allow remote access by editing /etc/mysql/my.cnf.
```
$ sudo nano /etc/mysql/my.cnf
```

Add the following to the end of the file:

[mysqld]
bind-address = 0.0.0.0

When finished, /etc/mysql/my.cnf should look like this:

[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

[mysqld]
bind-address = 0.0.0.0

Restart the MySQL service.
```
$ service mysql restart
```

Verify the port is open.

$ lsof -i -P -n | grep 3306

The result should look like this:

mariadbd  35985           mysql   16u  IPv4  92217      0t0  TCP *:3306 (LISTEN)

View my.cnf to find the database password.

$ cat /etc/mysql/conf.d/my.cnf

The result should look like this:

[client]
user = root
password = xxxx_example_password_xxxx
socket = /run/mysqld/mysqld.sock

Grant privileges to access the database from web-server.

Replace the IP 10.0.0.1 with your web server's IP address and xxxx_example_password_xxxx with the password from the previous step.
```
$ mysql -e "grant all privileges on *.* to 'root'@'10.0.0.1' IDENTIFIED BY 'xxxx_example_password_xxxx' with grant option;"
$ mysql -u root -p -e "flush privileges;"
```

4. Configure web-server with WordOps

Connect to web-server as your sudo user through SSH.
Install WordOps.
```
$ wget -qO wo wops.cc && sudo bash wo
```
Install the WordOps main stacks.
```
$ wo stack install        
```
The MySQL server isn't required on web-server, so remove it.
```
$ wo stack remove --mysql
```
Install the MySQL client on web-server.
```
$ wo stack install --mysqlclient
```
Verify you can connect to the MySQL database. Replace 10.0.0.2 with db-server's IP address.
```
$ mysql -uroot -h10.0.0.2 -p
```
Create a new /etc/mysql/conf.d/my.cnf file.
```
$ sudo nano /etc/mysql/conf.d/my.cnf
```
Paste the following content.
- Replace 10.0.0.2 with db-server's IP address.
- Replace xxxx_example_password_xxxx with the MySQL password from the previous section.
```
[client]
host = 10.0.0.2
user = root
password = xxxx_example_password_xxxx
```
Save and close the file.
Edit the /etc/wo/wo.conf file.
```
$ sudo nano /etc/wo/wo.conf
```
Find the following line:
```
grant-host = localhost
```
and replace it with:
```
grant-host = %
```
Save and close the file.

5. Create a WordPress Website

Up to this point, you've only worked with the private IP addresses of the servers. In fact, db-server only needs to be available to web-server, and can be blocked entirely from the public Internet. Obviously, web-server does need to be accessible from the Internet to be useful. For the next step, locate the public IP address of web-server in your Vultr customer portal.

Create an A Record for web-server at your domain registrar or DNS host. For example, assign 192.0.2.123 to the A record for web-server.example.com. This is also known as a Fully-Qualified Domain Name (FQDN).

Create firewall rules for web-server. This allows access to SSH, HTTP, HTTPS, NTP, and the WordOps Dashboard port.

$ sudo ufw default allow outgoing
$ sudo ufw default deny incoming
$ sudo ufw allow 22 comment 'SSH'
$ sudo ufw allow http comment 'HTTP'
$ sudo ufw allow https comment 'HTTPS'
$ sudo ufw allow 123 comment 'NTP'
$ sudo ufw allow 22222 comment 'WordOps'
$ sudo ufw enable

Create a WordPress website with Nginx fastcgi_cache and secure the site with Let's Encrypt SSL certificate. Replace web-server.example.com with your FQDN. Save the admin credentials reported by WordOps.
```
$ wo site create web-server.example.com --wpfc -le
```

Your website is now ready to use.

Access your website at https://example.com
Access the WordOps Dashboard at https://example.com:22222

More Information

Want to contribute?

You could earn up to $600 by adding new articles.

Submit your article Request an article

Article