Code, InfoSec and Server Stuff
Views are my own and not my employer's
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
Read the full article here: https://fearby.com/article/setup-a-dedicated-debian-subdomain-vm-install-mysql-14-and-connect-to-it-from-a-wordpress-on-a-different-vm
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:
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
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”
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
I moved my domain to UpCloud (on the other side of the world) from Vultr (Sydney) and could not be happier with the performance. Here is what I did to set up a complete Ubuntu 18.04 system (NGINX, PHP, MySQL, WordPress etc). This is not a paid review (just me documenting my steps over 2 days).
Background (CPanel hosts)
In 1999 I hosted my first domain (www.fearby.com) on a host in Seattle (for $10 USD a month), the host used CPanel and all was good. After a decade I was using the domain more for online development and the website was now too slow (I think I was on dial-up or ADSL 1 at the time). I moved my domain to an Australian host (for $25 a month).
After 8 years the domain host was sold and performance remained mediocre. After another year the new host was sold again and performance was terrible.
Advertisement:
Page load times were near 30 seconds.
I started receiving Resource Limit Is Reached warnings (basically this was a plot by the new CPanel host to say “Pay us more and this message will go away”).
The straw that broke the camel’s back was their demand of $150/year for a dodgy SSL certificate.
I needed to move to a self-managed server where I was in control.
Advertisement:
Buying a Domain Name
Buy a domain name from Namecheap here.
Self Managed Server
I found a good web IDE ( http://www.c9.io/ ) that allowed me to connect to a cloud VM. C9 allowed me to open many files and terminal windows and reconnect to them later. Don’t get excited, though, as AWS have purchased C9 and it’s not the same.
C9 IDE
I spun up a Digital Ocean Server at the closest data centre in Singapore. Here was my setup guide creating a Digital Ocean VM, connecting to it with C9 and configuring it. I moved my email to G Suite and moved my WordPress to Digital Ocean (other guides here and here).
I was happy since I could now send emails via CLI/code, set up free SSL certs, add second domain email to G Suite and Secure G Suite. No more usage limit errors either.
Self-managing servers requires more work but it is more rewarding (flexible, faster and cheaper). Page load times were now near 20 seconds (10-second improvement).
Latency Issue
Over 6 months, performance on Digital Ocean (in Singapore) from Australia started to drop (mentioned here). I tried upgrading the memory but that did not help (latency was king).
Moved the website to Australia
I moved my domain to Vultr in Australia (guide here and here). All was good for a year until traffic growth started to increase.
I tried upgrading the memory on Vultr and I setup PHP child workers, setup Cloudflare.
GT Metrix scores were about a “B” and Google Page Speed Scores were in the lower 40’s. Page loads were about 14 seconds (5-second improvement).
Tweaking WordPress
I set up an image compression plugin in WordPress then set up a cloud image compression and CDN Plugin from the same vendor. Page Speed info here.
GT Metrix scores were now occasionally an “A” and Page Speed Scores were in the lower 20’s. Page loads were about 3-5 seconds (10-second improvement).
Mixed bag from Vultr (more optimisation and performance improvements were needed).
Google Chrome Developer Console Audit Results on Vultr hosted website were not very good (I stopped checking as nothing helped).
The problem was the Vultr server (400km away in Sydney) was offline (my issue) and everything above (adding more memory, adding 2x CDN’s (EWWW and Cloudflare), adding PHP Child workers etc) did not seem to help???
Enter UpCloud…
Recently, a friend sent a link to a blog article about a host called “UpCloud” who promised “Faster than SSD” performance. This can’t be right: “Faster than SSD”? I was intrigued. I wanted to check it out as I thought nothing was faster than SSD (well, maybe RAM).
I signed up for a trial and ran a disk IO test (read the review here) and I was shocked. It’s fast. Very fast.
Summary: UpCloud was twice as fast (Disk IO and CPU) as Vultr (+ an optional $4/m firewall and $3/m for 1x backup).
fyi: Labels above are K Bytes per second. iozone loops through all file sizes from 4 KB to 16,348 KB and measures through the reads per second. To be honest, the meaning of the numbers doesn’t interest me, I just want to compare apples to apples.
(image snip from http://www.iozone.org/ which explains the numbers)
I might have to copy my website on UpCloud and see how fast it is.
Where to Deploy and Pricing
UpCloud Pricing: https://www.upcloud.com/pricing/
UpCloud does not have a data centre in Australia yet so why choose UpCloud?
Most of my site’s visitors are based in the US and UpCloud have disk IO twice as fast as Vultr (win-win?). I could deploy to Chicago?
My site’s traffic is growing and I need to ensure the site is fast enough in the future.
Advertisement:
Creating an UpCloud VM
I used a friend’s referral code and signed up to create my first VM.
FYI: use my Referral code and get $25 free credit. Sign up only takes 2 minutes.
https://www.upcloud.com/register/?promo=D84793
UpCould support page is located here: https://www.upcloud.com/support/
More UpCloud links to read:
I was not sure how to log in after signing up so I went back to https://www.upcloud.com/and clicked Sign In ( https://my.upcloud.com/login ).
Now I can see a dashboard 🙂
I was happy to see 24/7 support is available.
UpCloud Dashboard
After logging in I noticed I could customize the dashboard.
It would be nice to see an UpCloud API or some way that I can push data to the dashboard from my VM (via scheduled cron jobs). I’d really like to see my data in this dashboard, not at the level of PM2 but things like CPU/Memory history, logs, disk used/free, connections etc.
UpCloud Trial Mode
I am not sure what the time limit is to the trial mode (5 days)?
UPDATE: A friend signed up for a trial and did not deposit some money (e.g $10) in 7 days and his server was deleted. We deposited $10 via PayPal and he was still in trial mode. If you are signing up and want to keep your server you will need to exit trial mode by depositing money from a DEBIT/CC card (that is not blocked overseas) ASAP!
Deploy My First UpCloud Server
This is how I deployed a server.
If you are going to deploy a server consider using my referral code and get $25 credit for free.
Under the “deploy a server” widget I named the server and chose a location (I think I was supposed to use an FQDN name -e.g., “fearby.com”). The deployment worked though. I clicked continue, then more options were made available:
FYI: How to generate a new SSH Key (on OSX or Ubuntu)
1 | ssh-keygen -t rsa |
Output
1 2 3 4 5 6 7 8 9 | Generating public/private rsa key pair. Enter file in which to save the key (/root/.ssh/id_rsa): /temp/example_rsa Enter passphrase (empty for no passphrase): ********************************* Enter same passphrase again:********************************* Your identification has been saved in /temp/example_rsa. Your public key has been saved in /temp/example_rsa.pub. The key fingerprint is: SHA256:########################### user@sever Outputted public and private key |
Did the key export? (yes)
> /temp# ls /temp/ -al
> drwxr-xr-x 2 root root 4096 Jun 9 15:33 .
> drwxr-xr-x 27 root root 4096 Jun 8 14:25 ..
> -rw——- 1 user user 1766 Jun 9 15:33 example_rsa
> -rw-r–r– 1 user user 396 Jun 9 15:33 example_rsa.pub
“example_rsa” is the private key and “example_rsa.pub “is the public key.
Initialisation script (after deployment)
I was pleased to see an initialization script section that calls actions after the server is deployed. I configured the initialisation script to pull down a few GB of backups from my Vultr website in Sydney (files now removed).
This was my Initialisation script:
1 2 3 4 5 6 | #!/bin/bash echo "Downloading the Vultr websites backups" mkdir /backup cd /backup wget -o www-mysql-backup.sql https://fearby.com/.../www-mysql-backup.sql wget -o www-blog-backup.zip https://fearby.com/.../www-blog-backup.zip |
Confirm and Deploy
I clicked “Confirm and deploy” but I had an alert that said trial mode can only deploy servers up to 1024MB of memory.
Exiting UpCloud Trial Mode
I opened the dashboard and clicked My Account then Billing, I could see the $25 referral credit but I guess I can’t use that in Trial.
I exited trial mode by depositing $10 (USD).
FYI: Server prices are listed below (or view prices here).
Now I can go back and deploy the server with the same settings above (1x CPU, 2GB Memory, Ubuntu 18.04, MaxIOPS Storage etc)
Advertisement:
Deployment in progress:
UpCloud Server Deployed
The server is now deployed; now I can connect to it with my SSH program (vSSH). Simply add the server’s IP, username, password and the SSH private key (generated above) to your ssh program of choice.
fyi: The public key contents start with “ssh-rsa”.
I noticed that the initialisation script downloaded my 2+GB of files already. Nice.
UpCloud Billing Breakdown
I can now see on the UpCloud billing page in my dashboard that credit is deducted daily (68c); at this rate, I have 49 days credit left?
I can manually deposit funds or set up automatic payments 🙂
UpCloud Backup Options
Note: Backups are charged at $0.056 for every GB stored – so $5.60 for every 100GB per month (half that for 50GB etc)
I made a manual backup and can set an automatic schedule if need be. Nice.
UpCloud Firewall Options
I set up a firewall at UpCloud to only allow the minimum number of ports (UpCloud DNS, HTTP, HTTPS and My IP to port 22). The firewall feature is charged at $0.0056 an hour ($4.03 a month)
I love the ability to set firewall rules on incoming, destination and outgoing ports.
Update: I modified my firewall to allow inbound ICMP (IPv4/IPv6) and UDP (IPv4/IPv6) packets.
Because my internet provider has a dynamic IP, I set up a VPN with a static IP and whitelisted it for backdoor access.
Local Ubuntu ufw Firewall
I duplicated the rules in my local ufw (2nd level) firewall (and blocked mail)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | sudo ufw status numbered Status: active To Action From -- ------ ---- [ 1] 80 ALLOW IN Anywhere [ 2] 443 ALLOW IN Anywhere [ 3] 25 DENY OUT Anywhere (out) [ 4] 53 ALLOW IN 93.237.127.9 [ 5] 53 ALLOW IN 93.237.40.9 [ 6] 22 ALLOW IN REMOVED (MY WHITELISTED IP)) [ 7] 80 (v6) ALLOW IN Anywhere (v6) [ 8] 443 (v6) ALLOW IN Anywhere (v6) [ 9] 25 (v6) DENY OUT Anywhere (v6) (out) [10] 53 ALLOW IN 2a04:3540:53::1 [11] 53 ALLOW IN 2a04:3544:53::1 |
UpCloud Download Speeds
I pulled down a 1.8GB Ubuntu 18.08 Desktop ISO 3 times from gigenet.com and the file downloaded in 32 seconds (57MB/sec). Nice.
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 | $/temp# wget http://mirrors.gigenet.com/ubuntu/18.04/ubuntu-18.04-desktop-amd64.iso --2018-06-08 18:02:04-- http://mirrors.gigenet.com/ubuntu/18.04/ubuntu-18.04-desktop-amd64.iso Resolving mirrors.gigenet.com (mirrors.gigenet.com)... 69.65.15.34 Connecting to mirrors.gigenet.com (mirrors.gigenet.com)|69.65.15.34|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 1921843200 (1.8G) [application/x-iso9660-image] Saving to: 'ubuntu-18.04-desktop-amd64.iso' ubuntu-18.04-desktop-amd64.iso 100%[==================================================================>] 1.79G 57.0MB/s in 32s 2018-06-08 18:02:37 (56.6 MB/s) - 'ubuntu-18.04-desktop-amd64.iso' saved [1921843200/1921843200] $/temp# wget http://mirrors.gigenet.com/ubuntu/18.04/ubuntu-18.04-desktop-amd64.iso --2018-06-08 18:02:46-- http://mirrors.gigenet.com/ubuntu/18.04/ubuntu-18.04-desktop-amd64.iso Resolving mirrors.gigenet.com (mirrors.gigenet.com)... 69.65.15.34 Connecting to mirrors.gigenet.com (mirrors.gigenet.com)|69.65.15.34|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 1921843200 (1.8G) [application/x-iso9660-image] Saving to: 'ubuntu-18.04-desktop-amd64.iso.1' ubuntu-18.04-desktop-amd64.iso.1 100%[==================================================================>] 1.79G 57.0MB/s in 32s 2018-06-08 18:03:19 (56.6 MB/s) - 'ubuntu-18.04-desktop-amd64.iso.1' saved [1921843200/1921843200] $/temp# wget http://mirrors.gigenet.com/ubuntu/18.04/ubuntu-18.04-desktop-amd64.iso --2018-06-08 18:03:23-- http://mirrors.gigenet.com/ubuntu/18.04/ubuntu-18.04-desktop-amd64.iso Resolving mirrors.gigenet.com (mirrors.gigenet.com)... 69.65.15.34 Connecting to mirrors.gigenet.com (mirrors.gigenet.com)|69.65.15.34|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 1921843200 (1.8G) [application/x-iso9660-image] Saving to: 'ubuntu-18.04-desktop-amd64.iso.2' ubuntu-18.04-desktop-amd64.iso.2 100%[==================================================================>] 1.79G 57.0MB/s in 32s 2018-06-08 18:03:56 (56.8 MB/s) - 'ubuntu-18.04-desktop-amd64.iso.2' saved [1921843200/1921843200] |
Install Common Ubuntu Packages
I installed common Ubuntu packages.
1 | apt-get install zip htop ifstat iftop bmon tcptrack ethstatus speedometer iozone3 bonnie++ sysbench siege tree tree unzip jq jq ncdu pydf ntp rcconf ufw iperf nmap iozone3 |
Timezone
I checked the server’s time (I thought this was auto set before I deployed)?
1 2 | $hwclock --show 2018-06-06 23:52:53.639378+0000 |
I reset the time to Australia/Sydney.
1 2 3 4 | dpkg-reconfigure tzdata Current default time zone: 'Australia/Sydney' Local time is now: Thu Jun 7 06:53:20 AEST 2018. Universal Time is now: Wed Jun 6 20:53:20 UTC 2018. |
Now the timezone is set 🙂
Shell History
I increased the shell history.
1 2 | HISTSIZEH =10000 HISTCONTROL=ignoredups |
SSH Login
I created a ~/.ssh/authorized_keys file and added my SSH public key to allow password-less logins.
1 2 | mkdir ~/.ssh sudo nano ~/.ssh/authorized_keys |
I added my pubic ssh key, then exited the ssh session and logged back in. I can now log in without a password.
Install NGINX
1 | apt-get install nginx |
nginx/1.14.0 is now installed.
A quick GT Metrix test.
Install MySQL
Run these commands to install and secure MySQL.
1 2 | apt install mysql-server mysql_secure_installation |
Securing the MySQL server deployment.
> Would you like to setup VALIDATE PASSWORD plugin?: n
> New password: **********************************************
> Re-enter new password: **********************************************
> Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
> Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
> Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
> Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
> Success.
I disabled the validate password plugin because I hate it.
MySQL Ver 14.14 Distrib 5.7.22 is now installed.
Set MySQL root login password type
Set MySQL root user to authenticate via “mysql_native_password”. Run the “mysql” command.
1 2 3 4 5 6 7 8 9 10 | mysql SELECT user,authentication_string,plugin,host FROM mysql.user; +------------------+-------------------------------------------+-----------------------+-----------+ | user | authentication_string | plugin | host | +------------------+-------------------------------------------+-----------------------+-----------+ | root | | auth_socket | localhost | | mysql.session | hiddden | mysql_native_password | localhost | | mysql.sys | hiddden | mysql_native_password | localhost | | debian-sys-maint | hiddden | mysql_native_password | localhost | +------------------+-------------------------------------------+-----------------------+---------- |
Now let’s set the root password authentication method to “mysql_native_password”
1 2 | ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '*****************************************'; Query OK, 0 rows affected (0.00 sec) |
Check authentication method.
1 2 3 4 5 6 7 8 9 | mysql> SELECT user,authentication_string,plugin,host FROM mysql.user; +------------------+-------------------------------------------+-----------------------+-----------+ | user | authentication_string | plugin | host | +------------------+-------------------------------------------+-----------------------+-----------+ | root | ######################################### | mysql_native_password | localhost | | mysql.session | hiddden | mysql_native_password | localhost | | mysql.sys | hiddden | mysql_native_password | localhost | | debian-sys-maint | hiddden | mysql_native_password | localhost | +------------------+-------------------------------------------+-----------------------+-----------+ |
Now we need to flush permissions.
1 2 | mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) |
Done.
Advertisement:
Install PHP
Install PHP 7.2
1 2 3 4 5 | apt-get install software-properties-common add-apt-repository ppa:ondrej/php apt-get update apt-get install -y php7.2 php -v |
PHP 7.2.5, Zend Engine v3.2.0 with Zend OPcache v7.2.5-1 is now installed. Do update PHP frequently.
I made the following changes in /etc/php/7.2/fpm/php.ini
> cgi.fix_pathinfo=0
> max_input_vars = 1000
> memory_limit = 1024M
> max_file_uploads = 20M
> post_max_size = 20M
Install PHP Modules
sudo apt-get install php-pear php7.2-curl php7.2-dev php7.2-mbstring php7.2-zip php7.2-mysql php7.2-xml
Install PHP FPM
1 | apt-get install php7.2-fpm |
Configure PHP FPM config.
Edit /etc/php/7.2/fpm/php.ini
> cgi.fix_pathinfo=0
> max_input_vars = 1000
> memory_limit = 1024M
> max_file_uploads = 20M
> post_max_size = 20M
Reload php sudo service.
1 | php7.2-fpm restart service php7.2-fpm status |
Install PHP Modules
1 | sudo apt-get install php-pear php7.2-curl php7.2-dev php7.2-mbstring php7.2-zip php7.2-mysql php7.2-xml |
Configuring NGINX
If you are not comfortable editing NGINX config files read here, here and here.
I made a new “www root” folder, set permissions and created a default html file.
1 2 3 | mkdir /www-root chown -R www-data:www-data /www-root echo "Hello World" >> /www-root/index.html |
I edited the “root” key in “/etc/nginx/sites-enabled/default” file and set the root a new location (e.g., “/www-root”)
I added these performance tweaks to /etc/nginx/nginx.conf
> worker_cpu_affinity auto;
> worker_rlimit_nofile 100000
I add the following lines to “http {” section in /etc/nginx/nginx.conf
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 | client_max_body_size 10M; gzip on; gzip_disable "msie6"; gzip_comp_level 5; gzip_min_length 256; gzip_vary on; gzip_types application/atom+xml application/ld+json application/manifest+json application/rss+xml application/vnd.geo+json application/vnd.ms-fontobject application/x-font-ttf application/x-web-app-manifest+json application/xhtml+xml font/opentype image/bmp image/x-icon text/cache-manifest text/vcard text/vnd.rim.location.xloc text/vtt text/x-component text/x-cross-domain-policy; #text/html is always compressed by gzip module gzip_proxied any; gzip_buffers 16 8k; gzip_http_version 1.1; gzip_types text/plain text/css application/json application/javascript text/xml application/xml application/xml+rss te$ |
Check NGINX Status
1 2 3 4 5 6 7 8 9 10 | service nginx status * nginx.service - A high performance web server and a reverse proxy server Loaded: loaded (/lib/systemd/system/nginx.service; enabled; vendor preset: enabled) Active: active (running) since Thu 2018-06-07 21:16:28 AEST; 30min ago Docs: man:nginx(8) Main PID: # (nginx) Tasks: 2 (limit: 2322) CGroup: /system.slice/nginx.service |- # nginx: master process /usr/sbin/nginx -g daemon on; master_process on; `- # nginx: worker process |
Install Open SSL that supports TLS 1.3
This is a work in progress. The steps work just fine for me on Ubuntu 16.04. but not Ubuntu 18.04.?
Installing Adminer MySQL GUI
I will use the PHP based Adminer MySQL GUI to export and import my blog from one server to another. All I needed to do is install it on both servers (simple 1 file download)
1 2 | cd /utils wget -o adminer.php https://github.com/vrana/adminer/releases/download/v4.6.2/adminer-4.6.2-mysql-en.php |
Use Adminer to Export My Blog (on Vultr)
On the original server open Adminer (http) and..
Save the “.sql” file.
I used Adminer on the UpCloud server to Import My Blog
FYI: Depending on the size of your database backup you may need to temporarily increase your upload and post sizes limits in PHP and NGINX before you can import your database.
Edit /etc/php/7.2/fpm/php.ini
> max_file_uploads = 100M
> post_max_size =100M
And Edit: /etc/nginx/nginx.conf
> client_max_body_size 100M;
Don’t forget to reload NGINX config and restart NGINX and PHP. Take note of the maximum allowed file size in the screenshot below. I temporarily increased my upload limits to 100MB in order to restore my 87MB blog.
Now I could open Adminer on my UpCloud server.
Don’t forget to create a user and assign permissions (as required – check your wp-config.php file).
Tip: Don’t forget to lower the maximum upload file size and max post size after you import your database,
Cloudflare DNS
I use Cloudflare to manage DNS, so I need to tell it about my new server.
You can get your server’s IP details from the UpCloud dashboard.
At Cloudflare update your DNS details to point to the server’s new IPv4 (“A Record”) and IPv6 (“AAAA Record”).
Advertisement:
Domain Error
I waited an hour and my website was suddenly unavailable. At first, I thought this was Cloudflare forcing the redirection of my domain to HTTP (that was not yet set up).
I chatted with UpCloud chat on their webpage and they kindly assisted me to diagnose all the common issues like DNS values, DNS replication, Cloudflare settings and the error was pinpointed to my NGINX installation. All NGINX config settings were ok from what we could see? I uninstalled NGINX and reinstalled it (and that fixed it). Thanks UpCloud Support 🙂
Reinstalled NGINX
1 | sudo apt-get purge nginx nginx-common |
I reinstalled NGINX and reconfigured /etc/nginx/nginx.conf (I downloaded my SSL cert from my old server just in case).
Here is my /etc/nginx/nginx.conf file.
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 | user www-data; worker_processes auto; worker_cpu_affinity auto; pid /run/nginx.pid; include /etc/nginx/modules-enabled/*.conf; error_log /var/log/nginx/www-nginxcriterror.log crit; events { worker_connections 768; multi_accept on; } http { client_max_body_size 10M; sendfile on; tcp_nopush on; tcp_nodelay on; keepalive_timeout 65; types_hash_max_size 2048; server_tokens off; server_names_hash_bucket_size 64; server_name_in_redirect off; include /etc/nginx/mime.types; default_type application/octet-stream; ssl_protocols TLSv1.1 TLSv1.2; ssl_prefer_server_ciphers on; access_log /var/log/nginx/www-access.log; error_log /var/log/nginx/www-error.log; gzip on; gzip_vary on; gzip_disable "msie6"; gzip_min_length 256; gzip_proxied any; gzip_comp_level 6; gzip_buffers 16 8k; gzip_http_version 1.1; gzip_types text/plain text/css application/json application/javascript text/xml application/xml application/xml+rss text/javascript; include /etc/nginx/conf.d/*.conf; include /etc/nginx/sites-enabled/*; } |
Here is my /etc/nginx/sites-available/default file (fyi, I have not fully re-setup TLS 1.3 yet so I commented out the settings)
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 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 | proxy_cache_path /tmp/nginx-cache keys_zone=one:10m;# server { root /www-root; # Listen Ports listen 80 default_server http2; listen [::]:80 default_server http2; listen 443 ssl default_server http2; listen [::]:443 ssl default_server http2; # Default File index index.html index.php index.htm; # Server Name server_name www.fearby.com fearby.com localhost; # HTTPS Cert ssl_certificate /etc/nginx/ssl-cert-path/fearby.crt; ssl_certificate_key /etc/nginx/ssl-cert-path/fearby.key; ssl_dhparam /etc/nginx/ssl-cert-path/dhparams4096.pem; # HTTPS Ciphers # TLS 1.2 ssl_protocols TLSv1.2; ssl_prefer_server_ciphers on; ssl_ciphers "EECDH+AESGCM:EDH+AESGCM:AES256+EECDH:AES256+EDH"; # TLS 1.3 #todo # ssl_ciphers # ECDHE-RSA-AES256-GCM-SHA512:DHE-RSA-AES256-GCM-SHA512:ECDHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:DES-CBC3-SHA; # ssl_ecdh_curve secp384r1; # Force HTTPS if ($scheme != "https") { return 301 https://$host$request_uri; } # HTTPS Settings server_tokens off; ssl_session_cache shared:SSL:10m; ssl_session_timeout 30m; ssl_session_tickets off; add_header Strict-Transport-Security "max-age=63072000; includeSubdomains; preload"; add_header X-Frame-Options DENY; add_header X-Content-Type-Options nosniff; add_header X-XSS-Protection "1; mode=block"; #ssl_stapling on; # Requires nginx >= 1.3.7 # Cloudflare DNS resolver 1.1.1.1 1.0.0.1 valid=60s; resolver_timeout 1m; # PHP Memory fastcgi_param PHP_VALUE "memory_limit = 1024M"; # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000 location ~ .php$ { try_files $uri =404; # include snippets/fastcgi-php.conf; fastcgi_split_path_info ^(.+.php)(/.+)$; fastcgi_index index.php; fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name; include fastcgi_params; fastcgi_pass unix:/run/php/php7.2-fpm.sock; # NOTE: You should have "cgi.fix_pathinfo = 0;" in php.ini # fastcgi_pass 127.0.0.1:9000; } location / { # try_files $uri $uri/ =404; try_files $uri $uri/ /index.php?q=$uri&$args; index index.php index.html index.htm; proxy_set_header Proxy ""; } # Deny Rules location ~ /.ht { deny all; } location ~ ^/.user.ini { deny all; } location ~ (.ini) { return 403; } # Headers location ~* .(?:ico|css|js|gif|jpe?g|png|js)$ { expires 30d; add_header Pragma public; add_header Cache-Control "public"; } } |
SSL Labs SSL Certificate Check
All good thanks to the config above.
Install WP-CLI
I don’t like setting up FTP to auto-update WordPress plugins. I use the WP-CLI tool to manage WordPress installations by the command line. Read my blog here on using WP-CLI.
Download WP-CLI
1 2 3 | mkdir /utils cd /utils curl -O https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar |
Move WP-CLI to the bin folder as “wp”
1 2 | chmod +x wp-cli.phar sudo mv wp-cli.phar /usr/local/bin/wp |
Test wp
1 2 3 4 5 6 7 8 9 10 11 12 13 | wp --info OS: Linux 4.15.0-22-generic #24-Ubuntu SMP Wed May 16 12:15:17 UTC 2018 x86_64 Shell: /bin/bash PHP binary: /usr/bin/php7.2 PHP version: 7.2.5-1+ubuntu18.04.1+deb.sury.org+1 php.ini used: /etc/php/7.2/cli/php.ini WP-CLI root dir: phar://wp-cli.phar WP-CLI vendor dir: phar://wp-cli.phar/vendor WP_CLI phar path: /www-root WP-CLI packages dir: WP-CLI global config: WP-CLI project config: WP-CLI version: 1.5.1 |
Update WordPress Plugins
Now I can run “wp plugin update” to update all WordPress plugins
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 | wp plugin update Enabling Maintenance mode... Downloading update from https://downloads.wordpress.org/plugin/wordfence.7.1.7.zip... Unpacking the update... Installing the latest version... Removing the old version of the plugin... Plugin updated successfully. Downloading update from https://downloads.wordpress.org/plugin/wp-meta-seo.3.7.1.zip... Unpacking the update... Installing the latest version... Removing the old version of the plugin... Plugin updated successfully. Downloading update from https://downloads.wordpress.org/plugin/wordpress-seo.7.6.1.zip... Unpacking the update... Installing the latest version... Removing the old version of the plugin... Plugin updated successfully. Disabling Maintenance mode... Success: Updated 3 of 3 plugins. +---------------+-------------+-------------+---------+ | name | old_version | new_version | status | +---------------+-------------+-------------+---------+ | wordfence | 7.1.6 | 7.1.7 | Updated | | wp-meta-seo | 3.7.0 | 3.7.1 | Updated | | wordpress-seo | 7.5.3 | 7.6.1 | Updated | +---------------+-------------+-------------+---------+ |
Update WordPress Core
WordPress core file can be updated with “wp core update”
1 2 | wp core update Success: WordPress is up to date. |
Troubleshooting: Use the flag “–allow-root “if wp needs higher access (unsafe action though).
Install PHP Child Workers
I edited the following file to setup PHP child workers /etc/php/7.2/fpm/pool.d/www.conf
Changes
> pm = dynamic
> pm.max_children = 40
> pm.start_servers = 15
> pm.min_spare_servers = 5
> pm.max_spare_servers = 15
> pm.process_idle_timeout = 30s;
> pm.max_requests = 500;
> php_admin_value[error_log] = /var/log/www-fpm-php.www.log
> php_admin_value[memory_limit] = 512M
Advertisement:
Restart PHP
1 | sudo service php7.2-fpm restart |
Test NGINX config, reload NGINX config and restart NGINX
1 2 3 | nginx -t nginx -s reload /etc/init.d/nginx restart |
Output (14 workers are ready)
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 | Check PHP Child Worker Status sudo service php7.2-fpm status * php7.2-fpm.service - The PHP 7.2 FastCGI Process Manager Loaded: loaded (/lib/systemd/system/php7.2-fpm.service; enabled; vendor preset: enabled) Active: active (running) since Thu 2018-06-07 19:32:47 AEST; 20s ago Docs: man:php-fpm7.2(8) Main PID: # (php-fpm7.2) Status: "Processes active: 0, idle: 15, Requests: 2, slow: 0, Traffic: 0.1req/sec" Tasks: 16 (limit: 2322) CGroup: /system.slice/php7.2-fpm.service |- # php-fpm: master process (/etc/php/7.2/fpm/php-fpm.conf) |- # php-fpm: pool www |- # php-fpm: pool www |- # php-fpm: pool www |- # php-fpm: pool www |- # php-fpm: pool www |- # php-fpm: pool www |- # php-fpm: pool www |- # php-fpm: pool www |- # php-fpm: pool www |- # php-fpm: pool www |- # php-fpm: pool www |- # php-fpm: pool www |- # php-fpm: pool www |- # php-fpm: pool www - # php-fpm: pool www |
Memory Tweak (set at your own risk)
1 | sudo nano /etc/sysctl.conf |
vm.swappiness = 1
Setting swappiness to a value of 1 all but disables the swap file and tells the Operating System to aggressively use ram, a value of 10 is safer. Only set this if you have enough memory available (and free).
Possible swappiness settings:
> vm.swappiness = 0 Swap is disabled. In earlier versions, this meant that the kernel would swap only to avoid an out of memory condition when free memory will be below vm.min_free_kbytes limit, but in later versions, this is achieved by setting to 1.[2]> vm.swappiness = 1 Kernel version 3.5 and over, as well as Red Hat kernel version 2.6.32-303 and over: Minimum amount of swapping without disabling it entirely.
> vm.swappiness = 10 This value is sometimes recommended to improve performance when sufficient memory exists in a system.[3]
> vm.swappiness = 60 The default value.
> vm.swappiness = 100 The kernel will swap aggressively.
The “htop” tool is a handy memory monitoring tool to “top”
Also you can use good old “watch” command to show near-live memory usage (auto-refreshes every 2 seconds)
1 | watch -n 2 free -m |
Script to auto-clear the memory/cache
As a habit, I am setting up a cronjob to check when free memory falls below 100MB, then cache is automatically cleared (freeing memory).
Script Contents: clearcache.sh
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 | #!/bin/bash # Script help inspired by https://unix.stackexchange.com/questions/119126/command-to-display-memory-usage-disk-usage-and-cpu-load ram_use=$(free -m) IFS=$'n' read -rd '' -a ram_use_arr <<< "$ram_use" ram_use="${ram_use_arr[1]}" ram_use=$(echo "$ram_use" | tr -s " ") IFS=' ' read -ra ram_use_arr <<< "$ram_use" ram_total="${ram_use_arr[1]}" ram_used="${ram_use_arr[2]}" ram_free="${ram_use_arr[3]}" d=`date '+%Y-%m-%d %H:%M:%S'` if ! [[ "$ram_free" =~ ^[0-9]+$ ]]; then echo "Sorry ram_free is not an integer" else if [ "$ram_free" -lt "100" ]; then echo "$d RAM LOW (Total: $ram_total MB, Used: $ram_used MB, Free: $ram_free MB) - Clearing Cache..." sync; echo 1 > /proc/sys/vm/drop_caches sync; echo 2 > /proc/sys/vm/drop_caches #sync; echo 3 > /proc/sys/vm/drop_caches #Not advised in production # Read for more info https://www.tecmint.com/clear-ram-memory-cache-buffer-and-swap-space-on-linux/ exit 1 else if [ "$ram_free" -lt "256" ]; then echo "$d RAM ALMOST LOW (Total: $ram_total MB, Used: $ram_used MB, Free: $ram_free MB)" exit 1 else if [ "$ram_free" -lt "512" ]; then echo "$d RAM OK (Total: $ram_total MB, Used: $ram_used MB, Free: $ram_free MB)" exit 1 else echo "$d RAM LOW (Total: $ram_total MB, Used: $ram_used MB, Free: $ram_free MB)" exit 1 fi fi fi fi |
I set the cronjob to run every 15 mins, I added this to my cronjob.
1 2 | SHELL=/bin/bash */15 * * * * root /bin/bash /scripts/clearcache.sh >> /scripts/clearcache.log |
Sample log output
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 2018-06-10 01:13:22 RAM OK (Total: 1993 MB, Used: 981 MB, Free: 387 MB) 2018-06-10 01:15:01 RAM OK (Total: 1993 MB, Used: 974 MB, Free: 394 MB) 2018-06-10 01:20:01 RAM OK (Total: 1993 MB, Used: 955 MB, Free: 412 MB) 2018-06-10 01:25:01 RAM OK (Total: 1993 MB, Used: 1002 MB, Free: 363 MB) 2018-06-10 01:30:01 RAM OK (Total: 1993 MB, Used: 970 MB, Free: 394 MB) 2018-06-10 01:35:01 RAM OK (Total: 1993 MB, Used: 963 MB, Free: 400 MB) 2018-06-10 01:40:01 RAM OK (Total: 1993 MB, Used: 976 MB, Free: 387 MB) 2018-06-10 01:45:01 RAM OK (Total: 1993 MB, Used: 985 MB, Free: 377 MB) 2018-06-10 01:50:01 RAM OK (Total: 1993 MB, Used: 983 MB, Free: 379 MB) 2018-06-10 01:55:01 RAM OK (Total: 1993 MB, Used: 979 MB, Free: 382 MB) 2018-06-10 02:00:01 RAM OK (Total: 1993 MB, Used: 980 MB, Free: 380 MB) 2018-06-10 02:05:01 RAM OK (Total: 1993 MB, Used: 971 MB, Free: 389 MB) 2018-06-10 02:10:01 RAM OK (Total: 1993 MB, Used: 983 MB, Free: 376 MB) 2018-06-10 02:15:01 RAM OK (Total: 1993 MB, Used: 967 MB, Free: 392 MB) |
I will check the log (/scripts/clearcache.log) in a few days and view the memory trends.
After 1/2 a day Ubuntu 18.04 is handling memory just fine, no externally triggered cache clears have happened 🙂
I used https://crontab.guru/every-hour to set the right schedule in crontab.
I rebooted the VM.
Swap File
FYI: Here is a handy guide on viewing swap file usage here. I’m not using swap files so it is only an aside.
After the system rebooted I checked if the swappiness setting was active.
1 2 | sudo cat /proc/sys/vm/swappiness 1 |
Yes, swappiness is set.
File System Tweaks – Write Back Cache (set at your own risk)
First, check your disk name and file system
1 | sudo lsblk -o NAME,FSTYPE,SIZE,MOUNTPOINT,LABEL |
Take note of your disk name (e.g vda1)
I used TuneFS to enable writing data to the disk before writing to the journal. tunefs is a great tool for setting file system parameters.
Warning (snip from here): “I set the mode to journal_data_writeback. This basically means that data may be written to the disk before the journal. The data consistency guarantees are the same as the ext3 file system. The downside is that if your system crashes before the journal gets written then you may loose new data — the old data may magically reappear.”
Warning this can corrupt your data. More information here.
I ran this command.
1 | tune2fs -o journal_data_writeback /dev/vda1 |
I edited my fstab to append the “writeback,noatime,nodiratime” flags for my volume after a reboot.
Edit FS Tab:
1 | sudo nano /etc/fstab |
I added “writeback,noatime,nodiratime” flags to my disk options.
1 2 3 4 5 6 7 8 9 10 | # /etc/fstab: static file system information. # # Use 'blkid' to print the universally unique identifier for a # device; this may be used with UUID= as a more robust way to name devices # that works even if disks are added and removed. See fstab(5). # # <file system> <mount point> <type> <options> <dump> <pass> # / was on /dev/vda1 during installation # <device> <dir> <fs> <options> <dump> <fsck> UUID=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx / ext4 errors=remount-ro,data=writeback,noatime,nodiratime 0 1 |
Updating Ubuntu Packages
Show updatable packages.
1 | apt-get -s dist-upgrade | grep "^Inst" |
Update Packages.
1 | sudo apt-get update && sudo apt-get upgrade |
Unattended Security Updates
Read more on Ubuntu 18.04 Unattended upgrades here, here and here.
Install Unattended Upgrades
1 | sudo apt-get install unattended-upgrades |
Enable Unattended Upgrades.
1 | sudo dpkg-reconfigure --priority=low unattended-upgrades |
Now I configure what packages not to auto update.
Edit /etc/apt/apt.conf.d/50unattended-upgrades
Find “Unattended-Upgrade::Package-Blacklist” and add packages that you don’t want automatically updated, you may want to manually update these (and monitor updates).
I prefer not to auto-update critical system apps (I will do this myself).
1 2 3 4 5 6 7 8 9 10 11 12 | Unattended-Upgrade::Package-Blacklist { "nginx"; "nginx-common"; "nginx-core"; "php7.2"; "php7.2-fpm"; "mysql-server"; "mysql-server-5.7"; "mysql-server-core-5.7"; "libssl1.0.0"; "libssl1.1"; }; |
FYI: You can find installed packages by running this command:
1 | apt list --installed |
Enable automatic updates by editing /etc/apt/apt.conf.d/20auto-upgrades
Edit the number at the end (the number is how many days to wait before updating) of each line.
> APT::Periodic::Update-Package-Lists “1”;
> APT::Periodic::Download-Upgradeable-Packages “1”;
> APT::Periodic::AutocleanInterval “7”;
> APT::Periodic::Unattended-Upgrade “1”;
Set to “0” to disable automatic updates.
The results of unattended-upgrades will be logged to /var/log/unattended-upgrades
Update packages now.
1 | unattended-upgrade -d |
Almost done.
I Rebooted
Advertisement:
GT Metrix Score
I almost fell off my chair. It’s an amazing feeling hitting refresh in GT Metrix and getting sub 2-second score consistently.
I ran a GT Metrix score again 2 days later and I get a 1.5-second result again.
WebPageTest.org Test Score
Nice. I am not sure why the effective use of CDN has an X rating as I have the EWWW CDN and Cloudflare. First Byte time is now a respectable “B”, This was always bad.
Update: I found out the longer you set cache delays in Cloudflare the higher the score.
GT Metrix has a nice historical breakdown of load times (night and day)
Google Page Speed Insight Desktop Score
This will help with future SEO rankings. It is well known that Google is pushing fast servers.
Google Chrome 69 Dev Console Audit (Desktop)
This is amazing, I never expected to get this high score. I know Google like (and are pushing) sub 1-second scores.
My site is loading so well it is time I restored some old features that were too slow on other servers
GTMetrix and WebpageTest sores are still good (even after adding bloat)
FYI: WordPress Plugins I use.
These are the plugins I use.
How I use these plugins to speed up my site.
Let’s Test UpCloud’s Disk IO in Chicago
Looks good to me, Read IO is a little bit lower than UpCloud’s Singapore data centre but still, it’s faster than Vultr. I can’t wait for more data centres to become available around the world.
Why is UpCloud Disk IO so good?
I asked UpCloud on Twitter why the Disk IO was so good.
My Answers to Questions to support
Q1) What’s the difference between backups and snapshots (a Twitter user said Snapshots were a thing)
A1) Backups and snapshots are the same things with our infrastructure.
Q2) What are charges for backup of a 50GB drive?
A2) We charge $0.06 / GB of the disk being captured. But capture the whole disk, not just what was used. So for a 50GB drive, we charge $0.06 * 50 = $3/month. Even if 1GB were only used.
Q3) What are data charges if I go over my 2TB quota?
A3) Outgoing data charges are $0.056/GB after the pre-configured allowance.
Q4) What happens if my balance hits $0?
A4) You will get notification of low account balance 2 weeks in advance based on your current daily spend. When your balance reaches zero, your servers will be shut down. But they will still be charged for. You can automatically top-up if you want to assign a payment type from your Control Panel. You deposit into your balance when you want. We use a prepay model of payment, so you need to top up before using, not billing you after usage. We give you lots of chances to top-up.
Support Tips
I think it’s time to delete my domain from Vultr in Sydney.
Deleted my Vultr domain
I deleted my Vultr domain.
Done.
More Reading on UpCloud
https://www.upcloud.com/documentation/faq/
UpCloud Server Status
What I would like
Advertisement:
Update: Post UpCloud Launch Tweaks (Awesome)
I had a look at https://www.webpagetest.org/ results to see where else I can optimise webpage delivery.
Disable dasjhicons.min.css (for unauthenticated WordPress users).
Find functions.php in the www root
1 | sudo find . -print |grep functions.php |
Edit functions.php
1 | sudo nano ./wp-includes/functions.php |
Add the following
1 2 3 4 5 6 7 | // Remove dashicons in frontend for unauthenticated users add_action( 'wp_enqueue_scripts', 'bs_dequeue_dashicons' ); function bs_dequeue_dashicons() { if ( ! is_user_logged_in() ) { wp_deregister_style( 'dashicons' ); } } |
HTTP2 Push
I added http2 to my listening servers
1 2 3 4 5 6 7 8 9 | server { root /www; ... listen 80 default_server http2; listen [::]:80 default_server http2; listen 443 ssl default_server http2; listen [::]:443 ssl default_server http2; ... |
I tested a http2 push page by defining this in /etc/nginx/sites-available/default
1 2 3 4 5 6 | location = /http2/push_demo.html { http2_push /http2/pushed.css; http2_push /http2/pushedimage1.jpg; http2_push /http2/pushedimage2.jpg; http2_push /http2/pushedimage3.jpg; } |
Once I tested that push (demo here) was working I then defined two files to push that were being sent from my server
1 2 3 4 5 6 | location / { ... http2_push /https://fearby.com/wp-includes/js/jquery/jquery.js; http2_push /wp-content/themes/news-pro/images/favicon.ico; ... } |
I used the WordPress Plugin Autoptimize to remove Google font usage (this removed a number of files being loaded when my page loads).
I used the WordPress Plugin WP-Optimize plugin into to remove comments and disable pingbacks and trackbacks.
WordPress wp-config.php tweaks
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | # Memory define('WP_MEMORY_LIMIT','1024M'); define('WP_MAX_MEMORY_LIMIT','1024M'); set_time_limit (60); # Security define( 'FORCE_SSL_ADMIN', true); # Disable Updates define( 'WP_AUTO_UPDATE_CORE', false ); define( 'AUTOMATIC_UPDATER_DISABLED', true ); # ewww.io define( 'WP_AUTO_UPDATE_CORE', false ); |
Tweaks Todo
Conclusion
I love UpCloud’s fast servers, give them a go (use my link and get $25 free credit).
I love Cloudflare for providing a fast CDN.
I love ewww.io’s automatic Image Compression and Resizing plugin that automatically handles image optimisations and pre Cloudflare/first hit CDN caching.
Read my post about server monitoring with Nixstats here.
Let the results speak for themselves (sub <1 second load times).
I hope this guide helps someone.
Please consider using my referral code and get $25 credit for free.
https://www.upcloud.com/register/?promo=D84793
Ask a question or recommend an article
Revision History
v1.9 Spelling and grammar
v1.8 Trial mode gotcha (deposit money ASAP)
v1.7 Added RSA Private key info
v1.7 – Added new firewall rules info.
v1.6 – Added more bloat to the site, still good.
v1.5 Improving Accessibility
v1.4 Added Firewall Price
v1.3 Added wp-config and plugin usage descriptions.
v1.2 Added GTMetrix historical chart.
v1.1 Fixed free typos and added final conclusion images.
v1.0 Added final results
v0.9 added more tweaks (http2 push, removing unwanted files etc)
v0.81 Draft – Added memory usage chart and added MaxIOPS info from UpCloud.
v0.8 Draft post.
This is a simple guide that demonstrates how you can log in to a host that offers the CPanel tools to backup all of your website files (and databases). Backing up your website should be done often and especially before you migrate to any another website host. I used to change hosts every few years (they don’t own your site, you do).
Advertisement:
I have a number of guides on moving away from CPanel, setting up VM’s on UpCloud, AWS, Vultr or Digital Ocean along with installing and managing WordPress from the command line copying files to a server via command line editing remote files locally etc but how do you manage a website with CPanel?
You can normal login to CPanel tools on a shared host by loading www.yourdomainnam.com/cpanel (failing that login to your domain hosts web GUI and find your CPanel interface there).
Step 1: Login to your Host
Login to your web host
Step 2: Find your CPanel Interface
Hosts are a bit different but in this case, I just click my domain to find the CPanel link.
I found it, I clicked the CPanel login.
Step 3: CPanel Applications
CPanel does offer good tools to manage your websites like web-based File Manager and Database tool called phpMyAdmin.
Aside: CPanel/Hosts Downsides
The thing I don’t like about hosts that offer CPanel is they usually limit delivery of your website to extract more money. Nothing worse than receiving Resource Limit Is Reached errors.
Also shared hosts usually lag way behind in newer software versions like PHP and MySQL (this is a security concern).
TIP: You can scan your site for vulnerabilities using Qualsys Freescan, Zap or Kali Linux.
Here is a security scan of a shared host (with CPanel) that I was using in 1999. Note the high vulnerabilities and old version of Linux.
Also, a shared host will often overcharge you (e.g $150 a year) for a poorly configured SSL certificate.
This was an SSL cert I paid $150 a year for (evaluated with SSL Labs SSL Test) on a shared host with CPanel.
Aside: Self Managed Upsides
After I moved my domain to a self-managed virtual machine I migrated WordPress, set up a free SSL certificate, sped up my site with a CDN, setup Cloudflare, setup better TLS security etc
When you manage your own server you can install a free SSL certificate in under 1 minute.
Below is my SSL certificate. A strong SSL certificate will increase search engine traffic
Aside: Compare Shared host speed v Self Managed
FYI: https://gtmetrix.com/ is a great site for measuring the speed of a website (shared of self-managed). I found great speed improvements after moving away from a host offering CPanel, tweaking the server and setting up cloudflare. A self-managed server will allow you to tweak anything you want.
GTMetrix results:
I like how self-managed servers allow you to scale the server’s resources yourself, move servers or add storage etc.
Aside: SSL Certificate
If you have an SSL cert you should test it often as vulnerabilities pop up from time to time.
FYI: All sites will soon require an SSL certificate to be sent traffic from search engines (no SSL = lower traffic).
SSL Test my site: https://dev.ssllabs.com/ssltest/analyze.html?d=fearby.com&s=104.27.154.69
Now enough with the self-managed serve asides and back to how to backup your website with CPanel tools.
Step 4: Backup your web files in CPanel
Use the File Explorer app in CPanel
Highlight all files that you want to backup (highlight everything but not past backup files).
View the files to compress summary
Click Compress Files(s) and view the backup progress
You can now download the backup zip file in your browser (click the file and click Download).
Download Progress.
Step 5: Backup your database in CPanel
Now we need to backup any MySQL database(s) that may be used by WordPress
Open the phpMyAdmin app in CPanel.
FYI: Alternatively, you can use a free tool called Adminer to backup and restore our database.
Click your WordPress database (on the left). You can identify your current WordPress database by opening the wp-config.php file.
The first step is to perform an online cold backup of the WordPress database.
Now you have an online cold spare that you can use just in case the original database corrupts itself. You can rename the database or configure WordPress to point to this new database if need be.
Now let’s download a copy of the database (Repeat for multiple databases).
You should now have a backup of your website in a zip file and an export of your database in a .sql text file, SQL files can be re-imported to databases later.
TIP: Backup often.
I hope this guide helps someone.
Ask a question or recommend an article
Revision History
v1.0 Initial post
Adminer is a free GUI tool that can you can easily install on a PHP web server. Adminer allows you to easily connect to your MySQL instance, create databases/tables/indexes/rows and backup/import databases and much more.
Advertisement:
You can read my other posts on Useful Linux Terminal Commands and Useful OSX Terminal Commands.
I used to use phpMyAdmin to manage MySQL databases on AWS, Digital Ocean and Vultr but switched to Adminer due to forgotten issues. You can always manage MySQL via command line but that is quite boring.
The below screenshots were taken on my local Development Mac Laptop (with optional OSX Apache SSL Setup (that reports “Not Secure” (but it is good enough to use locally)). I prefer to code in SSL and warn when SSL is not detected.
Downloading and Installing Adminer
Navigate to https://www.adminer.org/ and click Download.
Click English only (.php file)
Save the Adminder for MySQL (.php) file to your web server and give it a random name and put in a folder also with a random name (I use https://www.grc.com/passwords.htm to generate strong password).
Tip: Uploading this file to a live serve offers hackers and unauthorized people potential access to your MySQL server. I would remove this file from live serves when you are not using it not to be sure.
Tip: Read my guide here on setting up NGINX, MySQL and PHP here. Basically I did this to setup MySQL on Ubuntu 16.04.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | sudo apt-get install mysql-common sudo apt-get install mysql-server mysql --version >mysql Ver 14.14 Distrib 5.7.19, for Linux (x86_64) using EditLine wrapper sudo mysql_secure_installation >Y (Valitate plugin) >2 (Strong passwords) >N (Don't chnage root password) >Y (Remove anon accounts) >Y (No remote root login) >Y (Remove test DB) >Y (Reload) service mysql status > mysql.service - MySQL Community Server |
TIP: Ensure MySQL is secure and has a good root password, also consider setting up Ubuntu Firewalls and Securing Ubuntu. Also ensure the Server is patched and does not have exploits like Spectre and meltdown.
Now you can access your Admirer php file on your Web Server (hopefully with an obfiscated name).
Login to Adminer with your MySQL root password.
Click Create databaase
Give the database a name and choose the character coding standard (e.g UTF8 ceneral ci). Different standards have different performance impacts too.
Now that you have a database you can create a table.
Consider adding an auto-incrementing ID and say a Key and Value varchar column.
When the table is created you can add a row to the table.
I created one with a “TestKey” and “TestValue” row.
The row was inserted.
The final thing to do is add a database user that code can connect to the database with. Click Privileges.
Click Create user
Tick All privileges and click Save
Now the user is added to the database
Let’s create a PHP file and talk to the database. Let’s use parameterized queries
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 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 | <?php date_default_timezone_set('Australia/Sydney'); echo "Last modified: " . date ("F d Y H:i:s.", getlastmod()) . "<br /><br />"; // Turn on if you need to see errors // error_reporting(E_ALL); // ini_set('display_errors', 0); $dbhost = '127.0.0.1'; $dbname = 'dbtest'; $dbusername = 'dbtestuser'; $dbpassword = '*****************************************''; $con = mysqli_connect($dbhost, $dbusername, $dbpassword, $dbname); // Turn on debug stuff if you need it // echo var_dump($con); // printf(" - Error: %s.n", $stmt->error); if($con->connect_errno > 0){ printf(" - Error: %s.n", $stmt->error); die("Error: Unable to connect to MySQL"); } else { echo "Charset set to utf8<br />"; mysqli_set_charset($con,"utf8"); } if (!$con) { echo "Error: Unable to connect to MySQL (E002)" . PHP_EOL; echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL; echo "Debugging error: " . mysqli_connect_error() . PHP_EOL; exit; } else { echo "Database Connection OK<br />"; echo " Success: A proper connection to MySQL was made! The $dbname database is great." . PHP_EOL . "<br />"; echo " - Host information: " . mysqli_get_host_info($con) . PHP_EOL . "<br />"; echo " - Server Info: '" . mysqli_get_server_info($con) . "'<br />"; echo " - Server Protocol Info : ". mysqli_get_proto_info($con) . "<br />"; echo " - Server Version: " . mysqli_get_server_version($con) . "<br />"; //echo " - Server Connection Stats: " . print_r(vmysqli_get_connection_stats($con)) . "<br />"; echo " - Client Version: " . mysqli_get_client_version($con) . "<br />"; echo " - Client Info: '" . mysqli_get_client_info() . "'<br />"; echo "Ready to Query the database '$dbname'.<br />"; // Input Var's that are parameterized/bound into the query statement $in_key = mysqli_real_escape_string($con, 'TestKey'); // Output Var's that the query fills after querying the database // These variables will be filled with data from the current returned row $out_id = ""; $out_key = ""; $out_value = ""; echo "1. About to query the database: '$dbname'<br />"; $stmt = mysqli_stmt_init($con); $sql = "SELECT testid, testkey, testvalue FROM tbtest WHERE testkey = ?"; echo "SQL: $sql (In = $in_key)<br /"; if (mysqli_stmt_prepare($stmt, $sql)) { echo "2. Query Returned<br />"; /* Type specification chars Character Description i corresponding variable has type integer d corresponding variable has type double s corresponding variable has type string b corresponding variable is a blob and will be sent in packets */ mysqli_stmt_bind_param($stmt, 's', $in_key); mysqli_stmt_execute($stmt); mysqli_stmt_bind_result($stmt, $out_id, $out_key, $out_value); mysqli_stmt_fetch($stmt); // Do something with the 1st returned row echo " - Row: ID: $out_id, KEY: $out_key, VAL: $out_value <br />";// // Do we have more rows to process while($stmt->fetch()) { // Output returned values echo " - Row: ID: $out_id, KEY: $out_key, VAL: $out_value <br />";// } mysqli_stmt_close($stmt); echo "Done<br />"; } else { echo "3. Error Querying<br/>"; printf(" - Error: %s.n", $stmt->error); } } ?> |
Result
If you don’t have a server check out my guides on AWS, Digital Ocean and Vultr.
Happy coding and I hope this helps someone.
Donate and make this blog better
Ask a question or recommend an article
Revision History
v1.0 Initial Version
Sanitising user input is a golden rule with web developing (see https://www.owasp.org/index.php/Input_Validation_Cheat_Sheet ), here is my code snippet to sanitise and parameterise MySQL queries in PHP 7.0.
Advertisement:
First, watch and follow @jawache (Asim Hussein) demo how common hacks happen and why you should update and patch software often, sanitise user data and set up a firewall.
I have blogged before on how to set up a Vultr server and configure it, How to secure Ubuntu in the Cloud, How to run an Ubuntu System Audit and Beyond SSL with Content Security Policy, Public Key Pinning etc but a 100% secure server is impossible as zero-day exploits and flaws (e.g WPA WiFi) reminds us how limited technology lifespans can be. Yes, you can setup firewalls on Ubuntu and WordPress but you are only one exploit away from being hacked.Below is my code snippet (in PHP) to sanitise incoming data, query a MySQL database with object-oriented calls in PHP 7.0 and return data in variables. I have set up a firewall to block access to MySQL and non-essential ports (use https://www.shodan.io/ to test your server’s ports). I was using older deprecated PHP 5 era database calls and I researched newer calls available in PHP 7.0.
When you log in to an Ubuntu server and it says the following you should update
1 2 | 89 packages can be updated. 35 updates are security updates. |
Also update software, node, npm etc
This code outputs too much information but will help you setup and get data on your servers (as long as you replace your database, table and field names).
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 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | <?php echo "Last modified: " . date ("F d Y H:i:s.", getlastmod()) . "<br /><br />"; date_default_timezone_set('Australia/Sydney'); $dbhost = '127.0.0.1'; $dbusername = 'themysqlaccount'; $dbpassword = 'themysqlpassword'; $dbname = 'thedatabasename'; $con = mysqli_connect($dbhost, $dbusername, $dbpassword, $dbname); //Debug stuff //echo var_dump($con); //printf(" - Error: %s.\n", $stmt->error); if($con->connect_errno > 0){ printf(" - Error: %s.\n", $stmt->error); die("Error: Unable to connect to MySQL (E001)"); } else { echo "Charset set to utf8<br />"; mysqli_set_charset($con,"utf8"); } if (!$con) { echo "Error: Unable to connect to MySQL (E002)" . PHP_EOL; echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL; echo "Debugging error: " . mysqli_connect_error() . PHP_EOL; exit; } else { echo "Database Connection OK<br />"; echo " Success: A proper connection to MySQL was made! The my_db database is great." . PHP_EOL . "<br />"; echo " - Host information: " . mysqli_get_host_info($con) . PHP_EOL . "<br />"; echo " - Server Info: '" . mysqli_get_server_info($con) . "'<br />"; echo " - Server Protocol Info : ". mysqli_get_proto_info($con) . "<br />"; echo " - Server Version: " . mysqli_get_server_version($con) . "<br />"; //echo " - Server Connection Stats: " . print_r(mysqli_get_connection_stats($con)) . "<br />"; echo " - Client Version: " . mysqli_get_client_version($con) . "<br />"; echo " - Client Info: '" . mysqli_get_client_info() . "'<br />"; echo "Ready to Query the database '$dbname'.<br />"; // Input Var's that are parameterized/bound into the query statement // I pre fill three vaiables with known fields in my users table // Goot article in manual sanitization of strings in PHP http://php.net/manual/en/filter.filters.sanitize.php $in_username = mysqli_real_escape_string($con, 'FearTec'); $in_f_guid = mysqli_real_escape_string($con, '5161a571-4a51-468d-9e96-6a5db5d35b1c'); $in_mobile = mysqli_real_escape_string($con,'0456629533'); // Output Var's that the query fills after querying the database // These variables will be filled with data from the current returned row $out_id = ""; $out_f_guid = ""; $out_username = ""; $out_mobile = ""; echo "1. About to query the database: '$dbname'<br />"; $stmt = mysqli_stmt_init($con); if (mysqli_stmt_prepare($stmt,"SELECT id, username, guid, user_mobile FROM thedatabasename WHERE username = ? AND guid = ? AND user_mobile = ?")) { echo "2. Query Returned<br />"; /* Type specification chars Character Description i corresponding variable has type integer d corresponding variable has type double s corresponding variable has type string b corresponding variable is a blob and will be sent in packets */ mysqli_stmt_bind_param($stmt, 'sss', $in_username, $in_guid, $in_mobile); mysqli_stmt_execute($stmt); mysqli_stmt_bind_result($stmt, $out_id, $out_username, $out_guid, $out_mobile); mysqli_stmt_fetch($stmt); // Do something with the 1st returned row echo " - Row: ID: $out_id, GUID: $out_guid, USR: $out_username, MOB: $out_mobile";// // Do we have more rows to process while($stmt->fetch()) { // Deal with other rows echo " - Row: ID: $out_id, GUID: $out_f_guid, USR: $out_username, MOB: $out_mobile<br />"; } mysqli_stmt_close($stmt); echo "c<br />"; } else { echo "3. Error Querying<br/>"; printf(" - Error: %s.\n", $stmt->error); } } ?> |
Returned Data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Last modified: November 01 2017 16:43:01. Charset set to utf8 Database Connection OK Success: A proper connection to MySQL was made! The my_db database is great. - Host information: 127.0.0.1 via TCP/IP - Server Info: '5.7.19-0ubuntu0.16.04.1' - Server Protocol Info : 10 - Server Version: 50719 - Client Version: 50012 - Client Info: 'mysqlnd 5.0.12-dev - 20150407 - $Id: b5######################## $' Ready to Query the database 'thedatabasename'. 1. About to query the database: 'thedatabasename' 2. Query Returned - Row: ID: 1, GUID: 0000000-0000-0000-0000-000000000001, USR: Bob, MOB: 1234567890 - Row: ID: 2, GUID: 0000000-0000-0000-0000-000000000002, USR: Joe, MOB: 1234567891 - Row: ID: 3, GUID: 0000000-0000-0000-0000-000000000003, USR: Jane, MOB: 1234567892 |
Variable Bind Parameter Types
When you bind an incoming variable you can inform MySQL what the data type is expected to be.
1 2 3 4 5 6 | mysqli_stmt_bind_param: Type specification chars Character Description i corresponding variable has type integer d corresponding variable has type double s corresponding variable has type string b corresponding variable is a blob and will be sent in packets |
Debug Options
Errors Enabled: Turn on PHP Debug Errors On
Turning on errors on production servers is bad but on on development.
First, find php.ini files
1 2 3 4 5 6 7 8 9 10 | locate php.ini /php.ini.bak /.c9/metadata/workspace/etc/php/7.0/fpm/php.ini /etc/php/7.0/apache2/php.ini /etc/php/7.0/cli/php.ini /etc/php/7.0/fpm/php.ini /etc/php/7.0/phpdbg/php.ini /usr/lib/php/7.0/php.ini-development /usr/lib/php/7.0/php.ini-production /usr/lib/php/7.0/php.ini-production.cli |
Edit your appropriate PHP file
1 | sudo nano /etc/php/7.0/fpm/php.ini |
And turn on Error reporting.
Restart PHP and NGINX
1 2 | sudo /etc/init.d/php7.0-fpm restart sudo /etc/init.d/nginx restart |
If you need to view your active php.ini file or see php configuration settings, add this to a .php file on your web server and view it’s contents.
1 2 3 | <?php phpinfo() ?> |
It is amazing how clear errors can be
Dump Connection Vars: PHP mysqli_connect: var_dump($con)
1 | echo var_dump($con); |
Output:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | public 'affected_rows' => int 0 public 'client_info' => string 'mysqlnd 5.0.12-dev - 20150407 - $Id: b5###############################3 $' (length=79) public 'client_version' => int 50012 public 'connect_errno' => int 0 public 'connect_error' => null public 'errno' => int 0 public 'error' => string '' (length=0) public 'error_list' => array (size=0) empty public 'field_count' => int 0 public 'host_info' => string '127.0.0.1 via TCP/IP' (length=20) public 'info' => null public 'insert_id' => int 0 public 'server_info' => string '5.7.19-0ubuntu0.16.04.1' (length=23) public 'server_version' => int 50719 public 'stat' => string 'Uptime: 1828089 Threads: 1 Questions: 15702 Slow queries: 0 Opens: 1529 Flush tables: 1 Open tables: 1461 Queries per second avg: 0.008' (length=142) public 'sqlstate' => string '00000' (length=5) public 'protocol_version' => int 10 public 'thread_id' => int 7367 public 'warning_count' => int 0 |
Show Environment Vars: mysqli_get_host_info, mysqli_get_proto_info, mysqli_get_server_version, mysqli_get_client_version and mysqli_get_client_info.
1 2 3 4 5 6 7 8 | echo " Success: Database connection OK." . PHP_EOL . "<br />"; echo " - Host information: " . mysqli_get_host_info($con) . PHP_EOL . "<br />"; echo " - Server Info: '" . mysqli_get_server_info($con) . "'<br />"; echo " - Server Protocol Info : ". mysqli_get_proto_info($con) . "<br />"; echo " - Server Version: " . mysqli_get_server_version($con) . "<br />"; //echo " - Server Connection Stats: " . print_r(mysqli_get_connection_stats($con)) . "<br />"; echo " - Client Version: " . mysqli_get_client_version($con) . "<br />"; echo " - Client Info: '" . mysqli_get_client_info() . "'<br />"; |
Output:
1 2 3 4 5 6 7 | Success: Database connection OK. - Host information: 127.0.0.1 via TCP/IP - Server Info: '5.7.19-0ubuntu0.16.04.1' - Server Protocol Info : 10 - Server Version: 50719 - Client Version: 50012 - Client Info: 'mysqlnd 5.0.12-dev - 20150407 - $Id: b5######################################## $' |
Show errors in failed if statements: mysqli_stmt_prepare else
1 | printf(" - Error: %s.\n", $stmt->error); |
Output: