Building MySQL on OCI Compute with Scripting

By | February 25, 2019

The Oracle Cloud has a lot to offer with it’s Security Focused 2nd Generation OCI environmentMy previous blog on OCI IaaS walks us through some of the great IaaS features such as compartments and VCN Subnets where you can run services across private ip addresses.  What I’d like to look at this time is one initial way for building MySQL Enterprise Edition on Compute instances.  I’ll use the Oracle cloud for this task.

Scripting MySQL on OCI Compute

Yes, using more interesting tools such as Terraform for building cloud assets certainly makes the most sense.  I’m starting here though to give a bit more focus on interacting with the Oracle Cloud to become familiar with it.  Also, not everyone is familiar with automation tools like Terraform yet… so this is a good starting point.

Building MySQL – Part 1 – launch compute & attach storage

In order to build MySQL on “Compute“, we need to launch a compute instance, and then I want to guide us on attaching “Block Storage” for hosting the MySQL data directory files.  We’ll do this by navigating through the Oracle Cloud Console.

Block Storage Setup

Like the picture, on the side of the Oracle Cloud console, first navigate to the Block Storage section and SELECT “Block Volumes” to create our first block storage device.

  1. Click “Create Block Volume
  2. Choose your “Compartment
  3. Give a name to the block storage, maybe one that associates it to compute instance you are building.  Mine is named Blog-Storage
  4. Select an “Availability Domain” that matches the same location where the compute instance will be located.  Mine is in ‘Availability Domain #1’
  5. The block storage size default to 1TB, adjust it to a size of your liking & possibly a backup policy.
  6. You can even choose the option to encrypt the block storage device using key management as well.  Not needed for this exercise.

I’ve created a 2TB block storage setup with no backups or encryption.

OCI Compute Instance creation

Next, I would like us to navigate to the “Compute” -> “Instances” section to create our first compute instance.

  1. Click “Create Instance
  2. Give a name to your compute instance.  I called mine Blog-Compute
  3. Choose the matching availibility domain as your block storage.
  4. The default virtual machine “Shape” is a “VM.Standard2.1”.  It has 1 OCPU (1 OCPU==2 vCPUs) & 15GB memory. Feel free to keep the default.
  5. Select a public key which you would like to connect to the server with. You will need to private key to connect, of course.
  6. You will then be given options to choose you’re:
    1. Compartment” for the VM
    2. the “VCN” to use
    3. The “Subnet’s Compartment
    4. and then the “Subnet” itself. You’ll need to choose a subnet that has public IPs supported (if you don’t already have another compute instance to connect to in the same area)
    5. There are some “advanced” options after that, but let’s just click “Create

Attach Block Storage to VM Compute instance

