Blog

Automated MySQL Server Preparation Using Ansible

In this blog article I’m going to discuss why and how we use Ansible (automation tool for the pre-configuration of MySQL servers) for Tungsten purposes at Continuent, including a step by step description on how to use Ansible.

In the article you can find a link to our GitHub repository with continuent-ansible tool. This tool provides automated pre-configuration and Continuent Tungsten installation into your hosts.

Why Ansible

Ansible is a very simple automation tool, for configuration, management and application deployment tasks. Its declarative language is easy to remember and supported by one of the best documentations I have ever seen. This open-source tool is growing very fast in functionality and popularity, which in turn means that there is a vast and growing community building up around this tool thanks to world-wide usage in many different organizations and projects; in particular, handling errors and workarounds are mastered down to the smallest details.

How we use Ansible at Continuent

At Continuent we prepare MySQL, MariaDB or Percona Server clusters using Ansible for QA, support or demoing. Focused on delivering the best MySQL high availability software on the market for our customers, we test our products across multiple Linux distributions.

Having all these distributions running on physical servers is not very practical, so we use AWS EC2 services. But also in AWS we can’t hold a bunch of instances online all the time, because it’s not very cost-efficient. For this reason we use Ansible to pre-configure the exact number of instances, which are then used for testing.

After each successful test, instances are terminated and we prepare brand new instances for each new test. This is quite beneficial for us, as we don’t need to maintain our clusters; and every time we start new instances, their packages are always up to date.

We also don’t need to repair file structures after failures, or servers can stay in a failed state, until we fix the issue.

Tasks for multiple nodes are executed in parallel. This is why Ansible is so fast, when it comes to cluster configuration.

Ansible - The steps to take

Installation

As described in the Ansible Documentation, there are 3 methods of installation of Ansible to your local machine:

  • using your OS package manager
  • installation with pip
  • install from source

Ansible should be installed only on your local machine (or control node). In remote machines Ansible will be automatically installed, with all prerequisites.

Configuration

You need to have SSH access configured from your local host to all remote hosts. Let’s say this is your /etc/hosts file:

# /etc/hosts
 
# ...
 
192.0.2.51  host1
192.0.2.52  host2
192.0.2.53  host3

After this, you can define your inventory. This is configured by populating the following file /etc/ansible/hosts. Define the hostnames of all your remote hosts, or use groups as described in Inventory documentation.

# /etc/ansible/hosts
 
host1
host2
host3

Alternatively, you can use a custom ~/.ansible/hosts file for listing remote hosts. In this case hosts file should be defined in the ~/.ansible.cfg file as in the following example:

[defaults]
inventory = ~/.ansible/host

To make sure nodes are available, use the ping command:

$ ansible all -m ping

Playbooks

Once your remote hosts are defined in your inventory, you can create your first Playbook with configuration tasks. For this article we’re using a CentOS 7 configuration as an example, but you can find Playbooks for different Linux distributions like Amazon Linux, Debian, Ubuntu or SUSE in our continuent-ansible tool repository.

Directory organization

vars/                       # define variables used in playbook
  tungsten_vars.yml
 
roles/
  prerequsites/             # our main role
 
    files/                  # files we need to copy to remote machines
      id_rsa.tungsten
      id_rsa.tungsten.pub
      ssh_config
      tungsten.ini
      ...
     
    tasks/                  # tasks files
      main.yml
     
    templates/              # templates we need to fill and copy to remote machines
      hosts.j2
      mysql.cnf.j2
      root-my.cnf.j2
      tungsten-my.cnf.j2
 
playbook.yml

Tasks

In our playbook.yml file we define which hosts we want to configure and what tasks (which role) should be used:

# playbook.yml
---
 
- name: Configure remote machines
  hosts: all
  remote_user: root     # user on remote hosts
  become: yes           # enable root privileges
  gather_facts: yes     # collect useful variables about remote hosts
  vars_files:
    - ./vars/tungsten_vars.yml
  roles:
    - prerequsites

Into vars/tungsten_vars.yml file we put passwords for MySQL users:

# vars/tungsten_vars.yml
---
 
passwd_mysql_tungsten: password
passwd_mysql_root: Secret-4-root
passwd_mysql_app_user: secret

Now we step through the required prerequisites for Tungsten Clustering as defined in the Continuent Documentation and create specific tasks.

When we set gather_facts: yes in our main playbook, we then gain access to some special variables like inventory_hostname or hostvars. These variables are used to set the host’s hostname and generate the /etc/hosts file for local DNS.

# roles/prerequsites/tasks/main.yml
---
 
- name: Set hostname
  hostname:
    name: "{{ inventory_hostname }}"
 
- name: Generate /etc/hosts file
  template:
    src: ../templates/hosts.j2
    dest: /etc/hosts
# roles/prerequsites/templates/hosts.j2
 
# {{ ansible_managed }}
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
 
{% for host in play_hosts %}
{{ hostvars[host].ansible_eth0.ipv4.address }} {{ host }}
{% endfor %}

In our continuent-ansible tool we support multiple Linux distributions, thus we need to include_tasks specific for individual distribution, but generally, the next step will be to upgrade packages and install new, required packages. In our case we use the yum package manager task and define all the required software:

# append roles/prerequsites/tasks/main.yml
 
- name: Upgrade all packages
  yum:
    name: "*"
    state: latest
 
- name: Install required packages
  yum:
    name: "{{ packages }}"
    state: present
  vars:
    packages:
      - java-1.8.0-openjdk
      - perl
      - rsync
      - ruby
      - rubygem-io-console
      - rubygem-net-ssh
      - rubygem-net-scp
      - tar
      - zip

We also create the new tungsten OS user that will be used to manage and install Tungsten Clustering, then we add this user into the mysql group and create directories specific for the Tungsten software installation.

- name: Create mysql group
  group:
    name: mysql
    state: present
         
- name: Create the tungsten user
  user:
    name: tungsten
    shell: /bin/bash
    groups: mysql
    comment: Tungsten User
 
- name: Add tungsten user to sudoers file
  lineinfile:
    dest: /etc/sudoers
    state: present
    regexp: '^tungsten.*ALL$'
    line: 'tungsten ALL=(ALL) NOPASSWD: ALL'
    validate: 'visudo -cf %s'
 
- name: Create tungsten folders
  file:
    path: "{{ item }}"
    state: directory
    owner: tungsten
    group: tungsten
    mode: 0700
  with_items:
    - /etc/tungsten
    - /opt/continuent
    - /opt/continuent/software
 
- name: Set security limits for the tungsten user
  copy:
    src: ../files/limits.conf
    dest: /etc/security/limits.d/99-tungsten.conf
    owner: root
    group: root
    mode: 0644
# roles/prerequsites/files/limits.conf
 
tungsten    -    nofile    65535
mysql       -    nofile    65535
 
tungsten    -    nproc    8096
mysql       -    nproc    8096

In the roles/prerequsites/files/ directory we need prepared SSH keys and SSH configuration files for the Tungsten OS user which we are going to copy on to the remote hosts.

- name: Create tungsten ssh folder
  file:
    path: /home/tungsten/.ssh
    state: directory
    owner: tungsten
    group: tungsten
    mode: 0700
 
- name: Copy the ssh keys and config for tungsten user
  copy:
    src: '{{ item.src }}'
    dest: '/home/tungsten/.ssh/{{ item.dest }}'
    owner: tungsten
    group: tungsten
    mode: '{{ item.mode }}'
  with_items:
    - { src: '../files/id_rsa.tungsten', dest: 'id_rsa', mode: '0600' }
    - { src: '../files/id_rsa.tungsten.pub', dest: 'id_rsa.pub', mode: '0644' }
    - { src: '../files/ssh_config', dest: 'config', mode: '0644' }
 
- name: Set ssh authorized key for the tungsten user
  authorized_key:
    user: tungsten
    state: present
    key: "{{ lookup('file', '../files/id_rsa.tungsten.pub') }}"

From this point we need to setup the MySQL database. By installing mysql-community-server package in CentOS 7 we get MySQL 8 by default.

- name: Install MySQL repository
  yum:
    name: "https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm"
    state: present
 
# in case we need older version of MySQL we disable
# repository version 8.0 and enable version 5.7
 
- name: Disable MySQL 8.0 repository
  shell: yum-config-manager --disable mysql80-community
 
- name: Enable MySQL 5.7 repository
  shell: yum-config-manager --enable mysql57-community
 
- name: Install MySQL packages
  yum:
    name: "{{ packages }}"
    state: present
  vars:
    packages:
      - mysql-community-server
      - mysql-community-client
 
- name: Enable the MySQL service
  service:
    name: mysqld
    state: started
    enabled: true

The steps below show how to set our own password for the root user:

- name: Acquire temporary root password
  shell: "awk -F': ' '$0 ~ \"temporary password\"{print $2}' /var/log/mysqld.log"
  register: mysql_root_password_temp
 
- name: Set new root password from default temporary password
  shell: "mysql -e \"SET PASSWORD = '{{ passwd_mysql_root }}';\"
         --connect-expired-password -uroot -p'{{ mysql_root_password_temp.stdout }}'
         && touch /root/.my.password.changed"
  args:
    creates: /root/.my.password.changed

The nest few steps demonstrate user management:

- name: Ensure root can login into MySQL localhost
  shell: "mysql -e \"CREATE USER 'root'@'{{ item }}';\" 
         -uroot -p'{{ passwd_mysql_root }}'"
  with_items:
    - ::1
    - 127.0.0.1
    - localhost
  ignore_errors: yes
 
# ignore_errors flag allow you to execute some steps without making script fail
 
