Managing MySQL replication with Ansible

Known as one of the most popular open source database, MySQL is widely used in various scenarios, ApsaraDB RDS for MySQL of Alibaba Cloud provided critical relief for companies, especially small and medium-sized enterprises affected by novel coronavirus (COVID-19).

Ansible is an emerging IT automation tool. This article will show you how to configure and manage MySQL master through Ansible, automate the deployment process from the replication environment, and experience the thrill of Ansible simple and fast.

Ansible user

Brief introduction: ansible user module

Ansible is a configuration management and application Deployment tool that functions like the current Industry configuration management tool Chef,puppet,saltstack. Ansible is developed through the Python language. The Ansible platform was created by Michael DeHaan, ansible create user who is also the author of the well-known software Cobbler and Func. Ansible's first version was released in February 2012, and Ansible is still very young compared to other similar products, but this does not affect his vigorous development and the love of his people.

Ansible add user

Ansible the machine is managed by default via the SSH protocol, so Ansible does not need to install the client program on the server. You only need to install Ansible on one server, and after the Ansible is installed, you can manage to control other servers. You do not need to configure the database for it, Ansible does not start or remain running in daemons manner. Ansible's objectives are as follows:

• Automating deployment of applications
• Automated Management configuration
• Continuous delivery of automation
• Automated (AWS) cloud service management.

According to Ansible's official information, users currently using Ansible are: Evernote, Rackspace, NASA, Atlassian, Twitter, etc.

Ansible add user to group

Preparation before installation
Ansible server requirements

The current Ansible can be run on any machine with Python2.6 installed (temporarily does not support the Windows machine-centric control server), which includes Redhat,debian,centos,os X,bsds and so on.

Installing Ansible

If you are a centos/redhat, Debian, Ubuntu user, you can use the System Package Manager yum or Apt-get installation (Centos/redhat need to install Epel package to install via Yum). But here, I strongly recommend that you use PIP to install Ansible. So what is PIP? Pip is a Python package installation and management tool that features a gem of NPM, Ruby, similar to node. js. Pip makes it easy to install, upgrade, delete, and manage Python packages. As described earlier Ansible is to enable Python development, then natural Ansible can also be installed via PIP. For more details on PIP, please visit https://pypi.python.org/pypi/pip/.

Installing Ansible with PIP

Before using PIP, make sure that your system already has the Pthon Setuptools package installed

Yum–y Install Python-setuptools

Install PIP

Easy_install pip

Installing Ansible with PIP

Pip Install Ansbile

What do you think? Installing Ansible via PIP is not very simple, as long as the simple three steps to complete the operation. As long as the Python operating system is supported, you can use PIP installation.

OK, now if you do, you have installed the Ansible on your server.

Described earlier Ansible is the SSH protocol to manage the machine, Ansible the default use of SSH Keys communication, this is Ansible official highly recommended method, but if you want to use the password, of course, is also possible. In order to enable password authentication, using the--ask-pass option, in this article, the author uses the key authentication method to demonstrate, the reader can choose whether to use the password authentication or the key authentication according to the need.

Ansible combat

 use Ansible to execute commands on a remote server

After installing the Ansible, use Ansible to perform a command on the other server to confirm the connectivity of the Ansible server to other servers. Before executing the Ansible Management Server, you need to configure the server information. Ansible uses the file to store the server information you want to manage, this file is called the manifest file in Ansible, the default manifest file is stored in/etc/ansible/hosts, and if the file does not exist, you can create the file. Of course, you can also execute the path to the manifest file when you execute the Ansible command. The format of the manifest file is similar to the INI file, as follows:

[Webserver]  192.168.1.1  192.168.1.2  [databaseserver]  192.168.1.10  192.168.1.11

The bracket symbol is the group name, the group name can be customized, but it is recommended to use a descriptive name, such as Webserver, Databaseserver, the group name is the member IP within each group. The following author executes a command on a remote server via Ansible:

Ansible webserver-a "WhoAmI"

