快速搭建网站信息库,专门用于网页制作的软件,贵阳专业的翻译公司,网站项目接单文章目录 Ansible部署MariaDB galera集群(多主)介绍节点规划基础环境准备编写剧本文件执行剧本文件查看集群状态测试 Ansible部署MariaDB galera集群(多主)
介绍
MariaDB Galera集群是一套基于同步复制的、多主的MySQL集群解决方案#xff0c;使用节点没有单点故障#xff… 文章目录 Ansible部署MariaDB galera集群(多主)介绍节点规划基础环境准备编写剧本文件执行剧本文件查看集群状态测试 Ansible部署MariaDB galera集群(多主)
介绍
MariaDB Galera集群是一套基于同步复制的、多主的MySQL集群解决方案使用节点没有单点故障可用性高读写性能高可扩展性好。
主要特点 同步复制主备无延迟 多主架构允许多个节点成为集群中的主节点并且所有主节点都可以处理写入请求这意味着你可以在任何节点上写入数据而不仅仅是在单个节点上。 无单点故障如果一个节点失败其他节点仍然可以继续工作并且当故障节点恢复时它会自动重新加入集群
节点规划
IP主机名节点192.168.200.10ansibleAnsible节点192.168.200.20node1Node1节点192.168.200.30node2Node2节点192.168.200.40node3Node3节点
基础环境准备
(1)修改主机名
[rootlocalhost ~]# hostnamectl set-hostname ansible
[rootlocalhost ~]# hostnamectl set-hostname node1
[rootlocalhost ~]# hostnamectl set-hostname node2
[rootlocalhost ~]# hostnamectl set-hostname node3(2)安装ansible
[rootansible ~]# yum install -y epel-release
[rootansible ~]# yum install -y ansible(3)配置Ansible节点和远程主机的连接
[rootansible ~]# ssh-keygen
[rootnode1 ~]# ssh-keygen
[rootnode2 ~]# ssh-keygen
[rootnode3 ~]# ssh-keygen(4)配置主机组
[rootansible ~]# vim /etc/ansible/hosts
[node1]
192.168.200.20
[node2]
192.168.200.30
[node3]
192.168.200.40(5)配置Yum文件
Mariadb10.310.3自带galera软件
[rootansible ~]# vim mariadb.repo
[mariadb]
nameMariaDB
baseurlhttp://mirrors.ustc.edu.cn/mariadb/yum/10.3/centos7-amd64/
gpgkeyhttp://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck1(6)配置server-node(1~3).conf文件用于复制到远程节点
[rootansible ~]# vim server-node1.cnf
[server]
[mysqld]
[galera]
wsrep_onON
wsrep_provider/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_addressgcomm://192.168.200.20,192.168.200.30,192.168.200.40
binlog_formatrow
default_storage_engineInnoDB
wsrep_node_namenode1
[embedded]
[mariadb]
[mariadb-10.3]
[rootnode1 ~]# cp server-node1.cnf server-node2.cnf
[rootnode1 ~]# cp server-node1.cnf server-node3.cnf
[rootnode1 ~]# sed -i s/wsrep_node_namenode1/wsrep_node_namenode2/g server-node2.cnf
[rootnode1 ~]# sed -i s/wsrep_node_namenode1/wsrep_node_namenode3/g server-node3.cnf 配置文件server.conf参数详解
wsrep_onON # 是否启用插件
wsrep_provider/usr/lib64/galera/libgalera_smm.so # 指定galera的库文件的地址
wsrep_cluster_addressgcomm://192.168.200.20,192.168.200.30,192.168.200.40 # 集群IP
binlog_formatrow # 二进制日志的格式为row
default_storage_engineInnoDB # 默认的存储引擎为InnoDB
wsrep_node_namenode3 # 指定了当前节点名称(7)测试主机连通性
[rootansible ~]# 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
}编写剧本文件
[rootansible ~]# 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: srcmariadb.repo dest/etc/yum.repos.d/- name: install mariadbyum: namemariadb-server stateinstalled- name: start mariadbservice: namemariadb statestarted enabledyes- name: init_mysqlshell: mysqladmin -uroot password 000000- name: stop mariadbservice: namemariadb statestopped- hosts: node1remote_user: roottasks:- name: copy server-node1.cnfcopy: srcserver-node1.cnf dest/etc/my.cnf.d/server.cnf- name: chushihuashell: galera_new_cluster- hosts: node2remote_user: roottasks:- name: copy server-node2.cnfcopy: srcserver-node2.cnf dest/etc/my.cnf.d/server.cnf- hosts: node3remote_user: roottasks:- name: copy server-node3.cnfcopy: srcserver-node3.cnf dest/etc/my.cnf.d/server.cnf- hosts: node2,node3remote_user: roottasks:- name: start mariadbshell: systemctl start mariadb执行剧本文件
[rootansible ~]# 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 : ok11 changed8 unreachable0 failed0 skipped0 rescued0 ignored0
192.168.200.30 : ok12 changed8 unreachable0 failed0 skipped0 rescued0 ignored0
192.168.200.40 : ok12 changed8 unreachable0 failed0 skipped0 rescued0 ignored0 查看集群状态
(node1node2node3都可以查看)
[rootnode1 ~]# 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 infocodership.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写入数据
[rootnode1 ~]# 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查看是否同步
[rootnode2 ~]# 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宕机了
[rootnode3 ~]# 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 |
-------------------------------------
# 数据还在