Now we need to attach the “Block Volume” to the “Compute” instance we just created. In the cloud console, navigate to your compute instance you just created and click on it’s “name” to expand all the details for that Compute node.  My “Compute” is named Blog-Compute. and my “Block Volume” is named Blog-Storage.

  1. Scroll to the bottom of your compute instance’s details
  2. Click the LINK on the left-hand side that is named “Attached Block Volumes (#)
  3. Select the button “Attach Block Volume
  4. Use the ISCSI option when using it for a MySQL database or high IOPS performant storage.
  5. Choose the block volume’s Compartment (should be same as the compute instance)
  6. Select the Volume” from the drop down menu, and the block volume you created should be there.  Choose it.
  7. Select the Device Path“, first one in the list is fine.  This will be the reserved device path for your block volume to be mounted upon.
  8. Then click the “Attach” button at the bottom!
  9. Now we want to view some details about the “Block Volume” we just attached, simply click on the name of the block storage device you just created.  It will expand out its details in the “Block Volumes” page.
  10. Click on “iSCSI Commands & Information” and copy the details from the  following sections for using with shell scripts to complete the storage volumes setup on the Compute instance.

DEVICE PATH
IP ADDRESS AND PORT
VOLUME IQN

Building MySQL – Part 2 – the Repo Server

We now need to build a Repo Server, and start by referring this task to another blog of mine, building a MySQL Enterprise Edition Yum Repo Server.   Why is this important?  Because it gives us a central respository for not only our MySQL Yum installation binaries, which doubles over to support hosting our scripting artifacts and other content we might need to retreive.

I build this “webserver” that hosts this role in a subnet that only has private IP addressing. Therefore, easily limiting access from outside the VCN’s Subnet network space, and certainly restricts internet facing traffic from ever seeing it.  So even though its based on a Webserver, its internal-only private infrastructure.

Directory Structure of the webserver

The Installation File Server

I double the purpose for this Repository server beyond just serving yum’s RPM files for MySQL Enterprise Edition.  I use it to pull the scripted MySQL installation packaging too.  The /var/www/html/files  directory hosts installation files that are used for scripted installations of MySQL.

The 3-Line MySQL install

This one modularized script does all the substantial work. Notice the iSCSI Block Volume options are added to the script’s execution.  The scripting assumes execution takes place as the ‘root’ OS user.

Note: my repo server is hosted at private ip 10.0.10.10. Adjust the wget command above to use your own Repo Server’s IP address.

  1. SSH as the ‘opc’ user into your OCI Compute where you will install MySQL.
  2. Switch to the ‘root’ user and execute the 3-line command.

Building MySQL – Part 3 – Review the Script!

There are a variety of things that I address and deal with in this script.  Each core activity is broken into it’s own defined bash shell function.

Honestly, I don’t know why I haven’t been  using bash functions for so long…I’m reminded of how fantastic they can be.  We’ll look at the “header” section of the script, the various bash functions and what each one’s main purpose is, and the trailing section, where the bash functions are actually executed.

The Header Section

The header section here defines 2 sets of data.  Positional parameters captured on the command line when this script is executed. Followed by environment or fixed settings that define where files are located and names of configuration items, etc.

The Footer Section

The Footer section is where all the magic happens.  All the core scripting is of course bundled into Bash shell “functions” noted above. But the function can’t be called until after the page has read its definition.  So function calls all execute at the bottom of the script.  This also keeps them neatly organized and easy to know the execution sequence.

You can see I also prefixed each bash function with it’s typical sequence number to ensure this visual queue prompts on the proper order of execution.

 

01-Prepare Server

Although brief in scripting, this section addresses some “OCI Compute Best Practices” and includes some items in my previous blog on Oracle Cloud – Getting started.  This gets the server packages  up to date, adds a few key packages that enable using the Oracle Cloud’s NTP Service for tighter clock synchronization.

Also, the OCI Utilities are installed, which give us some foundational tools for allowing script automation to handle our Block Storage device setup and mounting.  I’ve added a yum package to be installed for handling Selinux on our compute instance too for MySQL.

02-Volume Setup and Mount

This function could be re-used for implementing block storage for MySQL backups or for non-MySQL systems.  It would just need a flexible mount point environment variable.  I just named my mount point variable “DATADIR”, so that is easy to understand it’s purpose.

It could even be adjusted and re-used for nvme/SSD storage on the Oracle Cloud’s DenseIO or BareMetal compute options.

Update: I’ve adjusted this code to to work with two improved implementation items mentioned or inspired by this Oracle Cloud blog post on Managing OCI iSCS Block Volume attachments with Terraform.

  1. Use a /dev/disk/by-path instead of guessing what the actual device reference might be
    Since we are using the web console, we actually will now use “Consistent Device Path” instead.
  2. Using a tool called sgdisk to use GPT partitioning of the volume

03-Initial Install and Configuration of MySQL

Here is the “meat” of the matter at hand…actually installing MySQL!  Hooray!

Tasks I aim to address here, in sequence of the script:

  1. Dynamically pre-configure “server-id” with a randomly generated value
  2. Get the “report-host” to acquire the fully qualified private DNS address for the compute instance it belongs too.  Otherwise, the server associates to the short hostname and it only resolves to itself, and doesn’t resolve outside the compute instance.
  3. Install the local Repo Server’s yum config for MySQL 8.0 EE into the Linux server’s yum.repos.d area.
  4. Copy a preconfigured mysql-log-rotate configuration file so that Error logs get properly rotated
  5. Explicitly add the MySQL user & group both using the default ID of 27.  I prefer to explicit declare the user and a consistent ID for the user/group so that my builds are consistent across environments.  Creating the user account in this way gives it a home directory which can be ideal
  6. Configure Network Port access with the OS’s firewall and Selinux to allow MySQL full functionality
  7. Increase the nofile file limits setting for all users
  8. Install the MySQL rpm packages using our custom in-cloud private Repo Server

04-Re-Initialize MySQL Server

This particular shell function is special, in that it can be re-run anytime to re-initialize the database re-create base user accounts…essentially enabling the server to start from a fresh new set of database files.

05-MySQL Enterprise Monitor Agent

The MEM Agent installation is pretty streamlined given it accepts a configuration file that guides the install and setup.  So thiss can be done in 3 lines.

06-Add MySQL Enterprise Backup Script Setups

The MySQL Enterprise Backup (MEB) utility can support many ways of doing backups.  I have a script that works with the requirements of Tranparent Data Encryption and holds a good standard for backup handling. When MEB does backups of MySQL systems implementing TDE, the jobs are best run (if not outright required) as the ‘mysql’ user account, or the account that the MySQL Server executes as.   Hence, I host the scripts and the resulting backup with the MySQL OS user.

This shell function that installs these components is as follows…

Hope you enjoy the scripts, the Oracle Cloud, and of course, MySQL !!