From the execution results of Ansible, we know that the Ansible server's connectivity to the remote server is no problem and the execution of the command succeeds. If you want to specify the path to the manifest file, then use the –i parameter, plus the file path, as follows:

Ansible webserver–i/etc/ansible/other-hosts–a "WhoAmI"

The above is a AD-HOC command, that is, the temporary execution of the command. Ad-hoc is a concept in Ansible, using Ad-hoc You can quickly complete the operation of the command, if your operation contains only command operations, but not the content of configuration management, it is highly recommended to use AD-HOC commands, such as file transfer, package management, user and group management, Service management and so on.

Managing MySQL replication with Ansible

Ansible comes with a lot of modules, which Ansible called "Module libraries." The module can be executed directly on the remote server, or it can be executed via playbooks, and the playbooks will be described later. Ansible's modules are very rich, including cloud service management, files, databases, commands, networks and many other aspects. I am here to demonstrate the management of MySQL replication by Ansible one of the modules "Mysql_replication". First, configure the MySQL master server with the slave server's MySQL environment, make it meet the configuration requirements of MySQL replication, and MySQL master and slave server to install the Python mysqldb module, through Ansible on the master, slave server Install MYSQLDB.

Ansible databaseserver-m yum-a "Name=mysql-python state=present"

Create a MySQL replication account on the MySQL master server

mysql -uroot -p 
 mysql > GRANT REPLICATION SLAVE,REPLICATION CLIENT
  ON *.* TO ‘repl‘@‘192.168.1.%‘ IDENTIFIED BY ‘123456‘; 
 mysql > flush privileges; 
 mysql > quit;

On the MySQL master server, open the MySQL binary logging feature and add the following in the [Mysqld] section of the/etc/my.cnf file:

Server-id = 1

Log-bin=mysql-bin

Restart MySQL for the settings to take effect.

/etc/init.d/mysqld restart

Modify the MySQL configuration file for the MySQL slave database and add the following in the [Mysqld] section of the/etc/my.cnf file:

Server-id = 2

Restart MySQL for the settings to take effect.

/etc/init.d/mysqld restart

Get the status of the MySQL master server via the Ansible mysql_replication module

ansible mysql-master -m mysql_replication 
				 -a "login_user=root login_password=123456 mode=getmaster"

The results of Ansible are shown in JSON format, and the result is output of MySQL's used binary log file name and the value of Position, which is recorded for both values.

Specify the MySQL master server and set replication information on the MySQL Slave server via the Ansible mysql_replication module.

ansible mysql-slave -m mysql_replication 
				 – a "login_user=root login_password=123456 
 mode=changemaster master_host=‘172.16.64.147‘ master_user=repl 
 master_password=123456 master_log_file=mysql.000003 master_log_pos=711"

Start replication on MySQL from the server

Ansible mysql-slave-m mysql_replication-a "Login_user=root login_password=123456 mode=startslave"

View MySQL Replication Status

Ansible mysql-slave-m mysql_replication-a "Login_user=root login_password=123456 mode=getslave"

Note that the Red box selection in the figure shows that the values for slave_io_running and slave_sql_running are "Yes", and the Slave_io_state status is "Waiting for master to send event", This means that a copy of the MySQL master-slave server just established via ansible is normal. Now you can try to add some data to the MySQL master server to see if it will sync synchronously from the server.

Manage MySQL replication with Ansible playbooks

It is not easy to use module commands by using Ansible's Ad-hoc command-line directives, but we still have to do a lot of things manually, such as installing MySQL, configuring the MySQL replication environment, and so on. Although these can also be done through the Ad-hoc command line, but if I want to configure MySQL replication on other servers next time, then I have to repeat the instructions again, if so, then this will be the same very bad thing. So Ansible have a way to solve this problem? Of course there is, Ansible playbooks can be a good solution to this problem, playbooks translated into Chinese is the meaning of the script, as the name implies, is all the operation in accordance with the agreed rules in the document definition of what to do, and then play a big play according to the script. The playbooks is ideal for deploying complex applications and configuration reuse. The playbooks statement is configured and you set the configuration push to the specified remote host application. Playbooks is the same as Saltstack's SLS (Salt State Tree) and puppet configuration management.

