Actual combat mariadb Galera cluster cluster architecture

MARIADB Galera cluster Installation:
Operating system: Centos7.4 version
Number of clusters: 3 nodes
Host information: 192.168.153.142 node1 selinux=disabled firewalld off
192.168.153.143 Node2 selinux=disabled Firewalld Close
192.168.153.144 node3 selinux=disabled Firewalld Close
Build steps
1. Mutual parsing between hosts: Three nodes are executed
Vim/etc/hosts
192.168.153.142 Node1
192.168.153.143 Node2
192.168.153.144 Node3
2. Install the Package
First method: (Yum install-y mariadb-server mariadb-client galera)
Configuring the Yum installation source and configuring the MARIADB Galera installation source
Yum Source Configuration Hang ISO
Set the Yum source for mariadb and install it (all nodes are needed)
Modifying the Yum source file
Vi/etc/yum.repos.d/mariadb.repo

[mariadb]name = MariaDBbaseurl = http://yum.mariadb.org/10.3.5/centos74-amd64gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDBgpgcheck=1enabled=0安装galera软件时需要解决它的依赖包：boost-program-options.x86_64 （直接yum源安装即可）第二种方法：（rpm包安装）三个节点都需要安装从网上下载rpm包：  galera-25.3.23-1.rhel7.el7.centos.x86_64.rpm                    MariaDB-10.3.5-centos74-x86_64-client.rpm                    MariaDB-10.3.5-centos74-x86_64-compat.rpm                    MariaDB-10.3.5-centos74-x86_64-common.rpm                    MariaDB-10.3.5-centos74-x86_64-server.rpmrpm -ivh MariaDB-10.3.5-centos74-x86_64-compat.rpm --nodepsrpm -ivh MariaDB-10.3.5-centos74-x86_64-common.rpm rpm -ivh MariaDB-10.3.5-centos74-x86_64-client.rpmyum install -y boost-program-options.x86_64 (解决安装galera的依赖包)rpm -ivh galera-25.3.23-1.rhel7.el7.centos.x86_64.rpmrpm -ivh MariaDB-10.3.5-centos74-x86_64-server.rpm

