ansible配置mysql主从

ansible配置mysql主从

主机清单

[root@ansible ~]#cd /etc/ansible/
[root@ansible /etc/ansible]#vim hosts
...
[mysql]
node1
node2
[mysql_master]
node1
[mysql_slave]
node2

创建mariadb角色

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

部署mariadb

[root@ansible /etc/ansible/roles]#cd mariadb/
[root@ansible /etc/ansible/roles/mariadb]#vim tasks/main.yml
---
# tasks file for mariadb
- name: stop firewalld
  service:
	name: firewalld
	state: stopped
	enabled: no
- name: stop selinux
  lineinfile:
	path: /etc/selinux/config
	regexp: '^SELINUX='
	line: SELINUX=disabled
- name: stop selinux1
  shell:
	cmd: setenforce 0
- name: set yum
  script: yum.sh
- name: install mariadb
  yum:
	name: "{{ packages }}"
	vars:
      packages:
         - mariadb-server
  		 - mariadb
- name: cp config1
  template:
	src: mastermy.cnf.j2
	dest: /etc/my.cnf
  when: inventory_hostname in {{ groups.mysql_master }}
- name: cp config2
  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 for 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: master
  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 }}

编写yum.sh脚本

[root@ansible /etc/ansible/roles/mariadb]#vim files/yum.sh
#!/bin/bash
rm -rf /etc/yum.repos.d/*
/usr/bin/curl -o /etc/yum.repos.d/CentOS-Base.repo
https://mirrors.aliyun.com/repo/Centos-vault-8.5.2111.repo
yum install -y https://mirrors.aliyun.com/epel/epel-release-latest-8.noarch.rpm
/usr/bin/sed -i
's|^#baseurl=https://download.example/pub|baseurl=https://mirrors.aliyun.com|'
/etc/yum.repos.d/epel*
/usr/bin/sed -i 's|^metalink|#metalink|' /etc/yum.repos.d/epel*

编写template文件

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

编写执行playbook

[root@ansible /etc/ansible]#vim mariadb.yml
---
- name: mariadb
  hosts: mysql
  roles:
- mariadb

执行

[root@ansible /etc/ansible]#ansible-playbook mariadb.yml

验证

[root@node2~]# mysql -uroot -pEnter password:
Nelcome to the MariaDB monitor.Commands end with ; or lg.Your MariaDB connection id is 20
Server version:10.3.28-MariaDB-log MariaDB Server
Copyright (c)2000，2018，Oracle,MariaDB Corporation Ab and others.
Iype '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: node2-relay-bin.000001
			       Relay_Log_Pos: 4
	               Relay_Master_Log_File:
                            slave_I0_Running: Connecting
                           slave_SQL_Running: Yes
		       	     Replicate_Do_DB:
	      	         Replicate_Ignore_DB: