1、环境,本文只是介绍mxc 的配置，其他配置（全部配置）我会发下载连接 https://down.51cto.com/data/2467920

在赣榆等地区，都构建了全面的区域性战略布局，加强发展的系统性、市场前瞻性、产品创新能力，以专注、极致的服务理念，为客户提供成都网站设计、成都网站建设 网站设计制作定制网站建设,公司网站建设,企业网站建设,高端网站设计,全网整合营销推广,外贸网站制作,赣榆网站建设费用合理。

192.168.56.21   ansible
192.168.56.20   es1
192.168.56.19   es2
192.168.56.18   es3

2、目录总览，包括mxc、主从和单节点安装

[root@mgr ~]# tree mariadb
mariadb
├── common
│   ├── config_master_slaves.yaml
│   ├── config_mxc.yaml
│   ├── config_MySQL_service_and_start_mysql.yaml
│   ├── create_user_and_config_file.yaml
│   ├── initialize_mysql_datadir.yaml
│   ├── install_mysql_dependents.yaml
│   ├── install_mysql.yaml
│   ├── make_mysql_security.yaml
├── install_mariadb_galera_cluster.yaml
├── install_master_slaves.yaml
├── install_single_mysql.yaml
├── softwares
│   └── mariadb-10.2.24-linux-x86_64.tar.gz
├── template
│   ├── confmxc.cnf
│   ├── master_slaves.sql
│   ├── my.cnf
│   ├── mysql.conf
│   ├── mysql.service
│   ├── secure.sql
│   ├── startmxc.sh
├── uninstall.yaml
└── vars
    ├── config.yaml
    ├── mariadb_galera_cluster.yaml
    └── master_slaves.yaml

3、install_mariadb_galera_cluster.yaml

[root@mgr mariadb]# cat install_mariadb_galera_cluster.yaml 
---
 - hosts: es
   remote_user: root
   become_user: root
   vars_files:
    - ./vars/config.yaml
    - ./vars/mariadb_galera_cluster.yaml
   tasks:
    - name: create user and config file
      import_tasks: common/create_user_and_config_file.yaml

    - name: install mysql dependents 
      import_tasks: common/install_mysql_dependents.yaml

    - name: install mysql to /usr/local/mysql
      import_tasks: common/install_mysql.yaml

    - name: init mysql datadir
      import_tasks: common/initialize_mysql_datadir.yaml

    - name: config mysql service and start mysql
      import_tasks: common/config_mysql_service_and_start_mysql.yaml

    - name: sleep 15's
      shell: sleep 15

    - name: make mysql secure
      import_tasks: common/make_mysql_security.yaml

    - name: config mysql group replication 
      import_tasks: common/config_mxc.yaml
...
[root@mgr mariadb]#

4、config.yaml

[root@mgr mariadb]# cat vars/config.yaml 
---

#mysql 安装包所在的目录
mariadb_packages_dir: /root/mariadb/softwares/
mariadb_package_name: mariadb-10.2.24-linux-x86_64.tar.gz
#linux 系统级别mysql用户相关信息
mysql_user: mysql
mysql_group: mysql
mysql_user_uid: 3306
mysql_user_gid: 3306
#mysql 安装目录
mysql_base_dir: /usr/local/mysql/
#mysql 真正的datadir就会是mysql_data_dir_base+mysql_port
mysql_data_dir_base: /database/mysql/data/
mysql_port: 3306
mysql_root_password: mariadb0352
mysql_rple_user: repl
mysql_rple_password: repl0352
mysql_wsrep_sst_user: sstuser
mysql_wsrep_sst_password: sstuser1234

#mysql 配置文件模版
mysql_binlog_format: row
mysql_innodb_log_files_in_group: 16
mysql_innodb_log_file_size: 256M
mysql_innodb_log_buffer_size: 64M
mysql_innodb_open_files: 65535
mysql_max_connections: 1000
mysql_thread_cache_size: 256
mysql_sync_binlog: 1
mysql_binlog_cache_size: 64K
mysql_innodb_online_alter_log_max_size: 128M
mysql_performance_schema: 'on'

with_php: 1
with_mariadb_galera_cluster: 0
[root@mgr mariadb]#

5、mariadb_galera_cluster.yaml