3.MARIADB initialization (all three nodes need to be executed)
When the installation is complete, you will be prompted to initialize the MARIADB (set password)
Systemctl Start mariadb
Mysql_secure_installation (set MySQL password as prompted)
Systemctl Stop MARIADB
4. Configure Galera
Master node configuration file server.cnf
Vim/etc/my.cnf.d/server.cnf
[Galera]
Wsrep_on=on
Wsrep_provider=/usr/lib64/galera/libgalera_smm.so
Wsrep_cluster_address= "gcomm://192.168.153.142,192.168.153.143,192.168.153.144"
Wsrep_node_name= Node1
wsrep_node_address=192.168.153.142
Binlog_format=row
Default_storage_engine=innodb
innodb_autoinc_lock_mode=2
Wsrep_slave_threads=1
Innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=120m
Wsrep_sst_method=rsync
Wsrep_causal_reads=on
Copy this file to Mariadb-2, mariadb-3, and notice that you want to change wsrep_node_name and wsrep_node_address to the hostname and IP of the corresponding node.
5. Start the Cluster service:
Start MariaDB galera Cluster service:
[Email protected] ~]#/bin/galera_new_cluster
The remaining two nodes start with the following methods:
[Email protected] ~]# systemctl start mariadb
View cluster Status: (Cluster service uses 4567 and 3306 ports))
[Email protected] ~]# NETSTAT-TULPN | GREP-E 4567-e 3306
TCP 0 0 0.0.0.0:4567 0.0.0.0:LISTEN 3557/mysqld
TCP6 0 0::: 3306:: :LISTEN 3557/mysqld
6. Verify the cluster Status:
Execute on Node1:
[Email protected] ~]# Mysql-uroot-p # #进入数据库
To see if the Galera plugin is enabled
Connect mariadb to see if the Galera plugin is enabled
MariaDB [(None)]> show status Like "Wsrep_ready";
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| Wsrep_ready | On |
+---------------+-------+
1 row in Set (0.004 sec)
Current number of cluster machines
MariaDB [(None)]> show status Like "Wsrep_cluster_size";
+--------------------+-------+
| variable_name | Value |
+--------------------+-------+
| Wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in Set (0.001 sec)
View cluster status
MariaDB [(None)]> show status Like "wsrep%";
+------------------------------+----------------------------------------------------------------+
| variable_name | Value |
+------------------------------+----------------------------------------------------------------+
| Wsrep_apply_oooe | 0.000000 |
| Wsrep_apply_oool | 0.000000 |
| Wsrep_apply_window | 1.000000 |
| Wsrep_causal_reads | 14 |
| Wsrep_cert_deps_distance | 1.200000 |
| Wsrep_cert_index_size | 3 |
| Wsrep_cert_interval | 0.000000 |
| wsrep_cluster_conf_id | 22 |
| Wsrep_cluster_size | 3 | # #集群成员
| Wsrep_cluster_state_uuid | B8ECF355-233A-11E8-825E-BB38179B0EB4 | # #UUID Cluster unique tag
| Wsrep_cluster_status | Primary | # #主服务器
| Wsrep_commit_oooe | 0.000000 |
| Wsrep_commit_oool | 0.000000 |
| Wsrep_commit_window | 1.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_paused | 0.000000 |
| Wsrep_flow_control_paused_ns | 0 |
| Wsrep_flow_control_recv | 0 |
| wsrep_flow_control_sent | 0 |
| Wsrep_gcomm_uuid | 0eba3aff-2341-11e8-b45a-f277db2349d5 |
| wsrep_incoming_addresses | 192.168.153.142:3306,192.168.153.143:3306,192.168.153.144:3306 | # #连接中的数据库
| wsrep_last_committed | 9 | # #sql Commit a record
| Wsrep_local_bf_aborts | 0 | # #从执行事务过程被本地中断
| Wsrep_local_cached_downto | 5 |
| Wsrep_local_cert_failures | 0 | # #本地失败事务
| Wsrep_local_commits | 4 | # #本地执行的sql
| Wsrep_local_index | 0 |
| Wsrep_local_recv_queue | 0 |
| Wsrep_local_recv_queue_avg | 0.057143 |
| 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 | B8ECF355-233A-11E8-825E-BB38179B0EB4 | # #集群ID
| wsrep_protocol_version | 8 |
| Wsrep_provider_name | Galera |
| Wsrep_provider_vendor | Codership Oy <[email protected]> |
| wsrep_provider_version | 25.3.23 (r3789) |
| Wsrep_ready | On | # #插件是否应用中
| wsrep_received | 35 | # #数据复制接收次数
| Wsrep_received_bytes | 5050 |
| Wsrep_repl_data_bytes | 1022 |
| Wsrep_repl_keys | 14 |
| Wsrep_repl_keys_bytes | 232 |
| Wsrep_repl_other_bytes | 0 |
| wsrep_replicated | 5 | # #随着复制发出的次数
| Wsrep_replicated_bytes | 1600 | # #数据复制发出的字节数
| Wsrep_thread_count | 2 |
+------------------------------+----------------------------------------------------------------+
(0.003 sec)
To view connected hosts
MariaDB [(None)]> show status Like "Wsrep_incoming_addresses";
+--------------------------+----------------------------------------------------------------+
| variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| wsrep_incoming_addresses | 192.168.153.142:3306,192.168.153.143:3306,192.168.153.144:3306 |
+--------------------------+----------------------------------------------------------------+
1 row in Set (0.002 sec)
7. Test whether the cluster Mariad data is synchronized
MariaDB [(None)]> CREATE Database Lizk;
Query OK, 1 row affected (0.010 sec)

MariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| china              || hello              || hi                 || information_schema || lizk               || mysql              || performance_schema || test               |+--------------------+8 rows in set (0.001 sec)在其他两个节点上可以查看lizk库已经同步。

