IoT, Code, Security, Server Stuff etc

Views are my own and not my employer's.

Personal Development Blog...

Coding for fun since 1996, Learn by doing and sharing.

Buy a domain name, then create your own server (get $25 free credit)

View all of my posts.

Setup

Setting up the free MySQL database server on Windows 10

by

This guide assumes you are a beginner using Windows 10 and maybe have a default website configured on Windows 10 using the built in Information Server (IIS) along with PHP (e.g PHP 7.3.4 or greater).

If you are have never used Internet Information Server (IIS) then XAMPP is a great way (single install) to setup a web server, database server and PHP with little fuss.

In this case I will manually install MySQL Community Server to add alongside Internet Information Server (IIS) on Windows 10.

Downloading
MySQL Server (Community Edition)

Go to here and click MySQL Installer for Windows.

Screenshot of Download installer link

Click MySQL Installer for Windows.

fyi: I sent myself on a goose chase as I could only see a 32 bit installer (I spent days trying to find a 64-bit installer or manual install binaries that were 64 bit). I should have read the bit that said “MySQL Installer is 32 bit, but will install both 32 bit and 64 bit binaries.“

MySQL Installer is 32 bit, but will install both 32 bit and 64 bit binaries.

You can read the installer documentation here if you wish.

I downloaded the larger of the two available installers (one 16MB the other 300+ MB, same version etc.). I had to login with an Oracle ID to start the download.

Download MySQL Installer screenshot

Install file downloaded

Installing MySQL Server (Community Edition)

I started the installer (accepted the licence agreement)

I accepted the licence agreement

I selected “Full Install“

I could have selected server only or custom.

I selected Full Install

I downloaded and installed Python 3.7, Thanks MySQL Installer for adding a link to the Python download.

I Installed Python 3.7

After Python was installed I clicked refresh in MySQL and now MySQL can see Python 3.7.

Now MySQL can see Python 3.7.

I had a Visual Studio plugin install error (because Visual Studio was not installed).

Visual Studio plugin install error (Visual Studio is not installed)

Full Install (all components selected) reported the items are ready for install.

Full Install (all components selected)

Installation status complete.

Installation status list (Full Install)

List of items to configure (post install)

List of items to configure.

I setup a standard MySQL Server (not a Cluster)

I setup a standard MySQL Server (not a Cluster)

I setup MySQL as a standard Development computer on port 3306 over TCP/IP (no Named Pipe, Shared Memory etc).

I setup MySQL as a standard Development computer on port 3306 over TCP/IP.

I enforced strong passwords.

I enforced strong passwords.

I set a root password and added few app usernames (with passwords).

I set a root password and a few app usernames (with passwords)

I named the MySQL Instance and set it to auto start when windows starts as a standard system account.

I named the MySQL Instance and set it to auto start when windows starts as a standard account.

Post installation in progress.

Installation in progress.

I accepted the defaults for the next screen (configuring routers). I tested the connection to MySQL Server.

Connect to MySQL server test screen.

Installation complete.

Installation complete screen.

MySQL WorkBench

I opened the MySQL Workbench software and viewed the server instance information. Nice.

MySQL Workbench Instance information

MySQL Workbench server performance graphs are nice.

MySQL Workbench performance graphs.

I am used to Adminer for managing MySQL on Linux so I install that now.

Install Adminer (formerly phpMinAdmin)

I prefer the PHP based Admirer database management tool from here.

https://adminer.net website screenshot.

I downloaded a single PHP file and placed it in my IIS website root folder (as /adminer.php).

I tried to connect to my MySQL instance but received this error “The server requested authentication method unknown to the client”.

Unable to connect to MySQL with Adminer: Error "he server requested authentication method unknown to the client"

I googled and checked that I had a MySQL extension in php.ini (It did).

I opened MySQL Workbench and opened Manage Server Connections and located my my.ini file location (“C:\ProgramData\MySQL\MySQL Server 8.0\my.ini“). I opened my my.ini in a text editor and commented out the following line

#default_authentication_plugin=caching_sha2_password

and added 

default_authentication_plugin= mysql_native_password

I saved the my.ini file, stopped and started the MySQL Service.

MySQL Workbench restarting database service UI screenshot.