[root@mgr mariadb]# cat vars/mariadb_galera_cluster.yaml 
with_mariadb_galera_cluster: 1
mxc_port: 4567
mxc_hosts: 
    - '192.168.56.18'
    - '192.168.56.19'
    - '192.168.56.20'
[root@mgr mariadb]#

6、create_user_and_config_file.yaml

[root@mgr mariadb]# cat common/create_user_and_config_file.yaml
---
 - name: create mysql user
   user:
    name: mysql
    state: present
    uid: "{{mysql_user_uid}}"

 - name: config /etc/my.cnf 
   template:
    src: ../template/my.cnf
    dest: /etc/my.cnf
    owner: mysql
    group: mysql
    backup: yes
[root@mgr mariadb]#

7、install_mysql_dependents.yaml

[root@mgr mariadb]# cat common/install_mysql_dependents.yaml
---
 - name: install libaio-devel
   yum:
    name: libaio-devel
    state: present

 - name: install numactl-devel
   yum:
    name: numactl-devel
    state: present

 - name: install perl-Data-Dumper 
   yum:
    name: perl-Data-Dumper 
    state: present

 - name: install lsof
   yum:
    name: lsof 
    state: present

 - name: install rsync 
   yum:
    name: rsync 
    state: present


 - name: install socat 
   yum:
    name: socat 
    state: present

 - name: install perl-DBD-MySQL 
   yum:
    name: perl-DBD-MySQL 
    state: present

 - name: perl-Time-HiRes 
   yum:
    name: perl-Time-HiRes 
    state: present
[root@mgr mariadb]#

8、install_mysql.yaml

[root@mgr mariadb]# cat  common/install_mysql.yaml
---
#由本地传输mysql安装包到目标主机、并把安装包解压到/usr/local/目录
#等价bash
#scp mysql-xxxx.tar.gz 192.168.xx.xx:/tmp/
#tar -xvf /tmp/mysq-xxxx.tar.gz -C /usr/local/
 - name: transfer mysql install package to remote host and unarchive to /usr/local/
   unarchive:
    src: "{{ mariadb_packages_dir }}/{{ mariadb_package_name }}"
    dest: /usr/local/
    owner: mysql
    group: mysql

#设置权限
#等价bash
#chown -R mysql:mysql /usr/local/mysql-xxxx
 - name: change owner to mysql user
   when: mariadb_package_name.find('tar.gz') != -1
   file:
    path: /usr/local/{{ mariadb_package_name | regex_replace('.tar.gz','') }}
    owner: mysql
    group: mysql
    recurse: yes

#chown -R mysql:mysql /usr/local/mysql-xxxx
 - name: change owner to mysql user
   when: mariadb_package_name.find('tar.xz') != -1
   file:
    path: /usr/local/{{ mariadb_package_name | regex_replace('.tar.xz','') }}
    owner: mysql
    group: mysql
    recurse: yes

#创建连接文件
#等价bash
#ln -s /usr/local/mysql-xxxx /usr/local/mysql
 - name: make link /usr/local/mysql-xx.yy.zz to /usr/local/mysql
   when: mariadb_package_name.find('tar.gz') != -1
   file:
    src: /usr/local/{{ mariadb_package_name | regex_replace('.tar.gz','') }}
    dest: /usr/local/mysql
    state: link
    owner: mysql
    group: mysql

 - name: make link /usr/local/mysql-xx.yy.zz to /usr/local/mysql
   when: mariadb_package_name.find('tar.xz') != -1
   file:
    src: /usr/local/{{ mariadb_package_name | regex_replace('.tar.xz','') }}
    dest: /usr/local/mysql
    state: link
    owner: mysql
    group: mysql

#导出mysql的库文件
 - name: export mysql share object (*.os)
   template:
    src: ../template/mysql.conf
    dest: /etc/ld.so.conf.d/mysql.conf

#加载共享库
 - name: load share object
   shell: ldconfig

#导出PATH环境变量
 - name: export path env variable
   lineinfile:
    path: /etc/profile
    line: export PATH=/usr/local/mysql/bin/:$PATH
    insertafter: EOF

 - name: export path env to /root/.bashrc
   lineinfile:
    path: /root/.bashrc
    line: export PATH=/usr/local/mysql/bin/:$PATH
    insertafter: EOF  

 - name: remove /usr/include/mysql
   file:
    dest: /usr/include/mysql
    state: absent

