By | November 23, 2018

Features and capabilities continue to arrive in MySQL 8.0, most recently noted in the GA release of MySQL 8.0.13.  The mysql-shell 8.0.12 release introduced a number of things, an important part was a cross-platform mysql-shell secure password handling facility.  The MySQL Login-paths are a part of that security focus.  Here we will look at where InnoDB Cluster 8.0, is going, and focus on the enhanced Devops-focused alignment that MySQL is putting forward. Particularly InnoDB Cluster command structure introduced in MySQL-Shell 8.0.13.

By aiming to look at the big picture, we can more confidently think about our own technology environment and build out processes that remove friction/resistance in how we deploy technology and integrate MySQL Cluster solutions at the same time..

This blog is part 1 in a 3 part series on MySQL InnoDB Cluster 8.0.13’s feature introductions, associated scripting we can use, devops approached enhancements and general capabilities.

Building an InnoDB Cluster – DevOps Aligned Considerations

Let me outline the MySQL capabilities and enablements that help us build a  MySQL 8.0 InnoDB Cluster with a DevOps mindset, split into a few sections:

  1. Quick Reminder on Yum Repo Benefits
  2. Initial and repeatable MySQL install cluster setup readiness
  3. Review: Secrets handling and resulting password stores
  4. The new InnoDB Cluster command syntax and execution Sequence to build a cluster

1 – Quick Reminder on Yum Repo Benefits

A previous blog of mine already went through building and using a MySQL Repo for simple and localized ways to “pull” rpm install packages into a deployment environment in a very simple way.  I’ve also added a section to that blog for configuring the yum repo webserver, in case that reference for the commands I demonstrated is useful. Below, I show the output of the MySQL 8.0 Enterprise Edition binaries that I have available.

Why this reminder on having a Yum Repo to draw upon?

  • In a Devops world, we need to make our technology implementations as friction-less as possible.
  • Having our MySQL installation packages readily delivered to any platform in our company’s eco-system (or in our cloud environment) is critical.

** Note above: currently the mysql-shell is not in a commercial repo.  Full path URL in local repo as seen above, works fine.  In my DBA days, that is how our sysadmin would provide MySQL Enterprise Edition rpm files, by making simpler aliases to the rpms.  I would then use http paths to packages through our scripted MySQL installation builds.  So in a way, formal repo packaging isn’t critical, you can roll your own by having predictable URL paths to your RPM files.

2 – Initial and Repeatable MySQL install Cluster Setup Readiness

Here are the steps I utilize to prepare the ground for an InnoDB Cluster install using a script that will destroy and rebuild environments repeatedly. You need not use these explicit commands.  Customize and tailor to your environment and needs.  The idea is to have a repeatable reliable process.

  1.  Handle SELINUX and firewall setups.  Each OS distribution may have similar things.  Refer to the MySQL Ports blog post for guidance on which ports are used.
    1. For InnoDB Cluster setups, we need ports 3306, 33060, 33061
    2. Regular installs usually deal with port 3306 on installation, so its likely just the others needs to be made available
    3. Also there is the Admin Connection Port using port 33062.
    4. Lastly, for MySQL 8.0.17 onwards, nis_enabled helps handle ephemeral port handling
  2. Stop the MySQL Server if its installed and delete the data directory’s files.
    systemctl stop mysqld; rm -rf /var/lib/mysql/*
  3. Scripting to drop in a new configuration file, or simply re-implement a common one so you always have the same starting point
    cp -f ${CONFIG_FILE_PATH} /etc/my.cnf
  4. Install your RPM files, which would include your MySQL Server as noted above
    yum install mysql-server mysql-router ...
  5. Re-initialize your database files & the ssl certificates to start from scratch!
  6. Ensure that no entries exist on your local server where the domain name refers back to the loopback address.  If the DNS refers back to the server, it should be changed to the unique IPv4 external Server IP address (IPv6 is supported in MySQL 8.0.15).  For me, the vagrant plugin “vagrant-hostmanager” does a pretty good job, but I do need to remove or comment out the typical entry in the /etc/hosts file.  This script below works no matter where the offending entry is in the file.
  7. Start MySQL and reset the password for the ‘root’ database user account.

3 – Review: Secrets handling and resulting password stores

Quick Moment to touch on Password schemes when using the MySQL-Shell and these fancy DevOps friendly approaches.

  1. MySQL has a few different key store options for the MySQL Shell in 8.0 as announced in their blog, MySQL Shell 8.0.12 – Storing Passwords Securely with more details found in the Pluggable Password documentation.  Password handling an important capability to ensure we have a means for running our tools within scripted tooling and maintain a secure space.
  2. The general options for MySQL client utilities such as the MySQL-Shell are to use
    1. “windows-credential” on Windows
    2. “keychain” on MacOS
    3. “login-path” on all platforms
  3. Login-paths have been out since MySQL 5.7 and they are native offering of MySQL.

Login Path usage in brief

You can use the mysql_config_editor  client to manage login-path credentials on your system.  With the mysql-shell, they’ll be made automatically for you based on the “user@host:port” credentials pattern used when using the mysql-shell.  When it prompts you to save it, those password credentials reside with your chosen or defaulted key store.

Other MySQL client utilities can use explicitly created and named login-path credentials too, so it’s a great re-use utility.

Note: Your password is stored in an encrypted format, and not in clear text.

4 – The new InnoDB Cluster command syntax and  execution Sequence to build a cluster

This will be a condensed outline on the command structure and implementation sequence.  This can be a reference for using commands that should be more favourable in scripted scenarios.  Some of the options may not be needed in your environment, or they should be different.  But the core syntax here can guide you.

Here are the Quick Sequence of commands to run, and on which servers.  My servers are hostnames: gr127, gr128, gr129, as seen on the shell prompt.  You can use that as a guide to ensure the commands are run from the proper server.

You’ll notice the last 3 commands can be run from a single server, the one of which I propose is the initial one configured – but it can be any server that has access to all 3.  This is the case if you’ve opted to use the “clusterAdmin” option for the system to creat that account for you.  You’ll notice those last 3 commands use that accounts access to do the final cluster setup.

Cluster status is shown here:

Enjoy MySQL Cluster!!  Comments welcome, as always.