ansible安装mariadb

最近在搞mariadb的高可用安装, 直接使用的官方ansible, 坑太多了, 现在记录下来

官方脚本下载: ansible-galaxy install geerlingguy.mysql

需要修改的地方:

1. roles/tasks/main.yml
   
   --- # Variable configuration. - include_tasks: variables.yml # Setup/install tasks. - include_tasks: setup-RedHat.yml when: ansible_os_family == 'RedHat' and not uninstall_mysql - include_tasks: setup-Debian.yml when: ansible_os_family == 'Debian' and not uninstall_mysql - include_tasks: setup-Archlinux.yml when: ansible_os_family == 'Archlinux' and not uninstall_mysql - name: Check if MySQL packages were installed. set_fact: mysql_install_packages: "{{ (rh_mysql_install_packages is defined and rh_mysql_install_packages.changed) or (deb_mysql_install_packages is defined and deb_mysql_install_packages.changed) or (arch_mysql_install_packages is defined and arch_mysql_install_packages.changed) }}" when: not uninstall_mysql - name: Include task list in play only if the condition is true include_tasks: "{{ item }}" with_items: - configure.yml - secure-installation.yml - databases.yml - users.yml - replication.yml when: not uninstall_mysql - name: Uninstall mariadb include: uninstall.yml when: uninstall_mysql

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

   --- # Variable configuration. - include_tasks: variables.yml   # Setup/install tasks. - include_tasks: setup-RedHat.yml   when: ansible_os_family == 'RedHat' and not uninstall_mysql   - include_tasks: setup-Debian.yml   when: ansible_os_family == 'Debian' and not uninstall_mysql   - include_tasks: setup-Archlinux.yml   when: ansible_os_family == 'Archlinux' and not uninstall_mysql   - name: Check if MySQL packages were installed.   set_fact:     mysql_install_packages: "{{ (rh_mysql_install_packages is defined and rh_mysql_install_packages.changed)       or (deb_mysql_install_packages is defined and deb_mysql_install_packages.changed)       or (arch_mysql_install_packages is defined and arch_mysql_install_packages.changed) }}"   when: not uninstall_mysql   - name: Include task list in play only if the condition is true   include_tasks: "{{ item }}"   with_items:     - configure.yml     - secure-installation.yml     - databases.yml     - users.yml     - replication.yml   when: not uninstall_mysql   - name: Uninstall mariadb   include: uninstall.yml   when: uninstall_mysql

   
    