#导出头文件
 - name: export include file to /usr/include/mysql
   file:
    src: /usr/local/mysql/include
    dest: /usr/include/mysql
    state: link

 - name: create libmysqlclient_r.so file for php-5.6
   when: with_php == 1
   file:
    src: "{{mysql_base_dir + 'lib/libmysqlclient.so'}}"
    dest: "{{mysql_base_dir + 'lib/libmysqlclient_r.so'}}"
    state: link
    owner: mysql
    group: mysql


[root@mgr mariadb]#

9、initialize_mysql_datadir.yaml

[root@mgr mariadb]# cat common/initialize_mysql_datadir.yaml
---
 - name: create datadir
   file:
    path: "{{mysql_data_dir_base}}/{{mysql_port}}"
    state: directory
    owner: mysql
    group: mysql

 - name: initialize-insecure 
   shell: /usr/local/mysql/scripts/mysql_install_db --user={{mysql_user}} --datadir={{mysql_data_dir_base}}/{{mysql_port}} --basedir={{mysql_base_dir}}  
[root@mgr mariadb]#

10、config_mysql_service_and_start_mysql.yaml

[root@mgr mariadb]# cat common/config_mysql_service_and_start_mysql.yaml
  - name: create systemd config file
    when: ansible_distribution_major_version == "7"
    template:
     src: ../template/mysql.service
     dest: /usr/lib/systemd/system/mysql.service

  - name: start mysql(sytemctl)
    when: ansible_distribution_major_version == "7"
    systemd:
     name: mysql
     state: started
     daemon_reload: yes

  - name: config mysql.service start up on boot
    when: ansible_distribution_major_version == "7"
    systemd:
     name: mysql
     enabled: yes
     daemon_reload: yes

  - name: config sysv start script
    when: ansible_distribution_major_version == "6"
    copy:
     remote_src: yes
     src: "{{mysql_base_dir + 'support-files/mysql.server'}}"
     dest: /etc/init.d/mysqld
     mode: 0755

  - name: start mysql(service)
    when: ansible_distribution_major_version == "6"
    service: 
     name: mysqld
     state: started

  - name: config mysql.service start up on boot
    when: ansible_distribution_major_version == "6"
    shell: chkconfig mysqld on[root@mgr mariadb]# 
[root@mgr mariadb]#

11、make_mysql_security.yaml

[root@mgr mariadb]# cat common/make_mysql_security.yaml
  - name: transfer sql statement to remonte
    template:
     src: ../template/secure.sql
     dest: /tmp/make_mysql_secure.sql

  - name: make mysql secure
    shell: mysql -hlocalhost -uroot -S /tmp/mysql.sock < /tmp/make_mysql_secure.sql

  - name: remove temp file /tmp/make_mysql_secure.sql
    file:
     dest: /tmp/make_mysql_secure.sql
     state: absent
[root@mgr mariadb]#

12、config_mxc.yaml

[root@mgr mariadb]# cat common/config_mxc.yaml
  - name: transfer config mxc to remonte
    template:
     src: ../template/confmxc.cnf
     dest: /tmp/confmxc.cnf

  - name: transfer shell startmxc to remonte
    template:
     src: ../template/startmxc.sh
     dest: /tmp/startmxc.sh
     mode: '0777'

  - name: config mariadb galera cluster
    shell: /tmp/startmxc.sh

  - name: remove temp file /tmp/startmxc.sh
    file:
     dest: /tmp/startmxc.sh
     state: absent
[root@mgr mariadb]#

13、my.cnf

[root@mgr mariadb]# cat template/my.cnf 
[mysql]
auto-rehash
socket                              =/tmp/mysql.sock                #   /tmp/mysql.sock


[mysqld]
####: for global
user                                =mysql                          #   mysql
basedir                             ={{ mysql_base_dir }}              #        /usr/local/mysql/
datadir                             ={{ mysql_data_dir_base }}{{mysql_port}}      #     /usr/local/mysql/data
server_id                           ={{ 1024 |random(1) }}                            # 0
port                                ={{ mysql_port }}                           #       3306
character_set_server                =utf8                           #   latin1
log_bin_trust_function_creators     =on                             #   0
max_prepared_stmt_count             =1048576
#log_timestamps                      =system                         #  utc
socket                              =/tmp/mysql.sock                #   /tmp/mysql.sock
read_only                           =0                              #   off
skip_name_resolve                   =1                              #   0
auto_increment_increment            =1                              #   1
auto_increment_offset               =1                              #   1
lower_case_table_names              =1                              #   0
secure_file_priv                    =                               #   null
open_files_limit                    =65536                          #   1024
max_connections                     ={{mysql_max_connections | default(1000)}}
thread_cache_size                   ={{ [ansible_processor_count * 8,256] | max}}                              #   9
table_open_cache                    =4096                           #   2000
table_definition_cache              =2000                           #   1400
table_open_cache_instances          =32                             #   16

