Code, Security and Server Stuff

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

MySQL

I moved my domain to UpCloud (on the other side of the world) from Vultr (Sydney) and could not be happier with the performance.

by

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

This is a screenshot of my old host giving a Usage Limit Exceeded error

The straw that broke the camel’s back was their demand of $150/year for a dodgy SSL certificate.

This is a screenshot of a crappy C rated SSL certificate (measured at ssl labs)

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.

Domain names for just 88 cents!

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

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.

Blog Growth

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

This screenshot is showing poor www.gtmetrix.com scores , pool google page speed index scores and upgrading from 1GB to 2GB memory on my server.

Google Chrome Developer Console Audit Results on Vultr hosted website were not very good (I stopped checking as nothing helped).

This is a screenshot showing poor site performance (screenshot taken in Google Dev tools audit feature)

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

This is a screenshot showing Vultr.com servers getting half the read and write disk io performance compared to upcloud.com.

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.

This is am image showing iozone results breakdown chart (kbytes per sec on vertical axis, file size in horizontal axis and transfer size on third access)

(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 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?

This image sows most of my visitors are in the US

My site’s traffic is growing and I need to ensure the site is fast enough in the future.

This image shows that most of my sites visitors are hitting my site on week days.

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

When you click the link above you will receive 25$ to try out serves for 3 days. You can exit his trail and deposit $10 into UpCloud.

Trial Limitations

The trial mode restrictions are as following:

* Cloud servers can only be accessed using SSH, RDP, HTTP or HTTPS protocols
* Cloud servers are not allowed to send outgoing e-mails or to create outbound SSH/RDP connections
* The internet connection is restricted to 100 Mbps (compared to 500 Mbps for non-trial accounts)
* After your 72 hours free trial, your services will be deleted unless you make a one-time deposit of $10

UpCloud Links

The UpCloud support page is located here: https://www.upcloud.com/support/

More UpCloud links to read:

Signing up to UpCloud

Navigate to https://upcloud.com/signup and add your username, password and email address and click signup.

New UpCloud Signup Page

Add your address and payment details and click proceed (you don’t need to pay anything ($1 may be charged and instantly refunded to verify the card)

Add address and payment details

That’s it, check yout email.

Signup Done

Look for the UpCloud email and click https://my.upcloud.com/

Check Email

Now login

Login to UpCloud

Now I can see a dashboard 🙂

UpCloud Dashboard

I was happy to see 24/7 support is available.

This image shows the www.upcloud.com live chat

I opted in for the new dashboard

UpCloud new new dashboard

Deploy My First UpCloud Server

This is how I deployed a server.

Note: 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:

  1. Enter a short server description.
  2. Choose a location (Frankfurt, Helsinki, Amsterdam, Singapore, London and Chicago)
  3. Choose the number of CPU’s and amount of memory
  4. Specify disk number/names and type (MaxIOPS or HDD).
  5. Choose an Operating System
  6. Select a Timezone
  7. Define SSH Keys for access
  8. Allowed login methods
  9. Choose hardware adapter types
  10. Where the send the login password

Deploy Server

FYI: How to generate a new SSH Key (on OSX or Ubuntu)

Output

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.

  • The public key needs to be added to the server to allow access.
  • The private key needs to be added to any local ssh program used for remote access.

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:

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.

This image shows I cant deploy servers with 2/GB in trial modeExiting 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).

View Billing Details

Make a manual 1-time deposit of $10 to exit trial mode.

Deposit $10 to exit the trial

FYI: Server prices are listed below (or view prices here).

UpCloud Pricing

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 takes a few minutes and depending on how you specified a password may be emailed to you.

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

This image shows me connecting to my sever via ssh

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?

Billing Breakdown

I can manually deposit funds or set up automatic payments at any time 🙂

UpCloud Backup Options

You do not need to setup backups but in case you want to roll back (if things stuff up), it is a good idea. Backups are an additional charge.

I have set up automatic daily backups with an auto deletion after 2 days

To view backup scheduled click on your deployed server then click backup

List of UpCloud Backups

Note: Backups are charged at $0.056 for every GB stored – so $5.60 for every 100GB per month (half that for 50GB etc)

You can take manual backups at any time (and only be charged for the hour)

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.

To view your firewall click on your deployed server then click firewall

UpCloud firewall

Update: I modified my firewall to allow inbound ICMP (IPv4/IPv6) and UDP (IPv4/IPv6) packets.

(Note: Old firewall screenshot)

Firewall Rules Allow port 80, 443 and DNS

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)

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.

Install Common Ubuntu Packages

I installed common Ubuntu packages.

Timezone

I checked the server’s time (I thought this was auto set before I deployed)?

I reset the time to Australia/Sydney.

Now the timezone is set 🙂

Shell History

I increased the shell history.

SSH Login

I created a ~/.ssh/authorized_keys file and added my SSH public key to allow password-less logins.

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

nginx/1.14.0 is now installed.

A quick GT Metrix test.

This image shows awesome static nginx performance ratings of of 99%

Install MySQL

Run these commands to install and secure MySQL.

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.

Now let’s set the root password authentication method to “mysql_native_password”

Check authentication method.

Now we need to flush permissions.

