Ansible installs multi-instance MariaDB 10.2.23

Battlestar Configuration

Place mariadb binary packages under root home directory

[Mon Apr 29 19:52
 root@ansible-centos7 ~]# ls
 -rw-r--r--  1 root root 456950538 Apr 26 08:16 mariadb-10.2.23-linux-x86_64.tar.gz

Create ansible directory under / data directory and roles folder internally

[Mon Apr 29 20:05
 root@ansible-centos7 /data/ansible] # ll
drwxr-xr-x 5 root root 4096 Apr 29 09:40 roles

Create the directories you need under roles directory

[Mon Apr 29 19:54
 root@ansible-centos7 /data/ansible/roles]# mkdir mariadb/{tasks,templates,vars} -pv

View the current directory structure

[Mon Apr 29 20:05
 root@ansible-centos7 /data/ansible/roles]# tree mariadb/
mariadb/
|-- tasks
|-- templates
|-- vars

Create a database management account group.yml:

[Mon Apr 29 20:16
 root@ansible-centos7 /data/ansible/roles]# vim mariadb/tasks/group.yml
  - name: create group                                                                                                           group: name=mysql system=yes gid=336

Create a database management account.yml:

[Mon Apr 29 20:06
 root@ansible-centos7 /data/ansible/roles]# vim mariadb/tasks/user.yml
  - name: create user                                                                                                          
    user: name=mysql uid=336 system=yes group=mysql home=/data/mysql create_home=no

Create the required storage directory for multiple instances.yml:

[Mon Apr 29 20:17
 root@ansible-centos7 /data/ansible/roles]# vim mariadb/tasks/mkdir.yml
 - name: mkdir /data/mysql/...
   shell: mkdir -pv /data/mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid}

Modify storage directory permissions.yml:

[Mon Apr 29 20:20
 root@ansible-centos7 /data/ansible/roles]# vim mariadb/tasks/chown.yml
 - name: change /data/mysql owner
   shell: chown -R mysql.mysql /data/mysql 

Unzip the mariadb package to the controlled host/usr/local/.yml:

[Mon Apr 29 20:20
 root@ansible-centos7 /data/ansible/roles]# vim mariadb/tasks/unarchive.yml
 - name: unarchive mariadb.tar
   unarchive: src=/root/{{package}} dest=/usr/local/
 - name: create link
   file: src=/usr/local/{{package1}} path=/usr/local/mysql state=link

Generate database content.yml:

[Mon Apr 29 20:27
 root@ansible-centos7 /data/ansible/roles]# vim mariadb/tasks/scp.yml
 - name: ctreate the database
   shell: cd /usr/local/mysql/; ./scripts/mysql_install_db --datadir=/data/mysql/{{item}}/data  --user=mysql           
   with_items:
    - 3306
    - 3307
    - 3308

Copy my.cnf.j2 to generate my.cnf configuration file.yml:

[Mon Apr 29 20:30
 root@ansible-centos7 /data/ansible/roles]# vim mariadb/tasks/config.yml
 - name: copy config1                                                                                              
   template: src=my.cnf.j2 dest=/data/mysql/{{port1}}/etc/my.cnf
 - name: copy config2
   template: src=my1.cnf.j2 dest=/data/mysql/{{port2}}/etc/my.cnf
 - name: copy config3
   template: src=my2.cnf.j2 dest=/data/mysql/{{port3}}/etc/my.cnf

Copy template file to generate startup service file.yml:

[Mon Apr 29 20:31
 root@ansible-centos7 /data/ansible/roles]# vim mariadb/tasks/mysqld.yml
 - name: copy service1                                                                                          
   template: src=mysqld.j2 dest=/data/mysql/{{port1}}/bin/mysqld mode=754
 - name: copy service2
   template: src=mysqld1.j2 dest=/data/mysql/{{port2}}/bin/mysqld1 mode=754
 - name: copy service3
   template: src=mysqld2.j2 dest=/data/mysql/{{port3}}/bin/mysqld2 mode=754

Write the main file.yml:

[Mon Apr 29 20:31
 root@ansible-centos7 /data/ansible/roles]# vim mariadb/tasks/main.yml
 - include: group.yml                                                                                              
 - include: user.yml
 - include: mkdir.yml
 - include: unarchive.yml
 - include: scp.yml
 - include: chown.yml
 - include: config.yml
 - include: mysqld.yml

Copy template files needed

[Mon Apr 29 20:37
 root@ansible-centos7 /data/ansible/roles]# cp /etc/my.cnf mariadb/templates/my.cnf.j2

Modify my.cnf.j2 template file, this file needs to copy three copies in the current directory, named my1.cnf.j2,my2,cnf.j2, strain {port1}, {port2}, and {port3} respectively.
my.cnf.j2

[Mon Apr 29 20:38
 root@ansible-centos7 /data/ansible/roles]# vim mariadb/templates/my.cnf.j2
[mysqld]                                                                                                               
port={{port1}}
datadir=/data/mysql/{{port1}}/data
socket=/data/mysql/{{port1}}/socket/mysql.sock