Playbooks uses YAML format, which is simple to use in grammar. The following author uses Ansilbe to implement MySQL installation, MySQL replication environment configuration, MySQL replication settings.

In order to organize and manage the files in Ansible effectively, the author uses the roles feature of Ansible Playbook, which is valid in Ansible version 1.2 or later. In the/etc/ansible directory, the author established a roles directory for the playbooks definition of the various roles, this is the Ansible official definition of the default roles directory, of course, you can add or modify the roles directory path, by modifying the/etc/ans The Roles_path value in the Ible/ansible.cfg file. The roles directory is the directory name for each application, and the name can be customized by applying the name command, but it is best to set a meaningful name, as in this example the Mysql,mysql command includes defaults, handlers, meta, tasks, Templates, VARs directory, respectively, corresponding to different functions, the names of these directories are Ansible the official definition of the good, can not be modified, the description of the directory function is shown in table 1. The default Ansible only handles the actions defined in the file name main.yml in each directory, and if you have multiple files, you can include other files in the Main.yml file.

Table 1. Description of each directory function

Directory name	Description
Defaults	Default Variable Storage directory
Handlers	Handlers (actions to be taken when a change occurs)
Meta	Role dependency Processing
Tasks	Task action definition for specific execution
Templates	Template file storage Directory
VARs	Variable file directory

MAIN.YML information for code 1.defaults directories

cat /etc/ansible/roles/mysql/defaults/main.yml 
 --- 
 mysql_port: 3306 
 mysql_bind_address: "0.0.0.0"
 mysql_root_db_pass: 123456 

 mysql_db: 
 - name: foo 
 replicate: yes 
 - name: bar 
 replicate: no 

 mysql_users: 
 - name: jack 
 pass: 123456 
 priv: "*.*:ALL"

 mysql_repl_user: 
 - name: repl 
 pass: 123456 

 mysql_repl_role: master 
 mysql_db_id: 7

This file contains some MySQL-related default variable information, where you set the MySQL port, the binding address, the root user's password, and the new database, user, replication user information.

MAIN.YML information for code 2.handlers directories

CAT/ETC/ANSIBLE/ROLES/MYSQL/HANDLERS/MAIN.YML  ---  -name:restart mysql  service:name={{mysql_service} } state=restarted

The main.yml file in the handlers directory contains operations that are available for invocation after the server changes after the tasks have been performed, in this case restarting the MySQL operation

MAIN.YML information in the Code 3.tasks directory