Done.

Advertisement:



Install PHP

Install PHP 7.2

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

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.

Install PHP Modules

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.

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

Check NGINX Status

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)

Use Adminer to Export My Blog (on Vultr)

On the original server open Adminer (http) and..

  1. Login with the MySQL root account
  2. Open your database
  3. Choose “Save” as the output
  4. Click on Export

This image shows the export of the wordpress adminer page

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.

  1. Create a new database
  2. Click on the database and click Import
  3. Choose the SQL file
  4. Click Execute to import it

Import MuSQL backup with Adminer

Don’t forget to create a user and assign permissions (as required – check your wp-config.php file).

Import MySQL Database

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.

Find IP

At Cloudflare update your DNS details to point to the server’s new IPv4 (“A Record”) and IPv6 (“AAAA Record”).

Cloudflare DNS

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

DNS Not Replicated Yet

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

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.

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)

SSL Labs SSL Certificate Check

All good thanks to the config above.

SSL Labs

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

Move WP-CLI to the bin folder as “wp”

Test wp

Update WordPress Plugins

Now I can run “wp plugin update” to update all WordPress plugins

Update WordPress Core

WordPress core file can be updated with “wp core update”

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

Test NGINX config, reload NGINX config and restart NGINX

Output (14 workers are ready)

Memory Tweak (set at your own risk)

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)

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

I set the cronjob to run every 15 mins, I added this to my cronjob.

Sample log output

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 🙂

Free memory over time

I used https://crontab.guru/every-hour to set the right schedule in crontab.

I rebooted the VM.

Update: I now use Nixstats monitoring

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.

Yes, swappiness is set.

File System Tweaks – Write Back Cache (set at your own risk)

First, check your disk name and file system

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.

I edited my fstab to append the “writeback,noatime,nodiratime” flags for my volume after a reboot.

Edit FS Tab:

I added “writeback,noatime,nodiratime” flags to my disk options.

Updating Ubuntu Packages

Show updatable packages.

Update Packages.

Unattended Security Updates

Read more on Ubuntu 18.04 Unattended upgrades here, here and here.

Install Unattended Upgrades

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

FYI: You can find installed packages by running this command:

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.

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 (and that is with 17 assets loading and 361KB of HTML content)

0.9sec load times

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.

Web Page Test

GT Metrix has a nice historical breakdown of load times (night and day).

Upcloud Site Speed in GTMetrix

Google Page Speed Insight Desktop Score

I benchmarked with https://developers.google.com/speed/pagespeed/insights/

This will help with future SEO rankings. It is well known that Google is pushing fast servers.

100% Desktop page speed score

Google Chrome 70 Dev Console Audit (Desktop)

100% Chrome Audit Score

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

  • I disabled Lazy loading of images (this was not working on some Android devices)
  • I re-added the News Widget and news images.

GTMetrix and WebpageTest sores are still good (even after adding bloat)

Benchmarks are still good

My WordPress site is not really that small either

Large website

FYI: WordPress Plugins I use.

These are the plugins I use.

How I use these plugins to speed up my site.

  • I use EWWW Image Optimizer plugin to auto-compress my images and to provide a CDN for media asset deliver (pre-Cloudflare). Learn more about ExactDN and EWWW.io here.
  • I use Autoptimize plugin to optimise HTML/CSS/JS and ensure select assets are on my EWWW CDN. This plugin also removes WordPress Emojis, removed the use of Google Fonts, allows you to define pre-configured domains, Async Javascript-files etc.
  • I use BJ Lazy Load to prevent all images in a post from loading on load (and only as the user scrolls down the page).
  • GTmetrix for WordPress and Cloudflare plugins are for information only?
  • I use WP-Optimize to ensure my database is healthy and to disable comments/trackbacks and pingbacks.

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.

  • “MaxIOPS is UpCloud’s proprietary block-storage technology. MaxIOPS is physically redundant storage technology where all customer’s data is located in two separate physical devices at all times. UpCloud uses InfiniBand (!) network to connect storage backends to compute nodes, where customers’ cloud servers are running. All disks are enterprise-grade SSD’s. And using separate storage backends, it allows us to live migrate our customers’ cloud servers freely inside our infrastructure between compute nodes – whether it be due to hardware malfunction (compute node) or backend software updates (example CPU vulnerability and immediate patching).“

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.

  • Support confirmed that each backup is charged (so 5 times manual backups are charged 5 times). Setting up a daily auto backup schedule for 2 weeks would create 14 billable backup charges.
  • I guess a 25GB server will be $1.50 a month

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

  • One thing to note, when deleting servers (CPU, RAM) instances, you get the option to delete the storages separately via a pop-up window. Choose to delete permanently to delete the disk, to save credit. Any disk storage lying around even unattached to servers will be billed.
  • Charges are in USD.

I think it’s time to delete my domain from Vultr in Sydney.

Deleted my Vultr domain

I deleted my Vultr domain.

Delete Vultr Server

Done.

More Reading on UpCloud

https://www.upcloud.com/documentation/faq/

UpCloud Server Status

http://status.upcloud.com

Check out my new guide on Nixstats for awesome monitoring

