MariaDB Galera Cluster (mariadb10.1.22) deployment

First, Galera cluster Introduction 1.1 Galera Cluster Introduction

Galera cluster is a dual-live multi-master cluster of MARIADB, which allows all nodes of MARIDB to remain in sync, Galera provides synchronous replication (relative to native asynchronous replication), so it can guarantee ha, and it currently only supports MARIADB InnoDB Storage Engine (Extended support MyISAM), and can only be used under Linux. Starting with Mariadb 10.1, the Wsrep API is already included by default in Galera cluster. MARIADB 10.0 and MARIADB 5.5 are still independent, so the installation deployment may be different, see MARIADB official introduction.

Features of the 1.2 Galera cluster:

A true multi-master architecture, where any node can read and write.
Synchronous replication, no latency between nodes and no data loss due to node downtime.
Tightly coupled, all nodes remain in the same state, and there are no different data between nodes.
No master or slave switching operation or VIP usage.
Hot standby, no downtime during the failover process (due to the absence of failover).
Automatic node configuration eliminates the need to manually back up the current database and copy to the new node.
Supports the InnoDB storage engine.
Corresponds to transparency, without changing the app or making minimal changes.
No read-write separation is required.

Advantages of 1.3 Galera cluster:

Because it is multi-master, there is no slavelag (delay).
There is no case of loss of transactions.
It also has the ability to expand both read and write.
A smaller client latency.
Data between nodes is synchronous, while the Master/slave pattern is asynchronous, and binlog on different slave may be different.

Galera Cluster Limitations:

The current replication only supports the InnoDB storage engine, and any tables written to other engines, including the mysql.* table, will not be copied, but the DDL statements will be copied, so the created user will be copied, but insert into mysql.user ... will not be copied.
The delete operation does not support tables that do not have a primary key, and tables that do not have a primary key will be different in different node order if a SELECT ... LIMIT ... A different result set appears.
In a multi-master environment Lock/unlock tables is not supported, as well as the lock function Get_lock (), Release_lock () ....
The query log cannot be saved in the table, and can only be saved to a file if the query log is turned on.
The maximum transaction size allowed is defined by wsrep_max_ws_rows and wsrep_max_ws_size, and any large operation will be rejected, such as a large load data operation.
Because the cluster is optimistic concurrency control, a transaction commit may be aborted at that stage. If there are two transactions written to and committed to the same row in a different node in the cluster, the failed node is aborted. For cluster-level aborts, the cluster returns a deadlock error code (error:1213 sqlstate:40001 (Er_lock_deadlock)).
XA transactions are not supported due to possible rollback on commit.
The write throughput of the entire cluster is limited by the weakest node, and if one node becomes slow, the entire cluster will be slow. For stable high-performance requirements, all nodes should use unified hardware.
Cluster nodes recommend a minimum of 3.
If there is a problem with the DDL statement, the cluster will be destroyed.

Second, galera cluster configuration deployment

Description: 3 Machines 192.168.10.43/192.168.10.44/192.168.10.45

2.1 Install MARIADB (43/44/45) 2.2 Create cluster Sync account 2.3 Turn off the firewall (43/44/45)

[root@db04 ~]# systemctl stop firewalld.service[root@db04 ~]# systemctl disable firewalld.service

2.4 Creating the Cluster profile WSREP.CNF

/usr/local/mariadb/support-files/wsrep.cnf /etc/my.cnf.d/在/etc/my.cnf里面添加一行!includedir /etc/my.cnf.d/

Edit Wsrep File

[Mysqld]Binlog_format=rowDefault-storage-engine=innodbInnodb_autoinc_lock_mode=2bind-address=192.168.10.43# Galera Provider Configurationwsrep_on=OnWsrep_provider=/usr/local/mariadb/lib/galera/libgalera_smm.sowsrep_provider_options="Gcache.name =/data/galera/galera.cache; gcache.size=1g; gcache.page_size=1g; Gcache.recover = yes "# galera Cluster Configurationwsrep_cluster_name="Test_cluster"wsrep_cluster_address= "gcomm:/ /192.168.10.43,192.168.10.44,192.168.10.45 "#wsrep_cluster_address =" gcomm://"Wsrep_sst_auth=galera:  000000# Galera synchronization configurationwsrep_sst_method=xtrabackup-v2 # galera Node Configuration wsrep_node_address= "192.168.10.43"wsrep_node_name="Node1"wsrep_slave_threads=8 

2.5 Synchronize wsrep.cnf to 44 and 452.6 close all databases

# service mysqld stop

2.7 Starting the first node 43

# mysqld --wsrep-new-cluster# service mysqld start --wsrep_new_cluster  或者# service mysqld start --galera_new_cluster

2.8 Starting a 2nd/3 node

Galera cluster Frequently Asked questions 1

Build Galera cluster When set SST for Xtrabackup, start node1 error.
[ERROR] Wsrep:it May is safe to bootstrap the cluster from this node. It was wasn't the last one to leave the cluster and may not be contain all the updates. To force cluster bootstrap with this node, edit the Grastate.dat file manually and set Safe_to_bootstrap to 1.
How to resolve:
Grastate.dat file of the node intend to use as the first node.
You need to remove the file and restart it.

Question 2

Workaround:
1. Install the plugin yum-y install Socat
2. Check whether the user Galera can log on in the machine

Question 3

The node is out of the cluster because multiple nodes simultaneously send updates to the same record causing the cluster nodes to fall off, if this happens then whether to read and write separation, and finally wsrep_slave_tereads the number of threads based on personal conduct core decision.

MariaDB Galera Cluster (mariadb10.1.22) deployment