I opened the MySQL Workbench and ran a query (File then “New Query Tab“) to set/reset the password.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password
BY 'your_password_goes_here';

Screenshot

Reset root password SQL statement "ALTER USER

I then tested logging in to MySQL with Adminer.

Adminer view showing default databases installed by MySQL.

Success, I can now created databases and tables.

Adminer create datbase table screenshot showing a table with 4 fields.

I hope this helps someone.

Setup a Certification Authority Authorization (CAA) DNS record(s) to prevent https cert issue/misuse

by

On February 22nd 2017 CAA’s that issue https certificates are required to check what CAA’s are allowed to issue HTTP’s certificates for a website. To limit who can create HTTP’s certificates for your site all you need to do is specify a number of DNS records.

DNSSEC

Before adding DNS CAA records ensure you have enabled DNSSEC for extra security, this is not needed to setup CAA records but it’s a good idea.

DNSSEC Explained

Read my post here on setting up DNSSEC with Cloudflare here.

Namecheap allows you do set DNSSEC with 1 click (making the above guide not required unless you use Cloudflare).

One Click Enable DNS SEC

Testing DNSSEC

First, test DNSSEC on your website here: https://dnssec-analyzer.verisignlabs.com/ (I already have DNSSEC enabled)

I use Namecheap for buying domains and HTTP’s certs (you can buy a new domain here). Namecheap allow you to easily enable DNSSEC and CAA DNS records.

Read Namecheap’s CAA guide here.

Scott Helme tagged a great write up on CAA here.

Testing CAA (on your website)

Go to https://dev.ssllabs.com/ssltest/ and scan your website

https://dev.ssllabs.com screenshot showing a domain input box

You will see if CAA is enabled after the https test is complete (scroll past the rating)

https://dev.ssllabs.com scan showing A+

In my case CAA records were not detected.

Adding DNS CAA records at Namecheap

I logged into Namecheap, clicked Manage domain and clicked the Advanced DNS tab

Screenshot showing Namecheap Advanced DNS screen.
I click Add New Record (DNS), then I selected CAA
Screenshot of add NDS CAA record at Namecheap

Here are records for my main domain (allowing Comodo/Sectigo HTTP’s certificates only)

Type, Host, Value, TTL

CAA Record @ 0 issue "comodoca.com" Automatic
CAA Record @ 0 issue "comodo.com" Automatic
CAA Record @ 0 issue "usertrust.com" Automatic
CAA Record @ 0 issue "trust-provider.com" Automatic
CAA Record @ 0 issue "sectigo.com" Automatic

Here is my record allowing a sub domain (allowing Lets Encrypt HTTP’s certificates only)

Type, Host, Value, TTL

CAA Record audit.fearby.com 0 issue "letsencrypt.org" Automatic

It is also possible to setup email alerts of CAA violations where CAA’s support it. I setup a [email protected] email alias.

Type, Host, Value, TTL

CAA Record audit.fearby.com 0 iodef "mailto:[email protected]" Automatic
CAA Record@ 0 iodef "mailto:[email protected]" Automatic

Image of my final Namecheap DNS config.

Screenshot os Namecheap DNS entries (table below)

Test CAA Records

I visited https://dev.ssllabs.com/ssltest/ and performed a final scan.

CNS CAA Final scan now passes at dev.ssllabs.com

Pass 🙂

I do have real time remote server monitoring reporting on https presence and uptime, read the post here.

Nixstats graphs

Plug(s)

Warning

I had an issue where I failed to update my DNS (and define a CAA record) for the sub domain used for Nixstat reporting. I was receiving this error.

Connection not private warning.

dev.ssllabs.com was reporting the cert expired?

dev.ssllabs.com ssl report

The awesome chat support (Vincent) over at Nixstats found out it was because I did not have CAA record for the sub domain allowing “letsencrypt.org” to generate certs.

