Ansible to build MariaDB Galera Clus

1. Environment. This article only introduces the configuration of mxc. Other configurations (all configurations) I will download and connect to https://down.51cto.com/data/2467920.

192.168.56.21 ansible192.168.56.20 es1192.168.56.19 es2192.168.56.18 es3

2. Directory overview, including mxc, master-slave and single-node installation

[root@mgr ~] # tree mariadbmariadb ├── common │ ├── config_master_slaves.yaml │ ├── config_mxc.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 15s 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-# directory where the mysql installation package is located mariadb_packages_dir: / root/mariadb/softwares/mariadb_package_name: mariadb-10.2.24-linux-x86_64.tar.gz#linux system-level mysql user related information mysql_user: mysqlmysql_group: mysqlmysql_user_uid: 3306mysql_user_gid: 3306#mysql installation directory mysql_base_dir: / usr/local/mysql/#mysql true The positive datadir will be 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 configuration file template 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: 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---# transfers the mysql installation package locally to the target host, And extract the installation package to the / usr/local/ directory # equivalent 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# set permissions # equivalent 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# create connection file # equivalent 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# exports the library file of mysql-name: export mysql share object (* .os) template: src:.. / template/mysql.conf dest: / etc/ld.so.conf.d/mysql.conf# load shared library-name: load share object shell: ldconfig# export PATH environment variable-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# Export header file-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] #