Create Multiple MariaDB VM Instances in Google Cloud

5-minute read
MariaDB
MariaDB • Google Cloud

Introduction

We are going to install MariaDB database using a Virtual Machine Instance on the Compute Engine component of Google Cloud Platform as a base for generation of more MariaDB instances.
Our first instance will be customized according our needs and then we will generate an Image and an Instance Template to easily future duplication.

MariaDB Instance Server

Select a Google Cloud Project

Login to your Google Cloud Console and select your project:

On Google Cloud Platform -> Project button -> New Project

  • Project name: Databases-2020
  • Location: No Organization

Create Virtual Machine

Create a new Virtual Machine with the following parameters:

On Google Cloud Platform - Compute Engine - VM Instances - Create Instance

  • Name: patomariadb
  • Type: f1-micro (1 vCPU, 0.6 GB memory)
  • Image: Ubuntu 19.10
    • Standard persistent disk: 10GB
  • Identity and API access
    • Access scopes: Set access for each API
      • Compute Engine: Read Write
      • Storage: Full
  • Networking
    • Hostname: patomariadb.databases

SSH Login

Using PuTTYgen generate a private key of type RSA 2048 bits and set your username in the Key comment field.
Save your private key to your laptop and copy-paste the generated public key to:

Google Cloud Platform -> Compute Engine -> Metadata -> SSH Keys -> Edit -> Add Item

ssh-rsa AAA ... ... uQ== pato

Now login to your virtual machine as user@External-IP using PuTTY loading your private key.

Software Install

Once inside update Ubuntu and set your time zone:

$ sudo apt update && sudo apt upgrade -y
$ sudo timedatectl set-timezone America/Mexico_City

Install MariaDB Server, then enable the MariaDB service for automatic start at boot, and validate installation by checking version:

$ sudo apt-get install mariadb-server

$ sudo systemctl enable mariadb

$ mysql --version

Install MariaDB Backup tool and create a backup directory with proper permissions:

$ sudo apt-get install mariadb-backup
$ sudo -p mkdir /respaldo
$ sudo chown -R mysql:mysql /respaldo

Enable Remote Login

Allow traffic on MariaDB port 3306 in the Google Cloud Firewall using the following parameters:

On Google Cloud Platform -> Networking -> VPC Network -> Firewall -> Create Firewall Rule

  • Name: patonet-mariadb
  • Network: default
  • Priority: 1000
  • Direction of traffic: Ingress
  • Action on match: Allow
  • Targets: All instances in the network
  • Source Filter: IP Ranges
  • Source IP Ranges: 0.0.0.0/0
  • Protocols and ports: Specified protocols and ports
    • tcp: 3306

Allow MariaDB remote connections by comment out bind address to localhost in server configuration:

$ sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
...
#bind-address            = 127.0.0.1
...

Users and Security

Define a password for the root user and secure your installation with the procedure mysql_secure_installation:

$ sudo mysql_secure_installation
Set root password? [Y/n] Y
Remove anonymous users? [Y/n]
Disallow root login remotely? [Y/n]
Remove test database and access to it? [Y/n]
Reload privilege tables now? [Y/n]

Create an administrator user with plugin unix_socket for passwordless local connection:

$ sudo mysql

MariaDB [(none)]> CREATE USER 'pato'@'localhost' IDENTIFIED VIA unix_socket;

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'pato'@'localhost' WITH GRANT OPTION;

Create remote user that can connect from any host @'%':

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'remoto'@'%' IDENTIFIED BY '********';

Create backup user with proper permissions:

MariaDB [(none)]> GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO respalda@localhost IDENTIFIED BY '********';

and flush privileges so new grants get activated:

MariaDB [(none)]> FLUSH PRIVILEGES;

Shutdown the Instance

We finish our customizations so we need to shutdown it for the next steps.

patomariadb $ sudo shutdown

Replicate MariaDB Virtual Instance

Now that we have installed and customized our instance we are going to create more Virtual Machines duplicating the one we just generated.

Create Image

Create an Image of the disk used by our base Virtual Machine.

On Google Cloud Platform -> Compute Engine -> Images -> Create Image

  • Name: patomariadb-image
  • Source: Disk
  • Source disk: patomariadb
  • Location: Multi-regional us (United States)

Create Instance Template

Create an Instance Template with the characteristics you require for your future virtual machines and use our recently created image as boot disk:

On Google Cloud Platform -> Compute Engine -> Instance Templates -> Create Instance Template

  • Name: patomariadb-template
  • Machine Type: f1-micro (1 vCPU, 0.6 GB memory)
  • Boot Disk
    • Custom Image
      • Image: patomariadb-image
      • Standard persistent disk: 10GB
  • Identity and API access
    • Access scopes: Set access for each API
      • Compute Engine: Read Write
      • Storage: Full

Generate New Instance(s)

With the Image and Template in place, to generate a new MariaDB instance you just need to provide a new VM name and hostname:

On Google Cloud Platform - Compute Engine - VM Instances - Create Instance - New VM Instance from template

  • Select template: patomariadb-template
  • Name: patomariavm2
  • Networking
    • Hostname: patomariavm2.databases

or if you prefer to use Cloud Shell, generate the new instance like this:

$ gcloud config set compute/zone us-central1-f

$ gcloud compute instances create patomariavm2 --hostname patomariavm2.databases --source-instance-template patomariadb-template

Created [https://www.googleapis.com/compute/v1/projects/databases-20202/zones/us-central1-f/instances/patomariavm2].
NAME          ZONE           MACHINE_TYPE  PREEMPTIBLE  INTERNAL_IP  EXTERNAL_IP    STATUS
patomariavm2  us-central1-f  f1-micro                   10.128.0.38  35.224.125.50  RUNNING

And that’s it, you can easily generate as many instances as you need and even delete all the instances when you no longer use them. You only need to preserve your base Image and your Template and you can recreate them again.

Conclusion

We were able to create a base image of MariaDB instance for future duplication. This allowed us to went through the main steps of a normal installation.

With the use of Image and Instance Template functionalities of Google Cloud we can create more similar Virtual Machines avoiding reinstallation and customization. This VM approach will help you to have multiple MariaDB instances for clustering and replication or for any testing you need to do, with a more Production like approach than using containers technology.