This is how I set up a dedicated Debian subdomain (VM), Installed MySQL 14 and connected to it from a WordPress installation on a different VM
Advertisement:
Aside
If you have not read my previous posts I have now moved my blog to the awesome UpCloud host (signup using this link to get $25 free UpCloud VM credit). I compared Digital Ocean, Vultr and UpCloud Disk IO here and UpCloud came out on top by a long way (read the blog post here). Here is my blog post on moving fearby.com from Vultr to UpCloud.
Buy a domain name here
Now on with the post.
Fearby.com
I will be honest, fearby.com is my play server where I can code, learn about InfoSec and share (It’s also my stroke rehab blog).
There is no faster way to learn than actually doing. The problem is my “doing” usually breaks the live site from time to time (sorry).
I really need to set up a testing environment (DEV-TEST-LIVE or GREEN-BLUE) server(s). GREEN-BLUE has advantages as I can always have a hot spare ready. All I need to do is toggle DNS and I can set the GREEN or BLUE server as the live server.
But first I need to separate my database from my current fearby.com server and setup up a new web server. Having a Green and Blue server that uses one database server will help with near real-time production website switches.
Dedicated Database Server
I read the following ( Should MySQL and Web Server share the same server? ) at Percona Database Performance Blog. Having a separate database server should not negatively impact performance (It may even help improve speeds).
Deploy a Debian VM (not Ubuntu)
I decided to set up a Debian server instead of Ubuntu (mostly because of the good focus on stability and focus on security within Debian).
I logged into the UpCloud dashboard on my mobile phone and deployed a Debian server in 5 mins. I will be using my existing how to setup Ubuntu on UpCloud guide (even though this is Debian).
TIP: Sign up to UpCloud using this link to get $25 free UpCloud VM credit.
Deploy a Debian server setup steps:
- Login to UpCloud and go to Create server.
- Name your Server (use a fully qualified domain name)
- Add a description.
- Choose your data centre (Chicago for me)
- Choose the server specs (1x CPU, 50GB Disk, 2GB Memory, 2TB Traffic for me)
- Name the Primary disk
- Choose an operating system (Debian for me)
- Select an SSH Key
- Choose misc settings
- Click Deploy server
After 5 mins your server should be deployed.
Advertisement:
After Deploy
Setup DNS
Login to your DNS provider and create DNS records to the new IP’s (IPv4 and IPv6) provided by UpCloud. It took DNS 12 hours to replicate to my in Australia.
Setup a Firewall (at UpCloud)
I would recommend you set up a firewall at UpCloud as soon as possible (don’t forget to add the recommended UpCloud DNS IP’s and any whitelisted IP’s your firewall).
Block everything and only allow
- Port 22: Allow known IP(s) of your ISP or VPN.
- Port 53: Allow known UpCloud DNS servers
- Port 80 (ALL)
- Port 443 (ALL)
- Port 3306 Allow your WordPress site and known IP(s) of your ISP or VPN.
Read my post on setting up a whitelisted IP on an UpCloud VM… as it is a good idea.
UpCloud thankfully has a copy firewall feature that is very handy.
After I set up the firewall I SSH’ed into my server (I use vSSH on OSX buy you could use PUTTY).
I updated the Debian system with the following command
1 | sudo apt update |
Get the MySQL Package
Visit http://repo.mysql.com/ and get the URL of the latest apt-config repo deb file (e.g “mysql-apt-config_0.8.9-1_all.deb”). Make a temp folder.
1 2 | mkdir /temp cd /temp |
Download the MySQL deb Package
1 | wget http://repo.mysql.com/mysql-apt-config_0.8.9-1_all.deb |
Install the package
1 | sudo dpkg -i mysql-apt-config_0.8.9-1_all.deb |
Update the system again
1 | sudo apt update |
Install MySQL on Debian
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 | sudo apt install mysql-server Reading package lists... Done Building dependency tree Reading state information... Done The following additional packages will be installed: libaio1 libatomic1 libmecab2 mysql-client mysql-common mysql-community-client mysql-community-server psmisc The following NEW packages will be installed: libaio1 libatomic1 libmecab2 mysql-client mysql-common mysql-community-client mysql-community-server mysql-server psmisc 0 upgraded, 9 newly installed, 0 to remove and 1 not upgraded. Need to get 37.1 MB of archives. After this operation, 256 MB of additional disk space will be used. Do you want to continue? [Y/n] y Get:1 http://repo.mysql.com/apt/debian stretch/mysql-5.7 amd64 mysql-community-client amd64 5.7.22-1debian9 [8886 kB] Get:2 http://deb.debian.org/debian stretch/main amd64 mysql-common all 5.8+1.0.2 [5608 B] Get:3 http://deb.debian.org/debian stretch/main amd64 libaio1 amd64 0.3.110-3 [9412 B] Get:4 http://deb.debian.org/debian stretch/main amd64 libatomic1 amd64 6.3.0-18+deb9u1 [8966 B] Get:5 http://deb.debian.org/debian stretch/main amd64 psmisc amd64 22.21-2.1+b2 [123 kB] Get:6 http://deb.debian.org/debian stretch/main amd64 libmecab2 amd64 0.996-3.1 [256 kB] Get:7 http://repo.mysql.com/apt/debian stretch/mysql-5.7 amd64 mysql-client amd64 5.7.22-1debian9 [12.4 kB] Get:8 http://repo.mysql.com/apt/debian stretch/mysql-5.7 amd64 mysql-community-server amd64 5.7.22-1debian9 [27.8 MB] Get:9 http://repo.mysql.com/apt/debian stretch/mysql-5.7 amd64 mysql-server amd64 5.7.22-1debian9 [12.4 kB] Fetched 37.1 MB in 12s (3023 kB/s) Preconfiguring packages ... Selecting previously unselected package mysql-common. (Reading database ... 34750 files and directories currently installed.) Preparing to unpack .../0-mysql-common_5.8+1.0.2_all.deb ... Unpacking mysql-common (5.8+1.0.2) ... Selecting previously unselected package libaio1:amd64. Preparing to unpack .../1-libaio1_0.3.110-3_amd64.deb ... Unpacking libaio1:amd64 (0.3.110-3) ... Selecting previously unselected package libatomic1:amd64. Preparing to unpack .../2-libatomic1_6.3.0-18+deb9u1_amd64.deb ... Unpacking libatomic1:amd64 (6.3.0-18+deb9u1) ... Selecting previously unselected package mysql-community-client. Preparing to unpack .../3-mysql-community-client_5.7.22-1debian9_amd64.deb ... Unpacking mysql-community-client (5.7.22-1debian9) ... Selecting previously unselected package mysql-client. Preparing to unpack .../4-mysql-client_5.7.22-1debian9_amd64.deb ... Unpacking mysql-client (5.7.22-1debian9) ... Selecting previously unselected package psmisc. Preparing to unpack .../5-psmisc_22.21-2.1+b2_amd64.deb ... Unpacking psmisc (22.21-2.1+b2) ... Selecting previously unselected package libmecab2:amd64. Preparing to unpack .../6-libmecab2_0.996-3.1_amd64.deb ... Unpacking libmecab2:amd64 (0.996-3.1) ... Selecting previously unselected package mysql-community-server. Preparing to unpack .../7-mysql-community-server_5.7.22-1debian9_amd64.deb ... Unpacking mysql-community-server (5.7.22-1debian9) ... Selecting previously unselected package mysql-server. Preparing to unpack .../8-mysql-server_5.7.22-1debian9_amd64.deb ... Unpacking mysql-server (5.7.22-1debian9) ... Setting up libatomic1:amd64 (6.3.0-18+deb9u1) ... Setting up psmisc (22.21-2.1+b2) ... Setting up mysql-common (5.8+1.0.2) ... update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode Setting up libmecab2:amd64 (0.996-3.1) ... Processing triggers for libc-bin (2.24-11+deb9u3) ... Setting up libaio1:amd64 (0.3.110-3) ... Processing triggers for systemd (232-25+deb9u4) ... Processing triggers for man-db (2.7.6.1-2) ... Setting up mysql-community-client (5.7.22-1debian9) ... Setting up mysql-client (5.7.22-1debian9) ... Setting up mysql-community-server (5.7.22-1debian9) ... update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode Created symlink /etc/systemd/system/multi-user.target.wants/mysql.service -> /lib/systemd/system/mysql.service. Setting up mysql-server (5.7.22-1debian9) ... Processing triggers for libc-bin (2.24-11+deb9u3) ... Processing triggers for systemd (232-25+deb9u4) ... |
Secure MySQL
Advertisement:
You can secure the MySQL server deployment (set options as needed)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | sudo mysql_secure_installation Enter password for user root: ******************************************** VALIDATE PASSWORD PLUGIN can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD plugin? Press y|Y for Yes, any other key for No: No Using existing password for root. Change the password for root ? ((Press y|Y for Yes, any other key for No) : No ... skipping. By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? (Press y|Y for Yes, any other key for No) : Yes Success. Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : No ... skipping. By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Yes - Dropping test database... Success. - Removing privileges on test database... Success. Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Yes Success. All done! |
Install NGINX
I installed NGINX to allow Adminer MySQL GUI to be used
I ran these commands to install NGINX.
1 2 3 | sudo apt update sudo apt upgrade sudo apt-get install nginx |
I edited my NGINX configuration as required.
I reloaded NGINX
1 2 3 | sudo nginx -t sudo nginx -s reload sudo systemctl restart nginx |
Advertisement:
Install PHP
I followed this guide to install PHP on Debian.
1 2 3 4 5 6 7 8 9 10 | sudo apt update sudo apt upgrade sudo apt install ca-certificates apt-transport-https wget -q https://packages.sury.org/php/apt.gpg -O- | sudo apt-key add - echo "deb https://packages.sury.org/php/ stretch main" | sudo tee /etc/apt/sources.list.d/php.list sudo apt update sudo apt install php7.2 sudo apt install php-pear php7.2-curl php7.2-dev php7.2-mbstring php7.2-zip php7.2-mysql php7.2-xml php7.2-cli php7.2-common |
Install PHP FPM
1 | apt-get install php7.2-fpm |
Increase Upload Limits
You may need to temporarily increase upload limits in NGINX and PHP before you can restore a WordPress database. My feabry.com blog is about 87MB.
Add “client_max_body_size 100M;” to “/etc/nginx/nginx.conf”
Add the following to “/etc/php/7.2/fpm/php.ini”
- post_max_size = 100M
- upload_max_filesize = 100M
Restore a backup of my MySQL database in MySQL
You can now use Adminer to restore your blog to MySQL. Read my post here on Adminer here. I used Adminer to move my WordPress site from CPanel to a self-managed server a year ago.
First login to your source server and export your desired database then login to the target server and import the database.
Firewall Check
Don’t forget to allow your WordPress site’s 2x Public IP’s and 1x Private IP to access port 3306 in your UpCloud Firewall.
How to check open ports on your current server
1 | sudo netstat -plunt |
Set MySQL Permissions
Open MySQL
1 | mysql --host=localhost --user=root --password=*************************************************************************** |
I ran these statements to grant the user logging in on the nominate IP’s access to MySQL.
1 2 3 4 5 | mysql> GRANT ALL ON databasenmae.* TO databaseusername@IPv4Server1PublicAddress IDENTIFIED BY '***********sql*user*password*************'; GRANT ALL ON databasenmae.* TO databaseusername@IPv4Server1PrivateAddress IDENTIFIED BY '***********sql*user*password*************'; GRANT ALL ON databasenmae.* TO databaseusername@IPv4Server2PublicAddress IDENTIFIED BY '***********sql*user*password*************'; GRANT ALL ON databasenmae.* TO databaseusername@IPv4Server1PrivateAddress IDENTIFIED BY '***********sql*user*password*************'; |
Reload permissions in MySQL
1 | FLUSH PRIVILEGES; |
Allow access to the Debian machine from known IP’s
Edit “/etc/host.allow”
Additions (known safe IP’s that need access to this MySQL remotely).
1 2 3 4 5 6 | mysqld : IPv4Server1PublicAddress : allow mysqld : IPv4Server1PrivateAddress : allow mysqld : IPv4Server2PublicAddress : allow mysqld : IPv4Server1PrivateAddress : allow mysqld : ALL : deny |
Tell MySQL to listen on
Edit “/etc/mysql/my.cnf”
Added..
1 2 3 4 5 6 7 8 9 10 | [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/English bind-address = DebianServersIntenalIPv4Address |
I guess you could change the port to something random???
Restart MySQL
1 | sudo service mysql restart |
Install a second local firewall on Debian
Advertisement:
Install ufw
1 | sudo apt-get instal ufw |
Do add the IP of your desired server or VPN to access SSH
1 | sudo ufw allow from 123.123.123.123 to any port 22 |
Do add the IP of your desired server or VPN to access WWW
1 | sudo ufw allow from 123.123.123.123 to any port 80 |
Now add the known IP’s (e.g any web servers public (IPv4/IPv6) or Private IP’s) that you wish to grant access to MySQL (e.g the source website that used to have MySQL)
1 | sudo ufw allow from 123.123.123.123 to any port 3306 |
Do add UpCloud DNS Servers to your firewall
1 2 3 4 | sudo ufw allow from 94.237.127.9 to any port 53 sudo ufw allow from 94.237.40.9 to any port 53 sudo ufw allow from 2a04:3544:53::1 to any port 53 sudo ufw allow from 2a04:3540:53::1 to any port 53 |
Add all other rules as needed (if you stuff up and lock your self out you can login to the server with the Console on UpCloud)
Restart the ufw firewall
1 2 | sudo ufw disable sudo ufw enable |
Prevent MySQL starting on the source server
Now we can shut down MySQL on the source server (leave it there just in case).
Edit “/etc/init/mysql.conf”
Comment out the line that contains “start on ” and save the file
and run
1 | sudo systemctl disable mysql |
Reboot
1 | shutdown -r now |
Stop and Disable NGINX on the new DB server
We don’t need NGINX running now the database has been imported with Adminer.
Stop and prevent NGINX from starting up on startup.
1 2 3 | /etc/init.d/nginx stop sudo update-rc.d -f nginx disable sudo systemctl disable nginx |
Check to see if MySQL is Disabled
1 2 3 4 | service mysql status * mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; disabled; vendor preset: enabled) Active: inactive (dead) |
Yep
Test access to the database server in PHP code
Add to dbtest.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | <em>SELECT guid FROM wp_posts</em>()<br /> <ul><?php //External IP (charged after quota hit) //$servername = 'db.yourserver.com'; //Private IP (free) //$servername = '10.x.x.x'; $username = 'username'; $password = '********your*password*********'; $dbname = 'database'; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = 'SELECT guid FROM wp_posts'; $result = $conn->query($sql); if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { echo $row["guid"] . "<br>"; } } else { echo "0 results"; } $conn->close(); ?></ul> Done |
Check for open ports.
You can install nmap on another server and scan for open ports
Advertisement:
Install nmap
1 | sudo apt-get install nmap |
Scan a server for open ports with nmap
You should see this on a server that has access to see port 3306 (port 3306 should not be visible by non-whitelisted IP’s). Port 3shouldoudl not be seen via everyone.
1 2 3 4 5 6 7 8 9 | sudo nmap -PN db.yourserver.com Starting Nmap 7.40 ( https://nmap.org ) at 2018-07-20 14:15 UTC Nmap scan report for db.yourserver.com (IPv4IP) Host is up (0.0000070s latency). Other addresses for db.yourserver.com (not scanned): IPv6IP Not shown: 997 closed ports PORT STATE SERVICE 3306/tcp open mysql |
You should see something like this on a server that has access to see port 80/443 (a web server)
1 2 3 4 5 6 7 8 9 10 | sudo nmap -PN yourserver.com Starting Nmap 7.40 ( https://nmap.org ) at 2018-07-20 14:18 UTC Nmap scan report for db.yourserver.com (IPv4IP) Host is up (0.0000070s latency). Other addresses for db.yourserver.com (not scanned): IPv6IP Not shown: 997 closed ports PORT STATE SERVICE 80/tcp open http 443/tcp open https |
I’d recommend you use a service like https://pentest-tools.com/network-vulnerability-scanning/tcp-port-scanner-online-nmap# to check for open ports. https://hackertarget.com/tcp-port-scan/ is a great tool too.
https://www.infobyip.com/tcpportchecker.php is also a free port checker that you can use to verify individual closed ports.
Hardening MySQL and Debian
Read: https://www.debian.org/doc/manuals/securing-debian-howto/ap-checklist.en.html
Configuring WordPress use the dedicated Debian VM
On the source server that used to have MySQL edit your wp-config.php file for WordPress.
Remove
1 | define('DB_HOST', 'localhost'); |
add (read the update below, I changed the DNS IP to the Private IP to have free traffic)
1 2 3 4 5 6 7 8 | //Oriinal localhost //define('DB_HOST', 'localhost'); //New external host via DNS (Charged after quota hit) //define('DB_HOST', 'db.fearby.com'); //New external host via Private IP (Free) define('DB_HOST','10.x.x.x'); |
Restart NGINX
1 2 3 | sudo nginx -t sudo nginx -s reload sudo systemctl restart nginx |
Restart PHP-FPM
1 | service php7.2-fpm restart |
Conclusion
Nice, I seem to have shaved off 0.3 seconds in load times (25% improvement)
Update: Using a Private IP or Public IP between WordPress and MySQL servers
After I released this blog post (version 1.0 with no help from UpCloud) UpCloud contacted me and said the following.
1 2 3 4 5 6 7 8 9 10 | Hello Simon, I notice there's no mention of using the private network IPs. Did you know that we automagically assign you one when you deploy with our templates. The private network works out of the box without additional configuration, you can use that communicate between your own cloud servers and even across datacentres. There's no bandwidth charge when communicating over private network, they do not go through public internet as well. With this, you can easily build high redundant setups. Let me know if you have any other questions. -- Kelvin from UpCloud |
I will have updated my references in this post and replace the public IP address (that is linked to DNS record for db.fearby.com) and instead use the private ip address (e.g 10.x.x.x), your servers private IP address is listed against the public IPv$ and IPv6 address.
I checked that the local ufw firewall did indeed allow the private IP access to MySQL.
1 2 | sudo ufw status numbered |grep 10.x.x.x [27] 3306 ALLOW IN 10.x.x.x |
On my new Debian MySQL server, I edited the file /etc/mysql/my.cnf and changed the IP to the private IP and not the public IP.
Now it looked like
1 2 3 4 5 6 7 8 9 10 | [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/English bind-address = 10.x.x.x |
(10.x.x.x my Debian servers private IP)
On my WordPress instance, I edited the file /www-root/wp-config.php
I added the new private host
1 2 3 4 5 6 7 8 | //Oriinal localhost //define('DB_HOST', 'localhost'); //New external host via DNS (Charged after quota hit) //define('DB_HOST', 'db.fearby.com'); //New external host via Private IP (Free) define('DB_HOST','10.x.x.x'); |
(10.x.x.x my Debian servers private IP)
Alos on Debian/MySQL ensure you have granted access to the private IP of the WordPress server
Edit /etc/host.allow
Add
1 | mysqld : 10.x.x.x : allow |
Restart MySQL
1 | sudo systemctl restart mysql |
TIP: Enable UpCloud Backups
Do setup automatic backups (and or take manual backups). Backups are an extra charge but are essential IMHO.
Troubleshooting
If you can’t access MySQL log back into MySQL
1 | mysql --host=localhost --user=root --password=*************************************************************************** |
and run
1 | GRANT ALL PRIVILEGES ON *.* TO databaseuser@'%' IDENTIFIED BY '***********sql*user*password*************''; FLUSH PRIVILEGES; |
Reboot
Lower Upload Limits
Don’t forget to lower file upload sizes in NGINX and PHP (e.g 2M) now that the database has been restored.
I hope this guide helps someone.
TIP: Sign up to UpCloud using this link to get $25 free UpCloud VM credit.
https://www.upcloud.com/register/?promo=D84793
Ask a question or recommend an article
Advertisement:
Revision History
v1.6 Changed Public IP use to private IP use to ensure we are not charged when the serves sage goes over the quota
v1.5 Fixed 03 type (should have been 0.3)
v1.4 added disable nginx info
v1.3 added https://www.infobyip.com/tcpportchecker.php
v1.1 added https://hackertarget.com/tcp-port-scan/
v1.0 Initial Post