--- 
 - name: Add the OS specific variables 
 include_vars: "{{ ansible_os_family }}.yml"

 - name: Install the mysql packages in Redhat derivatives 
 yum: name={{ item }} state=installed 
 with_items: mysql_pkgs 
 when: ansible_os_family == ‘RedHat‘

 - name: Install the mysql packages in Debian derivatives 
 apt: name={{ item }} state=installed update_cache=yes 
 with_items: mysql_pkgs 
 environment: env 
 when: ansible_os_family == ‘Debian‘

 - name: Copy the my.cnf file 
 template: src=my.cnf.{{ ansible_os_family }}.j2 dest={{ mysql_conf_dir }}/my.cnf 
 notify: 
 - restart mysql 

 - name: Start the mysql services Redhat 
 service: name={{ mysql_service }} state=started enabled=yes 

 - name: update mysql root password for all root accounts 
 mysql_user: name=root host={{ item }} password={{ mysql_root_db_pass }} 
 with_items: 
 - "{{ ansible_hostname }}"
 - 127.0.0.1 
 - ::1 
 - localhost 
 when: ansible_hostname != ‘localhost‘ 

 - name: update mysql root password for all root accounts 
 mysql_user: name=root host={{ item }} password={{ mysql_root_db_pass }} 
 with_items: 
 - 127.0.0.1 
 - ::1 
 - localhost 
 when: ansible_hostname == ‘localhost‘ 


 - name: ensure anonymous users are not in the database 
 mysql_user: name=‘‘ host={{ item }} login_user=root
  login_password={{ mysql_root_db_pass }} state=absent 
 with_items: 
 - localhost 
 - "{{ ansible_hostname }}"

 - name: remove the test database 
 mysql_db: name=test login_user=root 
 login_password={{ mysql_root_db_pass }} state=absent 

 - name: Create the database‘s 
 mysql_db: name={{ item.name }} 
 login_user=root login_password={{ mysql_root_db_pass }} state=present 
 with_items: mysql_db 
 when: mysql_db|lower() != ‘none‘

 - name: Create the database users 
 mysql_user: login_user=root 
 login_password={{ mysql_root_db_pass }}
  name={{ item.name }} password={{ item.pass|default("foobar") }} 
 priv={{ item.priv|default("*.*:ALL") }} 
 state=present host={{ item.host | default("localhost") }} 
 with_items: mysql_users 
 when: mysql_users|lower() != ‘none‘

 - name: Create the replication users 
 mysql_user: login_user=root login_password={{ mysql_root_db_pass }} 
 name={{ item.name }} host="%" password={{ item.pass|default("foobar") }} 
 priv=*.*:"REPLICATION SLAVE" state=present 
 with_items: mysql_repl_user 
 when: mysql_repl_role == ‘master‘

 - name: Check if slave is already configured for replication 
 mysql_replication: login_user=root 
 login_password={{ mysql_root_db_pass }} mode=getslave 
 ignore_errors: true 
 register: slave 
 when: mysql_repl_role == ‘slave‘

 - name: Ensure the hostname entry 
 for master is available for the client. 
 lineinfile: dest=/etc/hosts regexp="{{ mysql_repl_master }}" 
 line="{{ mysql_repl_master + " " +
  hostvars[mysql_repl_master].ansible_default_ipv4.address }}"
   state=present 
 when: slave|failed and mysql_repl_role == ‘slave‘
  and mysql_repl_master is defined 

 - name: Get the current master servers replication status 
 mysql_replication: login_user=root login_password=
 {{ mysql_root_db_pass }} mode=getmaster 
 delegate_to: "{{ mysql_repl_master }}"
 register: repl_stat 
 when: slave|failed and mysql_repl_role == ‘slave‘ 
 and mysql_repl_master is defined 

 - name: Change the master in slave to start the replication 
 mysql_replication: login_user=root 
 login_password={{ mysql_root_db_pass }} mode=changemaster
  master_host={{ mysql_repl_master }}
   master_log_file={{ repl_stat.File }} 
   master_log_pos={{ repl_stat.Position }} 
   master_user={{ mysql_repl_user[0].name }} 
   master_password={{ mysql_repl_user[0].pass }} 
 when: slave|failed and mysql_repl_role == ‘slave‘
  and mysql_repl_master is defined 

 - name: start slave in slave to start the replication 
 mysql_replication: login_user=root login_password={{ mysql_root_db_pass }}
  mode=startslave 
 when: slave|failed and mysql_repl_role == ‘slave‘ 
 and mysql_repl_master is defined

The main.yml in the Tasks directory includes all the actions to be performed in Playbooks, each of which has a name for a simple description of the operation, and the tasks in this example include the following:

• MySQL installation;
• MySQL Administrator user root password modification;
• MySQL configuration file generation;
• Delete the test library;
• Deletion of empty users;
• Creation of the database (if necessary);
• Creation and empowerment of database users (hardship required);
• Copy user creation and password settings;
• Gets the master state;
• Slave Connect master and turn on replication.

Ansible playbooks Each task is executed sequentially, this can be seen Ansible playbooks execution process.

My.cnf.RedHat.j2 in the Code 4.templates directory

