Ansible部署MariaDB galera集群(多主)
文章目录
- Ansible部署MariaDB galera集群(多主)
- 介绍
- 节点规划
- 基础环境准备
- 编写剧本文件
- 执行剧本文件
- 查看集群状态
- 测试
Ansible部署MariaDB galera集群(多主)
介绍
MariaDB Galera集群是一套基于同步复制的、多主的MySQL集群解决方案，使用节点没有单点故障，可用性高，读写性能高，可扩展性好。
主要特点：
-
同步复制，主备无延迟
-
多主架构允许多个节点成为集群中的主节点，并且所有主节点都可以处理写入请求，这意味着你可以在任何节点上写入数据，而不仅仅是在单个节点上。
-
无单点故障如果一个节点失败，其他节点仍然可以继续工作，并且当故障节点恢复时，它会自动重新加入集群
节点规划
| IP | 主机名 | 节点 |
|---|---|---|
| 192.168.200.10 | ansible | Ansible节点 |
| 192.168.200.20 | node1 | Node1节点 |
| 192.168.200.30 | node2 | Node2节点 |
| 192.168.200.40 | node3 | Node3节点 |
基础环境准备
(1)修改主机名
[root@localhost ~]# hostnamectl set-hostname ansible
[root@localhost ~]# hostnamectl set-hostname node1
[root@localhost ~]# hostnamectl set-hostname node2
[root@localhost ~]# hostnamectl set-hostname node3
(2)安装ansible
[root@ansible ~]# yum install -y epel-release
[root@ansible ~]# yum install -y ansible
(3)配置Ansible节点和远程主机的连接
[root@ansible ~]# ssh-keygen
[root@node1 ~]# ssh-keygen
[root@node2 ~]# ssh-keygen
[root@node3 ~]# ssh-keygen
(4)配置主机组
[root@ansible ~]# vim /etc/ansible/hosts
[node1]
192.168.200.20
[node2]
192.168.200.30
[node3]
192.168.200.40
(5)配置Yum文件
Mariadb10.3（10.3自带galera软件）
[root@ansible ~]# vim mariadb.repo
[mariadb]
name=MariaDB
baseurl=http://mirrors.ustc.edu.cn/mariadb/yum/10.3/centos7-amd64/
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
(6)配置server-node(1~3).conf文件，用于复制到远程节点
[root@ansible ~]# vim server-node1.cnf
[server]
[mysqld]
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.200.20,192.168.200.30,192.168.200.40"
binlog_format=row
default_storage_engine=InnoDB
wsrep_node_name=node1
[embedded]
[mariadb]
[mariadb-10.3]
[root@node1 ~]# cp server-node1.cnf server-node2.cnf
[root@node1 ~]# cp server-node1.cnf server-node3.cnf
[root@node1 ~]# sed -i 's/wsrep_node_name=node1/wsrep_node_name=node2/g' server-node2.cnf
[root@node1 ~]# sed -i 's/wsrep_node_name=node1/wsrep_node_name=node3/g' server-node3.cnf
配置文件server.conf参数详解
wsrep_on=ON # 是否启用插件
wsrep_provider=/usr/lib64/galera/libgalera_smm.so # 指定galera的库文件的地址
wsrep_cluster_address="gcomm://192.168.200.20,192.168.200.30,192.168.200.40" # 集群IP
binlog_format=row # 二进制日志的格式为row
default_storage_engine=InnoDB # 默认的存储引擎为InnoDB
wsrep_node_name=node3 # 指定了当前节点名称
(7)测试主机连通性
[root@ansible ~]# ansible all -m ping
192.168.200.20 | SUCCESS => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": false, "ping": "pong"
}
192.168.200.30 | SUCCESS => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": false, "ping": "pong"
}
192.168.200.40 | SUCCESS => {"ansible_facts": {"discovered_interpreter_python": "/usr/bin/python"}, "changed": false, "ping": "pong"
}
编写剧本文件
[root@ansible ~]# vim install_galera_cluster.yaml
- hosts: allremote_user: roottasks:- name: stop firewalld setenforce 0shell: systemctl stop firewalld && setenforce 0- name: copy hostscopy: src=/etc/hosts dest=/etc/hosts- name: copy repocopy: src=mariadb.repo dest=/etc/yum.repos.d/- name: install mariadbyum: name=mariadb-server state=installed- name: start mariadbservice: name=mariadb state=started enabled=yes- name: init_mysqlshell: mysqladmin -uroot password 000000- name: stop mariadbservice: name=mariadb state=stopped- hosts: node1remote_user: roottasks:- name: copy server-node1.cnfcopy: src=server-node1.cnf dest=/etc/my.cnf.d/server.cnf- name: chushihuashell: galera_new_cluster- hosts: node2remote_user: roottasks:- name: copy server-node2.cnfcopy: src=server-node2.cnf dest=/etc/my.cnf.d/server.cnf- hosts: node3remote_user: roottasks:- name: copy server-node3.cnfcopy: src=server-node3.cnf dest=/etc/my.cnf.d/server.cnf- hosts: node2,node3remote_user: roottasks:- name: start mariadbshell: systemctl start mariadb
执行剧本文件
[root@ansible ~]# ansible-playbook install_galera_cluster.yaml PLAY [all] ***********************************************************************************************************************************************TASK [Gathering Facts] ***********************************************************************************************************************************
ok: [192.168.200.20]
ok: [192.168.200.30]
ok: [192.168.200.40]TASK [stop firewalld setenforce 0] ***********************************************************************************************************************
changed: [192.168.200.20]
changed: [192.168.200.30]
changed: [192.168.200.40]TASK [copy hosts] ****************************************************************************************************************************************
ok: [192.168.200.40]
ok: [192.168.200.30]
ok: [192.168.200.20]TASK [copy repo] *****************************************************************************************************************************************
changed: [192.168.200.20]
changed: [192.168.200.30]
changed: [192.168.200.40]TASK [install mariadb] ***********************************************************************************************************************************
changed: [192.168.200.20]
changed: [192.168.200.30]
changed: [192.168.200.40]TASK [start mariadb] *************************************************************************************************************************************
changed: [192.168.200.40]
changed: [192.168.200.20]
changed: [192.168.200.30]TASK [init_mysql] ****************************************************************************************************************************************
changed: [192.168.200.20]
changed: [192.168.200.40]
changed: [192.168.200.30]TASK [stop mariadb] **************************************************************************************************************************************
changed: [192.168.200.40]
changed: [192.168.200.20]
changed: [192.168.200.30]PLAY [node1] *********************************************************************************************************************************************TASK [Gathering Facts] ***********************************************************************************************************************************
ok: [192.168.200.20]TASK [copy server-node1.cnf] *****************************************************************************************************************************
changed: [192.168.200.20]TASK [chushihua] *****************************************************************************************************************************************
changed: [192.168.200.20]PLAY [node2] *********************************************************************************************************************************************TASK [Gathering Facts] ***********************************************************************************************************************************
ok: [192.168.200.30]TASK [copy server-node2.cnf] *****************************************************************************************************************************
changed: [192.168.200.30]PLAY [node3] *********************************************************************************************************************************************TASK [Gathering Facts] ***********************************************************************************************************************************
ok: [192.168.200.40]TASK [copy server-node3.cnf] *****************************************************************************************************************************
changed: [192.168.200.40]PLAY [node2,node3] ***************************************************************************************************************************************TASK [Gathering Facts] ***********************************************************************************************************************************
ok: [192.168.200.30]
ok: [192.168.200.40]TASK [start mariadb] *************************************************************************************************************************************
changed: [192.168.200.40]
changed: [192.168.200.30]PLAY RECAP ***********************************************************************************************************************************************
192.168.200.20 : ok=11 changed=8 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
192.168.200.30 : ok=12 changed=8 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
192.168.200.40 : ok=12 changed=8 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
查看集群状态
(node1，node2，node3都可以查看)
[root@node1 ~]# mysql -uroot -p000000MariaDB [(none)]> show status like '%wsrep%';
+-------------------------------+-------------------------------------------------------------+
| Variable_name | Value |
+-------------------------------+-------------------------------------------------------------+
| wsrep_applier_thread_count | 1 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_waits | 0 |
| wsrep_apply_window | 0.000000 |
| wsrep_causal_reads | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_cert_index_size | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_cluster_conf_id | 2 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 08f31069-2ec0-11ee-b090-fff9379da121 |
| wsrep_cluster_status | Primary |
| wsrep_cluster_weight | 3 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_connected | ON |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_flow_control_active | false |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_requested | false |
| wsrep_flow_control_sent | 0 |
| wsrep_gcomm_uuid | 08f1dd06-2ec0-11ee-829f-06675c76d425 |
| wsrep_gmcast_segment | 0 |
| wsrep_incoming_addresses | 192.168.200.20:3306,192.168.200.40:3306,192.168.200.30:3306 |
| wsrep_last_committed | 0 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_cached_downto | 18446744073709551615 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_commits | 0 |
| wsrep_local_index | 0 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.111111 |
| wsrep_local_recv_queue_max | 2 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_local_state_uuid | 08f31069-2ec0-11ee-b090-fff9379da121 |
| wsrep_open_connections | 0 |
| wsrep_open_transactions | 0 |
| wsrep_protocol_version | 9 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 25.3.37(rd0a7bd7) |
| wsrep_ready | ON |
| wsrep_received | 9 |
| wsrep_received_bytes | 552 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_rollbacker_thread_count | 1 |
| wsrep_thread_count | 2 |
+-------------------------------+-------------------------------------------------------------+-
wsrep_cluster_size 3 ：集群成员有3个
-
wsrep_cluster_status Primary： 主服务器
-
wsrep_connected ON：是否处于连接中
-
wsrep_incoming_addresses 192.168.200.20:3306,192.168.200.40:3306,192.168.200.30:3306 ：连接中的主机
-
wsrep_ready ON ：插件是否应用中
测试
使用node1写入数据
[root@node1 ~]# mysql -uroot -p000000
MariaDB [(none)]> create database node1;MariaDB [(none)]> use node1;MariaDB [node1]> CREATE TABLE employees (-> id INT PRIMARY KEY AUTO_INCREMENT,-> name VARCHAR(50),-> age INT,-> department VARCHAR(50)-> );MariaDB [node1]> INSERT INTO employees (name, age, department) VALUES-> ('John Doe', 30, 'Sales'),-> ('Jane Smith', 25, 'Marketing'),-> ('David Johnson', 35, 'HR');node2查看是否同步
[root@node2 ~]# mysql -uroot -p000000
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| node1 |
| performance_schema |
| test |
+--------------------+
MariaDB [node1]> select * from employees;
+----+---------------+------+------------+
| id | name | age | department |
+----+---------------+------+------------+
| 1 | John Doe | 30 | Sales |
| 4 | Jane Smith | 25 | Marketing |
| 7 | David Johnson | 35 | HR |
+----+---------------+------+------------+
如果此时node3宕机了
[root@node3 ~]# systemctl stop mariadb
查看集群信息
MariaDB [node1]> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 2 |
+--------------------+-------+
# 立马剔除宕机的主机
如果给他恢复？数据是否还在
MariaDB [node1]> select * from employees;
+----+---------------+------+------------+
| id | name | age | department |
+----+---------------+------+------------+
| 1 | John Doe | 30 | Sales |
| 4 | Jane Smith | 25 | Marketing |
| 7 | David Johnson | 35 | HR |
+----+---------------+------+------------+
# 数据还在