ansible 構建 MariaDB Galera Clus

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]#