Install mariadb node of galera + haproxy + keepalived

Easy playbook for this:

- name: INSTALLATION
  hosts: testcluster
  gather_facts: no
  roles:
#    - prerequest
    - rh_mariadb_install
    - firewalld
    - haproxy_keepalived

  handlers:
    - include: handlers/main.yml

And lets go!

j2 config for rh-mariadb102-mariadb

[mysqld]
datadir=/home/opt/rh/rh-mariadb102/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/home/opt/rh/rh-mariadb102/log/mariadb/mariadb.log
pid-file=/run/rh-mariadb102-mariadb/mariadb.pid
character-set-server=utf8

# network
connect_timeout = 60
wait_timeout = 28800
interactive_timeout = 28800
max_connections = 200
max_connect_errors = 100
max_allowed_packet = 128M

event_scheduler = ON
back_log = 50 

# innodb
default_storage_engine=InnoDB
innodb_file_per_table = 1
innodb_log_buffer_size = 128M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_buffer_pool_size = 6G

[galera]
wsrep_on=ON
wsrep_provider=/opt/rh/rh-mariadb102/root/usr/lib64/galera/libgalera_smm.so

#U must change this after installation
wsrep_cluster_address=gcomm://

binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="Cluster"
wsrep_node_address={{ inventory_hostname }}
wsrep_sst_method=rsync

role rh_mariadb_install:

- name: Install
  block:
    - name: install centos-release
      yum:
        name: 
          - centos-release-scl-rh
          - centos-release-scl
        state: present

    - name: install mariadb102
      yum:
        name:
        - rh-mariadb102-mariadb-server
        - rh-mariadb102-mariadb-server-galera
        enablerepo: centos-sclo-rh
        state: present
      notify:     
        - add utf8 into mysqld section
        - copy var in home
        - move galera.cnf    

# ansible cant create normal file in directory /etc/profile.d/
# ansible stops on command 'scl enable rh-mariadb102' bash because that invoke subshell
# ansible not change environmnet variables in session, even 'source /opt/rh/rh-mariadb102/enable' not work
    - name: COPY rh-mariadb102.sh
      copy:
        src: "{{ item.source }}"
        dest: "{{ item.dest }}"
#        owner: root
#        group: root
      loop:
#         - { source: 'templates/rh-mariadb102.sh', dest: '/etc/profile.d/rh-mariadb102.sh' }
         - { source: 'templates/mysql_secure_installation.sql', dest: '/etc/my.cnf.d/mysql_secure_installation.sql' }
#      notify:
#        - enable rh-mariadb102 bash 
#        - start rh-mariadb102-mariadb 

    - name: COPY mariadb-server.cnf
      template:
        src: "{{ item.source }}"
        dest: "{{ item.dest }}"
        owner: root
        group: root
      loop:
         - { source: 'templates/mariadb-server.j2', dest: '/etc/opt/rh/rh-mariadb102/my.cnf.d/mariadb-server.cnf' }

#    - name: start rh-mariadb102-mariadb
#      service:
#        name: rh-mariadb102-mariadb
#        state: started
#        enabled: yes
#      ignore_errors: yes

#    - name: auto secure-install mysql
#      shell: mysql -sfu root < /etc/my.cnf.d/mysql_secure_installation.sql

  ignore_errors: yes

role firewalld:

- name: firewall
  block:
    - name: start firewalld
      service:
        name: firewalld
        state: started
        enabled: yes

    - name: mysql firewalld
      firewalld:
        service: mysql
        permanent: true
        state: enabled

    - name: ports firewalld
      firewalld:
        port: "{{ item.port }}/tcp"
        permanent: true
        state: enabled
      loop:
         - { port: 3306 }
         - { port: 4567 }
         - { port: 4568 }
         - { port: 4444 }
      notify: firewall reload


  ignore_errors: yes

j2 config for haproxy:

#The connect option specifies the maximum time to wait for a connection attempt to a VPS to succeed.
#The client and server timeouts apply when the client or server is expected to acknowledge or send data during the TCP process. 
#HAProxy recommends setting the client and server timeouts to the same value.

The retries directive sets the number of retries to perform on a VPS after a connection failure.
frontend mariadb
bind {{ inventory_hostname }}:3307
timeout connect 30m
timeout client 3h
timeout server 3h
mode tcp
default_backend mariadb_galera
backend mariadb_galera
balance source
mode tcp
option tcpka
timeout connect 30m
timeout client 3h
timeout server 3h
option mysql-check user haproxy
server HOSTNAME IPADDRESS:3306 check weight 1
server HOSTNAME IPADDRESS:3306 check weight 1
server HOSTNAME IPADDRESS:3306 check weight 1