What I would like

  1. Ability to name individual manual backups (tag with why I backed up).
  2. Ability to push user defined data from my VM to the dashboard
  3. Cheaper scheduled backups
  4. Sydney data centres (one day)

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.

Optimisation Options

Disable dasjhicons.min.css (for unauthenticated WordPress users).

Find functions.php in the www root

Edit functions.php

Add the following

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

I tested a http2 push page by defining this in /etc/nginx/sites-available/default 

Once I tested that push (demo here) was working I then defined two files to push that were being sent from my server

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

Add 2FA Authentication to server logins.

I recently checked out YubiCo YubiKeys and I have secured my Linux servers with 2FA prompts at login. Read the guide here. I secured my WordPress too.

Tweaks Todo

  • Compress placeholder BJ Lazy Load Image (plugin is broken)
  • Solve 2x Google Analytics tracker redirects (done, switched to Matomo)

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

Results

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

[contact-form-7 404 "Not Found"]

Revision History

v2.1 Newer GTMetrix scores

v2.0 New UpCloud UI Update and links to new guides.

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.

I set the cronjob to run every 15 mins, I added this to my cronjob.

Sample log output
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 🙂
Free memory over time
I used https://crontab.guru/every-hour to set the right schedule in crontab.
I rebooted the VM.
Update: I now use Nixstats monitoring
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.
Yes, swappiness is set.
File System Tweaks – Write Back Cache (set at your own risk)
First, check your disk name and file system
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.
I edited my fstab to append the “writeback,noatime,nodiratime” flags for my volume after a reboot.
Edit FS Tab:
I added “writeback,noatime,nodiratime” flags to my disk options.
Updating Ubuntu Packages
Show updatable packages.
Update Packages.
Unattended Security Updates
Read more on Ubuntu 18.04 Unattended upgrades here, here and here.
Install Unattended Upgrades
Enable 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).
FYI: You can find installed packages by running this command:
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.
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 (and that is with 17 assets loading and 361KB of HTML content)
0.9sec load times
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.
Web Page Test
GT Metrix has a nice historical breakdown of load times (night and day).
Upcloud Site Speed in GTMetrix
Google Page Speed Insight Desktop Score
I benchmarked with https://developers.google.com/speed/pagespeed/insights/
This will help with future SEO rankings. It is well known that Google is pushing fast servers.
100% Desktop page speed score
Google Chrome 70 Dev Console Audit (Desktop)
100% Chrome Audit Score
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
  • I disabled Lazy loading of images (this was not working on some Android devices)
  • I re-added the News Widget and news images.
GTMetrix and WebpageTest sores are still good (even after adding bloat)
Benchmarks are still good
My WordPress site is not really that small either
Large website
FYI: WordPress Plugins I use.
These are the plugins I use.
How I use these plugins to speed up my site.
  • I use EWWW Image Optimizer plugin to auto-compress my images and to provide a CDN for media asset deliver (pre-Cloudflare). Learn more about ExactDN and EWWW.io here.
  • I use Autoptimize plugin to optimise HTML/CSS/JS and ensure select assets are on my EWWW CDN. This plugin also removes WordPress Emojis, removed the use of Google Fonts, allows you to define pre-configured domains, Async Javascript-files etc.
  • I use BJ Lazy Load to prevent all images in a post from loading on load (and only as the user scrolls down the page).
  • GTmetrix for WordPress and Cloudflare plugins are for information only?
  • I use WP-Optimize to ensure my database is healthy and to disable comments/trackbacks and pingbacks.
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.
  • “MaxIOPS is UpCloud’s proprietary block-storage technology. MaxIOPS is physically redundant storage technology where all customer’s data is located in two separate physical devices at all times. UpCloud uses InfiniBand (!) network to connect storage backends to compute nodes, where customers’ cloud servers are running. All disks are enterprise-grade SSD’s. And using separate storage backends, it allows us to live migrate our customers’ cloud servers freely inside our infrastructure between compute nodes – whether it be due to hardware malfunction (compute node) or backend software updates (example CPU vulnerability and immediate patching).“
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.
  • Support confirmed that each backup is charged (so 5 times manual backups are charged 5 times). Setting up a daily auto backup schedule for 2 weeks would create 14 billable backup charges.
  • I guess a 25GB server will be $1.50 a month
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
  • One thing to note, when deleting servers (CPU, RAM) instances, you get the option to delete the storages separately via a pop-up window. Choose to delete permanently to delete the disk, to save credit. Any disk storage lying around even unattached to servers will be billed.
  • Charges are in USD.
I think it’s time to delete my domain from Vultr in Sydney.
Deleted my Vultr domain
I deleted my Vultr domain.
Delete Vultr Server
Done.
More Reading on UpCloud
https://www.upcloud.com/documentation/faq/
UpCloud Server Status
http://status.upcloud.com
Check out my new guide on Nixstats for awesome monitoring
What I would like
  1. Ability to name individual manual backups (tag with why I backed up).
  2. Ability to push user defined data from my VM to the dashboard
  3. Cheaper scheduled backups
  4. Sydney data centres (one day)
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.
Optimisation Options
Disable dasjhicons.min.css (for unauthenticated WordPress users).
Find functions.php in the www root
Edit functions.php
Add the following
HTTP2 Push
I added http2 to my listening servers
I tested a http2 push page by defining this in /etc/nginx/sites-available/default 
Once I tested that push (demo here) was working I then defined two files to push that were being sent from my server
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
Add 2FA Authentication to server logins.
I recently checked out YubiCo YubiKeys and I have secured my Linux servers with 2FA prompts at login. Read the guide here. I secured my WordPress too.
Tweaks Todo
  • Compress placeholder BJ Lazy Load Image (plugin is broken)
  • Solve 2x Google Analytics tracker redirects (done, switched to Matomo)
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).
Results
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
 [contact-form-7 404 "Not Found"]