- name: Create the MySQL tungsten user
  shell: "mysql -e \"CREATE USER 'tungsten'@'{{ item }}' IDENTIFIED BY 
         '{{ passwd_mysql_tungsten }}'; GRANT ALL PRIVILEGES ON *.* TO 
         'tungsten'@'{{item}}' WITH GRANT OPTION;\" 
         -uroot -p'{{ passwd_mysql_root }}'"
  with_items:
    - ::1
    - 127.0.0.1
    - localhost
    - "%"
 
- name: Create the MySQL app_user
  shell: "mysql -e \"CREATE USER 'app_user'@'%' IDENTIFIED BY 
         '{{ passwd_mysql_app_user }}'; GRANT ALL PRIVILEGES ON *.* TO 
         'app_user'@'%';\" -uroot -p'{{ passwd_mysql_root }}'"
 
- name: Revoke the SUPER privileges from the MySQL app_user
  command: mysql -NBe "REVOKE SUPER ON *.* FROM app_user@'%'"
 
- name: Adding user mysql to group tungsten
  user:
    name: mysql
    groups: tungsten
    append: yes

From the roles/prerequsites/templates directory, we copy the MySQL configuration files:

- name: Generate MySQL server-id
  shell: hostname -I | sed -e 's/ \+\([a-z0-9]\+\:\)\+[a-z0-9]\+//' | sed -e 's/ 
         /\n/' | grep -v '^$' | tail -1 | awk -F. '{print $3 * 256 + $4}'
  register: mysql_server_id
 
- name: Create /etc/my.cnf
  template:
    src: ../templates/mysql.cnf.j2
    dest: /etc/my.cnf
    owner: root
    group: root
    mode: 0644
    backup: true
 
- name: Copy root .my.cnf file
  template:
    src: ../templates/root-my.cnf.j2
    dest: /root/.my.cnf
    owner: root
    group: root
    mode: 0600
 
- name: Copy tungsten .my.cnf file
  template:
    src: ../templates/tungsten-my.cnf.j2
    dest: /home/tungsten/.my.cnf
    owner: tungsten
    group: tungsten
    mode: 0600
 
- name: Restart the MySQL service
  service:
    name: mysqld
    state: restarted
# roles/prerequsites/templates/mysql.cnf.j2
 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
symbolic-links=0
bind-address = 0.0.0.0
server-id = {{ mysql_server_id.stdout }}
port = 13306
open_files_limit = 65535
log-bin = mysql-bin
sync_binlog = 1
max_allowed_packet = 52m
default-storage-engine = InnoDB
innodb_flush_log_at_trx_commit = 2
innodb_flush_method=O_DIRECT
binlog_format = row
auto-increment-increment = 10
auto-increment-offset = 1
# roles/prerequsites/templates/root-my.cnf.j2
 
[client]
user=root
password={{ passwd_mysql_root }}
# roles/prerequsites/templates/tungsten-my.cnf.j2
 
[client]
user=tungsten
password={{ passwd_mysql_tungsten }}

Finally, we copy our tungsten.ini, extract the Tungsten Clustering tarball and install it. Before this task we need to put {{ tungsten_version }}.tar.gz package into the root directory of your project.

- name: Copy tungsten.ini file
  copy:
    src: ../files/tungsten.ini
    dest: /etc/tungsten
    owner: tungsten
    group: tungsten
    mode: 0770
 
- name: Unarchive Tungsten Clustering software
  unarchive:
    src: "../../../{{ tungsten_version }}.tar.gz"
    dest: /opt/continuent/software
 
- name: Install Tungsten Clustering
  shell: "/opt/continuent/software/{{ tungsten_version }}/tools/tpm install"

Now we can run our Playbook. If you followed steps in this article and you have created your own Playbook, run:

ansible-playbook -e "tungsten_version=tungsten-clustering-6.1.3-37" playbook.yml

In case you use continuent-ansible tool, you probably defined your own hosts inventory, run:

ansible-playbook -i path/to/myhosts.txt -e "tungsten_version=tungsten-clustering-6.1.3-37" playbook.yml

Conclusion

Ansible is a very powerful and must-have tool for system administrators. It saves you time, money and nerves.

To summarise, the main reasons we use Ansible are:

  • It’s an easy-to-remember language supported by great documentation.
  • It helps us automate the deployment of MySQL database clusters as and when we need them (whether it’s for QA, Support or Pre-Sales Demos).
  • No need to maintain our clusters in AWS: every time we start new instances, their packages are always up to date.

Check out our Playbooks for different Linux distributions like Amazon Linux, Debian, Ubuntu or SUSE in our repository.

About the Author

Patrik Michalák
DevOps and QA Engineer

Patrik has been with Continuent for two years, having previously worked as a full-stack web and mobile application developer, while also finishing university. Patrik currently splits his time between school and Continuent. He’s a technology enthusiast, and has been awarded at country-level for a photography processing project, and has been involved as an IoT architect in a scooter sharing project in his country for the past year. Patrik is skilled in Perl, Python, JavaScript (ES6), C, Java, SQL, PHP, including technical skills in Linux administration and automation tools.

Add new comment