[mysqld_safe]
log-error=/data/mysql/{{port1}}/log/mariadb.log
pid-file=/data/mysql/{{port1}}/pid/mariadb.pid
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

my1.cnf.j2

[Mon Apr 29 20:43
 root@ansible-centos7 /data/ansible/roles]# vim mariadb/templates/my1.cnf.j2
[mysqld]
port={{port2}}
datadir=/data/mysql/{{port2}}/data
socket=/data/mysql/{{port2}}/socket/mysql.sock

[mysqld_safe]
log-error=/data/mysql/{{port2}}/log/mariadb.log
pid-file=/data/mysql/{{port2}}/pid/mariadb.pid                                                                         
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

my2.cnf.j2

[Mon Apr 29 20:41
 root@ansible-centos7 /data/ansible/roles]# vim mariadb/templates/my2.cnf.j2
 [mysqld]
port={{port3}}
datadir=/data/mysql/{{port3}}/data
socket=/data/mysql/{{port3}}/socket/mysql.sock

[mysqld_safe]
log-error=/data/mysql/{{port3}}/log/mariadb.log
pid-file=/data/mysql/{{port3}}/pid/mariadb.pid                                                                         
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Service startup script, this script will also copy three copies into the templates directory, named mysqld.j2,mysqld1.j2,mysqld2.j2: internal need to change the port, variable automatic generation

[Mon Apr 29 20:47
 root@ansible-centos7 /data/ansible/roles]# vim mariadb/templates/mysqld.j2
#!/bin/bash                                                                                                            

port={{port1}}
mysql_user="root"
mysql_pwd=""
cmd_path="/usr/local/mysql/bin"
mysql_basedir="/data/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf  &> /dev/null  &
    else
      printf "MySQL is running...\n"
      exit
    fi
}

function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
   fi
}

function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}

case $1 in
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac                                                        

Variables used above are defined in the vars directory

[Mon Apr 29 20:43
 root@ansible-centos7 /data/ansible/roles]# vim mariadb/vars/main.yml
package: mariadb-10.2.23-linux-x86_64.tar.gz                      
package1: mariadb-10.2.23-linux-x86_64
port1: 3306
port2: 3307
port3: 3308

Create the main script mariadb.yml in the anslible directory

[Mon Apr 29 20:52
 root@ansible-centos7 /data/ansible]# vim mariadb.yml
---                                                                                                                    
 - hosts: 172.16.36.112
   remote_user: root
   roles:
    - role: mariadb

The final directory structure:

|-- mariadb
    |   |-- tasks
    |   |   |-- chown.yml
    |   |   |-- config.yml
    |   |   |-- group.yml
    |   |   |-- main.yml
    |   |   |-- mkdir.yml
    |   |   |-- mysqld.yml
    |   |   |-- scp.yml
    |   |   |-- unarchive.yml
    |   |   `-- user.yml
    |   |-- templates
    |   |   |-- my1.cnf.j2
    |   |   |-- my2.cnf.j2
    |   |   |-- my.cnf.j2
    |   |   |-- mysqld1.j2
    |   |   |-- mysqld2.j2
    |   |   `-- mysqld.j2
    |   `-- vars
    |       `-- main.yml

After the script runs out

ansbile 172.16.36.112 -m shell -a 'echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mariadb.sh;source /etc/profile.d/mariadb.sh'

Startup may be slightly inconvenient due to the use of multi-instance-generated databases
Using the following commands on the 172.16.36.112 host, ss-tnlp can see the success of three ports and the corresponding mysqld service.

# bash /data/mysql/3306/bin/mysqld start
# bash /data/mysql/3307/bin/mysqld1 start
# bash /data/mysql/3308/bin/mysqld2 start
[root@node2-centos7 ~]# ss -tnlp
State       Recv-Q Send-Q              Local Address:Port                             Peer Address:Port              
LISTEN      0      128                             *:111                                         *:*                   users:(("rpcbind",pid=6100,fd=4),("systemd",pid=1,fd=81))
LISTEN      0      128                             *:22                                          *:*                   users:(("sshd",pid=6456,fd=3))
LISTEN      0      100                     127.0.0.1:25                                          *:*                   users:(("master",pid=6618,fd=13))
LISTEN      0      80                             :::3306                                       :::*                   users:(("mysqld",pid=10667,fd=20))
LISTEN      0      80                             :::3307                                       :::*                   users:(("mysqld",pid=10792,fd=20))
LISTEN      0      80                             :::3308                                       :::*                   users:(("mysqld",pid=10907,fd=20))
LISTEN      0      128                            :::111                                        :::*                   users:(("rpcbind",pid=6100,fd=6),("systemd",pid=1,fd=83))
LISTEN      0      128                            :::22                                         :::*                   users:(("sshd",pid=6456,fd=4))
LISTEN      0      100                           ::1:25                                         :::*                   users:(("master",pid=6618,fd=14))