Revision History
v2.1 Newer GTMetrix scores
v2.0 New UpCloud UI Update and links to new guides.
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.
How to backup and restore a MySQL database on Windows and Linux
 by 
Why backup and restore
This is a quick guide demonstrating how you can backup and restore a MySQL database on Windows and Linux using Adminer.
You may need to know how to backup a restore a database for a number of reasons..
e.g
  • Send the database to someone to debug or give feedback while learning.
  • Move the database from a local machine to the cloud
  • Move the database from cloud vendor A to cloud vendor B
  • etc.
Advertisement:
   


Having a backup of the VM is good but having a backup of the database too is better.  I use UpCloud for hosting my VM’s and setting backups is easy.  But I cannot download those backups.
UpCloud Backup Screen
Murphy’s Law
“If anything can go wrong, it will”
The most important reason for taking a backup and knowing how to restore it is for disaster recovery reasons.
Backup (the easiest way) with Adminer
Adminer is a free PHP based IDE for MySQL and other databases. Simply install Adminer and save the file on your local computer or remote web server directory.
FYI: The Adminer author Jakub Vrana has a patron page, I am a patron of this awesome software.
Snip from Adminers website. “Adminer (formerly phpMinAdmin) is a full-featured database management tool written in PHP. Conversely to phpMyAdmin, it consist of a single file ready to deploy to the target server. Adminer is available for MySQL, MariaDB, PostgreSQL, SQLite, MS SQL, Oracle, Firebird, SimpleDB, Elasticsearch andMongoDB.”
adminer.php file icon screenshot
TIP: The file would be publicly accessible to anyone so don’t save it to a common area, obfuscate the file, protect it of delete the file when you are done using it.
Advertisement:
   


Once Adminer is installed load it in a web browser, login with your MySQL credentials. Once you login you will see all databases and an Import and Export menu.
Adminer main screen, all databases and import and export menu.
tbtest is a simple database with one table and 4 fields (ID, Key, Value and Modified)
.Click Export to open the export screen.
Export screen showing a list of databases and export options
Click Export, a SQL file will be generated (this is the export of the database).
Here is a save of the file: 
https://fearby.com/wp-content/uploads/export.txt
Exported view of https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-management.html
Its that simple.
If I add a binary blob file to the table and upload a PNG file lets see how the export looks.
Screenshot o the new table with a blog field in Adminer UI
Advertisement:
   


Let export the database again in Adminer and check out the output. I used Sublime Text editor to view the export file.
New Export shows the binary file in the Backup SQL file
Restore (the easiest way) with Adminer
OK lets delete the tbtest database and then restore it with Adminer.  I used Adminer to delete (DROP) the database.
Database dropped with Adminer
Database “dbtest” deleted.
Now lets create a blank database to restore to (same name).
Create database screen.
Database created.
dbtest created.
Advertisement:
   


Now lets import the database backup using Adminer.
Click Import, select the backup file and un-tick Stop on errors.
Import screenshot, dxtest selectded, Restore file selected, stop on errors disabled
TIP: The 2MB next the the choose file button is defined by your web server and PHP configuration. If you are trying to import a larger database (e.g 80MB) first increase the limits in your web server and PHP (via php.ini).
The Import (restore should take seconds)
Import Success
The database was imported from a backup, all tables and records imported just fine.
The database was imported from a backup
Bonus methods.
On Ubuntu use this guide to backup from the command line. If you use the Oracle MySQL Workbench read this.
I hope this helps someone.
Advertisement:
   


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.
Advertisement:
   


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.
Advertisement:
   


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)
Advertisement:
   


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.
Advertisement:
   


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';  
Advertisement:
   


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 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
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
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.
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.
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
Advertisement:

 
 
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
Advertisement:

 
 
 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
Advertisement:

 
 
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
Advertisement:

 
 
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 404 "Not Found"]
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
How to backup WordPress on a host that has CPanel
 by 
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
Login to 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.
Domain
I found it, I clicked the CPanel login.
Cpanel link
Step 3: CPanel Applications
CPanel does offer good tools to manage your websites like web-based File Manager and Database tool called phpMyAdmin.
CPanel Options
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.
cpenal_usage_exceeded
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.
Static Website
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
SSL Cert
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:
Shared Host Speed
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
File Manager
Highlight all files that you want to backup (highlight everything but not past backup files).
backup-cpanel-006
View the files to compress summary
Compress Summary
Click Compress Files(s) and view the backup progress
Backup Progress
You can now download the backup zip file in your browser (click the file and click Download).
Downaload ZIp
Download Progress.
Download File
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.
phpMyAdmin
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.
Database Options
The first step is to perform an online cold backup of the WordPress database.
  1. Click the database you want to backup (on the left)
  2. Click the Options tab
  3. Under the Copy To frame enter a new database name to copy the database too
  4. Click Go
Online Backup
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.
backup-cpanel-013
Now let’s download a copy of the database (Repeat for multiple databases).
  1. Click the database you want to backup
  2. Click the Export tab
  3. Click Go
backup-cpanel-014
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.
Export Done
I hope this guide helps someone.
Ask a question or recommend an article
[contact-form-7 404 "Not Found"]
Revision History
v1.0 Initial post
Using the free Adminer GUI for MySQL on your website
 by 
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.
Adminer GUIClick English only (.php file)
Adminer
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.
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).
Randomize
Login to Adminer with your MySQL root password.
Login
Click Create databaase
Create Database
Give the database a name and choose the character coding standard (e.g UTF8 ceneral ci). Different standards have different performance impacts too.
Save
Now that you have a database you can create a table.
Adminer
Consider adding an auto-incrementing ID and say a Key and Value varchar column.
Adminer
When the table is created you can add a row to the table.
Adminer
I created one with a “TestKey” and “TestValue” row.
Adminer
The row was inserted.
Adminer
The final thing to do is add a database user that code can connect to the database with. Click Privileges.
Adminer
Click Create user
Adminer
Tick All privileges and click Save
Adminer
Now the user is added to the database
Adminer
Let’s create a PHP file and talk to the database. Let’s use parameterized queries
<?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 "&nbsp; Success: A proper connection to MySQL was made! The $dbname database is great." . PHP_EOL . "<br />";
    echo "&nbsp; &nbsp;- Host information: " . mysqli_get_host_info($con) . PHP_EOL . "<br />";
    echo "&nbsp; &nbsp;- Server Info: '" . mysqli_get_server_info($con) . "'<br />";
    echo "&nbsp; &nbsp;- Server Protocol Info : ". mysqli_get_proto_info($con) . "<br />";
    echo "&nbsp; &nbsp;- Server Version: " . mysqli_get_server_version($con) . "<br />";
    //echo " - Server Connection Stats: " . print_r(vmysqli_get_connection_stats($con)) . "<br />";
    echo "&nbsp; &nbsp;- Client Version: " . mysqli_get_client_version($con) . "<br />";
    echo "&nbsp; &nbsp;- 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
Adminer Results
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
[contact-form-7 404 "Not Found"]
Revision History
v1.0 Initial Version
PHP 7 code to send object oriented sanitised input data via bound parameters to a MYSQL database
 by 
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
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).
<?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 "&nbsp; Success: A proper connection to MySQL was made! The my_db database is great." . PHP_EOL . "<br />";
    echo "&nbsp; &nbsp;- Host information: " . mysqli_get_host_info($con) . PHP_EOL . "<br />";
    echo "&nbsp; &nbsp;- Server Info: '" . mysqli_get_server_info($con) . "'<br />";
    echo "&nbsp; &nbsp;- Server Protocol Info : ". mysqli_get_proto_info($con) . "<br />";
    echo "&nbsp; &nbsp;- Server Version: " . mysqli_get_server_version($con) . "<br />";
    //echo " - Server Connection Stats: " . print_r(mysqli_get_connection_stats($con)) . "<br />";
    echo "&nbsp; &nbsp;- Client Version: " . mysqli_get_client_version($con) . "<br />";
    echo "&nbsp; &nbsp;- 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
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.
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
Edit your appropriate PHP file
And turn on Error reporting.
Restart PHP and NGINX
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.
<?php
phpinfo()
?>
It is amazing how clear errors can be
PHP Error
Dump Connection Vars: PHP mysqli_connect: var_dump($con)
echo var_dump($con);
Output:
  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.
echo "&nbsp; Success: Database connection OK." . PHP_EOL . "<br />";
echo "&nbsp; &nbsp;- Host information: " . mysqli_get_host_info($con) . PHP_EOL . "<br />";
echo "&nbsp; &nbsp;- Server Info: '" . mysqli_get_server_info($con) . "'<br />";
echo "&nbsp; &nbsp;- Server Protocol Info : ". mysqli_get_proto_info($con) . "<br />";
echo "&nbsp; &nbsp;- Server Version: " . mysqli_get_server_version($con) . "<br />";
//echo " - Server Connection Stats: " . print_r(mysqli_get_connection_stats($con)) . "<br />";
echo "&nbsp; &nbsp;- Client Version: " . mysqli_get_client_version($con) . "<br />";
echo "&nbsp; &nbsp;- Client Info: '" . mysqli_get_client_info() . "'<br />";
Output:
  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
printf(" - Error: %s.\n", $stmt->error);

Output:
Error: Table 'thedatabasename.invalidtablename' doesn't exist.
Debugging is your friend.
 
More to come..
 
Donate and make this blog better

 
Ask a question or recommend an article
 [contact-form-7 404 "Not Found"]
V 1.0 initial post
Short (Article):
NodeJS code to handle App logins via API (using MySQL connection pools (1000 connections) and query parameters)
 by 
