ROLES部署主从复制

Ansible Playbook：一键部署 MySQL 5.7 主从集群（CentOS 7）

本 Playbook 将实现以下功能：

1. 配置 SSH 免密登录（Ansible 控制机到所有节点）。
2. 安装 MySQL 5.7（使用官方 Yum 仓库，避免 CentOS 7 默认仓库的旧版本问题）。
3. 配置主从复制（一主二从，GTID 模式）。
4. 自动初始化复制关系。

1. 准备工作

(1) 目录结构

mkdir -p mysql_ha/{files,handlers,tasks,templates,vars}
cd mysql_ha

• files/：存放需要分发的静态文件（如 my.cnf 配置）。
• templates/：存放 Jinja2 模板文件（如主从配置模板）。
• vars/：定义变量（如 MySQL 密码、IP 地址等）。

(2) Inventory 文件 (inventory.ini)

[master]
mysql-master ansible_host=192.168.1.101  # 主库IP

[slaves]
mysql-slave1 ansible_host=192.168.1.102  # 从库1
mysql-slave2 ansible_host=192.168.1.103  # 从库2

[mysql:children]
master
slaves

[mysql:vars]
ansible_user=root      # 建议使用 root 或具有 sudo 权限的用户
ansible_ssh_private_key_file=~/.ssh/id_rsa

2. Playbook 主文件 (deploy_mysql_ha.yml)

---
- name: Configure SSH Key-Based Authentication
  hosts: all
  tasks:
    - name: Generate SSH key on control node (if not exists)
      openssh_keypair:
        path: ~/.ssh/id_rsa
        type: rsa
        size: 2048
        state: present

    - name: Copy public key to all MySQL nodes
      authorized_key:
        user: "{{ ansible_user }}"
        state: present
        key: "{{ lookup('file', '~/.ssh/id_rsa.pub') }}"

- name: Deploy MySQL 5.7 Master-Slave Cluster
  hosts: mysql
  become: yes
  vars_files:
    - vars/main.yml
  tasks:
    - include_tasks: tasks/install_mysql.yml
    - include_tasks: tasks/configure_mysql.yml
    - include_tasks: tasks/setup_replication.yml

3. 变量文件 (vars/main.yml)

# MySQL 主从配置参数
mysql_version: "5.7"
mysql_root_password: "Secure@123"  # 生产环境请改为复杂密码
mysql_repl_user: "repl_user"
mysql_repl_password: "Repl@123"   # 复制账号密码

# 主库和从库的IP（动态获取）
mysql_master_ip: "{{ hostvars['mysql-master']['ansible_host'] }}"
mysql_slave_ips: "{{ groups['slaves'] | map('extract', hostvars, 'ansible_host') | list }}"

4. 任务分解

(1) 安装 MySQL (tasks/install_mysql.yml)

- name: Add MySQL 5.7 Yum Repository
  yum_repository:
    name: mysql57-community
    description: MySQL 5.7 Community Repository
    baseurl: https://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
    gpgkey: https://repo.mysql.com/RPM-GPG-KEY-mysql
    gpgcheck: yes
    enabled: yes

- name: Install MySQL Server
  yum:
    name: mysql-community-server
    state: present
    enablerepo: mysql57-community

- name: Start and Enable MySQL Service
  service:
    name: mysqld
    state: started
    enabled: yes

- name: Get temporary root password
  shell: grep 'temporary password' /var/log/mysqld.log | awk '{print $NF}'
  register: mysql_temp_password
  ignore_errors: yes  # 首次安装可能没有临时密码

- name: Change MySQL root password
  mysql_user:
    login_user: root
    login_password: "{{ mysql_temp_password.stdout | default('') }}"
    name: root
    password: "{{ mysql_root_password }}"
    host: localhost
    priv: "*.*:ALL,GRANT"
    state: present
  when: mysql_temp_password.stdout != ""

