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.
Comments
Add new comment