8. Simulation of the treatment after cerebral fissure
In the following simulation, in the case of network jitter loss, two nodes were lost and caused the brain fissure. On the 192.168.153.143 and 192.168.153.144 two nodes, perform each of the following:
Iptables-a input-p TCP--sport 4567-j DROP
Iptables-a input-p TCP--dport 4567-j DROP
The above command is used to prohibit wsrep full synchronous replication of 4567 port traffic
On the 192.168.153.142 node View:
MariaDB [(None)]> show status Like "ws%";
+------------------------------+--------------------------------------------+
| variable_name | Value |
+------------------------------+--------------------------------------------+
| Wsrep_apply_oooe | 0.000000 |
| Wsrep_apply_oool | 0.000000 |
| Wsrep_apply_window | 1.000000 |
| Wsrep_causal_reads | 16 |
| Wsrep_cert_deps_distance | 1.125000 |
| Wsrep_cert_index_size | 3 |
| Wsrep_cert_interval | 0.000000 |
| wsrep_cluster_conf_id | 18446744073709551615 |
| Wsrep_cluster_size | 1 |
| Wsrep_cluster_state_uuid | B8ECF355-233A-11E8-825E-BB38179B0EB4 |
| Wsrep_cluster_status | Non-primary |
There is now a brain fissure and the cluster is unable to execute any commands.
To solve this problem, you can perform
Set global wsrep_provider_options= "Pc.bootstrap=true";
Use this command to force the recovery of a brain-splitting node.
Verify:
MariaDB [(None)]> set global wsrep_provider_options= "Pc.bootstrap=true";
Query OK, 0 rows affected (0.015 sec)

MariaDB [(none)]> select @@wsrep_node_name;+-------------------+| @@wsrep_node_name |+-------------------+| node1             |+-------------------+1 row in set (0.478 sec)最后我们将节点192.168.153.143和192.168.153.144恢复一下，只要清理一下iptables表即可(因为我的是测试环境，生产环境需要删除上面的规则即可)：[[email protected] mysql]# iptables -F恢复后验证一下：MariaDB [(none)]> show status like "wsrep_cluster_size";+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| wsrep_cluster_size | 3     |+--------------------+-------+1 row in set (0.001 sec)

9. If the fault requires two nodes of the cluster to check for downtime, whether the data can be synchronized after restarting the service,
to stop mariadb for 192.168.153.143 and 192.168.153.144:
[[email  Protected] mysql]# systemctl stop mariadb
Insert data on 192.168.153.142 nodes:
mariadb [test]> select * from Test1;
+------+
| id |
+------+
| 2 |
| 2 |
| 1 |
| 3 |
+------+
4 rows in Set (0.007 sec)
Now restarts the other two nodes in the cluster to see data consistency, as is the data for the master node.
10. Exception handling: When the engine room suddenly power outage, all Galera host is not normal shutdown, the call after the boot, will cause the Galera Cluster service can not start normally. How to deal with?
1th Step: Turn on the mariadb service of the Galera cluster host master.
Step 2nd: Turn on the MARIADB service for the member hosts of the Galera cluster.
Exception handling: The MySQL service for Galera cluster host and member hosts does not start, how do I handle it?
Workaround One: Step 1th, delete the/var/lib/mysql/grastate.dat state file for the Garlera host Master
/bin/galera_new_cluster start the service. Start normally. Log in and view the Wsrep status.
Step 2nd: Remove the/var/lib/mysql/grastate.dat state file from the Galera member host
Systemctl Restart mariadb Restart the service. Start normally. Log in and view the Wsrep status.
Workaround Two: Step 1th, modify the/var/lib/mysql/grastate.dat state file in the Garlera group master 0 to 1
/bin/galera_new_cluster start the service. Start normally. Log in and view the Wsrep status.
Step 2nd: Modify the/var/lib/mysql/grastate.dat 0 in the Galera member host to 1
systemctl Restart mariadb Restart the service. Start normally. Log in and view the Wsrep status.

Actual combat mariadb Galera cluster cluster architecture