2. roles/tasks/configure.yml: 如果要自定义一些目录的话, 需要创建目录并修改文件夹权限
   
   --- - name: Get MySQL version. command: 'mysql --version' register: mysql_cli_version changed_when: false check_mode: false - name: Copy my.cnf global MySQL configuration. template: src: my.cnf.j2 dest: "{{ mysql_config_file }}" owner: root group: root mode: 0644 force: "{{ overwrite_global_mycnf }}" notify: restart mysql - name: Verify mysql include directory exists. file: path: "{{ mysql_config_include_dir }}" state: directory owner: root group: root mode: 0755 when: mysql_config_include_files | length - name: Copy my.cnf override files into include directory. template: src: "{{ item.src }}" dest: "{{ mysql_config_include_dir }}/{{ item.src | basename }}" owner: root group: root mode: 0644 force: "{{ item.force | default(False) }}" with_items: "{{ mysql_config_include_files }}" notify: restart mysql - name: Create slow query log file (if configured). shell: > mysql_slow_query_log_file={{ mysql_slow_query_log_file }}; mkdir -p ${mysql_slow_query_log_file%/*}; touch ${mysql_slow_query_log_file} when: mysql_slow_query_log_enabled - name: Create datadir if it does not exist file: path: "{{ mysql_datadir }}" state: directory owner: mysql group: mysql mode: 0755 setype: mysqld_db_t - name: Set ownership on slow query log file (if configured). file: path: "{{ mysql_slow_query_log_file }}" state: file owner: mysql group: "{{ mysql_log_file_group }}" mode: 0640 when: mysql_slow_query_log_enabled - name: Create error log directory (if configured). shell: > mysql_log_error={{ mysql_log_error }}; mkdir -p ${mysql_log_error%/*}; touch ${mysql_log_error}; chown -R mysql:{{ mysql_log_file_group }} ${mysql_log_error%/*} when: - mysql_log == "" - mysql_log_error != "" tags: ['skip_ansible_galaxy'] - name: Set ownership on error log file (if configured). file: path: "{{ mysql_log_error }}" state: file owner: mysql group: "{{ mysql_log_file_group }}" mode: 0640 when: - mysql_log == "" - mysql_log_error != "" tags: ['skip_ansible_galaxy'] - name: Create socket/pid directory shell: > mysql_pid_file={{ mysql_pid_file }}; mysql_socket={{ mysql_socket }}; for file in `echo $mysql_pid_file $mysql_socket`; do dir=${file%/*}; if [[ ! -d $dir ]]; then mkdir -p ${dir} chown mysql:mysql ${dir} chmod 0755 ${dir} else echo "$dir already exist" fi done - name: Create tmpdir if it does not exist file: path: "{{ mysql_tmpdir }}" state: directory owner: mysql group: mysql mode: 0777 - name: init mysql db shell: > mysql_install_db --user=mysql --basedir=/usr --datadir={{ mysql_datadir }} ignore_errors: true - name: Ensure MySQL is started and enabled on boot. service: "name={{ mysql_daemon }} state=started enabled={{ mysql_enabled_on_startup }}" register: mysql_service_configuration

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116

   --- - name: Get MySQL version.   command: 'mysql --version'   register: mysql_cli_version   changed_when: false   check_mode: false   - name: Copy my.cnf global MySQL configuration.   template:     src: my.cnf.j2     dest: "{{ mysql_config_file }}"     owner: root     group: root     mode: 0644     force: "{{ overwrite_global_mycnf }}"   notify: restart mysql   - name: Verify mysql include directory exists.   file:     path: "{{ mysql_config_include_dir }}"     state: directory     owner: root     group: root     mode: 0755   when: mysql_config_include_files | length   - name: Copy my.cnf override files into include directory.   template:     src: "{{ item.src }}"     dest: "{{ mysql_config_include_dir }}/{{ item.src | basename }}"     owner: root     group: root     mode: 0644     force: "{{ item.force | default(False) }}"   with_items: "{{ mysql_config_include_files }}"   notify: restart mysql   - name: Create slow query log file (if configured).   shell: >     mysql_slow_query_log_file={{ mysql_slow_query_log_file }};     mkdir -p ${mysql_slow_query_log_file%/*};     touch ${mysql_slow_query_log_file}   when: mysql_slow_query_log_enabled   - name: Create datadir if it does not exist   file:     path: "{{ mysql_datadir }}"     state: directory     owner: mysql     group: mysql     mode: 0755     setype: mysqld_db_t   - name: Set ownership on slow query log file (if configured).   file:     path: "{{ mysql_slow_query_log_file }}"     state: file     owner: mysql     group: "{{ mysql_log_file_group }}"     mode: 0640   when: mysql_slow_query_log_enabled   - name: Create error log directory (if configured).   shell: >     mysql_log_error={{ mysql_log_error }};     mkdir -p ${mysql_log_error%/*};     touch ${mysql_log_error};     chown -R mysql:{{ mysql_log_file_group }} ${mysql_log_error%/*}   when:     - mysql_log == ""     - mysql_log_error != ""   tags: ['skip_ansible_galaxy']   - name: Set ownership on error log file (if configured).   file:     path: "{{ mysql_log_error }}"     state: file     owner: mysql     group: "{{ mysql_log_file_group }}"     mode: 0640   when:     - mysql_log == ""     - mysql_log_error != ""   tags: ['skip_ansible_galaxy']   - name: Create socket/pid directory   shell: >     mysql_pid_file={{ mysql_pid_file }};     mysql_socket={{ mysql_socket }};     for file in `echo $mysql_pid_file $mysql_socket`; do       dir=${file%/*};       if [[ ! -d $dir ]]; then         mkdir -p ${dir}         chown mysql:mysql ${dir}         chmod 0755 ${dir}       else         echo "$dir already exist"       fi     done   - name: Create tmpdir if it does not exist   file:     path: "{{ mysql_tmpdir }}"     state: directory     owner: mysql     group: mysql     mode: 0777      - name: init mysql db   shell: >     mysql_install_db --user=mysql --basedir=/usr --datadir={{ mysql_datadir }}   ignore_errors: true   - name: Ensure MySQL is started and enabled on boot.   service: "name={{ mysql_daemon }} state=started enabled={{ mysql_enabled_on_startup }}"   register: mysql_service_configuration

3. roles/tasks/database.yml: 注意修改默认字符集
   
   --- - name: Ensure MySQL databases are present. mysql_db: name: "{{ item.name }}" login_user: "{{ mysql_root_username }}" login_password: "{{ mysql_root_password }}" login_unix_socket: "{{ mysql_socket }}" collation: "{{ item.collation | default('utf8mb4_unicode_ci') }}" encoding: "{{ item.encoding | default('utf8mb4') }}" state: "{{ item.state | default('present') }}" with_items: "{{ mysql_databases }}"

   1 2 3 4 5 6 7 8 9 10 11

   --- - name: Ensure MySQL databases are present.   mysql_db:     name: "{{ item.name }}"     login_user: "{{ mysql_root_username }}"     login_password: "{{ mysql_root_password }}"     login_unix_socket: "{{ mysql_socket }}"     collation: "{{ item.collation | default('utf8mb4_unicode_ci') }}"     encoding: "{{ item.encoding | default('utf8mb4') }}"     state: "{{ item.state | default('present') }}"   with_items: "{{ mysql_databases }}"

4. roles/tasks/variables.yml:
   
   --- # Variable configuration. - name: Include OS-specific variables. include_vars: "{{ item }}" with_first_found: - files: - "vars/{{ ansible_os_family }}-{{ ansible_distribution_major_version }}.yml" - "vars/{{ ansible_os_family }}.yml" skip: true - name: Define mysql_packages. set_fact: mysql_packages: "{{ __mysql_packages | list }}" when: mysql_packages is not defined - name: Define mysql_daemon. set_fact: mysql_daemon: "{{ __mysql_daemon }}" when: mysql_daemon is not defined - name: Define mysql_slow_query_log_file. set_fact: mysql_slow_query_log_file: "{{ __mysql_slow_query_log_file }}" when: mysql_slow_query_log_file is not defined - name: Define mysql_log_error. set_fact: mysql_log_error: "{{ __mysql_log_error }}" when: mysql_log_error is not defined - name: Define mysql_syslog_tag. set_fact: mysql_syslog_tag: "{{ __mysql_syslog_tag }}" when: mysql_syslog_tag is not defined - name: Define mysql_pid_file. set_fact: mysql_pid_file: "{{ __mysql_pid_file }}" when: mysql_pid_file is not defined - name: Define mysql_config_file. set_fact: mysql_config_file: "{{ __mysql_config_file }}" when: mysql_config_file is not defined - name: Define mysql_config_include_dir. set_fact: mysql_config_include_dir: "{{ __mysql_config_include_dir }}" when: mysql_config_include_dir is not defined - name: Define mysql_socket. set_fact: mysql_socket: "{{ __mysql_socket }}" when: mysql_socket is not defined - name: Define mysql_supports_innodb_large_prefix. set_fact: mysql_supports_innodb_large_prefix: "{{ __mysql_supports_innodb_large_prefix }}" when: mysql_supports_innodb_large_prefix is not defined

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

   --- # Variable configuration. - name: Include OS-specific variables.   include_vars: "{{ item }}"   with_first_found:     - files:         - "vars/{{ ansible_os_family }}-{{ ansible_distribution_major_version }}.yml"         - "vars/{{ ansible_os_family }}.yml"       skip: true   - name: Define mysql_packages.   set_fact:     mysql_packages: "{{ __mysql_packages | list }}"   when: mysql_packages is not defined   - name: Define mysql_daemon.   set_fact:     mysql_daemon: "{{ __mysql_daemon }}"   when: mysql_daemon is not defined   - name: Define mysql_slow_query_log_file.   set_fact:     mysql_slow_query_log_file: "{{ __mysql_slow_query_log_file }}"   when: mysql_slow_query_log_file is not defined   - name: Define mysql_log_error.   set_fact:     mysql_log_error: "{{ __mysql_log_error }}"   when: mysql_log_error is not defined   - name: Define mysql_syslog_tag.   set_fact:     mysql_syslog_tag: "{{ __mysql_syslog_tag }}"   when: mysql_syslog_tag is not defined   - name: Define mysql_pid_file.   set_fact:     mysql_pid_file: "{{ __mysql_pid_file }}"   when: mysql_pid_file is not defined   - name: Define mysql_config_file.   set_fact:     mysql_config_file: "{{ __mysql_config_file }}"   when: mysql_config_file is not defined   - name: Define mysql_config_include_dir.   set_fact:     mysql_config_include_dir: "{{ __mysql_config_include_dir }}"   when: mysql_config_include_dir is not defined   - name: Define mysql_socket.   set_fact:     mysql_socket: "{{ __mysql_socket }}"   when: mysql_socket is not defined   - name: Define mysql_supports_innodb_large_prefix.   set_fact:     mysql_supports_innodb_large_prefix: "{{ __mysql_supports_innodb_large_prefix }}"   when: mysql_supports_innodb_large_prefix is not defined

   
    
5. roles/tasks/secure-installation.yml: 此文件坑较多, 若用官方的, 会出现没有mysql.user库的报错, 因为存在匿名帐户, 直接用mysql -uroot -p登录的话, 会看到没有默认的mysql库, 但是其实已经有库了, 只是登录时如果不写-h的话, 默认就直接解析到通过hostname了, 在my.cnf里添加skip-name-resolve,可以不通过DNS解析.  所以只能通过mysql -hlocalhost -uroot -p方式都能正确地进入到库, 所以更改如下:  [ mysql -NBe 改为: mysql -NB -h localhost -e ]
   
   --- - name: Ensure default user is present. mysql_user: name: "{{ mysql_user_name }}" host: 'localhost' password: "{{ mysql_user_password }}" priv: '*.*:ALL,GRANT' state: present when: mysql_user_name != mysql_root_username # Has to be after the password assignment, for idempotency. - name: Copy user-my.cnf file with password credentials. template: src: "user-my.cnf.j2" dest: "{{ mysql_user_home }}/.my.cnf" owner: "{{ mysql_user_name }}" mode: 0600 when: > mysql_user_name != mysql_root_username and (mysql_install_packages | bool or mysql_user_password_update) # - name: Disallow root login remotely # command: 'mysql -NBe "{{ item }}"' # with_items: # - DELETE FROM mysql.user WHERE User='{{ mysql_root_username }}' AND Host NOT IN ('localhost', '127.0.0.1', '::1') # changed_when: false - name: Allow root login remotely command: 'mysql -NB -h localhost -e "{{ item }}"' with_items: - GRANT ALL PRIVILEGES ON *.* TO '{{ mysql_root_username }}'@'%' IDENTIFIED BY '{{ mysql_root_password }}' WITH GRANT OPTION; changed_when: false - name: Get list of hosts for the root user. command: mysql -NB -h localhost -e "SELECT Host FROM mysql.user WHERE User = '{{ mysql_root_username }}' ORDER BY (Host='localhost') ASC" register: mysql_root_hosts changed_when: false check_mode: false when: mysql_install_packages | bool or mysql_root_password_update # Note: We do not use mysql_user for this operation, as it doesn't always update # the root password correctly. See: https://goo.gl/MSOejW # Set root password for MySQL >= 5.7.x. - name: Update MySQL root password for localhost root account (5.7.x). shell: > mysql -u root -NB -h localhost -e 'ALTER USER "{{ mysql_root_username }}"@"{{ item }}" IDENTIFIED WITH mysql_native_password BY "{{ mysql_root_password }}";' with_items: "{{ mysql_root_hosts.stdout_lines|default([]) }}" when: > ((mysql_install_packages | bool) or mysql_root_password_update) and ('5.7.' in mysql_cli_version.stdout or '8.0.' in mysql_cli_version.stdout) # Set root password for MySQL < 5.7.x. - name: Update MySQL root password for localhost root account (< 5.7.x). shell: > mysql -NB -h localhost -e 'SET PASSWORD FOR "{{ mysql_root_username }}"@"{{ item }}" = PASSWORD("{{ mysql_root_password }}");' with_items: "{{ mysql_root_hosts.stdout_lines|default([]) }}" when: > ((mysql_install_packages | bool) or mysql_root_password_update) and ('5.7.' not in mysql_cli_version.stdout and '8.0.' not in mysql_cli_version.stdout) # Has to be after the root password assignment, for idempotency. - name: Copy .my.cnf file with root password credentials. template: src: "root-my.cnf.j2" dest: "{{ mysql_root_home }}/.my.cnf" owner: root group: root mode: 0600 when: mysql_install_packages | bool or mysql_root_password_update # - name: Get list of hosts for the anonymous user. # command: mysql -NB -h localhost -e 'SELECT Host FROM mysql.user WHERE User = ""' # register: mysql_anonymous_hosts # changed_when: false # check_mode: false # - name: Remove anonymous MySQL users. # mysql_user: # name: "" # host: "{{ item }}" # state: absent # with_items: "{{ mysql_anonymous_hosts.stdout_lines|default([]) }}" - name: Remove anonymous users mysql_user: name: '' host: 'localhost' host_all: true login_user: root login_password: "{{ mysql_root_password }}" login_unix_socket: "{{ mysql_socket }}" state: absent - name: Remove the test database mysql_db: name: test login_user: root login_password: "{{ mysql_root_password }}" login_unix_socket: "{{ mysql_socket }}" state: absent

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107

   --- - name: Ensure default user is present.   mysql_user:     name: "{{ mysql_user_name }}"     host: 'localhost'     password: "{{ mysql_user_password }}"     priv: '*.*:ALL,GRANT'     state: present   when: mysql_user_name != mysql_root_username   # Has to be after the password assignment, for idempotency. - name: Copy user-my.cnf file with password credentials.   template:     src: "user-my.cnf.j2"     dest: "{{ mysql_user_home }}/.my.cnf"     owner: "{{ mysql_user_name }}"     mode: 0600   when: >     mysql_user_name != mysql_root_username     and (mysql_install_packages | bool or mysql_user_password_update)   # - name: Disallow root login remotely #   command: 'mysql -NBe "{{ item }}"' #   with_items: #     - DELETE FROM mysql.user WHERE User='{{ mysql_root_username }}' AND Host NOT IN ('localhost', '127.0.0.1', '::1') #   changed_when: false   - name: Allow root login remotely   command: 'mysql -NB -h localhost -e "{{ item }}"'   with_items:     - GRANT ALL PRIVILEGES ON *.* TO '{{ mysql_root_username }}'@'%' IDENTIFIED BY '{{ mysql_root_password }}' WITH GRANT OPTION;   changed_when: false   - name: Get list of hosts for the root user.   command: mysql -NB -h localhost -e     "SELECT Host     FROM mysql.user     WHERE User = '{{ mysql_root_username }}'     ORDER BY (Host='localhost') ASC"   register: mysql_root_hosts   changed_when: false   check_mode: false   when: mysql_install_packages | bool or mysql_root_password_update   # Note: We do not use mysql_user for this operation, as it doesn't always update # the root password correctly. See: https://goo.gl/MSOejW # Set root password for MySQL >= 5.7.x. - name: Update MySQL root password for localhost root account (5.7.x).   shell: >     mysql -u root -NB -h localhost -e     'ALTER USER "{{ mysql_root_username }}"@"{{ item }}"     IDENTIFIED WITH mysql_native_password BY "{{ mysql_root_password }}";'   with_items: "{{ mysql_root_hosts.stdout_lines|default([]) }}"   when: >     ((mysql_install_packages | bool) or mysql_root_password_update)     and ('5.7.' in mysql_cli_version.stdout or '8.0.' in mysql_cli_version.stdout)   # Set root password for MySQL < 5.7.x. - name: Update MySQL root password for localhost root account (< 5.7.x).   shell: >     mysql -NB -h localhost -e     'SET PASSWORD FOR "{{ mysql_root_username }}"@"{{ item }}" = PASSWORD("{{ mysql_root_password }}");'   with_items: "{{ mysql_root_hosts.stdout_lines|default([]) }}"   when: >     ((mysql_install_packages | bool) or mysql_root_password_update)     and ('5.7.' not in mysql_cli_version.stdout and '8.0.' not in mysql_cli_version.stdout)   # Has to be after the root password assignment, for idempotency. - name: Copy .my.cnf file with root password credentials.   template:     src: "root-my.cnf.j2"     dest: "{{ mysql_root_home }}/.my.cnf"     owner: root     group: root     mode: 0600   when: mysql_install_packages | bool or mysql_root_password_update   # - name: Get list of hosts for the anonymous user. #   command: mysql -NB -h localhost -e 'SELECT Host FROM mysql.user WHERE User = ""' #   register: mysql_anonymous_hosts #   changed_when: false #   check_mode: false   # - name: Remove anonymous MySQL users. #   mysql_user: #     name: "" #     host: "{{ item }}" #     state: absent #   with_items: "{{ mysql_anonymous_hosts.stdout_lines|default([]) }}"   - name: Remove anonymous users   mysql_user:     name: ''     host: 'localhost'     host_all: true     login_user: root     login_password: "{{ mysql_root_password }}"     login_unix_socket: "{{ mysql_socket }}"     state: absent   - name: Remove the test database   mysql_db:     name: test     login_user: root     login_password: "{{ mysql_root_password }}"     login_unix_socket: "{{ mysql_socket }}"     state: absent

6. roles/tasks/replication.yml, 此处注意, 官方脚本没有复制端口, 需要加上master_port, 以防不是默认3306端口:
   
   --- - name: Ensure replication user exists on master. mysql_user: name: "{{ mysql_replication_user.name }}" host: "{{ mysql_replication_user.host | default('%') }}" password: "{{ mysql_replication_user.password }}" priv: "{{ mysql_replication_user.priv | default('*.*:REPLICATION SLAVE,REPLICATION CLIENT') }}" login_user: "{{ mysql_root_username }}" login_password: "{{ mysql_root_password }}" login_unix_socket: "{{ mysql_socket }}" state: present when: - mysql_replication_role == 'master' - mysql_replication_user.name is defined - mysql_replication_master != '' tags: ['skip_ansible_galaxy'] - name: Check slave replication status. mysql_replication: mode: getslave login_user: "{{ mysql_replication_user.name }}" login_password: "{{ mysql_replication_user.password }}" login_unix_socket: "{{ mysql_socket }}" ignore_errors: true register: slave when: - mysql_replication_role == 'slave' - mysql_replication_master != '' tags: ['skip_ansible_galaxy'] - name: Check master replication status. mysql_replication: mode: getmaster login_unix_socket: "{{ mysql_socket }}" delegate_to: "{{ mysql_replication_master }}" register: master when: - (slave.Is_Slave is defined and not slave.Is_Slave) or (slave.Is_Slave is not defined and slave is failed) - mysql_replication_role == 'slave' - mysql_replication_master != '' tags: ['skip_ansible_galaxy'] - name: Configure replication on the slave. mysql_replication: mode: changemaster master_host: "{{ mysql_replication_master }}" master_port: "{{ hostvars[mysql_replication_master].mysql_port }}" master_user: "{{ mysql_replication_user.name }}" master_password: "{{ mysql_replication_user.password }}" master_log_file: "{{ master.File }}" master_log_pos: "{{ master.Position }}" login_unix_socket: "{{ mysql_socket }}" ignore_errors: true when: - (slave.Is_Slave is defined and not slave.Is_Slave) or (slave.Is_Slave is not defined and slave is failed) - mysql_replication_role == 'slave' - mysql_replication_user.name is defined - mysql_replication_master != '' - name: Start replication. mysql_replication: mode: startslave login_unix_socket: "{{ mysql_socket }}" when: - (slave.Is_Slave is defined and not slave.Is_Slave) or (slave.Is_Slave is not defined and slave is failed) - mysql_replication_role == 'slave' - mysql_replication_master != '' tags: ['skip_ansible_galaxy']

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68

   --- - name: Ensure replication user exists on master.   mysql_user:     name: "{{ mysql_replication_user.name }}"     host: "{{ mysql_replication_user.host | default('%') }}"     password: "{{ mysql_replication_user.password }}"     priv: "{{ mysql_replication_user.priv | default('*.*:REPLICATION SLAVE,REPLICATION CLIENT') }}"     login_user: "{{ mysql_root_username }}"     login_password: "{{ mysql_root_password }}"     login_unix_socket: "{{ mysql_socket }}"     state: present   when:     - mysql_replication_role == 'master'     - mysql_replication_user.name is defined     - mysql_replication_master != ''   tags: ['skip_ansible_galaxy']   - name: Check slave replication status.   mysql_replication:     mode: getslave     login_user: "{{ mysql_replication_user.name }}"     login_password: "{{ mysql_replication_user.password }}"     login_unix_socket: "{{ mysql_socket }}"   ignore_errors: true   register: slave   when:     - mysql_replication_role == 'slave'     - mysql_replication_master != ''   tags: ['skip_ansible_galaxy']   - name: Check master replication status.   mysql_replication:     mode: getmaster     login_unix_socket: "{{ mysql_socket }}"   delegate_to: "{{ mysql_replication_master }}"   register: master   when:     - (slave.Is_Slave is defined and not slave.Is_Slave) or (slave.Is_Slave is not defined and slave is failed)     - mysql_replication_role == 'slave'     - mysql_replication_master != ''   tags: ['skip_ansible_galaxy']   - name: Configure replication on the slave.   mysql_replication:     mode: changemaster     master_host: "{{ mysql_replication_master }}"     master_port: "{{ hostvars[mysql_replication_master].mysql_port }}"     master_user: "{{ mysql_replication_user.name }}"     master_password: "{{ mysql_replication_user.password }}"     master_log_file: "{{ master.File }}"     master_log_pos: "{{ master.Position }}"     login_unix_socket: "{{ mysql_socket }}"   ignore_errors: true   when:     - (slave.Is_Slave is defined and not slave.Is_Slave) or (slave.Is_Slave is not defined and slave is failed)     - mysql_replication_role == 'slave'     - mysql_replication_user.name is defined     - mysql_replication_master != ''   - name: Start replication.   mysql_replication:     mode: startslave     login_unix_socket: "{{ mysql_socket }}"   when:     - (slave.Is_Slave is defined and not slave.Is_Slave) or (slave.Is_Slave is not defined and slave is failed)     - mysql_replication_role == 'slave'     - mysql_replication_master != ''   tags: ['skip_ansible_galaxy']

   
    
7. roles/tasks/uninstall.yml(新增):
   
   --- - name: stop mysql service service: name: "{{ mysql_daemon }}" state: stopped enabled: false tags: uninstall - name: Uninstall mysql packages package: name: "{{ item }}" state: absent with_items: "{{ mysql_packages }}" tags: uninstall - name: delete mysql related files file: path: "{{ item }}" state: absent with_items: - "{{ mysql_datadir }}" - "{{ mysql_config_file }}" - "{{ mysql_config_include_dir }}.d" - "{{ mysql_slow_query_log_file }}" - "{{ mysql_log_error }}" tags: uninstall

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

   ---   - name: stop mysql service   service:     name: "{{ mysql_daemon }}"     state: stopped     enabled: false   tags: uninstall   - name: Uninstall mysql packages   package:     name: "{{ item }}"     state: absent   with_items: "{{ mysql_packages }}"   tags: uninstall   - name: delete mysql related files   file:     path: "{{ item }}"     state: absent   with_items:     - "{{ mysql_datadir }}"     - "{{ mysql_config_file }}"     - "{{ mysql_config_include_dir }}.d"     - "{{ mysql_slow_query_log_file }}"     - "{{ mysql_log_error }}"   tags: uninstall

   
    
8. roles/tasks/
9. vars/RedHat-7.yml:
   
   --- __mysql_daemon: mariadb __mysql_packages: - mariadb - mariadb-server - mariadb-libs - MySQL-python - perl-DBD-MySQL __mysql_slow_query_log_file: /var/log/mariadb/mysql-slow.log __mysql_log_error: /var/log/mariadb/mariadb.log __mysql_syslog_tag: mariadb __mysql_pid_file: /var/run/mariadb/mariadb.pid __mysql_config_file: /etc/my.cnf __mysql_config_include_dir: /etc/my.cnf.d __mysql_socket: /var/lib/mysql/mysql.sock __mysql_supports_innodb_large_prefix: true

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

   --- __mysql_daemon: mariadb __mysql_packages:   - mariadb   - mariadb-server   - mariadb-libs   - MySQL-python   - perl-DBD-MySQL __mysql_slow_query_log_file: /var/log/mariadb/mysql-slow.log __mysql_log_error: /var/log/mariadb/mariadb.log __mysql_syslog_tag: mariadb __mysql_pid_file: /var/run/mariadb/mariadb.pid __mysql_config_file: /etc/my.cnf __mysql_config_include_dir: /etc/my.cnf.d __mysql_socket: /var/lib/mysql/mysql.sock __mysql_supports_innodb_large_prefix: true

   
    
10. templates/my.cnf.j2:
    
    # {{ ansible_managed }} [client] #password = your_password port = {{ mysql_port }} socket = {{ mysql_socket }} default-character-set = utf8mb4 [mysqld] port = {{ mysql_port }} bind-address = {{ mysql_bind_address }} datadir = {{ mysql_datadir }} socket = {{ mysql_socket }} pid-file = {{ mysql_pid_file }} tmpdir = {{ mysql_tmpdir }} lc_messages_dir = /usr/share/mysql lc_messages = en_US skip-external-locking default-time-zone = '+08:00' character_set_server = utf8mb4 {% if mysql_skip_name_resolve %} skip-name-resolve {% endif %} {% if mysql_sql_mode %} sql_mode = {{ mysql_sql_mode }} {% endif %} # Logging configuration. {% if mysql_log_error == 'syslog' or mysql_log == 'syslog' %} syslog syslog-tag = {{ mysql_syslog_tag }} {% else %} {% if mysql_log %} log = {{ mysql_log }} {% endif %} log-error = {{ mysql_log_error }} {% endif %} {% if mysql_slow_query_log_enabled %} # Slow query log configuration. slow_query_log = 1 slow_query_log_file = {{ mysql_slow_query_log_file }} long_query_time = {{ mysql_slow_query_time }} {% endif %} {% if mysql_replication_master %} # Replication server-id = {{ mysql_server_id }} {% if mysql_replication_role == 'master' %} log_bin = mysql-bin log-bin-index = mysql-bin.index expire_logs_days = {{ mysql_expire_logs_days }} max_binlog_size = {{ mysql_max_binlog_size }} binlog_format = {{mysql_binlog_format}} {% for db in mysql_databases %} {% if db.replicate|default(1) %} binlog_do_db = {{ db.name }} {% else %} binlog_ignore_db = {{ db.name }} {% endif %} {% endfor %} {% endif %} {% if mysql_replication_role == 'slave' %} read_only relay-log = relay-bin relay-log-index = relay-bin.index {% if slave_skip_errors %} slave_skip_errors = {{ slave_skip_errors }} {% endif %} {% endif %} {% endif %} # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links = 0 # User is ignored when systemd is used (fedora >= 15). user = mysql # http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html ;performance_schema # Memory settings. key_buffer_size = {{ mysql_key_buffer_size }} max_allowed_packet = {{ mysql_max_allowed_packet }} table_open_cache = {{ mysql_table_open_cache }} sort_buffer_size = {{ mysql_sort_buffer_size }} bulk_insert_buffer_size = {{ mysql_bulk_insert_buffer_size }} read_buffer_size = {{ mysql_read_buffer_size }} read_rnd_buffer_size = {{ mysql_read_rnd_buffer_size }} myisam_sort_buffer_size = {{ mysql_myisam_sort_buffer_size }} thread_cache_size = {{ mysql_thread_cache_size }} {% if '8.0.' not in mysql_cli_version.stdout %} query_cache_type = {{ mysql_query_cache_type }} query_cache_size = {{ mysql_query_cache_size }} query_cache_limit = {{ mysql_query_cache_limit }} {% endif %} max_connections = {{ mysql_max_connections }} max_user_connections = {{ mysql_max_user_connections }} tmp_table_size = {{ mysql_tmp_table_size }} max_heap_table_size = {{ mysql_max_heap_table_size }} group_concat_max_len = {{ mysql_group_concat_max_len }} join_buffer_size = {{ mysql_join_buffer_size }} # Other settings. connect_timeout = {{ mysql_connect_timeout }} wait_timeout = {{ mysql_wait_timeout }} lower_case_table_names = {{ mysql_lower_case_table_names }} event_scheduler = {{ mysql_event_scheduler_state }} myisam_recover_options = BACKUP concurrent_insert = 2 default_storage_engine = InnoDB # InnoDB settings. {% if mysql_supports_innodb_large_prefix and '8.0.' not in mysql_cli_version.stdout %} innodb_large_prefix = {{ mysql_innodb_large_prefix }} innodb_file_format = {{ mysql_innodb_file_format }} {% endif %} innodb_file_per_table = {{ mysql_innodb_file_per_table }} innodb_buffer_pool_size = {{ mysql_innodb_buffer_pool_size }} innodb_log_file_size = {{ mysql_innodb_log_file_size }} innodb_log_buffer_size = {{ mysql_innodb_log_buffer_size }} innodb_flush_log_at_trx_commit = {{ mysql_innodb_flush_log_at_trx_commit }} innodb_lock_wait_timeout = {{ mysql_innodb_lock_wait_timeout }} innodb_strict_mode = 0 innodb_open_files = 400 innodb_io_capacity = 400 innodb_flush_method = O_DIRECT [database] default-character-set = utf8mb4 [mysqldump] quick max_allowed_packet = {{ mysql_mysqldump_max_allowed_packet }} [mysqld_safe] pid-file = {{ mysql_pid_file }} nice = 0 {% if mysql_config_include_files | length %} # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir {{ mysql_config_include_dir }} {% endif %} [galera] binlog_format = row

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153

    # {{ ansible_managed }}   [client] #password = your_password port = {{ mysql_port }} socket = {{ mysql_socket }} default-character-set = utf8mb4   [mysqld] port = {{ mysql_port }} bind-address = {{ mysql_bind_address }} datadir = {{ mysql_datadir }} socket = {{ mysql_socket }} pid-file = {{ mysql_pid_file }} tmpdir = {{ mysql_tmpdir }} lc_messages_dir = /usr/share/mysql lc_messages = en_US skip-external-locking default-time-zone = '+08:00' character_set_server = utf8mb4   {% if mysql_skip_name_resolve %} skip-name-resolve {% endif %}   {% if mysql_sql_mode %} sql_mode = {{ mysql_sql_mode }} {% endif %}   # Logging configuration. {% if mysql_log_error == 'syslog' or mysql_log == 'syslog' %} syslog syslog-tag = {{ mysql_syslog_tag }} {% else %} {% if mysql_log %} log = {{ mysql_log }} {% endif %} log-error = {{ mysql_log_error }} {% endif %}   {% if mysql_slow_query_log_enabled %} # Slow query log configuration. slow_query_log = 1 slow_query_log_file = {{ mysql_slow_query_log_file }} long_query_time = {{ mysql_slow_query_time }} {% endif %}   {% if mysql_replication_master %} # Replication server-id = {{ mysql_server_id }}   {% if mysql_replication_role == 'master' %} log_bin = mysql-bin log-bin-index = mysql-bin.index expire_logs_days = {{ mysql_expire_logs_days }} max_binlog_size = {{ mysql_max_binlog_size }} binlog_format = {{mysql_binlog_format}}   {% for db in mysql_databases %} {% if db.replicate|default(1) %} binlog_do_db = {{ db.name }} {% else %} binlog_ignore_db = {{ db.name }} {% endif %} {% endfor %} {% endif %}   {% if mysql_replication_role == 'slave' %} read_only relay-log = relay-bin relay-log-index = relay-bin.index {% if slave_skip_errors %} slave_skip_errors = {{ slave_skip_errors }} {% endif %} {% endif %} {% endif %}   # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links = 0   # User is ignored when systemd is used (fedora >= 15). user = mysql   # http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html ;performance_schema   # Memory settings. key_buffer_size = {{ mysql_key_buffer_size }} max_allowed_packet = {{ mysql_max_allowed_packet }} table_open_cache = {{ mysql_table_open_cache }} sort_buffer_size = {{ mysql_sort_buffer_size }} bulk_insert_buffer_size = {{ mysql_bulk_insert_buffer_size }} read_buffer_size = {{ mysql_read_buffer_size }} read_rnd_buffer_size = {{ mysql_read_rnd_buffer_size }} myisam_sort_buffer_size = {{ mysql_myisam_sort_buffer_size }} thread_cache_size = {{ mysql_thread_cache_size }} {% if '8.0.' not in mysql_cli_version.stdout %} query_cache_type = {{ mysql_query_cache_type }} query_cache_size = {{ mysql_query_cache_size }} query_cache_limit = {{ mysql_query_cache_limit }} {% endif %} max_connections = {{ mysql_max_connections }} max_user_connections = {{ mysql_max_user_connections }} tmp_table_size = {{ mysql_tmp_table_size }} max_heap_table_size = {{ mysql_max_heap_table_size }} group_concat_max_len = {{ mysql_group_concat_max_len }} join_buffer_size = {{ mysql_join_buffer_size }}   # Other settings. connect_timeout = {{ mysql_connect_timeout }} wait_timeout = {{ mysql_wait_timeout }} lower_case_table_names = {{ mysql_lower_case_table_names }} event_scheduler = {{ mysql_event_scheduler_state }} myisam_recover_options = BACKUP concurrent_insert = 2 default_storage_engine = InnoDB   # InnoDB settings. {% if mysql_supports_innodb_large_prefix and '8.0.' not in mysql_cli_version.stdout %} innodb_large_prefix = {{ mysql_innodb_large_prefix }} innodb_file_format = {{ mysql_innodb_file_format }} {% endif %} innodb_file_per_table = {{ mysql_innodb_file_per_table }} innodb_buffer_pool_size = {{ mysql_innodb_buffer_pool_size }} innodb_log_file_size = {{ mysql_innodb_log_file_size }} innodb_log_buffer_size = {{ mysql_innodb_log_buffer_size }} innodb_flush_log_at_trx_commit = {{ mysql_innodb_flush_log_at_trx_commit }} innodb_lock_wait_timeout = {{ mysql_innodb_lock_wait_timeout }} innodb_strict_mode = 0 innodb_open_files = 400 innodb_io_capacity = 400 innodb_flush_method = O_DIRECT   [database] default-character-set = utf8mb4   [mysqldump] quick max_allowed_packet = {{ mysql_mysqldump_max_allowed_packet }}   [mysqld_safe] pid-file = {{ mysql_pid_file }} nice = 0   {% if mysql_config_include_files | length %} # * IMPORTANT: Additional settings that can override those from this file! #   The files must end with '.cnf', otherwise they'll be ignored. # !includedir {{ mysql_config_include_dir }} {% endif %}   [galera] binlog_format = row

    
     
11. defaults/main.yml
    
    --- # If install or uninstall mysql uninstall_mysql: false # Set this to the user ansible is logging in as - should have root # or sudo access mysql_user_home: /root mysql_user_name: root mysql_user_password: root # The default root user installed by mysql - almost always root mysql_root_home: /root mysql_root_username: root mysql_root_password: "Yonyou*123" # Set this to `true` to forcibly update the root password. mysql_root_password_update: true mysql_user_password_update: false mysql_enabled_on_startup: true # Whether my.cnf should be updated on every run. overwrite_global_mycnf: true # The following variables have a default value depending on operating system. # mysql_config_file: /etc/my.cnf # mysql_config_include_dir: /etc/my.cnf.d # Pass in a comma-separated list of repos to use (e.g. "remi,epel"). Used only # for RedHat systems (and derivatives). mysql_enablerepo: "" # Define a custom list of packages to install; if none provided, the default # package list from vars/[OS-family].yml will be used. # mysql_packages: # - mysql # - mysql-server # - MySQL-python # MySQL connection settings. mysql_port: "3306" mysql_bind_address: '0.0.0.0' mysql_skip_name_resolve: true # slave_skip_errors: 1062：主键冲突 Duplicate entry '%s' for key %d slave_skip_errors: '1062' mysql_datadir: /var/lib/mysql mysql_tmpdir: "{{ mysql_datadir }}/tmp" mysql_sql_mode: '' # The following variables have a default value depending on operating system. mysql_pid_file: "{{ mysql_datadir }}/mariadb.pid" mysql_socket: "{{ mysql_datadir }}/mysql.sock" # Log file settings. mysql_log_file_group: mysql # Slow query log settings. mysql_slow_query_log_enabled: false mysql_slow_query_time: "10" # The following variable has a default value depending on operating system. # mysql_slow_query_log_file: /var/log/mysql-slow.log # Memory settings (default values optimized ~512MB RAM). mysql_key_buffer_size: "256M" mysql_max_allowed_packet: "100M" mysql_table_open_cache: "400" mysql_sort_buffer_size: "4M" mysql_bulk_insert_buffer_size: "16M" mysql_read_buffer_size: "2M" mysql_read_rnd_buffer_size: "4M" mysql_myisam_sort_buffer_size: "512M" mysql_thread_cache_size: "128" mysql_query_cache_type: "0" mysql_query_cache_size: "64M" mysql_query_cache_limit: "1M" mysql_max_connections: "1000" mysql_max_user_connections: "1000" mysql_tmp_table_size: "32M" mysql_max_heap_table_size: "32M" mysql_group_concat_max_len: "1024" mysql_join_buffer_size: "262144" # Other settings. mysql_lower_case_table_names: "1" mysql_connect_timeout: "5" mysql_wait_timeout: "31536000" mysql_event_scheduler_state: "OFF" # InnoDB settings. mysql_innodb_file_per_table: "1" # Set .._buffer_pool_size up to 80% of RAM but beware of setting too high. mysql_innodb_buffer_pool_size: "2048M" # Set .._log_file_size to 25% of buffer pool size. mysql_innodb_log_file_size: "64M" mysql_innodb_log_buffer_size: "1024M" mysql_innodb_flush_log_at_trx_commit: "1" mysql_innodb_lock_wait_timeout: "50" # These settings require MySQL > 5.5. mysql_innodb_large_prefix: "1" mysql_innodb_file_format: "barracuda" # mysqldump settings. mysql_mysqldump_max_allowed_packet: "64M" # Logging settings. mysql_log: "" # The following variables have a default value depending on operating system. # mysql_log_error: /var/log/mysql/mysql.err # mysql_syslog_tag: mysql mysql_config_include_files: [] # - src: path/relative/to/playbook/file.cnf # - { src: path/relative/to/playbook/anotherfile.cnf, force: yes } # Databases. mysql_databases: [] # - name: example # collation: utf8_general_ci # encoding: utf8 # replicate: 1 # Users. mysql_users: [] # - name: example # host: 127.0.0.1 # password: secret # priv: *.*:USAGE # Replication settings (replication is only enabled if master/user have values). mysql_server_id: "1" mysql_max_binlog_size: "100M" mysql_binlog_format: "ROW" mysql_expire_logs_days: "10" mysql_replication_role: '' mysql_replication_master: '' # Same keys as `mysql_users` above. mysql_replication_user: []

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138

    ---   # If install or uninstall mysql uninstall_mysql: false   # Set this to the user ansible is logging in as - should have root # or sudo access mysql_user_home: /root mysql_user_name: root mysql_user_password: root   # The default root user installed by mysql - almost always root mysql_root_home: /root mysql_root_username: root mysql_root_password: "Yonyou*123"   # Set this to `true` to forcibly update the root password. mysql_root_password_update: true mysql_user_password_update: false   mysql_enabled_on_startup: true   # Whether my.cnf should be updated on every run. overwrite_global_mycnf: true   # The following variables have a default value depending on operating system. # mysql_config_file: /etc/my.cnf # mysql_config_include_dir: /etc/my.cnf.d   # Pass in a comma-separated list of repos to use (e.g. "remi,epel"). Used only # for RedHat systems (and derivatives). mysql_enablerepo: ""   # Define a custom list of packages to install; if none provided, the default # package list from vars/[OS-family].yml will be used. # mysql_packages: #   - mysql #   - mysql-server #   - MySQL-python   # MySQL connection settings. mysql_port: "3306" mysql_bind_address: '0.0.0.0' mysql_skip_name_resolve: true # slave_skip_errors: 1062：主键冲突 Duplicate entry '%s' for key %d slave_skip_errors: '1062' mysql_datadir: /var/lib/mysql mysql_tmpdir: "{{ mysql_datadir }}/tmp" mysql_sql_mode: '' # The following variables have a default value depending on operating system. mysql_pid_file: "{{ mysql_datadir }}/mariadb.pid" mysql_socket: "{{ mysql_datadir }}/mysql.sock"   # Log file settings. mysql_log_file_group: mysql   # Slow query log settings. mysql_slow_query_log_enabled: false mysql_slow_query_time: "10" # The following variable has a default value depending on operating system. # mysql_slow_query_log_file: /var/log/mysql-slow.log   # Memory settings (default values optimized ~512MB RAM). mysql_key_buffer_size: "256M" mysql_max_allowed_packet: "100M" mysql_table_open_cache: "400" mysql_sort_buffer_size: "4M" mysql_bulk_insert_buffer_size: "16M" mysql_read_buffer_size: "2M" mysql_read_rnd_buffer_size: "4M" mysql_myisam_sort_buffer_size: "512M" mysql_thread_cache_size: "128" mysql_query_cache_type: "0" mysql_query_cache_size: "64M" mysql_query_cache_limit: "1M" mysql_max_connections: "1000" mysql_max_user_connections: "1000" mysql_tmp_table_size: "32M" mysql_max_heap_table_size: "32M" mysql_group_concat_max_len: "1024" mysql_join_buffer_size: "262144"   # Other settings. mysql_lower_case_table_names: "1" mysql_connect_timeout: "5" mysql_wait_timeout: "31536000" mysql_event_scheduler_state: "OFF"   # InnoDB settings. mysql_innodb_file_per_table: "1" # Set .._buffer_pool_size up to 80% of RAM but beware of setting too high. mysql_innodb_buffer_pool_size: "2048M" # Set .._log_file_size to 25% of buffer pool size. mysql_innodb_log_file_size: "64M" mysql_innodb_log_buffer_size: "1024M" mysql_innodb_flush_log_at_trx_commit: "1" mysql_innodb_lock_wait_timeout: "50"   # These settings require MySQL > 5.5. mysql_innodb_large_prefix: "1" mysql_innodb_file_format: "barracuda"   # mysqldump settings. mysql_mysqldump_max_allowed_packet: "64M"   # Logging settings. mysql_log: "" # The following variables have a default value depending on operating system. # mysql_log_error: /var/log/mysql/mysql.err # mysql_syslog_tag: mysql   mysql_config_include_files: [] #  - src: path/relative/to/playbook/file.cnf #  - { src: path/relative/to/playbook/anotherfile.cnf, force: yes }   # Databases. mysql_databases: [] #   - name: example #     collation: utf8_general_ci #     encoding: utf8 #     replicate: 1   # Users. mysql_users: [] #   - name: example #     host: 127.0.0.1 #     password: secret #     priv: *.*:USAGE   # Replication settings (replication is only enabled if master/user have values). mysql_server_id: "1" mysql_max_binlog_size: "100M" mysql_binlog_format: "ROW" mysql_expire_logs_days: "10" mysql_replication_role: '' mysql_replication_master: '' # Same keys as `mysql_users` above. mysql_replication_user: []

    
     
12. 最后, playbook文件:
    
    - name: deploy mysql cluster hosts: mysql pre_tasks: - name: Override variables for MySQL (RedHat). set_fact: mysql_users: - name: replication host: '%' password: replication*Yonyou*123 #priv: "*.*:ALL" mysql_replication_user: name: replication host: '%' password: replication*Yonyou*123 #priv: "*.*:ALL" mysql_databases: - name: yonyou_cloud collation: utf8mb4_unicode_ci encoding: utf8mb4 replicate: 1 - name: eoscloud collation: utf8mb4_unicode_ci encoding: utf8mb4 replicate: 1 - name: disconf collation: utf8mb4_unicode_ci encoding: utf8mb4 replicate: 1 - name: pushserver collation: utf8mb4_unicode_ci encoding: utf8mb4 replicate: 1 - name: serv_meta collation: utf8mb4_unicode_ci encoding: utf8mb4 replicate: 1 - name: zipkin collation: utf8mb4_unicode_ci encoding: utf8mb4 replicate: 1 - name: monitor collation: utf8mb4_unicode_ci encoding: utf8mb4 replicate: 1 # when: ansible_os_family == "RedHat" roles: - { role: mysql }

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

    - name: deploy mysql cluster   hosts: mysql   pre_tasks:     - name: Override variables for MySQL (RedHat).       set_fact:         mysql_users:           - name: replication             host: '%'             password: replication*Yonyou*123             #priv: "*.*:ALL"         mysql_replication_user:           name: replication           host: '%'           password: replication*Yonyou*123           #priv: "*.*:ALL"         mysql_databases:           - name: yonyou_cloud             collation: utf8mb4_unicode_ci             encoding: utf8mb4             replicate: 1           - name: eoscloud             collation: utf8mb4_unicode_ci             encoding: utf8mb4             replicate: 1           - name: disconf             collation: utf8mb4_unicode_ci             encoding: utf8mb4             replicate: 1           - name: pushserver             collation: utf8mb4_unicode_ci             encoding: utf8mb4             replicate: 1           - name: serv_meta             collation: utf8mb4_unicode_ci             encoding: utf8mb4             replicate: 1           - name: zipkin             collation: utf8mb4_unicode_ci             encoding: utf8mb4             replicate: 1           - name: monitor             collation: utf8mb4_unicode_ci             encoding: utf8mb4             replicate: 1       # when: ansible_os_family == "RedHat"   roles:     - { role: mysql }

    
    hosts.ini里需要如下定义:
    
    [mysql] 172.20.48.227 mysql_replication_role=master mysql_replication_master=172.20.48.227 mysql_server_id=1 mysql_port=3306 mysql_root_password=aa*123 mysql_datadir=/var/lib/mysql ansible_ssh_user=root ansible_ssh_port=22 172.20.48.254 mysql_replication_role=slave mysql_replication_master=172.20.48.227 mysql_server_id=2 mysql_port=3306 mysql_root_password=aa*123 mysql_datadir=/var/lib/mysql ansible_ssh_user=root ansible_ssh_port=22

    1 2 3

    [mysql] 172.20.48.227 mysql_replication_role=master mysql_replication_master=172.20.48.227 mysql_server_id=1 mysql_port=3306 mysql_root_password=aa*123 mysql_datadir=/var/lib/mysql ansible_ssh_user=root ansible_ssh_port=22 172.20.48.254 mysql_replication_role=slave mysql_replication_master=172.20.48.227 mysql_server_id=2 mysql_port=3306 mysql_root_password=aa*123 mysql_datadir=/var/lib/mysql ansible_ssh_user=root ansible_ssh_port=22