####: for binlog
binlog_format                       ={{mysql_binlog_format}}                          # row
log_bin                             =mysql-bin                      #   off
#binlog_rows_query_log_events        =on                             #  off
log_slave_updates                   =on                             #   off
expire_logs_days                    =7                              #   0
{# set binlog cache size #}
{% if mysql_binlog_format == 'mixed' %}
binlog_cache_size                   =32768                          #   32768(32k)
{% else %}
binlog_cache_size                   =65536                          #   65536(64k)
{% endif %}
{# set binlog cache size #}
binlog_checksum                     =none                           #   CRC32
sync_binlog                         =1                              #   1
#slave-preserve-commit-order         =ON                             #   

####: for error-log
log_error                           =err.log                        #   /usr/local/mysql/data/localhost.localdomain.err

{# set general log #}
general_log                         =off                            #   off
general_log_file                    =general.log                    #   hostname.log
{# set general log #}

####: for slow query log
slow_query_log                      =on                             #    off
slow_query_log_file                 =slow.log                       #    hostname.log
log_queries_not_using_indexes       =on                             #    off
long_query_time                     =10.000000                       #    10.000000

####: for gtid
#gtid_executed_compression_period    =1000                          #   1000
#gtid_mode                           =on                            #   off
#enforce_gtid_consistency            =on                            #   off


####: for replication
skip_slave_start                    =0                              #                              #    0
rpl_semi_sync_master_enabled        =1                             #    0
rpl_semi_sync_slave_enabled         =1                             #    0
rpl_semi_sync_master_timeout        =1000                          #    1000(1 second)
plugin_load_add                     =semisync_master.so            #
plugin_load_add                     =semisync_slave.so             #
#binlog_group_commit_sync_delay      =500                           #    0      500(0.05% seconde) 
#binlog_group_commit_sync_no_delay_count = 13                       #    0


####: for innodb
default_storage_engine                          =innodb                     #   innodb
default_tmp_storage_engine                      =innodb                     #   innodb
innodb_data_file_path                           =ibdata1:64M:autoextend     #   ibdata1:12M:autoextend
innodb_temp_data_file_path                      =ibtmp1:12M:autoextend      #   ibtmp1:12M:autoextend
innodb_buffer_pool_filename                     =ib_buffer_pool             #   ib_buffer_pool
innodb_log_group_home_dir                       =./                         #   ./
innodb_log_files_in_group                       ={{ mysql_innodb_log_files_in_group }}                          #       2
innodb_log_file_size                            ={{ mysql_innodb_log_file_size }}                        #      50331648(48M)
innodb_file_per_table                           =on                         #   on
innodb_online_alter_log_max_size                =128M                  #   134217728(128M)
innodb_open_files                               ={{mysql_innodb_open_files}}                       #   2000
innodb_page_size                                =16k                        #   16384(16k)
innodb_thread_concurrency                       =0                          #   0
innodb_read_io_threads                          =4                          #   4
innodb_write_io_threads                         =4                          #   4
innodb_purge_threads                            =4                          #   4(garbage collection)
innodb_page_cleaners                            =4                          #   4(flush lru list)
innodb_print_all_deadlocks                      =on                         #   off
innodb_deadlock_detect                          =on                         #   on
innodb_lock_wait_timeout                        =50                         #   50
innodb_spin_wait_delay                          =6                          #   6
innodb_autoinc_lock_mode                        =2                          #   1
innodb_io_capacity                              =200                        #   200
innodb_io_capacity_max                          =2000                       #   2000
#--------Persistent Optimizer Statistics
innodb_stats_auto_recalc                        =on                         #   on
innodb_stats_persistent                         =on                         #   on
innodb_stats_persistent_sample_pages            =20                         #   20
{# --                 set innodb_buffer_pool_instances                                 -- #}
{% if ((ansible_memtotal_mb * 0.6 // 1024) | int ) < 64  %}
innodb_buffer_pool_instances                    ={{ [ ((ansible_memtotal_mb * 0.6 // 1024) | int ) , 1 ] | max }}
{% else %}
innodb_buffer_pool_instances                    =64
{% endif %}
{# --                 set innodb_buffer_pool_instances                                 -- #}
innodb_adaptive_hash_index                      =on                         #   on
innodb_change_buffering                         =all                        #   all
innodb_change_buffer_max_size                   =25                         #   25
innodb_flush_neighbors                          =1                          #   1
#innodb_flush_method                             =                           #
innodb_doublewrite                              =on                         #   on
innodb_log_buffer_size                          ={{mysql_innodb_log_buffer_size}}                        #      16777216(16M)
innodb_flush_log_at_timeout                     =1                          #   1
innodb_flush_log_at_trx_commit                  =1                          #   1
innodb_buffer_pool_size                         ={{ (ansible_memtotal_mb * 0.6 // 128 ) | int * 128 }}M                  #      134217728(128M)
autocommit                                      =1                          #   1
#--------innodb scan resistant
innodb_old_blocks_pct                           =37                         #    37
innodb_old_blocks_time                          =1000                       #    1000
#--------innodb read ahead
innodb_read_ahead_threshold                     =56                         #    56 (0..64)
innodb_random_read_ahead                        =OFF                        #    OFF
#--------innodb buffer pool state
innodb_buffer_pool_dump_pct                     =25                         #    25 
innodb_buffer_pool_dump_at_shutdown             =ON                         #    ON
innodb_buffer_pool_load_at_startup              =ON                         #    ON
[root@mgr mariadb]#

14、mysql.conf 

[root@mgr mariadb]# cat template/mysql.conf 
{{mysql_base_dir + 'lib/'}}[root@mgr mariadb]# 
[root@mgr mariadb]#

15、secure.sql

[root@mgr mariadb]# cat template/secure.sql 
set sql_log_bin=0;
    update mysql.user set password=password('{{ mysql_root_password }}') where user='root';
    delete from mysql.user where user='';

{% if with_mariadb_galera_cluster == 1 %}

grant all on *.* to {{mysql_wsrep_sst_user}} identified by '{{mysql_wsrep_sst_password}}';

{% endif %}
    flush privileges;

set sql_log_bin=1;
[root@mgr mariadb]#

16、mysql.service

[root@mgr mariadb]# cat template/mysql.service 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 65536
Environment=MYSQLD_PARENT_PID=1
#Restart=on-failure
#RestartPreventExitStatus=1
#PrivateTmp=false
[root@mgr mariadb]#

17、confmxc.cnf 

[root@mgr mariadb]# cat template/confmxc.cnf  


{% if with_mariadb_galera_cluster == 1 %}

wsrep_on=on
wsrep_provider=/usr/local/mysql/lib/galera/libgalera_smm.so
wsrep_cluster_name=g_mariadb
#wsrep_cluster_address="gcomm://192.168.56.20,192.168.56.19,192.168.56.18"
wsrep_slave_threads=5

wsrep_sst_method=mariabackup

wsrep_sst_auth={{mysql_wsrep_sst_user}}:{{mysql_wsrep_sst_password}}

{% set gs = ':' + (mxc_port | string)+',' %}
wsrep_cluster_address                  ="gcomm://{{ mxc_hosts | join(gs) + ':' + (mxc_port | string)  }}"

{% endif %}
[root@mgr mariadb]#

wsrep_cluster_address                  ="gcomm://{{ mxc_hosts |join(',')}}"

18、startmxc.sh

[root@mgr mariadb]# cat template/startmxc.sh
#!/bin/bash

{% if ansible_distribution_major_version == "7" %}

systemctl stop mysql

{% else %}

/etc/init.d/mysqld stop

{% endif %}



cat /tmp/confmxc.cnf  >>/etc/my.cnf

sleep 10

{% if mxc_hosts[0] in ansible_all_ipv4_addresses %}

mysqld_safe --wsrep_new_cluster &

sleep 10

{% else %}
    sleep 90 
    {% if ansible_distribution_major_version == "7" %}

       systemctl start mysql

    {% else %}

       /etc/init.d/mysqld start

    {% endif %}

{% endif %}
[root@mgr mariadb]#

网页名称：ansible构建MariaDBGaleraClus
标题URL：http://kswjz.com/article/pdjjgg.html