(2) 配置 MySQL (tasks/configure_mysql.yml)

- name: Create custom my.cnf for master
  template:
    src: templates/my-master.cnf.j2
    dest: /etc/my.cnf
    owner: root
    group: root
    mode: 0644
  when: inventory_hostname in groups['master']

- name: Create custom my.cnf for slaves
  template:
    src: templates/my-slave.cnf.j2
    dest: /etc/my.cnf
    owner: root
    group: root
    mode: 0644
  when: inventory_hostname in groups['slaves']

- name: Restart MySQL to apply config
  service:
    name: mysqld
    state: restarted

(3) 配置主从复制 (tasks/setup_replication.yml)

- name: On Master - Create replication user
  mysql_user:
    login_user: root
    login_password: "{{ mysql_root_password }}"
    name: "{{ mysql_repl_user }}"
    password: "{{ mysql_repl_password }}"
    host: "%"
    priv: "REPLICATION SLAVE,REPLICATION CLIENT"
    state: present
  when: inventory_hostname in groups['master']

- name: On Master - Get binary log position (for traditional replication)
  mysql_query:
    login_user: root
    login_password: "{{ mysql_root_password }}"
    query: "SHOW MASTER STATUS;"
    register: master_status
  when: inventory_hostname in groups['master']

- name: On Slaves - Configure replication
  mysql_query:
    login_user: root
    login_password: "{{ mysql_root_password }}"
    query: |
      CHANGE MASTER TO
      MASTER_HOST='{{ mysql_master_ip }}',
      MASTER_USER='{{ mysql_repl_user }}',
      MASTER_PASSWORD='{{ mysql_repl_password }}',
      MASTER_AUTO_POSITION=1;
  when: inventory_hostname in groups['slaves']

- name: On Slaves - Start replication
  mysql_query:
    login_user: root
    login_password: "{{ mysql_root_password }}"
    query: "START SLAVE;"
  when: inventory_hostname in groups['slaves']

- name: On Slaves - Check replication status
  mysql_query:
    login_user: root
    login_password: "{{ mysql_root_password }}"
    query: "SHOW SLAVE STATUS\G"
    register: slave_status
  when: inventory_hostname in groups['slaves']

5. 配置文件模板

(1) 主库配置 (templates/my-master.cnf.j2)

[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_group_commit_sync_delay = 100
binlog_group_commit_sync_no_delay_count = 10

(2) 从库配置 (templates/my-slave.cnf.j2)

[mysqld]
server-id = {{ 100 + ansible_hostname.split('-')[-1] | int }}  # 自动生成 server-id (101, 102)
log_bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
read_only = ON
skip_slave_start = 1
relay_log = mysql-relay-bin

6. 执行 Playbook

# 首次运行前先测试 SSH 连接
ansible all -m ping -i inventory.ini

# 执行 Playbook
ansible-playbook -i inventory.ini deploy_mysql_ha.yml

7. 验证主从复制

# 在主库上检查二进制日志状态
mysql -uroot -pSecure@123 -e "SHOW MASTER STATUS\G"

# 在从库上检查复制状态
mysql -uroot -pSecure@123 -e "SHOW SLAVE STATUS\G" | grep -E "Running|Seconds_Behind"

关键点解析

1. SSH 免密登录

   • 使用 openssh_keypair 模块生成密钥，并通过 authorized_key 模块分发公钥。

2. MySQL 5.7 安装

   • 使用官方 Yum 仓库，避免 CentOS 7 默认仓库的旧版本问题。

3. GTID 复制

   • 主库和从库均启用 gtid_mode=ON，确保数据一致性。

4. 自动化主从配置

   • 动态获取主库 IP 和二进制日志位置，自动配置从库。

5. 安全增强

   • 从库默认设置为 read_only=ON，防止误写入。

这个 Playbook 可以直接用于生产环境，只需修改变量文件 (vars/main.yml) 中的密码和 IP 地址即可。