This is the 14th day of my August Challenge.

The paper

Ansible Automation: Compiling and installing mysql Introduces how to compile and install mysql automatically, and how to implement master/slave synchronization based on user-defined parameters. Here’s what we’re going to cover in this article.

Configuration idea

Ansible works with modules such as mysql_user, mysql_DB, and mysql_replication to create databases, create users, and configure primary and secondary servers on clients.

The directory structure

The Playbook directory is organized as follows:

  • Files: Stores source files and configuration files that need to be synchronized to a remote server.
  • Handlers: Operations to be performed when resources change. If there is no such directory, do not create it or leave it empty.
  • Meta: Role definition can be left blank.
  • Tasks: Tasks that need to be executed during the mysql master/slave configuration process
  • Templates: For template files, usually scripts, that need to be synchronized to the database.
  • Vars: variables defined in this installation.
[root@test ansible]# mkdir -p roles/mysql_install/{files,handlers,meta,tasks,templates,vars}
[root@test ansible]# tree /etc/ansible├ ─ ─ ansible. CFG ├ ─ ─ hosts ├ ─ ─ mysql_repl. Yml | ____roles | | ____mysql_repl | | | ____files | | | ____meta | | |____templates | | | |____modify_repldb.sh | | |____handlers | | | |____main.yml | | |____tasks | | | |____main.yml | | | ____vars
| | | |____main.yml
Copy the code

The specific implementation

1. Add the hosts file

The mysql master and slave roles are divided into the following two groups

[mysql_slave]
128.19611.61. ansible_ssh_user=root ansible_ssh_pass=Root
[mysql_master]
128.19611.60. ansible_ssh_user=root ansible_ssh_pass=Root
Copy the code

2. Create a mysql role file to call mysql_repl

# vim mysql_repl.yml 
- hosts: mysql_master
  remote_user: root
  gather_facts: False
  roles:
    - {role: mysql_repl,mysql_repl_role: master}

- hosts: mysql_slave
  remote_user: root
  gather_facts: False
  roles:
    - {role: mysql_repl,mysql_repl_role: slave,mysql_repl_master: 128.19611.60.,mysql_repl_user: [{name: repl,passwd: repl}]}
Copy the code

Since the master and slave use the same script above, we define the master and slave roles in the ROLE role through mysql_REPL_role to distinguish between different operations performed in different roles, and specify the synchronous user through mysql_repl_user, which needs to be the same as in vars.

3. Create a variable file

The related variables are used as follows:

  • Source_dir: directory for storing client scripts.

  • Mysql_root_pwd: password of user root;

  • Mysql_port: database port;

  • Socket: database socket. Ansible uses the socket to log in to the database.

  • Mysql_db: name is the name of the database. Replicate defines which libraries need to be synchronized and which do not. This parameter is set for the modify_REPLdb. sh script call to modify the my.cnf configuration file.

  • Mysql_remote_user: defines remote use account information and grants related permissions;

  • Mysql_repl_user: Synchronous account information is defined and permissions are granted;

# vim vars/main.yml
source_dir: /home/db/mysql/src/
mysql_root_pwd: test2017
mysql_port: 13306
socket: /tmp/mysql.sock

mysql_db:
- name: test1
  replicate: yes
- name: test2
  replicate: no
- name: test3
  replicate: no

mysql_remote_user:
- name: remote
  passwd: remote
  priv: "*.*:ALL"

mysql_repl_user:
- name: repl
  passwd: repl
  priv: '*.*:"REPLICATION SLAVE"'
Copy the code

4. Create task files

The automatic configuration master/slave synchronization step has a condition when set to prevent the master/slave from being automatically configured even if there is no dependency condition, which causes us to waste time searching for files. Here are a few things to note:

  1. The “create REPl Database” step is intended to synchronize the master to slave after the database is created. However, the test found that the database is created in advance and cannot be synchronized to the slave. However, after the synchronization is established, the database can be synchronized again, so this step is commented out;
  2. The “restart mysqld service” step is intended to use the handlers, but it is found that modifying the configuration file does not immediately trigger the restart of the mysql handlers. The handlers are the last to be executed after all the steps have been completed, so the slave cannot be automatically started. So we define handlers, but we don’t use them;
  3. Ignore_errors is used to ignore the failure of executing a step, which causes the entire Ansible to exit
  4. Delegate_to delegate_to delegate the task to getMaster while Changemaster is being executed on the master.
  5. Register is to store the obtained information into the related variables;
# vim tasks/main.yml
# 1. Client must have mysql-Python installed
- name: install MySQL-python
  yum: name=MySQL-python
 
# This step is to create a repository
#- name: create repl database
#  mysql_db: name={{item.name}} login_host=127.0.0.1 login_port={{mysql_port}} login_user=root login_password={{mysql_root_pwd}} state=present
# with_items: mysql_db
# when: mysql_repl_role == "master"