j2 config for keepalived:

global_defs {
router_id HOSTNAME
}
vrrp_script haproxy {
script "killall -0 haproxy" 
interval 2
weight 2
}
vrrp_instance 50 {
virtual_router_id 50
advert_int 1
priority 90
state BACKUP
interface ens192
virtual_ipaddress {
VIRTUAL_IPADDRESS dev ens192
}
track_script {
haproxy
}
}

role haproxy_keepalived:

- name: Install haproxy and keepalived
  block:
    - name: install haproxy and keepalived
      yum:
        name: 
          - haproxy
          - keepalived
        enablerepo: centos-sclo-rh
        state: present

    - name: COPY configs
      template:
        src: "{{ item.source }}"
        dest: "{{ item.dest }}"
        owner: root
        group: root
      loop:
         - { source: 'templates/keepalived.j2', dest: '/etc/keepalived/keepalived.conf' }
         - { source: 'templates/haproxy.j2', dest: '/etc/haproxy/haproxy.cfg' }

##need add two strings in /etc/sysctl.conf https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/load_balancer_administration/s1-initial-setup-forwarding-vsa
#net.ipv4.ip_forward = 1
#net.ipv4.ip_nonlocal_bind = 1
##Apply changes:
#sysctl -p
#https://packetpushers.net/vrrp-linux-using-keepalived-2/
  ignore_errors: yes

handlers:

- name: save iptables
  shell: iptables-save > /etc/sysconfig/iptables
  ignore_errors: yes

- name: start zabbix-agent
  service:
    name: zabbix-agent
    state: started
  ignore_errors: yes

- name: enable zabbix-agent
  command: chkconfig zabbix-agent on

- name: add utf8 into mysqld section
  shell: sed -i '/mysqld]/a character-set-server=utf8' /etc/opt/rh/rh-mariadb102/my.cnf.d/mariadb-server.cnf

- name: firewall reload
  systemd:
    name: firewalld
    state: reloaded

- name: move galera.cnf 
  shell: mv /etc/opt/rh/rh-mariadb102/my.cnf.d/galera.cnf /etc/opt/rh/rh-mariadb102/my.cnf.d/galera.cnf.org

#change database directory to home
- name: copy var in home
  shell: cp -pR /var/opt /home/opt

# Not works
#- name: enable rh-mariadb102 bash
#  shell: /etc/profile.d/rh-mariadb102.sh
#  shell: scl enable rh-mariadb102 bash
#  shell: source /opt/rh/rh-mariadb102/enable

Use scl enable rh-mariadb102 bash for export evironment variables.(or source /opt/rh/rh-mariadb102/enable) For save this variables after restart, create /etc/profile.d/rh-mariadb102.sh. This file not works if created in playbook (idk why?)

# create new
source /opt/rh/rh-mariadb102/enable
export X_SCLS="`scl enable rh-mariadb102 'echo $X_SCLS'`"

Happy end, or not

# Need for haproxy
echo "net.ipv4.ip_forward = 1" >> /etc/sysctl.conf
echo "net.ipv4.ip_nonlocal_bind = 1" >> /etc/sysctl.conf
sysctl -p

# Maria run
service rh-mariadb102-mariadb start
systemctl enable rh-mariadb102-mariadb
mysql -sfu root < /etc/my.cnf.d/mysql_secure_installation.sql

#Need import database scheme and backup of database
mkdir /backup
yum install cifs-utils
vi /etc/fstab
mount -a
zcat /backup/zabbix-server-mysql/create.sql.gz | mysql -uroot -p
gunzip < /backup/zabbix_backup_without_history.sql.gz | mysql -uroot -p

# Pul all nodes in hosts
vi /etc/hosts
# set hostname
hostnamectl set-hostname zabbixdb

#There can be surprise with logs of maria
mkdir /var/log/mysql
chmod 777 /var/log/mysql

# last change config to production and restart
vi /etc/opt/rh/rh-mariadb102/my.cnf.d/mariadb-server.cnf
service rh-mariadb102-mariadb stop
service rh-mariadb102-mariadb start

Built on GitHub with MkDocs and Pure theme