Ansible部署Mysql一主多从

实验环境及准备步骤：

1.centos7.9系统，一台主服务器，一台及以上从服务器（例子共4台服务器，一主三从）

2.关闭防火墙和selinux

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

vim /etc/sysconfig/selinux
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected. 
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted 

3.解析服务器IP

vim /etc/hosts   #假如以下内容，IP地址跟主机名字一一对应
192.168.72.152 ansible-server  #主
192.168.72.153 ansible-web1    #从
192.168.72.154 ansible-web2    #从
192.168.72.155 ansible-web3    #从

 4.主服务器安装好mysql，ansible

cd /etc/yum.repos.d/
vim mysql.repo
[mysql]    #这里使用的是mysql5.7版本的yum源
name=mysql
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
enabled=1
gpgcheck=0

yum list | grep mysql     #检查是否有mysql5.7的yum源

yum -y install mysql-communiti-server

yum -y install ansible

5.设置免密登陆从服务器

ssh-keygen

ssh-copy-id -i 192.168.72.153

ssh-copy-id -i 192.168.72.154

ssh-copy-id -i 192.168.72.155

配置主服务器的mysql和ansible

# 设置mysql的master账户
echo "skip-grant-tables" >> /etc/my.cnf

# 登陆进去mysql后执行
mysql> update mysql.user set authentication_string=password('新密码') where user='root';

vim /etc/my.cnf
#skip-grant-tables  #注释掉这一行

mysql -uroot -p'新密码'
mysql> set password='新密码'   #再重设一遍密码，可以跟之前的一样
mysql> grant replication slave,super,reload on *.* to "slave"@"%" identified by "设置密码"；



# 设置ansible的主机组
vim /etc/ansible/hosts
[server]
ansible-server

[clients]
ansible-web1
ansible-web2
ansible-web3 

[c1]
ansible-web1

[c2]
ansible-web2

[c3]
ansible-web3

编写ansible脚本

---
- hosts: clients
  tasks:
  - name: exchange mysql.repo          # 修改从服务器的mysql源
    copy: src=/etc/yum.repos.d/mysql.repo dest=/etc/yum.repos.d owner=root group=root mode=644
  - name: install mysql                # 安装从服务器的mysql源
    yum: name=mysql-community-server state=latest
    notify: start&&enable
  handlers:
  - name: start&&enable                # 启动mysql并设置自启动
    service: name=mysqld state=started enabled=YES
  - name: synchronize time             # 同步时间
    shell: ntpdate ntp.aliyun.com
  - name: skip-grant-tables&&restart   # 跳过密码登陆mysql并重启
    shell: echo "skip-grant-tables" >> /etc/my.cnf;systemctl restart mysqld

- hosts: clients
  user: root
  vars:
    src: /etc/my.cnf
    dest: /etc
  tasks:
  - name: modify password for mysqlroot # 修改mysql的登陆密码
    shell: mysql -e'update mysql.user set authentication_string=password("Mysql@123") where user="root"'
  - name: copy my.cnf                   # 复制修改过的配置文件
    copy: src={{ src }} dest={{ dest }}
  - name: modify my.cnf                 # 修改从服务器的ID，可以是任意
    shell: sed -i 's/server-id=1//' {{ src }};echo server-id="$RANDOM" >> {{ src }}
  - name: restart mysqld                # 重启mysql
    service: name=mysqld state=restarted
  - name: reset password                # 重设密码
    shell: mysqladmin -uroot -p'Mysql@123' password 'Mysql@123'
  - name: set slave                     # 配置从服务器的slave账户
    shell: /usr/bin/mysql -uroot -p'Mysql@123' -e'change master to master_host="ansible-server",master_user="slave",master_password="Mysql@123",master_auto_position=1'
    shell: mysql -uroot -p'Mysql@123' -e'start slave;show slave status\G'

最后显示IO和SQL 为YES即成功

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

可以测试在不同的从服务器上写数据，从服务器都能看见