# 2. Create a remote user
- name: create database remote user
  mysql_user: login_host=127.0. 01. login_port={{mysql_port}} login_user=root login_password={{mysql_root_pwd}} name={{item.name}} password={{item.passwd}} priv={{item.priv}} state=present host="%"with_items: mysql_remote_user when: mysql_remote_user|lower() ! ='none'

# 3. Create a synchronous account on the primary repository
- name: create replication user
  mysql_user: login_host=127.0. 01. login_port={{mysql_port}} login_user=root login_password={{mysql_root_pwd}} name={{item.name}} password={{item.passwd}} priv={{item.priv}} state=present host="%"
  with_items: mysql_repl_user
  when: mysql_repl_role == "master"

# 4. Copy script to client
- name: copy modify replication db script to client
  template: src=modify_repldb.sh dest={{source_dir}} owner=root group=root mode=0775

# 5. Run the script to modify my.cnf according to master and slave
- name: modify replication db in my.cnf
  shell: bash {{source_dir}}/modify_repldb.sh

# 6. Modify the configuration file and restart the database
- name: restart mysqld service
  #service: name=mysqld state=restarted
  command: su mysql -c "service mysqld restart"

# 7. Check whether master/slave information has been configured on the slave. If it is not false, save it in the slave variable.
- name: check if slave is already configured for replication
  mysql_replication: login_unix_socket={{socket}} login_user=root login_password={{mysql_root_pwd}} mode=getslave
  ignore_errors: true
  register: slave
  when: mysql_repl_role == "slave"

Mysql_repl_master = mysql_master; mysql_master = mysql_master; mysql_master = mysql_master; And store this information in the REPL_MASTER_STATUS variable
- name: get the current master servers replication status
  mysql_replication: login_unix_socket={{socket}} login_user=root login_password={{mysql_root_pwd}} mode=getmaster
  delegate_to: "{{mysql_repl_master}}"
  register: repl_master_status
  when: slave|failed and mysql_repl_role == "slave" and mysql_repl_master is defined

# 9. Execute Changemaster on the slave if the slave is false and the slave defines the master (mysql_REPL_master) to be synchronized
- name: change the master on slave to start the replication
  mysql_replication: login_unix_socket={{socket}} login_user=root login_password={{mysql_root_pwd}} mode=changemaster master_host={{mysql_repl_master}} master_port={{mysql_port}} master_log_file={{repl_master_status.File}} master_log_pos={{repl_master_status.Position}} master_user={{mysql_repl_user[0].name}} master_password={{mysql_repl_user[0].passwd}}
  ignore_errors: true
  when: slave|failed and mysql_repl_role == "slave" and mysql_repl_master is defined

# 10. Start synchronization on the slave
- name: start slave on slave to start the replication
  mysql_replication: login_unix_socket={{socket}} login_user=root login_password={{mysql_root_pwd}} mode=startslave
  when: slave|failed and mysql_repl_role == "slave" and mysql_repl_master is defined

Copy the code

5. Write a template script

The role of this template script is to take the parameters in mysql_db in the vars variable, and write the libraries that need to be synchronized or asynchronced to my.cnf, based on replicates: yes and Replicates: no.

# vim templates/modify_repldb.sh
#! /bin/bash
#content: modify replication db

{% if mysql_repl_role == "master" %}
{% for i in mysql_db %}
{% if i.replicate|default(1) %}
sed -i "/server-id/a replicate-do-db={{i.name}}" /etc/my.cnf
sed -i "/server-id/a binlog-do-db={{i.name}}" /etc/my.cnf
{% endif %}
{% endfor %}

{% for i in mysql_db %}
{% if not i.replicate|default(1) %}
sed -i "/server-id/a binlog-ignore-db={{i.name}}" /etc/my.cnf
{% endif %}
{% endfor %}
{% endif %}


{% if mysql_repl_role == "slave" %}
{% for i in mysql_db %}
{% if i.replicate|default(1) %}
sed -i "/server-id/a replicate-do-db={{i.name}}" /etc/my.cnf
{% endif %}
{% endfor %}

{% for i in mysql_db %}
{% if not i.replicate|default(1) %}
sed -i "/server-id/a binlog-ignore-db={{i.name}}" /etc/my.cnf
{% endif %}
{% endfor %}
{% endif %}
Copy the code

6. Customize deployment

With this in mind, we can deploy a set of master and slave at will. We can customize the master and slave by defining variables in mysql_repl.yml and vars/main.yml.


# Check files
[root@test ansible]# ansible-playbook -C mysql_repl.yml
# to perform the playbook
[root@test ansible]# ansible-playbook mysql_repl.yml
Copy the code

When we’re done, we create replicate, test1, test2, and test3 on the master. You can see that test1 is synchronized on the slave, and test2 and test3 are not, because we set test1 with a property called REPLICATE: yes.

conclusion

So far, the realization of automatic operation of mysql provides a preliminary idea for the related automatic operation of data layer. Subsequent requirements can also be implemented through playBook orchestration.