The below code is part of what I use to handle logins in an app. You can use this as a base for a NodeJS login routine (in a file called app.js). I was going to use StormPath API but was too slow and unreliable for  (as it turns out StormPath are merging with Okta and are closing down services).
Advertisement:
 
 
 
 
This code is not functionally complete but you can hack out what you need. Rolling your own  solution can be faster and slow you to fully embed in server side logic but make sure it is secure. 
 I am using these node modules (app.js).
var app = require('express')();
var http = require('http').Server(app);
var mysql = require('mysql');
var uuid = require('node-uuid');
var Type = require('type-of-is');
var jsesc = require('jsesc');
var bcrypt = require('bcrypt');
var filessystem = require('fs');
The following code is located in myutils.js is a  handy place to place your common code.  You don’t need it but it is demonstrated here.
module.exports = function(){ 
this.sum = function(a,b){
    return a+b
};
this.multiply = function(a,b){
    return a*b
    
  };
this.samplefunction = function(a){
    return 'Hello World'
    
  };
}
Don’t forget to include it myutils.js in app.js
require('./myutils.js')();  //Remove this if you dont use it
Declare the body parser module (app.js).
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());
Set the MySQL connection details and you can setup the number of ready MySQL connections in the pool here (app.js).
console.log(' mysql_pool.createPool()');
var mysql_pool  = mysql.createPool({
  connectionLimit : 1000,
  host            : 'localhost',
  database        : 'thedatabasename',
  user            : 'thedatabaseuser',
  password        : 'thedatabasepassword'
  
});
Create a static API endpoint (app.js).
// Generic static API Point
app.get('/api/myappname/v1/hello/world/',function(req,res){
	var data = { "Version":"" };
	data["Version"] = "Hello World"; // Add a JSON value
	res.json(data); // Return the JSON
});
Create MySQL functions (app.js).
mysql_pool.getConnection(function(err, connection){
	// See if a connection is available in the pool by querying the database
	console.log(' mysql_pool.getConnection()');
	  	if (err) {
	  		console.log(' error: ' + err);
	  		throw err;
	  	}
	connection.query('select * from thedatabasename;',  function(err2, rows){
	  	if (err2) {
	  		connection.release();
	  		console.log(' error: ' + err2);
	  		throw err2;
	  	} else {
	  		console.log( rows );
	  		console.log('Ready');
	  	}
	  	connection.release();
	});
	console.log(' mysql_pool.release()');
});

mysql_pool.on('connection', function (connection) {
  connection.query('SET SESSION auto_increment_increment=1')
  console.log(' mysql_pool.on(connection) - Set session increment');
});

mysql_pool.on('enqueue', function () {
  console.log(' mysql_pool.on(enqueue). Waiting for available connection slot.');
});