Created CAA record for status.feabry.com (CAA 0 issue "letsencrypt.org"

If you manually renew a Lets Encrypt cert with the following command without a CAA record you will see an error

> certbot -q renew

Error Output

Attempting to renew cert (subdomain.fearby.com) from /etc/letsencrypt/renewal/
subdomain.fearby.com.conf produced an unexpected error: Failed authorization procedure.
subdomain.fearby.com (http-01): urn:acme:error:caa :: CAA record for
subdomain.fearby.com prevents issuance. Skipping.
All renewal attempts failed.

DNS additions and changes take a while to propagate so monitor Whats My DNS for change status

https://www.whatsmydns.net/#CAA/status.fearby.com

Thanks for reading.

For simplicity I have removed all sub domain CAA settings for records and only set global ones

Revision History

v1.2 Troubleshooting

v1.1 Plugs

v1.0 initial Post

Setup a dedicated Debian subdomain (VM), Install MySQL 14 and connect to it from a WordPress on a different VM

by

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

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

Domain names for just 88 cents!

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 Debian Sevrer

Deploy a Debian server setup steps:

  1. Login to UpCloud and go to Create server.
  2. Name your Server (use a fully qualified domain name)
  3. Add a description.
  4. Choose your data centre (Chicago for me)
  5. Choose the server specs (1x CPU, 50GB Disk, 2GB Memory, 2TB Traffic for me)
  6. Name the Primary disk
  7. Choose an operating system (Debian for me)
  8. Select an SSH Key
  9. Choose misc settings
  10. Click Deploy server

After 5 mins your server should be deployed.

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.

Add a DNS record with your domain registra A NAMe = IPV4 and AAAA Name = IPv6

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.

Copy Firewall rules option at UpCloud

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

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.

Download the MySQL deb Package

Install the package

Update the system again

Install MySQL on Debian

Secure MySQL

You can secure the MySQL server deployment (set options as needed)

Install NGINX

I installed NGINX to allow Adminer MySQL GUI to be used

I ran these commands to install NGINX.

I edited my NGINX configuration as required.

  • Set a web server root
  • Set desired headers
  • Optimized NGINX (see past guides here, here and here)

I reloaded NGINX

Install PHP

I followed this guide to install PHP on Debian.

Install PHP 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

Set MySQL Permissions

Open MySQL

I ran these statements to grant the user logging in on the nominate IP’s access to MySQL.

Reload permissions in MySQL

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).

Tell MySQL to listen on

Edit “/etc/mysql/my.cnf”

Added..

I guess you could change the port to something random???

Restart MySQL

Install a second local firewall on Debian

Install ufw

Do add the IP of your desired server or VPN to access SSH

Do add the IP of your desired server or VPN to access WWW

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)

Do add UpCloud DNS Servers to your firewall

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

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

Reboot

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.

Check to see if MySQL is Disabled

Yep

Test access to the database server in PHP code

Add to dbtest.php

<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

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.

You should see something like this on a server that has access to see port 80/443 (a web server)

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.

Screeshot of https://www.infobyip.com/tcpportchecker.php

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

define('DB_HOST', 'localhost');

add (read the update below, I changed the DNS IP to the Private IP to have free traffic)

//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

Restart PHP-FPM

Conclusion

Nice, I seem to have shaved off 0.3 seconds in load times (25% improvement)

1sec gtmtrix load time

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.

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.

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

