ansible-playbook部署mysql主从

设置主机清单

[root@server ansible]# vim hosts 

[mysql]
node2
node3

[mysql_master]
node2

[mysqk_slave]
node3

创建角色

[root@server ansible]# cd roles/
[root@server roles]# ansible-galaxy init mariadb
- Role mariadb was created successfully

编写角色playbook

[root@server roles]# cd mariadb/
[root@server mariadb]# vim tasks/main.yml 
[root@server mariadb]# cat  tasks/main.yml 
---
# tasks file for mariadb
- name: mount cdrom
  mount: 
    src: /dev/cdrom
    path: /mnt
    fstype: iso9660
    state: mounted
- name: clear repo
  shell: 
    cmd: rm -rf /etc/yum.repos.d/*
    
- name: set repo1
  yum_repository: 
    file: server
    name: aa
    description: aa1
    baseurl: file:///mnt/BaseOS
    enabled: yes
    gpgcheck: no 

- name: set repo2
  yum_repository:
    file: server
    name: bb
    description: bb1
    baseurl: file:///mnt/AppStream
    enabled: yes
    gpgcheck: no

- name: install mariadb
  yum: 
    name: 
      - mariadb
      - mariadb-server
    state: present

- name: cp config
  template: 
    src: mastermy.cnf.j2
    dest: /etc/my.cnf
  when: inventory_hostname in {{ groups.mysql_master }}

- name: cp config
  template:
    src: slavemy.cnf.j2
    dest: /etc/my.cnf
  when: inventory_hostname in {{ groups.mysql_slave }}

- name: start mariadb
  service: 
    name: mariadb
    state: restarted
    enabled: yes

- name: grant root
  shell: 
    cmd: mysql -uroot -e "grant all privileges on *.* to root@'%' identified by 'redhat';"

- name: master
  shell: 
    cmd: mysql -uroot -e "grant replication slave on *.* to 'user'@'slave' identified by 'redhat';"
  when: inventory_hostname in {{ groups.mysql_master }}

- name: slave
  shell: 
    cmd: mysql -uroot -e "change master to master_host='master',master_user='user',master_password='redhat';"
  when: inventory_hostname in {{ groups.mysql_slave }}

- name: start slave
  shell: 
    cmd: mysql -uroot -e "start slave;"
  when: inventory_hostname in {{ groups.mysql_slave }}

编写变量

[root@server ansible]# vim roles/mariadb/templates/mastermy.cnf.
[root@server ansible]# vim roles/mariadb/templates/slavemy.cnf.j2
[root@server ansible]# cat  roles/mariadb/templates/slavemy.cnf.j2
[mysqld]
log_bin=mysql-bin
server_id=20
[root@server ansible]# cat roles/mariadb/templates/mastermy.cnf.j2
[mysqld]
log_bin=mysql-bin
server_id=10

编写执行playbook

[root@server ansible]# vim mariadb.yml
[root@server ansible]# cat mariadb.yml 
--- 
- name: use mariadb
  hosts: mysql
  roles: 
    - mariadb

执行

[root@server ansible]# ansible-playbook mariadb.yml 

PLAY [use mariadb] ********************************************************************************************************

TASK [Gathering Facts] ****************************************************************************************************
ok: [node3]
ok: [node2]

TASK [mariadb : mount cdrom] **********************************************************************************************
changed: [node3]
changed: [node2]

TASK [mariadb : clear repo] ***********************************************************************************************
[WARNING]: Consider using the file module with state=absent rather than running 'rm'.  If you need to use command because
file is insufficient you can add 'warn: false' to this command task or set 'command_warnings=False' in ansible.cfg to get
rid of this message.
changed: [node3]
changed: [node2]

TASK [mariadb : set repo1] ************************************************************************************************
changed: [node3]
changed: [node2]

TASK [mariadb : set repo2] ************************************************************************************************
changed: [node3]
changed: [node2]

TASK [install mariadb] ****************************************************************************************************
changed: [node2]
changed: [node3]

TASK [mariadb : cp config] ************************************************************************************************
[WARNING]: conditional statements should not include jinja2 templating delimiters such as {{ }} or {% %}. Found:
inventory_hostname in {{ groups.mysql_master }}
[WARNING]: conditional statements should not include jinja2 templating delimiters such as {{ }} or {% %}. Found:
inventory_hostname in {{ groups.mysql_master }}
skipping: [node3]
changed: [node2]

TASK [mariadb : cp config] ************************************************************************************************
[WARNING]: conditional statements should not include jinja2 templating delimiters such as {{ }} or {% %}. Found:
inventory_hostname in {{ groups.mysql_slave }}
[WARNING]: conditional statements should not include jinja2 templating delimiters such as {{ }} or {% %}. Found:
inventory_hostname in {{ groups.mysql_slave }}
skipping: [node2]
changed: [node3]

TASK [start mariadb] ******************************************************************************************************
changed: [node3]
changed: [node2]

TASK [mariadb : grant root] ***********************************************************************************************
changed: [node2]
changed: [node3]

TASK [mariadb : master] ***************************************************************************************************
[WARNING]: conditional statements should not include jinja2 templating delimiters such as {{ }} or {% %}. Found:
inventory_hostname in {{ groups.mysql_master }}
[WARNING]: conditional statements should not include jinja2 templating delimiters such as {{ }} or {% %}. Found:
inventory_hostname in {{ groups.mysql_master }}
skipping: [node3]
changed: [node2]

TASK [mariadb : slave] ****************************************************************************************************
[WARNING]: conditional statements should not include jinja2 templating delimiters such as {{ }} or {% %}. Found:
inventory_hostname in {{ groups.mysql_slave }}
[WARNING]: conditional statements should not include jinja2 templating delimiters such as {{ }} or {% %}. Found:
inventory_hostname in {{ groups.mysql_slave }}
skipping: [node2]
changed: [node3]

TASK [mariadb : start slave] **********************************************************************************************
[WARNING]: conditional statements should not include jinja2 templating delimiters such as {{ }} or {% %}. Found:
inventory_hostname in {{ groups.mysql_slave }}
[WARNING]: conditional statements should not include jinja2 templating delimiters such as {{ }} or {% %}. Found:
inventory_hostname in {{ groups.mysql_slave }}
skipping: [node2]
changed: [node3]

PLAY RECAP ****************************************************************************************************************
node2                      : ok=10   changed=9    unreachable=0    failed=0    skipped=3    rescued=0    ignored=0   
node3                      : ok=11   changed=10   unreachable=0    failed=0    skipped=2    rescued=0    ignored=0   

验证

[root@node3 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.3.17-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Connecting to master
                   Master_Host: master
                   Master_User: user
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: 
           Read_Master_Log_Pos: 4
                Relay_Log_File: node3-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: 
              Slave_IO_Running: Connecting
             Slave_SQL_Running: Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 0
               Relay_Log_Space: 256
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 2005
                 Last_IO_Error: error connecting to master 'user@master:3306' - retry-time: 60  maximum-retries: 86400  message: Unknown MySQL server host 'master' (-2)
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 0
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

ERROR: No query specified