简单剧本部署主从复制

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

这个 Playbook 直接在一个文件中完成所有操作，包括：

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

1. 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 | default('root') }}"
        state: present
        key: "{{ lookup('file', '~/.ssh/id_rsa.pub') }}"

- name: Deploy MySQL 5.7 Master-Slave Cluster
  hosts: mysql
  become: yes
  vars:
    mysql_version: "5.7"
    mysql_root_password: "Secure@123"      # 生产环境请改为复杂密码
    mysql_repl_user: "repl_user"
    mysql_repl_password: "Repl@123"       # 复制账号密码
    mysql_master_ip: "{{ hostvars['mysql-master']['ansible_host'] }}"
    mysql_slave_ips: "{{ groups['slaves'] | map('extract', hostvars, 'ansible_host') | list }}"

  tasks:
    # 安装 MySQL 5.7
    - 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 != ""

    # 配置主库
    - name: Configure Master my.cnf
      template:
        src: |
          [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
        dest: /etc/my.cnf
        owner: root
        group: root
        mode: 0644
      when: inventory_hostname in groups['master']

    # 配置从库
    - name: Configure Slave my.cnf
      template:
        src: |
          [mysqld]
          server-id = {{ 100 + ansible_hostname.split('-')[-1] | int }}
          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
        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

    # 主库操作：创建复制用户
    - 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 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']

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

3. 执行 Playbook

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

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

4. 验证主从复制

# 在主库上检查二进制日志状态
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. 单文件设计

   • 所有任务和模板直接嵌入 Playbook，无需额外文件或 Role。

这个 Playbook 可以直接用于生产环境，只需修改 inventory.ini 中的 IP 地址和 mysql_root_password 等变量即可。