MYSQL SUPPLEMENTAL STORAGE

UNDER CONSTRUCTION

A basic Azure Linux virtual machine can be used for a smaller database. As the tables grow there are some options, with low traffic and a large table extra storage is lower cost than a larger virtual  machine. First some tools are needed.

sudo apt install mysql-utilities

Now its possible to determine how much actual storage is being used.

The Azure portal right now is configured by default for 2TB but WordPress does not need more than a GB. So the default A0 is overkill. Still with additional storage we expand as needed. Its possible to run thousands of sites off one database. So this VM becomes a business opportunity.

sudo mysqldiskusage --server=user:pass@host:port:socket db1 --all

Azure is sluggish to get storage attached to a virtual machine. Linux uses ext4 which is similar to NTFS. We suggest grabbing some coffee after creating the disk(s) with Azure. It can take that long. Usually Azure disks is found on a SCSI controller as /dev/sdc but lshw can enumerate all disks to be certain.

sudo lshw -class disk
sudo fdisk /dev/sdc
sudo mkfs -t ext4 /dev/sdc

once the storage is partitioned and formatted it’s ready for use. so first we need a place to mount the disk under

sudo mkdir /sql
sudo mount -t ext4 /dev/sdc /sql

now you need to edit the file system table so this mount will survive a reboot etc

first you will need the UUID which is similar to what Windows is using for global unique ID etc

sudo blkid

now you need to edit the file system table so this mount will survive a reboot etc

sudo nano /etc/fstab

now with the new storage available, no need for the database to slug it out with the system disk for bandwidth, this makes life in the Azure data center much simpler, so now we have to stop MySQL

sudo service mysqld stop
sudo cp -rap /var/lib/mysq /sql
sudo chown mysql.mysql /sql

then we can edit the config file

sudo nano /etc/mysql/my.cnf

change or add

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
to
datadir=/sql
socket=/sql/mysql.sock

then you can restart MySQL

sudo service mysqld start

then you can begin setting up databases