Devops with MariaDB, Part 1

This new blog series will be about how to easily automate common MariaDB administration tasks using Ansible. We will showcase how to automatically install and configure software such as MariaDB server, MariaDB Galera and MaxScale with ease in reproducible environments.

What’s Ansible?

Ansible is an IT automation tool. It can configure systems, deploy software, and orchestrate more advanced IT tasks such as continuous deployments or zero downtime rolling updates.

Ansible’s goals are foremost those of simplicity and maximum ease of use. It also has a strong focus on security and reliability, featuring a minimum of moving parts, usage of OpenSSH for transport (with an accelerated socket mode and pull modes as alternatives), and a language that is designed around auditability by humans – even those not familiar with the program (source: Ansible documentation).

Target platform

Ansible can help deploying MariaDB Galera Cluster in the cloud, on your enterprise servers or even on your home testing server. For the purpose of testing I have used virtual machines deployed with Vagrant, which allows to easily deploy new instances. Any Linux distribution can be used for the purposes of this demonstration however I will stick to Ubuntu 14.04 (Trusty) which has most up-to-date packages.

Installing Ansible and Vagrant

This can be done easily through apt:

$ sudo apt-get install ansible vagrant

Installing Virtualbox for Ubuntu

$ sudo sh -c "echo 'deb http://download.virtualbox.org/virtualbox/debian '$(lsb_release -cs)' contrib non-free' > /etc/apt/sources.list.d/virtualbox.list" && wget -q http://download.virtualbox.org/virtualbox/debian/oracle_vbox.asc -O- | sudo apt-key add - && sudo apt-get update && sudo apt-get install virtualbox-4.3 dkms

Let’s define a Vagrantfile for one master and one slave instance:

VAGRANTFILE_API_VERSION = "2"
 
Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "ubuntu/trusty64"
  config.vm.provision "ansible" do |ansible|
    ansible.playbook = "maria.yml"
    ansible.sudo = true
  end
 
  config.vm.define "master" do |master|
    master.vm.hostname = "master"
    master.vm.network "forwarded_port", guest: 3306, host: 3336
    master.vm.network "private_network", ip: "192.168.50.2"        
  end
 
  config.vm.define "slave" do |slave|
    slave.vm.hostname = "slave"
    slave.vm.network "forwarded_port", guest: 3306, host: 3337
    slave.vm.network "private_network", ip: "192.168.50.3"                
  end
 
  config.vm.provider "virtualbox" do |v|
    v.memory = 512
    v.cpus = 1
  end
end

This Vagrantfile defines two Ubuntu Trusty virtual machines of equal configuration (1 CPU, 512MB memory) named aptly “slave” and “master”. We also forward the ports for easy local access to local port 3336 for the master, and 3337 for the slave. Ansible will be used as a provider and the playbook will be read from “maria.yml” file.

Now let’s define the Ansible playbook, aka the file where we give Ansible instructions on what tasks to execute on both VMs. Ansible uses YAML as an easy markup language to define instructions. We create the following “maria.yml” file:

- hosts: master:slave
  user: vagrant
  tasks:
  - name: Install MariaDB repository
    apt_repository: repo='deb http://ftp.igh.cnrs.fr/pub/mariadb/repo/10.0/ubuntu trusty main' state=present
  - name: Add repository key to the system
    apt_key: keyserver=keyserver.ubuntu.com id=0xcbcb082a1bb943db
  - name: Install MariaDB Server
    apt: name=mariadb-server state=latest update_cache=yes
  - name: Install python module
    apt: name=python-mysqldb state=installed
  - name: Create replication account
    mysql_user: name=repl host="%" password=lper priv=*.*:"REPLICATION SLAVE" state=present
  - name: Create readwrite user
    mysql_user: name=rwuser host="%" password=adminpwd priv=*.*:SELECT,INSERT,UPDATE,DELETE,CREATE,DROP state=present
  - name: Modify configuration file to listen on all interfaces
    lineinfile: dest=/etc/mysql/my.cnf regexp="^bind-address" line="bind-address=0.0.0.0"
- hosts: master
  user: vagrant
  tasks:
  - name: Modify configuration file to setup server ID
    lineinfile: dest=/etc/mysql/my.cnf regexp="^#server-id" line="server-id=1"
  - name: Restart mysql service
    service: name=mysql state=restarted
  - name: Reset master binlog
    command: /usr/bin/mysql -u root -e "RESET MASTER"
- hosts: slave
  user: vagrant
  tasks:
  - name: Modify configuration file to setup server ID
    lineinfile: dest=/etc/mysql/my.cnf regexp="^#server-id" line="server-id=2"
  - name: Setup replication
    command: /usr/bin/mysql -uroot -e "CHANGE MASTER TO master_host='192.168.50.2', master_user='repl', master_password='lper', master_use_gtid=current_pos"
  - name: Restart mysql service
    service: name=mysql state=restarted

Let’s go through the playbook to understand which tasks are planned:

• We install the MariaDB repository on both servers, and, using apt, install the latest version of MariaDB-server as well as mysql python module (needed for the account creation part)
• We create a ‘repl’@’%’ user with REPLICATION SLAVE privilege in order to setup replication later.
• We create an application user ‘rwuser’@’%’ in order to connect remotely from other servers.
• We modify the my.cnf file in order to setup correct server ids, as well as the requirement to listen on all interfaces (Ubuntu’s my.cnf is limited to localhost by defaults)
• We issue a CHANGE MASTER command using MariaDB 10.0 GTID to make the slave replicate from the master VM.
• We restart both mysqld services after installation in order to apply all those modifications.

Let’s start both instances and the playbook. As Vagrant is able to invoke Ansible directly we don’t need to issue any actual Ansible command (else, Ansible would have been called with ansible-playbook maria.yml to provision both servers).

$ vagrant up

If you have correctly created both files (Vagrantfile and mariadb.yml), Vagrant will first start both virtual machines:

Bringing machine 'master' up with 'virtualbox' provider...
Bringing machine 'slave' up with 'virtualbox' provider...

This might take some time as the Ubuntu VM image is downloaded from the internet.

Then when each VMs is booted, Vagrant should invoke Ansible:

==> master: Running provisioner: ansible...
 
PLAY [master:slave] ***********************************************************
 
GATHERING FACTS ***************************************************************
ok: [master]

Each task defined in the YAML playbook should now be played. Ansible should indicate success (or errors) for each configuration pass:

PLAY RECAP ********************************************************************
master                     : ok=12   changed=10   unreachable=0    failed=0 

We should now be able to connect with the application user and play with our master/slave setup:

$ mysql -h127.0.0.1 -P3336 -urwuser -padminpwd

Have fun experimenting with Ansible, and don’t hesitate to refer yourself to the Ansible documentation for more information.

In the next series we will push the experiment further using the Ansible template engine and leveraging it to configure a Galera cluster automatically. Stay tuned!