UBUNTU 16.04 MYSQL DATABASE

Azure does not have a branded MySQL so we had to setup a Linux virtual machine. Azure bought ClearDB but they are unsuitable.

We are using Ubuntu Server 16.04 LTS and the comments here are tested with that version of Linux. Other distributions may differ slightly. Ubuntu supports LTS releases for 5 years which cuts down on the work needed to run serveral virtual machines.

First we created the basic virtual machine with Azure, the cheapest is basic A0 but that can be upgraded if ever needed. The VM costs $13 per month. If A0 does not cut it, Azure can upgrade to A1 etc with more CPU time and memory. Beware that Azure store tries to flog more expensive SSD virtual machine which are much more expensive. Select all option to find the lower cost A series. Linux is not very demanding and A0 runs it very well.

Azure has a slightly more expensive version of the A series. The standard A has load balancing etc. This means that if a workload spikes etc the VM can borrow resources etc. This costs $1 more which is worth it as pages can explode and sometimes databases can be busy.

Azure does not automatically allocate a domain name for your virtual machine, which seems out of place, it needs some URL so that a terminal session can be established. Once the URL is created a Linux command session is possible and work can be completed. Somebody has to administrate the database etc.

PUTTY is free and it can be used to connect to the Azure VM with a telnet session. The URL allows PUTTY to connect, The once the terminal window is opened, you can then sign in. Putty does not support cut and paste, the right click can paste however which is enough to enter a strong password etc. PUTTY can also copy the whole session to the clipboard where its possible to copy results etc with notepad etc. The reason PUTTY does not support Windows keyboard accellerators is that keys are used by LInux too.

FIRST UPDATE LINUX

Updates require 2 commands, one to update the index and then to install the upgrades.

sudo apt-get update
sudo apt-get upgrade

INSTALL MYSQL

MySQL is part of the LAMP stack but the VM is configured as a pure database,.

sudo apt-get install mysql-server mysql-client
sudo apt install mysql-utilities

The Azure A0 has plenty of room on the system disk for WordPress. While using additional storage has advantages it’s not needed for a small site. Now you will need to stop the database to allow modification of the configuration files:

sudo /etc/init.d/mysql stop

The config file has been moved in Ubuntu 16.04 making manuals useless. This needs to be modified to permit remote access:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Comment out the line with bind-address = 127.0.0.1 with the hash mark (#). 127.0.0.1 is for local host only, then the database can be restarted with internet access enabled.

sudo /etc/init.d/mysql restart

CONNECT

sudo mysql -u root -p

Then enter your database password and you will have local host access to the database.

Now each web site will need a user account and password. Ideally the password can be generated which makes hacking almost impossible., Keep in mind all SQL commands end with the semicolon which common with many programming languages.

CREATE DATABASE databasename;

WIth the database created then user access be added. The % means anybody anywhere on the internet with the credentials can use the database. This is why the password needs to be very long and strong. Remember that the single quotes are needed as this is part of the SQL syntax.

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

By hard coding permissions to the web site means nobody is going to be able to use a SQL injection attack.

GRANT ALL  ON *.* TO 'siteuser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

Its possible that a warning will be displayed, but the grant will be OK. This seems to be a bug with Ubuntu 16.04.

FLUSH PRIVILEGES;

The flush simply tells MySQL to reload all the new changes.

SHOW GRANTS FOR 'user'@'%';

will show that the user account permissions are as expected,

SELECT User FROM mysql.user;

Will show the list of existing users. This can act as a check to be sure the accounts are all properly enabled.

BACKUP ENTIRE DATABASE

sudo mysqldump -u root -p --all-databases | gzip > backup-`date +'%m-%d-%Y'`.sql.gz

Using the dated filename allows for aged backups so that in case of the unthinkable. FTP can be used to download backups. This can also be used to move to a new server should the need arise..

FIREWALL

Azure has a firewall so port 3306 needs to be opened to let the database be available.