ansible安装mysql配置主从

简介

ansible`通过二进制包安装`mysql`并且配置主从，`mysql`安装包和`my.cnf`配置文件放在 `/web/data/software_resource/mysql`安装后mysql软件目录在`/web/soft`数据目录在`/web/data

host文件

[mysql_repl]
192.168.223.156
192.168.223.157

my.cnf

# The following options will be passed to all MySQL clients
[client]
port = 3306 
socket = /tmp/mysql.sock

[mysql]
no-auto-rehash
default-character-set=utf8

[mysqld]
user = root 
port = 3306 
socket = /tmp/mysql.sock 
#skip-grant-tables
secure_file_priv=/
basedir = /web/soft/mysql-5.7.26
datadir = /web/data/mysql_data
skip-name-resolve
character-set-server=utf8
collation-server=utf8_general_ci
lower_case_table_names = 1 
max_connections = 1000
max_connect_errors = 10000
transaction_isolation = READ-COMMITTED
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
skip-external-locking
join_buffer_size = 512M
tmp_table_size = 64M
tmpdir = /tmp
max_allowed_packet = 512M
read_buffer_size = 16M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
back_log=128
key_buffer_size=1024M
thread_stack=256k
thread_cache_size=64
query_cache_type=1
query_cache_size=128M
query_cache_limit = 10M
max_heap_table_size=256M
binlog_cache_size = 2M
table_open_cache=128

#-------------------- log settting --------------------
log_slave_updates = 1
log_error = /web/soft/mysql-5.7.26/logs/error.log
slow_query_log = 1
long_query_time = 10
slow_query_log_file = /web/soft/mysql-5.7.26/logs/slow.log

#-------------------- replication settting --------------------
log-bin=master-bin
log-bin-index=master-bin.index
relay_log_recovery = 1

server_id=123
auto-increment-increment = 2  
auto-increment-offset = 1   
slave-skip-errors = ddl_exist_errors
master_info_repository = TABLE
relay_log_info_repository = TABLE
expire_logs_days=7
binlog_format=MIXED

#-------------------- innodb setting --------------------
innodb_page_size = 8K
innodb_buffer_pool_size=2G
innodb_buffer_pool_instances = 8
innodb_log_file_size=128M
innodb_log_buffer_size = 3M
innodb_flush_log_at_trx_commit=1
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_io_capacity_max=6000
innodb_thread_concurrency = 8

# maridb audit plug
#log_output=FILE
#server_audit="FORCE_PLUS_PERMANENT"
#server_audit_file_path=/web/soft/mysql-5.7.26/logs/audit.log
#server_audit_events = 'QUERY_DDL,QUERY_DML_NO_SELECT'
#server_audit_logging=ON
#server_audit_file_rotate_size=1G
#server_audit_file_rotations=100
#server_audit_syslog_priority=LOG_INFO


