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

创新互联是一家专业提供同仁企业网站建设,专注与做网站、成都网站建设、H5技术、小程序制作等业务。10年已为同仁众多企业、政府机构等服务。创新互联专业网站制作公司优惠进行中。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]#

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

网站栏目：ansible构建MariaDBGaleraClus-创新互联
转载来源：http://scgulin.cn/article/cceesh.html