Ansible自动化部署MySQL主从

桃扇骨 2022-01-21 00:35 444阅读 0赞

1、添加hosts文件

# vim /etc/ansible/hosts 
    [mysql_slave]
    1.1.1.2 ansible_ssh_user=root ansible_ssh_pass=Root
    [mysql_master]
    1.1.1.1 ansible_ssh_user=root ansible_ssh_pass=Root

2.创建mysql角色文件，用于调用mysql\_repl

1 - hosts: mysql_master
      2   remote_user: root
      3   gather_facts: False
      4   roles:
      5     - {role: mysql_repl,mysql_repl_role: master}
      6 
      7 - hosts: mysql_slave
      8   remote_user: root
      9   gather_facts: False
     10   roles:
     11     - {role: mysql_repl,mysql_repl_role: slave,mysql_repl_master: 1.1.1.1,my    sql_repl_user: [{name: repl,passwd: repl}]}

3.创建变量文件

1 ---
      2 source_dir: /home/db/mysql/src/
      3 mysql_root_pwd: redhat123
      4 mysql_port: 13306
      5 socket: /tmp/mysql.sock
      6 
      7 mysql_db:
      8 - name: test1
      9   replicate: yes
     10 - name: test2
     11   replicate: no
     12 - name: test3
     13   replicate: no
     14 
     15 mysql_remote_user:
     16 - name: remote
     17   passwd: remote
     18   priv: "*.*:ALL"
     19 
     20 mysql_repl_user:
     21 - name: repl
     22   passwd: repl
     23   priv: '*.*:"REPLICATION SLAVE"'

4.创建任务文件

- name: install MySQL-python
      yum: name=MySQL-python
    
    #- name: create repl database
    #  mysql_db: name={
       {item.name}} login_host=127.0.0.1 login_port={
       {mysql_port}} login_user=root login_password={
       {mysql_root_pwd}} state=present
    #  with_items: mysql_db
    #  when: mysql_repl_role == "master"
    
    - name: create database remote user
      mysql_user: login_host=127.0.0.1 login_port={
       {mysql_port}} login_user=root login_password={
       {mysql_root_pwd}} name={
       {item.name}} password={
       {item.passwd}} priv={
       {item.priv}} state=present host="%"
      with_items: mysql_remote_user
      when: mysql_remote_user|lower() != 'none'
    
    - name: create replication user
      mysql_user: login_host=127.0.0.1 login_port={
       {mysql_port}} login_user=root login_password={
       {mysql_root_pwd}} name={
       {item.name}} password={
       {item.passwd}} priv={
       {item.priv}} state=present host="%"
      with_items: mysql_repl_user
      when: mysql_repl_role == "master"
    
    - name: copy modify replication db script to client
      template: src=modify_repldb.sh dest={
       {source_dir}} owner=root group=root mode=0775
      
    - name: modify replication db in my.cnf
      shell: bash {
       {source_dir}}/modify_repldb.sh
    
    
    - name: restart mysqld service
      #service: name=mysqld state=restarted
      command: su mysql -c "service mysqld restart"
    
    - name: check if slave is already configured for replication
      mysql_replication: login_unix_socket={
       {socket}} login_user=root login_password={
       {mysql_root_pwd}} mode=getslave
      ignore_errors: true
      register: slave
      when: mysql_repl_role == "slave"
    
    - name: get the current master servers replication status
      mysql_replication: login_unix_socket={
       {socket}} login_user=root login_password={
       {mysql_root_pwd}} mode=getmaster
      delegate_to: "{
       {mysql_repl_master}}"
      register: repl_master_status
      when: slave|failed and mysql_repl_role == "slave" and mysql_repl_master is defined
    
    - name: change the master on slave to start the replication
      mysql_replication: login_unix_socket={
       {socket}} login_user=root login_password={
       {mysql_root_pwd}} mode=changemaster master_host={
       {mysql_repl_master}} master_port={
       {mysql_port}} master_log_file={
       {repl_master_status.File}} master_log_pos={
       {repl_master_status.Position}} master_user={
       {mysql_repl_user[0].name}} master_password={
       {mysql_repl_user[0].passwd}}
      ignore_errors: true
      when: slave|failed and mysql_repl_role == "slave" and mysql_repl_master is defined
    
    - name: start slave on slave to start the replication
      mysql_replication: login_unix_socket={
       {socket}} login_user=root login_password={
       {mysql_root_pwd}} mode=startslave
      when: slave|failed and mysql_repl_role == "slave" and mysql_repl_master is defined

5.编写模板脚本

vim templates/modify_repldb.sh
    #!/bin/bash
    #auth:yanggd
    #content: modify replication db
    
    {% if mysql_repl_role == "master" %}
    {% for i in mysql_db %}
    {% if i.replicate|default(1) %}
    sed -i "/server-id/a replicate-do-db={
       {i.name}}" /etc/my.cnf
    sed -i "/server-id/a binlog-do-db={
       {i.name}}" /etc/my.cnf
    {% endif %}
    {% endfor %}
    
    {% for i in mysql_db %}
    {% if not i.replicate|default(1) %}
    sed -i "/server-id/a binlog-ignore-db={
       {i.name}}" /etc/my.cnf
    {% endif %}
    {% endfor %}
    {% endif %}
    
    
    {% if mysql_repl_role == "slave" %}
    {% for i in mysql_db %}
    {% if i.replicate|default(1) %}
    sed -i "/server-id/a replicate-do-db={
       {i.name}}" /etc/my.cnf
    {% endif %}
    {% endfor %}
    
    {% for i in mysql_db %}
    {% if not i.replicate|default(1) %}
    sed -i "/server-id/a binlog-ignore-db={
       {i.name}}" /etc/my.cnf
    {% endif %}
    {% endfor %}
    {% endif %}