ansible 构建 MariaDB Galera Clus

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

192.168.56.21   ansible192.168.56.20   es1192.168.56.19   es2192.168.56.18   es3

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

[root@mgr ~]# tree mariadbmariadb├── 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: mysqlmysql_group: mysqlmysql_user_uid: 3306mysql_user_gid: 3306#mysql 安装目录mysql_base_dir: /usr/local/mysql/#mysql 真正的datadir就会是mysql_data_dir_base+mysql_portmysql_data_dir_base: /database/mysql/data/mysql_port: 3306mysql_root_password: mariadb0352mysql_rple_user: replmysql_rple_password: repl0352mysql_wsrep_sst_user: sstusermysql_wsrep_sst_password: sstuser1234#mysql 配置文件模版mysql_binlog_format: rowmysql_innodb_log_files_in_group: 16mysql_innodb_log_file_size: 256Mmysql_innodb_log_buffer_size: 64Mmysql_innodb_open_files: 65535mysql_max_connections: 1000mysql_thread_cache_size: 256mysql_sync_binlog: 1mysql_binlog_cache_size: 64Kmysql_innodb_online_alter_log_max_size: 128Mmysql_performance_schema: 'on'with_php: 1with_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: 1mxc_port: 4567mxc_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-rehashsocket                              =/tmp/mysql.sock                #   /tmp/mysql.sock[mysqld]####: for globaluser                                =mysql                          #   mysqlbasedir                             ={{ mysql_base_dir }}              #        /usr/local/mysql/datadir                             ={{ mysql_data_dir_base }}{{mysql_port}}      #     /usr/local/mysql/dataserver_id                           ={{ 1024 |random(1) }}                            # 0port                                ={{ mysql_port }}                           #       3306character_set_server                =utf8                           #   latin1log_bin_trust_function_creators     =on                             #   0max_prepared_stmt_count             =1048576#log_timestamps                      =system                         #  utcsocket                              =/tmp/mysql.sock                #   /tmp/mysql.sockread_only                           =0                              #   offskip_name_resolve                   =1                              #   0auto_increment_increment            =1                              #   1auto_increment_offset               =1                              #   1lower_case_table_names              =1                              #   0secure_file_priv                    =                               #   nullopen_files_limit                    =65536                          #   1024max_connections                     ={{mysql_max_connections | default(1000)}}thread_cache_size                   ={{ [ansible_processor_count * 8,256] | max}}                              #   9table_open_cache                    =4096                           #   2000table_definition_cache              =2000                           #   1400table_open_cache_instances          =32                             #   16####: for binlogbinlog_format                       ={{mysql_binlog_format}}                          # rowlog_bin                             =mysql-bin                      #   off#binlog_rows_query_log_events        =on                             #  offlog_slave_updates                   =on                             #   offexpire_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                           #   CRC32sync_binlog                         =1                              #   1#slave-preserve-commit-order         =ON                             #   ####: for error-loglog_error                           =err.log                        #   /usr/local/mysql/data/localhost.localdomain.err{# set general log #}general_log                         =off                            #   offgeneral_log_file                    =general.log                    #   hostname.log{# set general log #}####: for slow query logslow_query_log                      =on                             #    offslow_query_log_file                 =slow.log                       #    hostname.loglog_queries_not_using_indexes       =on                             #    offlong_query_time                     =10.000000                       #    10.000000####: for gtid#gtid_executed_compression_period    =1000                          #   1000#gtid_mode                           =on                            #   off#enforce_gtid_consistency            =on                            #   off####: for replicationskip_slave_start                    =0                              #                              #    0rpl_semi_sync_master_enabled        =1                             #    0rpl_semi_sync_slave_enabled         =1                             #    0rpl_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 innodbdefault_storage_engine                          =innodb                     #   innodbdefault_tmp_storage_engine                      =innodb                     #   innodbinnodb_data_file_path                           =ibdata1:64M:autoextend     #   ibdata1:12M:autoextendinnodb_temp_data_file_path                      =ibtmp1:12M:autoextend      #   ibtmp1:12M:autoextendinnodb_buffer_pool_filename                     =ib_buffer_pool             #   ib_buffer_poolinnodb_log_group_home_dir                       =./                         #   ./innodb_log_files_in_group                       ={{ mysql_innodb_log_files_in_group }}                          #       2innodb_log_file_size                            ={{ mysql_innodb_log_file_size }}                        #      50331648(48M)innodb_file_per_table                           =on                         #   oninnodb_online_alter_log_max_size                =128M                  #   134217728(128M)innodb_open_files                               ={{mysql_innodb_open_files}}                       #   2000innodb_page_size                                =16k                        #   16384(16k)innodb_thread_concurrency                       =0                          #   0innodb_read_io_threads                          =4                          #   4innodb_write_io_threads                         =4                          #   4innodb_purge_threads                            =4                          #   4(garbage collection)innodb_page_cleaners                            =4                          #   4(flush lru list)innodb_print_all_deadlocks                      =on                         #   offinnodb_deadlock_detect                          =on                         #   oninnodb_lock_wait_timeout                        =50                         #   50innodb_spin_wait_delay                          =6                          #   6innodb_autoinc_lock_mode                        =2                          #   1innodb_io_capacity                              =200                        #   200innodb_io_capacity_max                          =2000                       #   2000#--------Persistent Optimizer Statisticsinnodb_stats_auto_recalc                        =on                         #   oninnodb_stats_persistent                         =on                         #   oninnodb_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                         #   oninnodb_change_buffering                         =all                        #   allinnodb_change_buffer_max_size                   =25                         #   25innodb_flush_neighbors                          =1                          #   1#innodb_flush_method                             =                           #innodb_doublewrite                              =on                         #   oninnodb_log_buffer_size                          ={{mysql_innodb_log_buffer_size}}                        #      16777216(16M)innodb_flush_log_at_timeout                     =1                          #   1innodb_flush_log_at_trx_commit                  =1                          #   1innodb_buffer_pool_size                         ={{ (ansible_memtotal_mb * 0.6 // 128 ) | int * 128 }}M                  #      134217728(128M)autocommit                                      =1                          #   1#--------innodb scan resistantinnodb_old_blocks_pct                           =37                         #    37innodb_old_blocks_time                          =1000                       #    1000#--------innodb read aheadinnodb_read_ahead_threshold                     =56                         #    56 (0..64)innodb_random_read_ahead                        =OFF                        #    OFF#--------innodb buffer pool stateinnodb_buffer_pool_dump_pct                     =25                         #    25 innodb_buffer_pool_dump_at_shutdown             =ON                         #    ONinnodb_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 ServerDocumentation=man:mysqld(8)Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network.targetAfter=syslog.target[Install]WantedBy=multi-user.target[Service]User=mysqlGroup=mysqlExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnfLimitNOFILE = 65536Environment=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=onwsrep_provider=/usr/local/mysql/lib/galera/libgalera_smm.sowsrep_cluster_name=g_mariadb#wsrep_cluster_address="gcomm://192.168.56.20,192.168.56.19,192.168.56.18"wsrep_slave_threads=5wsrep_sst_method=mariabackupwsrep_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.cnfsleep 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]#