Remote database – create your own vps data and connect to wordpress

Remote database – remote data connection. Split the wordpress code and its database data into two separate servers.

I will narrate how to create and connect vps containing data (Mysql remote database) and vps containing code (wordpress). The article is implemented on VPS of Vultr, ubuntu operating system. With other operating systems there is no difference in the steps it takes. It is important to remember which step is to do on the server that contains the code and which step is to do on the server containing the data. Stupid fool like me also took half a day to find out.

The configuration information is as follows:

VPS code – contains php code

  • Supplier: vultr
  • Operating System: Ubuntu 18.4
  • Webinoly
  • Private network IP

VPS data – contains sql data (remote data)

  • Supplier: vultr
  • OS: Ubuntu 18.4
  • Mariadb 10.4

SSH tool: MobaXterm . Because I’m not familiar with things like vi, nano … so it’s convenient to use it because both ssh and visual sftp are available.

See: How to install Webinoly + WordPress on vps

There are many instructions on the installation on the Internet. The difference here is that VPS data only installs the operating system and SQL server.

Enable Private Network on Vultr

Suppose the private ip (Private Network) of the vps code: 10.40.96.1

Suppose the private ip (Private Network) of the vps data: 10.40.96.2

Minh distinguishes 2vps as vps code and vps data for easy understanding. If not, go to settings in Vultr’s control panel to turn it on.

Vehicle configuration information Private Network

For example, to access the VPS CODE dashboard , In the settings, Under Public Network there is the line: “Need assistance? View our  networking configuration  tips and examples. ”

You click on the networking configuration to see complete configuration information by your server.

In the pages of Vultr configuration guide has just opened, moved to Ubuntu 17:10, Ubuntu 18.xx, 19.xx Ubuntu . Here you pay attention to the line:

Populate the /etc/netplan/10-ens7.yaml file with the following text.

That is the file location that will contain the Private Network configuration information. Below is the content of the file created according to your actual vps information, just copy it.

Add a Private Network configuration to vps

Next, go to your vps directory /etc/netplan/. Here, if you do not have the file 10-ens7.yaml, you will have to create it.

Now copy the contents of the file 10-ens7.yamlin the vultr’s instruction page to the 10-ens7.yaml file on the server. It has content like:

network:
  version: 2
  renderer: networkd
  ethernets:
    ens7:
      match:
        macaddress: 5c:00:xv:bf:21:07
      mtu: 1450
      dhcp4: no
      addresses: [10.40.96.1/20]

Note: The above is my information, don’t copy it, because maybe your mac and ip addresses will be different. Make a copy on your vultr configuration page.

Once saved, enter the following command to update:

netplan apply

To check, enter ip addr

With VPS data , do the same.

On VPS Data

Configuring Mariadb (10.4)

Fix file 50-server.cnf for maria 10.4. with Mysql or other versions it could be my.cnf

/etc/mysql/mariadb.conf.d/50-server.cnf

Find the line

bind-address = 127.0.0.1

Replace with the private ip address of your vps data . The purpose is to limit the IP that can access your data.

bind-address = 10.40.96.2

Once saved, it needs to be restarted

sudo service mysql restart

Or

systemctl restart mariadb.service

Check

sudo netstat -anp | grep 3306

The result should look like this

tcp        0      0 10.40.96.2:3306         0.0.0.0:*               LISTEN      644/mysqld

Open port 3306

Since Vultr does not lock port 3306, but Ubuntu 18.4 install via vultr also disables the fwu firewall, so port 3306 is already open.

Allow Vps Code to connect to vps data

Create database

ssh into vps data, Login to mysql

mysql -u root -p

Tip: because ssh with root and pass of vultr, so I use the following command to quickly

mysql -v

Create database, for example create data named: dbwordpress

CREATE DATABASE dbwordpress;

Create user and password accessible data from vps code. Example of creating user named uvpscode accessible from vps code with ip (own): 10.40.96.1 with password of password

CREATE USER 'uvpscode'@'10.40.96.1' IDENTIFIED BY 'password';

Authorizing the newly created user has full authority with dbwordpress data

GRANT ALL PRIVILEGES ON dbwordpress.* TO 'uvpscode'@'10.40.96.1' WITH GRANT OPTION;
FLUSH PRIVILEGES;

type exit to exit the mysql command line.

You need to save the above information for replacement in the wp-config .php file on the vps code.

In total, we have the following information:

  • dbwordpress: data that you will contain wp data later
  • uvpscode / password: username / password of dbwordpress
  • 10.40.96.1: Only vps with this ip can access the data

On VPS Code

Ssh into the vps code has its own ip 10.40.96.1 as mentioned above.

Enter the following command to test the connection:

mysql -h 10.40.96.2 -u uvpscode -p

You will enter the password of the new uvpscode user created above.

Successful connection will have results like this:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2677
Server version: 10.4.12-MariaDB-1:10.4.12+maria~bionic mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

If you type:

show databases;

Then dbwordpress data will be displayed

So we have successfully connected the Remote database . The next lesson will be to convert data data from vps code to vps data to complete the final step.

DATVNN.COM
Logo