[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

install_mysql.yml

xxx 自行修改

- hosts: mysql_repl
  gather_facts: no
  vars:
    master1: "192.168.223.156"
    master2: "192.168.223.157"
    mysql_version: 5.7.31
    mysql_port: 3306
    mysql_package: mysql-{{ mysql_version }}-linux-glibc2.12-x86_64
    mysql_package_path: /web/data/software_resource/mysql
    soft_dir: /web/soft
    mysql_basedir: /web/soft/mysql-{{ mysql_version }}
    mysql_datadir: /web/data/mysqldata_{{ mysql_version }}
    mysql_sock: "{{ mysql_datadir }}/mysql.sock"
    mysql_root_pass: "xxx"
    repl_user: {"name": "xxx", "pass": "xxx"}
    mysql_user_info:
      - {"name": "xxx", "pass": "xxx", "priv": "*.*:ALL","host": "%"}
      - {"name": "{{ repl_user.name }}", "pass": "{{ repl_user.pass }}", "priv": "*.*:REPLICATION SLAVE,REPLICATION CLIENT","host":"%"}
  tasks: 
    - name: Install Rely On
      yum:
        name:
          - "MySQL-python"
        disable_gpg_check: no
        state: present

    - name: Install Mariadb
      unarchive: 
        creates: "{{ mysql_basedir }}"
        src: "{{ mysql_package_path }}/{{ mysql_package }}.tar.gz" 
        dest: "{{ soft_dir }}"
        copy: yes 
        mode: 0755
    - name: Rename 
      shell: creates={{ mysql_basedir }} chdir={{ soft_dir }} mv {{ mysql_package }} mysql-{{ mysql_version }}

    - name: Create Dir
      file:
        name: "{{ item }}"
        state: directory
      with_items:
        - "{{ soft_dir }}"
        - "{{ mysql_basedir }}/data"
        - "{{ mysql_basedir }}/logs"
    - name: Touch Log File
      file: 
        name: "{{ mysql_basedir }}/logs/error.log"
        state: touch
    - name: Copy My.cnf
      copy: src={{ mysql_package_path }}/my.cnf dest=/etc/my.cnf mode=0755

    - name: Modify Conf
      replace:
        path: /etc/my.cnf
        regexp: "{{ item.match }}"
        replace: "{{ item.value }}"
      with_items:
        - {"match": "^port(.+)$", "value": "port={{ mysql_port }}"}
        - {"match": "^basedir(.+)$", "value": "basedir={{ mysql_basedir }}"}
        - {"match": "^datadir(.+)$", "value": "datadir={{ mysql_datadir }}"}
        - {"match": "^log_error(.+)$", "value": "log_error={{ mysql_basedir }}/logs/error.log"}
        - {"match": "^slow_query_log_file(.+)$", "value": "slow_query_log_file={{ mysql_basedir }}/logs/slow.log"}
        - {"match": "^innodb_buffer_pool_size(.+)$", "value": "innodb_buffer_pool_size=1G"}
        - {"match": "^socket(.+)$", "value": "socket={{ mysql_sock }}"}

    - name: Modify Repl Conf
      replace:
        path: /etc/my.cnf
        regexp: "{{ item.match }}"
        replace: "{{ item.value }}"
      with_items:
        - {"match": "^server_id(.+)$", "value": "server_id=1"}
        - {"match": "^auto-increment-offset(.+)$", "value": "auto-increment-offset=1"}
      when: inventory_hostname == master1

    - name: Modify Repl Conf
      replace:
        path: /etc/my.cnf
        regexp: "{{ item.match }}"
        replace: "{{ item.value }}"
      with_items:
        - {"match": "^server_id(.+)$", "value": "server_id=2"}
        - {"match": "^auto-increment-offset(.+)$", "value": "auto-increment-offset=2"}
      when: inventory_hostname == master2

    - name: Init Mysql
      shell: creates={{ mysql_datadir }}/mysql chdir={{ mysql_basedir }}/bin ./mysqld --defaults-file=/etc/my.cnf  --initialize-insecure --user=root

    - name: Set Service
      shell: "/bin/cp -f {{ mysql_basedir }}/support-files/mysql.server /etc/init.d/mysql && systemctl daemon-reload"

    - name: Start Mysql
      service: 
        name: mysql
        state: restarted

    - name: Modify Root User
      mysql_user:
        login_host: "localhost"
        login_user: "root"
        #login_password: ''
        login_port: "{{ mysql_port }}"
        login_unix_socket: "{{ mysql_sock }}"
        name: "root"
        password: "{{ mysql_root_pass }}"
        state: present
      ignore_errors: yes

    - name: Create User
      mysql_user:
        login_host: "localhost"
        login_user: "root"
        login_password: '{{ mysql_root_pass }}'
        login_port: "{{ mysql_port }}"
        login_unix_socket: "{{ mysql_sock }}"
        name: "{{ item.name }}"
        password: "{{ item.pass }}"
        priv: "{{ item.priv }}"
        host: "{{ item.host }}"
        state: present
      with_items: "{{ mysql_user_info }}"

    - name: Get Master 2
      mysql_replication:
        login_host: "{{ master2 }}"
        login_user: "{{ repl_user.name }}"
        login_password: '{{ repl_user.pass }}'
        login_port: "{{ mysql_port }}"
        login_unix_socket: "{{ mysql_sock }}"
        mode: getmaster
      register: masterinfo2
      when: inventory_hostname == master1

    - name: Get Master 1
      mysql_replication:
        login_host: "{{ master1 }}"
        login_user: "{{ repl_user.name }}"
        login_password: '{{ repl_user.pass }}'
        login_port: "{{ mysql_port }}"
        login_unix_socket: "{{ mysql_sock }}"
        mode: getmaster
      register: masterinfo1
      when: inventory_hostname == master2

    - name: Get Slave Info
      mysql_replication:
        login_host: "localhost"
        login_user: "root"
        login_password: '{{ mysql_root_pass }}'
        login_port: "{{ mysql_port }}"
        login_unix_socket: "{{ mysql_sock }}"
        mode: getslave
      register: slaveinfo

    - name: Change To Master2 Server 
      mysql_replication:
        login_host: "localhost"
        login_user: "root"
        login_password: '{{ mysql_root_pass }}'
        login_port: "{{ mysql_port }}"
        login_unix_socket: "{{ mysql_sock }}"
        mode: changemaster
        master_host: "{{ master2 }}"
        master_user: "{{ repl_user.name }}"
        master_password: "{{ repl_user.pass }}"
        master_log_file: "{{ masterinfo2.File }}"
        master_log_pos: "{{ masterinfo2.Position }}"
      when: 
        - inventory_hostname == master1
        - not slaveinfo.Is_Slave

    - name: Change To Master1 Server 
      mysql_replication:
        login_host: "localhost"
        login_user: "root"
        login_password: '{{ mysql_root_pass }}'
        login_port: "{{ mysql_port }}"
        login_unix_socket: "{{ mysql_sock }}"
        mode: changemaster
        master_host: "{{ master1 }}"
        master_user: "{{ repl_user.name }}"
        master_password: "{{ repl_user.pass }}"
        master_log_file: "{{ masterinfo1.File }}"
        master_log_pos: "{{ masterinfo1.Position }}"
      when: 
        - inventory_hostname == master2
        - not slaveinfo.Is_Slave 

    - name: Start Slave
      mysql_replication:
        login_host: "localhost"
        login_user: "root"
        login_password: '{{ mysql_root_pass }}'
        login_port: "{{ mysql_port }}"
        login_unix_socket: "{{ mysql_sock }}"
        mode: startslave
      when: not slaveinfo.Is_Slave or (slaveinfo.Slave_IO_Running and slaveinfo.Slave_IO_Running != 'Yes') or (slaveinfo.Slave_IO_Running and slaveinfo.Slave_SQL_Running != 'Yes')

执行安装

ansible-playbook install_mysql.yml