/api/myappname/v1/login function (app.js).
// Login ----------------------------------------------------------------------- 
app.post('/api/myappname/v1/login',function(req,res){
	console.log('API CALL: /api/myappname/v1/login');

	var errData = "";

	// Check to see if the users posted token is valid
	var useraccesstoken = req.headers['x-user-access-token'];
	if (appaccesstoken == undefined) {
		// If a user token is missng then block the login, we only process logins whre valid user tokens are posted. Users are generated a token at acount creation
		console.log(" 498 token required");
		errData = {"Status": "498", "Error": "Invalid user token. Please login again (error 498)."};
		res.statusCode = 499;
		res.send(errData);
		res.end();
	}

	// I usually send an x-access-token in the posted JSON payload to eliminate older apps.
	var appaccesstoken = req.headers['x-access-token'];
	if (appaccesstoken == undefined) {
		// If an app  token is missng then block the login, we only process logins whre valid app tokens are posted.
		console.log(" 499 token required");
		errData = {"Status": "499", "Error": "Invalid app token. Please update your app (error 499)."};
		res.statusCode = 499;
		res.send(errData);
		res.end();

	} else {
		// ok we have a token posted
		var appaccesstokenpassed = false;

		// Check the API Key against known API Keys
		if (appaccesstoken == "appname33ffda6d-4303-4c65-81ca-0ccf0f172a37") { appaccesstokenpassed = true;} // Debug API Key for ICS 0.9.1
		
		// OK Process the posted data
		if (appaccesstokenpassed == true) {
			
			var userenteredemail = req.body.email;
			var userenteredpass = req.body.password; //todo: Add Encryption/Decryption (currenty a BCrypt Hash is sent from the app)

			// If this is Yes a new session token is sent to the user later, this will in valitae older logged in sessions.
			var resetsessions = req.body.resetsessions;
			if (req.body.resetsessions == undefined) {
				resetsessions = "Yes";
			}
			
			// Log for debugging
			console.log(' Email: ' + userenteredemail);
			console.log(' Password: ' + pass);
			console.log(' Reset Sessions: ' + resetsessions);
			
			var data = {}
			
			mysql_pool.getConnection(function(err, connection) {
			if (err) {
				console.log(' mysql_pool.release()');
				connection.release();
		  		console.log(' Error getting mysql_pool connection: ' + err);
		  		throw err;
		  	}
		  		// Revised SQL
		  		connection.query("SELECT * FROM usertablename WHERE usertablename.active=1 AND usertablename.lockedout=0 AND usertablename.email=? LIMIT 1",[userenteredemail],function(err, rows, fields){
				
					if (err) {

						// Log database errors
						console.log(" 503 Service unavailable (database error)");
						errData = {"Status": "503", "Error": "Service unavailable (database error)"};
						res.statusCode = 503;
						res.send(errData);	
						res.end();

					} else {
						
						// Do we have  a recordset
						if (rows.length != 0) {
							
							// Check password hash - https://github.com/ncb000gt/node.bcrypt.js
							var salt = rows[0].password_salt;
							var hash = rows[0].password_hash; 		// bcrypt.hashSync(pass, salt);
							
							hash = hash.replace(/^\$2y(.+)$/i, '\$2a$1');
							var passwordok = false;
							console.log(' About to check password.');
							
							passwordok = bcrypt.compareSync(userenteredpass, hash);

							console.log(passwordok);
							if (passwordok == true) {
							
							// Allow access to records
								var userid = rows[0].id;									// User ID
								var user_guid = rows[0].user_guid;							// Internal App Guid
								var push_guid = rows[0].push_guid;							// Internal GUID user for Push Notifications
								
								
								var newkey = rows[0].usertokenkey;								// Generate a new API secret key
								var newpass = rows[0].usertokenpassword;								// Generate a a new API secret pass

								// This will create two guids to use for all future api hiots from the user
								if (resetsessions == "Yes") {
									newkey = uuid.v4();										// Generate a new API secret key
									newpass = uuid.v4();									// Generate a a new API secret pass
								}

								var stat_total_logins = rows[0].stat_total_logins;			// Read total Logins are stored in my usedatabase
									stat_total_logins = stat_total_logins + 1;				// Increment total logins for later saving 
								
								var username = rows[0].username;							// Read the user Username from the database
								var usertag = rows[0].usertagline;							// Read the user Tagling from the database
								var email = rows[0].email;									// Read the user Email from the database
								
								var block_transactions = rows[0].block_transactions;		// Has the User Blocked Transactions
								var credit = rows[0].credit;								// Credit Remaining
								var currency = rows[0].currency;							// Users Currency (used for the log)

								var smssendalertatlogin = rows[0].user_pref_sms_alert_at_login;  	// Does the user want SMS Sent a Login
								var emailsendalertatlogin = rows[0].user_pref_email_alert_at_login; 	// Does the user want an email
								var pushsendalertatlogin = rows[0].user_pref_push_alert_at_login;	// Push alert the user at login
								
								var user_pref_timezone = rows[0].user_pref_timezone;
								var user_pref_timezone_int = rows[0].user_pref_timezone_int;
								
								var stat_total_password_resets = rows[0].stat_total_password_resets;		// Pass this throgh to the App
								
								var active = rows[0].active;								// Is the user active
								var lockedout = rows[0].lockedout;							// Is the user locked ut
								var changing_password = rows[0].changing_password;

								var querylimit5min = rows[0].QueryLimit5Min;				// Read from he databse how manu API hits the user has a minute
								
								// I like to return the api version and version int with all api JSON payloads (makes checking on the App side easier)
								var jsonvalid = "yes";
								var jsonversion = "2017.01.01.22.00";
								var jsonversionint = "102";
								
								//Update API Keys
								mysql_pool.getConnection(function(err, connection) {
									if (err) {
										console.log(' mysql_pool.release()');
										connection.release();
								  		console.log(' Error getting mysql_pool connection: ' + err);
								  		throw err;
								  	}
								  	// Update the users logins and new keys if thye asked to wipe all past logins.
									connection.query('UPDATE usertablename SET usertokenkey = ?, usertokenpassword = ?, stat_total_logins = ?, last_login = NOW() WHERE id = ?', [newkey, newpass, stat_total_logins, userid],function(errupdate, rowsupdate, fieldsupdate){
										if (errupdate) {
											console.log(" 503 service unavailable (database update error)");
											errData = {"Status": "503", "Error": "503 service unavailable (database update error)"};
											res.statusCode = 503;
											res.send(errData);
											res.end();
										} else {
											if (rowsupdate.length != 0) {
												// Inform User of Valid  Login
												res.statusCode = 200;
												data["Data"] = "Successfully logged in..";									// statis string thta reports the successfull login
												
												// The Key and Pass are used liek OAUTH tokens and are stored in the app and user database. All API hits check these tokens. 
												data["Key"] = newkey;                       								//LOADS IN XCODE v0.9.1
												data["Pass"] = newpass;                      								//LOADS IN XCODE v0.9.1
												
												// User guids are used to store the unique user rather than the database record ID
												data["user_guid"] = user_guid;
												data["push_guid"] = push_guid;												// Internal GUID user for Push Notifications

												data["Logins"] = stat_total_logins;											// Total Logins are stored in my usedatabase
												data["Username"] = username;												// Username
												data["Email"] = email;														// Email
												
												// The notify user code ay ogin has been removed byt can be called here
												data["SMSAtLogin"] = smssendalertatlogin;
												data["EmailAtLogin"] = emailsendalertatlogin;
												data["PushAtLogin"] = pushsendalertatlogin;
												
												// What is the users timezome
												data["timezone"] = user_pref_timezone;
												data["timezoneint"] = user_pref_timezone_int;
												
												
												data["stat_total_password_resets"] = stat_total_password_resets;			// How many times has the user changed passwords
												
												data["active"] = active;													// Is the user active
												data["lockedout"] = lockedout;												// Is th user currently locked out
												data["changing_password"] = changing_password;								// Is the user currebtky changing a password
												
												data["levelquerylimit5min"] = querylimit5min;								// How any times can the user hit the API (handled by the app)
	
												data["jsonvalid"] = jsonvalid;												// JSON ID
												data["jsonversion"] = jsonversion;											// JSON Version
												data["jsonversionint"] = jsonversionint;									// JSON Version
												
												// Send the JSON back to the user
												res.json(data);
												res.end();
												
												//Login Confirmed
												console.log("User " + username + " / " + email + " logged in.");
												
											} else {
												console.log(" 504 service unavailable (database update error)");
												errData = {"Status": "504", "Error": "504 service unavailable (database update error)"};
												res.statusCode = 504;
												res.send(errData);
												res.end();		
											}
										}
										console.log(' mysql_pool.release()');
										connection.release();				// release the MySQL connection pool connection
									});	
								});
								
							} else {
								console.log('402 Invalid Login (' +  req.body.email + ', ' + req.body.password + ')');
								errData = {"Status": "402", "Error": "Invalid Login"};
								res.statusCode = 402;
								res.send(errData);
								res.end();								
							}
	
						} else {
							console.log('403 Account is locked out, not active or unknown. Please visit https://www.myappname.com to reactivate your account. (' +  req.body.email + ')');
							errData = {"Status": "403", "Error": "Account is locked out, not active or unknown."};
							res.statusCode = 403;
							res.send(errData);
							res.end();	
						}
					}
					console.log(' mysql_pool.release()');
					connection.release();
					
				});
			});

		} else {
			console.log("499 token required");
			errData = {"Status": "499", "Error": "Invalid app token. Please update your app (error 499)."};
			res.statusCode = 409;
			res.send(errData);	
			res.end();
		}
	}
});
 
