ansible 构建 MariaDB Galera Clus
短信预约 -IT技能 免费直播动态提醒
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:
class="lazy" data-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:
class="lazy" data-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:
class="lazy" data-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:
class="lazy" data-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:
class="lazy" data-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:
class="lazy" data-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:
class="lazy" data-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:
class="lazy" data-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_class="lazy" data-src: yes
class="lazy" data-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:
class="lazy" data-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:
class="lazy" data-src: ../template/confmxc.cnf
dest: /tmp/confmxc.cnf
- name: transfer shell startmxc to remonte
template:
class="lazy" data-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]#
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341