(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

//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

Restart MySQL

TIP: Enable UpCloud Backups

Do setup automatic backups (and or take manual backups). Backups are an extra charge but are essential IMHO.

UpCloud backups

Troubleshooting

If you can’t access MySQL log back into MySQL

and run

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

[contact-form-7 id=”30″ title=”Ask a Question”]

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

How to setup PHP FPM on demand child workers in PHP 7.x to increase website traffic

by

This blog post will show you how to setup PHP FPM on-demand child workers in PHP 7.x to increase website traffic.

My blog was experiencing a number of slow page loads and often running “sudo service php7.0-fpm restart” would resolve the problem.  I have blogged before about setting up Ubuntu Servers on AWS, Digital Ocean and Vultr but this post is about debugging and speeding up PHP on Ubuntu self-managed servers.

Background

I tried the normal tweaks in “/etc/php/7.0/fpm/php.ini” like

memory_limit = 512M

I setup servers like this.

Temporary Fix

I had even set up a temporary NGINX and php7.0-fpm restart ever 5 and 1-minute respectively until I had time to look into this.

Debug

I checked out the PHP7.0-fpm.log and I found the following

Setting up a PHP-FPM pool

Read the official guide here on configuring PHP FPM pools etc.

I edited “/etc/php/7.0/fpm/pool.d/www.conf” and added the following to set up a pool of PHP-FPM servers.

You may need more or fewer child processes depending on your needs and free memory.

After editing the PHP-FPM config file restart PHP-FPM

Restart Nginx

You will be able to view the PHP child process status by typing the following

You can use htop (commands here) to see child PHP processes in the pool and to verify free memory.

php-pool

This command is good for watching free memory on a server

I prefer to use up free memory (if available) and leave about 100mb free.

Hope this helps someone.

Donate and make this blog better

Ask a question or recommend an article

[contact-form-7 id=”30″ title=”Ask a Question”]

Revision History

v1.0 Initial Post

Setup an Ubuntu Desktop GUI on a Vultr VM Remotely

by

Lubuntu is a free Ubuntu desktop GUI that you can install on a Vultr or Digital Ocean VM in the cloud, more information on lubuntu here.

Introduction

When you set up a Vultr server (see my guide here on setting up a Vultr VM) you can also install an Ubuntu GUI Desktop if you prefer GUI access to your server (over SSH and the terminal command line).

After you have set up your server  (fyi: you can buy a server here on Vultr for as low as $2.5 a month or buy a Digital Ocean server for $5 a month) and secure it (perform an optional security audit).  Don’t forget to add a free SSL certificate.

Vultr allows you to log into an admin panel and access your servers web console (text or GUI)

Ubuntu GUI

Setup lubuntu

From the command line type

TIP: If you receive failures about “Temporary failure resolving ‘archive.ubuntu.com’” edit the file “/etc/resolv.conf” and add “nameserver 8.8.8.8” Update will then work.

There are two versions of the Ubuntu Desktop

1) Light Desktop

A Light version of lubuntu is available for low memory VMs

To install a light version run

Note: lubuntu-core should work on a VM with as low as 128MB of RAM. Read more here.

or

2) Full Desktop

If you have more memory (> 1GB RAM)  you can run a richer environment

Note: lubuntu-desktop will take a lot longer to install over the core version.

Install the Firefox browser

apt-get install firefox

Reboot Your Server

sudo shutdown -r now

After your server reboots login to the Vultr admin panel and click the Web Console icon (computer screen icon, 5 in from the right).

Ubuntu GUI

Core Version

After you login to the web console, you will receive this screen (for the core version).

Ubuntu GUI Login

You can log in as any existing user instead of a guest if you wish.

Ubuntu GUI Login Other

You have a limited desktop and environment with the core version.

Ubuntu

When you log out you can choose from the options below.

Logout

Full Desktop version

As before open the Vultr web console for the server.

Ubuntu GUI

If your lubuntu desktop session does not load try running

You can then log into the Ubuntu desktop (same as the light version above).  You will be able to use FireFox, use a Terminal, install packages with the Lubuntu Software Center and browse files. The start menu is per packed with software too,

Ubuntu

The full desktop has loads more software pre-installed (and available to install via the software center).

I opened a few apps and my server only used 472MB ram (it was using about 420MB before I installed lubuntu), all CPU cores were mostly idle.

Common Folders:

etc

Lubuntu Sofware Center

Installing MySQL workbench

MySQL Workbench

I Installed MySQL Workbench

MySQL Workbench

More software is available in the lubuntu Software Center

Synaptic Package Manager

The Synaptic package manager is also available if you are familiar with that.

Synaptic

Troubleshooting

You may receive this error on startup.

Error

Review your: /root/.profile file

Erroring line

It is safe to ignore this error message as the command is just trying to generate a suggests exit result. More on the mesg command here.

Check out the extensive Hardening a Linux Server guide at thecloud.org.uk: https://thecloud.org.uk/wiki/index.php?title=Hardening_a_Linux_Server

Enjoy

Donate and make this blog better




Ask a question or recommend an article
[contact-form-7 id=”30″ title=”Ask a Question”]

Revision History

v1.2 added link to hardening linux server.

v1.1 fixed typos (26th Sep 2017)

v1.0 Initial Post (26th Sep 2017)

etc