/api/myappname/v1/verifylogin verify login function (app.js).
// Verify Login ----------------------------------------------------------------------- 
app.post('/api/myappname/v1/verifylogin',function(req,res){
	console.log('API CALL: /api/myappname/v1/verifylogin');
	
	// check to see if the App included an api key
	var appaccesstoken = req.headers['x-access-token'];

	var errData = "";
	if (appaccesstoken == undefined) {
		console.log(" 499 token required");
		errData = {"Status": "499", "Error": "valid token required"};
		res.statusCode = 499;
		res.send(errData);
		res.end();

	} else {

	//console.log(" Received appaccesstoken: " + appaccesstoken);
		var appaccesstokenpassed = false;
		// Check the API Key against known API Keys
		if (appaccesstoken == "ics201701-1d95-9271-a6c7-a9a33770287") { appaccesstokenpassed = true;} // Debug API Key
		if (appaccesstokenpassed == true) {
			var key = req.body.key;
			var pass = req.body.pass;
			var data = {} 

			console.log(" - Key: " + key )
			console.log(" - Pass: " + pass )
			
			// Grab the user IP and log it
			var ip = req.headers['x-forwarded-for'] || req.connection.remoteAddress;
			console.log(" - IP: " + ip );
			
			mysql_pool.getConnection(function(err, connection) {
			if (err) {
				console.log(' mysql_pool.release()');
				connection.release();
		  		console.log(' Error getting mysql_pool connection: ' + err);
		  		throw err;
		  	}
		  	// Check the user record (only if the user is active and provided thier token key and passowrd)
		    connection.query("SELECT * FROM usertablename WHERE active=1 AND usertokenkey=? AND usertokenpassword=? LIMIT 1",[key, pass],function(err, rows, fields){
				if (err) {
					console.log(" 405 Login Session Expired");
					errData = {"Status": "405", "Error": "Login Session Expired"};
					res.statusCode = 405;
					res.send(errData);	
					res.end();
				} else {
					if (rows.length != 0) {
						console.log(" 200 Login Session OK");
						res.statusCode = 200;
						data["Status"] = "OK";
						res.json(data);
						res.end();
					} else {
						console.log(" 405 Login Session Expired");
						errData = {"Status": "405", "Error": "Login Session Expired"};
						res.statusCode = 405;
						res.send(errData);	
						res.end();
					}
				}
				console.log(' mysql_pool.release()');
				connection.release();
			});
			});
		} else {
			console.log("499 token required");
			errData = {"Status": "499", "Error": "valid token required"};
			res.statusCode = 409;
			res.send(errData);	
			res.end();
		}
	}
});

 
Start the web service (app.js).
http.listen(3000,function(){
	console.log("Connected & Listen to port 3000 at /api/myappname/v1/ ..");
	console.log(" Do..");
	
});
Hope this helps
 
Donate and make this blog better

 
 
 
Ask a question or recommend an article
 [contact-form-7 404 "Not Found"]
V1.0 Initial Post