cat /etc/ansible/roles/mysql/templates/my.cnf.RedHat.j2 
 [mysqld] 
 datadir=/var/lib/mysql 
 socket=/var/lib/mysql/mysql.sock 
 user=mysql 
 # Disabling symbolic-links is recommended to 
 prevent assorted security risks 
 symbolic-links=0 
 port={{ mysql_port }} 
 bind-address={{ mysql_bind_address }} 
 log_bin = mysql-bin 
 server-id = {{ mysql_db_id }} 

 {% if mysql_repl_role == ‘master‘ %} 
 #log_bin = mysql-bin 
 expire_logs_days = 10 
 max_binlog_size = 100M 

 {% for i in mysql_db %} 
 {% if i.replicate|default(1) %} 
 binlog_do_db = {{ i.name }} 
 {% endif %} 
 {% endfor %} 

 {% for i in mysql_db %} 
 {% if not i.replicate|default(1) %} 
 binlog_ignore_db = {{ i.name }} 
 {% endif %} 
 {% endfor %} 
 {% endif %} 

 [mysqld_safe] 
 log-error=/var/log/mysqld.log 
 pid-file=/var/run/mysqld/mysqld.pid

A sample MySQL configuration file is included in the Templates directory.

Main.yml in the Code 5.vars directory

CAT/ETC/ANSIBLE/ROLES/MYSQL/VARS/MAIN.YML  ---  env:  runlevel:1

Redhat.yml in the Code 6.vars directory

cat /etc/ansible/roles/mysql/vars/RedHat.yml 
 --- 
 mysql_pkgs: 
 - libselinux-python 
 - mysql-server 
 - MySQL-python 

 mysql_service: mysqld 

 mysql_conf_dir: "/etc/"

The VARs directory is the variable information involved in this operation.

Code 7.ansible playbook configuration file to perform MySQL replication configuration mysql_repl.yml

cat /etc/ansible/mysql_repl.yml 
 - hosts: mysql-master 
 roles: 
 - {role: mysql, mysql_db: none,mysql_users: 
 [{name: jack, pass: 123456, priv: "*.*:ALL"}],mysql_db_id: 1008 } 

 - hosts: mysql-slave 
 roles: 
 - {role: mysql, mysql_db: none, 
 mysql_users: none,mysql_repl_role: slave,
  mysql_repl_master: 192.168.1.10,mysql_db_id:1009,
   mysql_repl_user: [{name: repl, pass: 123456}] }

Install and configure the MySQL server and complete the MySQL master-slave replication settings.

Adding server information in the/etc/ansible/hosts file

cat /etc/ansible/hosts 
 [mysql-master] 
 192.168.1.10 
 [mysql-slave] 
 192.168.1.11

Configuring MySQL replication with Ansible Playbook

