MySQL Database Deployment Automation

Automation is in vogue these days. Technology evolves quickly, and more people are contributing to existing available third party automation software platforms. As the season of fast-paced deployments, automation is now necessary and considered as de facto; you cannot get rid of automating things deemed necessary. These necessities are important to get things done quickly and eliminate redundant tasks. Once this is present, organisations and companies can focus on the logical business things that make it more interesting for growth. This blog covers a short overview tools and consideration points for database automation followed by a a topic on how to use ClusterControl for database automation. 

Technologies for Database Deployment Automation

Sophisticated tools for Infrastructure as Code (IaC) such as Puppet, Chef, Ansible, SaltStack, and Terraform are usually the mainstream technologies to choose from. These tools help DBAs with tasks that are easily replicated, like deploying a MySQL. Automating your MySQL deployment saves you a great deal of time, especially if you have extensive platforms to support and follow the software engineering cycles steps to deploy for your QA, staging, or development environments.

Using automation makes these things quicker and easier to manage when it’s needed.

Choosing Your Automation Software

In this blog, let’s try to use Ansible. As stated earlier, using third-party software makes it easier and faster to provide automation especially for deploying your MySQL cluster. Now we’ll use existing modules particularly Ansible roles that are available. As stated in our previous blog, we can use Ansible Galaxy (repository for ansible collections and roles). 

Before that, you need to declare the host that shall be affected by the deployment. For example, in this blog, I have the following:

$ cat /etc/hosts
192.168.30.40 debnode4
192.168.30.50 debnode5

Then in my /etc/ansible/hosts file,

root@debnode3:~# tail -n2 /etc/ansible/hosts

debnode4
debnode5

Then, create the file main.yml just like below,

root@debnode3:~/deploy-mysql# cat main.yml
---
# tasks file for deploy-mysql
- hosts: all
  become: yes
  vars_files:
    - vars/main.yml
  roles:
    - role: geerlingguy.mysql

Since I am running using Ubuntu 16.04 for this simple MySQL deployment using Ansible for my automation, I end up having this,

root@debnode3:~/deploy-mysql# cat vars/main.yml
---
# vars file for deploy-mysql
mysql_root_password: "R00tP@55"
mysql_python_package_debian: python-mysqldb

Now, testing the MySQL deployment shows,

root@debnode3:~/deploy-mysql# ansible all -a  "mysql -Nse 'select concat("hello world at ", @@hostname,"!");' " 2>/dev/null
debnode4 | CHANGED | rc=0 >>
hello world at debnode4!
debnode5 | CHANGED | rc=0 >>
hello world at debnode5!

Automate, then formulate

As we have showcased above, it’s better to automate and take advantage of existing ones. In this blog, I chose Ansible because it’s easier to take advantage of existing modules such as roles in this example and just a few steps to deploy a MySQL server. 

Of course, that’s not how simple it is. There are existing roles, such as this one, that supports replication and more variables to tune. Whatever the existing modules can do, take advantage of it rather than reinventing your own. You can just use the existing ones, then modify them according to your needs.

ClusterControl for Automation

ClusterControl automates the deployment of MySQL proficiently. Instead of creating your own, ClusterControl is freely downloadable, and you can use it to deploy your MySQL how many times you want. You can use the software to set up a writer-reader replication or a writer-writer replication setup. Check out the screenshot below:

You just have to specify the source host/IP address of your desired MySQL servers.

ClusterControl deployment automation does a pre-checks to determine if the server is ready to manage the job to be deployed. This involves accessibility, storage availability, and hardware capacity. The checks are aligned to the variables to be tuned up once it’s ready to run the job. Check out the screenshot below:

Based on the job activity log, you’ll see that it adjusts the variables or parameters in the Linux systems we support. If you also look at the example screenshot we have below, it checks the memory and disk availability and prepares for the MySQL deployment. Thus, it installs the required packages, not only for MySQL to run normally but also for ClusterControl to monitor and register the server and its services that we collect for monitoring.

ClusterControl Developer Tools

ClusterControl has its feature where you can create your own scripts specifically for advisors. How important is this for your MySQL database deployment automation is that, once the database is registered and deployed, you can further enhance or create your own Advisors. For example, checkout our previous blog titled Using ClusterControl Advisor to Create Checks for SELinux and Meltdown/Spectre: Part One. After the deployment, you can write your first Advisors then check regularly for exploits. Not only that, but you can also check custom parameters that are unique to your needs.

ClusterControl s9s CLI tools

ClusterControl has CLI tools which we call as s9s CLI tools which can be found here at our github repository. The s9s CLI tools enable you to deploy MySQL with just one command. For example,

$ s9s cluster --create 
        --cluster-type=mysqlreplication      --nodes="192.168.1.117?master;192.168.1.113?slave;192.168.1.115?slave;192.168.1.116?master;192.168.1.118?slave;192.168.1.119?slave;" 
        --vendor=oracle 
        --db-admin="root" 
        --db-admin-passwd="root123" 
        --cluster-name=ft_replication_23986 
        --provider-version=5.7 
        --log

The command above allows you to deploy a MySQL Replication cluster provided the following IP addresses with its arguments, whether it’s a master or a slave.

This tool provides more powerful and efficient features aside from just automatic deployment of your favourite database that we support, such as MySQL. Check out our documentation for ClusterControl to learn more about the s9s CLI tools