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.