ansible-playbook mysql_repl.yml 
 PLAY [mysql-master] *********************************************************** 

 GATHERING FACTS *************************************************************** 
 ok: [192.168.1.10] 

 TASK: [mysql | Add the OS specific variables] ********************************* 
 ok: [192.168.1.10] 

 TASK: [mysql | Install the mysql packages in Redhat derivatives] ************** 
 changed: [192.168.1.10] => (item=libselinux-python,mysql-server,MySQL-python) 

 TASK: [mysql | Install the mysql packages in Debian derivatives] ************** 
 skipping: [192.168.1.10] 

 TASK: [mysql | Copy the my.cnf file] ****************************************** 
 ok: [192.168.1.10] 

 TASK: [mysql | Start the mysql services Redhat] ******************************* 
 changed: [192.168.1.10] 

 TASK: [mysql | update mysql root password for all root accounts] ************** 
 changed: [192.168.1.10] => (item=client001) 
 changed: [192.168.1.10] => (item=127.0.0.1) 
 changed: [192.168.1.10] => (item=::1) 
 changed: [192.168.1.10] => (item=localhost) 

 TASK: [mysql | update mysql root password for all root accounts] ************** 
 skipping: [192.168.1.10] => (item=127.0.0.1) 
 skipping: [192.168.1.10] => (item=::1) 
 skipping: [192.168.1.10] => (item=localhost) 

 TASK: [mysql | ensure anonymous users are not in the database] **************** 
 changed: [192.168.1.10] => (item=localhost) 
 ok: [192.168.1.10] => (item=client001) 

 TASK: [mysql | remove the test database] ************************************** 
 changed: [192.168.1.10] 

 TASK: [mysql | Create the database‘s] ***************************************** 
 changed: [192.168.1.10] => (item={‘name‘: ‘benz‘}) 
 changed: [192.168.1.10] => (item={‘name‘: ‘benz2‘}) 

 TASK: [mysql | Create the database users] ************************************* 
 changed: [192.168.1.10] => (item={‘pass‘: ‘foobar‘, ‘name‘: ‘ben3‘, ‘priv‘: ‘*.*:ALL‘}) 
 changed: [192.168.1.10] => (item={‘name‘: ‘ben2‘, ‘pass‘: ‘foo‘}) 

 TASK: [mysql | Create the replication users] ********************************** 
 changed: [192.168.1.10] => (item={‘name‘: ‘repl‘, ‘pass‘: ‘foobar‘}) 

 TASK: [mysql | Check if slave is already configured for replication] ********** 
 skipping: [192.168.1.10] 

 TASK: [mysql | Ensure the hostname entry for master is available for the client.] *** 
 skipping: [192.168.1.10] 

 TASK: [mysql | Get the current master servers replication status] ************* 
 skipping: [192.168.1.10] 

 TASK: [mysql | Change the master in slave to start the replication] *********** 
 skipping: [192.168.1.10] 

 TASK: [mysql | start slave in slave to start the replication] ***************** 
 skipping: [192.168.1.10] 
 #### Limited to space, part of the output is omitted ####### 

 PLAY RECAP ******************************************************************** 
 192.168.1.10 : ok=12 changed=8 unreachable=0 failed=0 
 192.168.1.11 : ok=17 changed=8 unreachable=0 failed=0

When you look at the output, you'll see that the ansible task is executed sequentially, each of which displays the name and state of the task, which is a good way to see the status of the task execution. Then at the end of the task (that is, PLAY RECAP) is a summary of the status statistics for each task, including the execution of each task, such as the number of successful executions, the number of changes, and the number of errors. Here you can tell if the task completed successfully.

Comparison of Ansible and other configuration management

The author chooses the current several mainstream and Ansible function similar configuration management software Puppet, Saltstack, here The contrast does not target each software performance to compare, only to each software characteristic makes a contrast. The specific contents are shown in Table 1:

Table 2.Ansible vs. Puppet vs. Saltstack comparison

	Puppet	saltstack	Ansible
Development language	Ruby	Python	Python
Whether there is a client	Yes	Yes	No
Two Development support	Not supported	Support	Support
Whether the server and the remote machine authenticate each other	Is	Is	Is
Whether the server communicates with the remote machine for encryption	Yes, Standard SSL protocol	Yes, use AES encryption	Yes, use OpenSSH
Platform Support	Supports AIX, BSD, HP-UX, Linux, MacOSX, Solaris, Windows	Support for BSD, Linux, Mac OS X, Solaris, Windows	Supports AIX, BSD, HP-UX, Linux, Mac OSX, Solaris
Whether to provide a Web UI	Provide	Provide	Available, but commercial version
Configuration file format	Ruby syntax format	Yaml	Yaml
Command line execution	Not supported, but can be implemented by configuring the module	Support	Support

Conclusion:

Ansible is an emerging IT automation management tool. It now has more than 1 million downloads. On GitHub, it is the top 10 Python project. It can be foreseen that the development of Ansible is limitless. In this article, I use anaible to manage MySQL replication to introduce Ansible some usage methods and application scenarios, hoping that this article will allow everyone to fall in love with this super Energy system automation management tool.

Managing MySQL replication with Ansible