MariaDB Galera Cluster Deployment practices

Official documents:

http://galeracluster.com/documentation-webpages/index.html

First, the working principle of Galera cluster

The main focus is data consistency. Transactions can be applied either to each node or not to all. So, as long as they are configured correctly, the database remains in sync.

Unlike traditional MySQL replication, galera replication plug-ins can solve multiple problems, including multiple master write conflicts, replication lag, and master never synchronizing.

In a typical Galera cluster instance, an application can write to any node in the cluster and then apply the transaction commit (RBR event) to all servers through authentication-based replication.

Authentication-based replication is another way of synchronizing database replication using group communication and transaction sequencing techniques

Second, Galera cluster installation

Description: The Galera cluster requires a minimum of three nodes of server hardware.

node-12:10.71.11.12node-13:10.71.11.13node-14:10.71.11.14

Operating system:

centos7

Kernel version:

 3.10.0-693.21.1.el7.x86_64

Start installation

Description: Perform the following steps for each node in the cluster. Document with NODE-12 configuration as an example
1. Edit the/etc/hosts file and configure the nodes to parse each other

[[email protected] ~]# cat /etc/hosts127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4::1         localhost localhost.localdomain localhost6 localhost6.localdomain610.71.11.12  node-1210.71.11.13  node-1310.71.11.14  node-14

2. Turn off the node firewall

[[email protected] ~]# cat /etc/sysconfig/selinux |grep di#     disabled - No SELinux policy is loaded.SELINUX=disabled#     minimum - Modification of targeted policy. Only selected processes are protected.

3. Configuring the Galera cluster yum source

[[email protected] ~]# cat /etc/yum.repos.d/galera.repo [galera]name = Galerabaseurl = http://releases.galeracluster.com/galera-3/centos/7/x86_64gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.comgpgcheck = 1[mysql-wsrep]name = MySQL-wsrepbaseurl =  http://releases.galeracluster.com/mysql-wsrep-5.6/centos/7/x86_64gpgkey = http://releases.galeracluster.com/GPG-KEY-galeracluster.comgpgcheck = 1

4. Updating the Yum source cache

yum makecache

5. Installation Services

yum install mysql-wsrep-shared-5.6

Description: If MySQL was previously installed on the node, executing the above command may install a package conflict error
6. Take node-12 as an example, edit/etc/my.cnf and add the following configuration

[[email protected] ~]# cat /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlbinlog_format=rowbind-address=10.71.11.12default_storage_engine=innodbinnodb_autoinc_lock_mode=2innodb_flush_log_at_trx_commit=0innodb_buffer_pool_size=122Mwsrep_provider=/usr/lib64/galera-3/libgalera_smm.sowsrep_provider_options="gcache.size=300M; gcache.page_size=300M"wsrep_sst_method=rsyncwsrep_cluster_name=MyClusterwsrep_cluster_address="gcomm://10.71.11.12,10.71.11.13,10.71.11.14"wsrep_node_name=node-12wsrep_node_address="10.71.11.12"[mysql_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

Description: Other node-13 and node-14 according to 1-6-step configuration, need to modify/etc/my.cnf corresponding to each node

System memory Configuration Instructions

The memory requirements of Galera cluster are difficult to predict accurately. The specific amount of memory it uses can vary greatly depending on the load received by a given node. If Galera cluster tries to use more memory than the node, the Mysqld instance crashes.
The way to protect a node system from crashing is to ensure that the service has enough swap space to partition or swap files

Check system available swap space

If the system swap partition does not meet the requirements of the environment, you can configure the swap partition as follows:
Description: Each node in the cluster is set to the relevant swap partition

1. Create a 2G size swap empty file in the root directory as required

[[email protected] ~]#fallocate -l 2048M /swapfile

or perform

[[email protected] ~]#dd if=/dev/zero of=/swapfile bs=1M count=2048

Set swap swap partition size
2. Set swap file permissions

[[email protected] ~]#chown 600 /swapfilell /swapfile -rw-r--r-- 1 600 root 2147483648 Apr 17 09:13 /swapfile

3. Formatting Swapfile

[[email protected] ~]# mkswap /swapfileSetting up swapspace version 1, size = 2097148 KiBno label, UUID=6341a320-1804-4fe1-8c96-7c22fe270eeb

4. Activating Swapfile

[[email protected] ~]# swapon /swapfileswapon: /swapfile: insecure permissions 0644, 0600 suggested.swapon: /swapfile: insecure file owner 600, 0 (root) suggested.

5. Edit/etc/fstab, add the following configuration at the bottom of the file

   /swapfile none swap defaults 0 0

   
   6. Verify that the Swap partition profile is successful

   [[email protected] ~]# swapon --summaryFilename                Type        Size    Used    Priority/swapfile                               file    2097148 0   -1

   Description: Because the node environment in which the Galera cluster is deployed now deploys other environments, the steps to delete a swap partition are described here

[[email protected] ~]# swapon --summaryFilename                Type        Size    Used    Priority/openstack/swap.img                     file    4194300 0   0/swapfile                               file    2097148 0   -1[[email protected] ~]# swapoff /openstack/swap.img   ##首先停止swap分区[[email protected] ~]# rm -rf /openstack/swap.img  ##删除swap分区文件[[email protected] ~]# swapon --summaryFilename                Type        Size    Used    Priority/swapfile                               file    2097148 0   -1

Third, Galera cluster initialization start cluster

Description: After you finish installing and configuring Galera cluster, you need to use--wsrep-new-cluster to select a node on which to start mysqld. This will initialize the core components of the cluster. Each node that is started after this is connected to the component and begins copying
1. Start the database service using the parameter--wsrep-new-cluster on node-12

[[email protected] mysql]# service mysql start --wsrep-new-cluster

Database initialization error resolution, delete logfile in/var/lib/mysq/directory

Note WARNING: Use the--wsrep-new-cluster parameter only when initializing the primary component. Do not execute this command if you want the node to connect to an existing cluster.
2. After starting the database service on the NODE-12 node, log in to the MySQL database library and use the Wsrep_cluster_size parameter to determine if the startup is successful.

SHOW STATUS LIKE ‘wsrep_cluster_size‘;

Description: The parameter Wsrep_cluster_size value represents the current node online number of Galera cluster

3. Add nodes to the Galera cluster, and execute the following command on NODE-12 and node-13 to start the database service

[[email protected] mysql]# service mysql startStarting MySQL.Logging to ‘/var/lib/mysql/node-13.err‘........ SUCCESS![[email protected] yum.repos.d]# service mysql startStarting MySQL.Logging to ‘/var/lib/mysql/node-14.err‘....... SUCCESS!

Then log in to the MySQL database on node-13 or node-14 to see the value of Wsrep_cluster_size

[[email protected] yum.repos.d]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.6.39 MySQL Community Server (GPL), wsrep_25.22Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> SHOW STATUS LIKE ‘wsrep_cluster_size‘;

In this case, the Galera cluster was built successfully

Cluster replication test

Description: To test whether the Galera cluster is working correctly
1. On the database client, verify that all nodes are connected to each other

[r[email protected] ~]# mysql -urootWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.6.39 MySQL Community Server (GPL), wsrep_25.22Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> show status like ‘wsrep_%‘;

Parameter description:
The synced in the Wsrep_local_state_comment parameter indicates that the node is connected to the cluster and functioning properly
The value of Wsrep_cluster_size 3 indicates that 3 nodes in the Galera cluster are online
Wsrep_ready on indicates that this node is connected to the cluster and capable of processing transactions.

2. Create a table on node-12 and insert the data

  [[email protected] mysql]# mysqlwelcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 5Server version:5.6.39 mysql Community Server (GPL), Wsrep_25.22copyright (c) 2000, 201 8, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> CREATE DATABASE galeratest; Query OK, 1 row affected (0.01 sec) mysql> use galeratest;database changedmysql> CREATE TABLE test_table (id INT PRIM ARY KEY auto_increment,msg TEXT) Engine=innodb; Query OK, 0 rows Affected (0.00 sec) mysql> INSERT into test_table (msg) VALUES ("Hello my dear Cluster."); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into test_table (msg) VALUES ("Hello, again, cluster dear."); Query OK, 1 row Affected (0.00 sec)  

3. View three nodes in NODE-13 or ndoe-14 to synchronize data operations on NODE-12

   [[email protected] mysql]# mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.6.39 MySQL Community Server (GPL), wsrep_25.22

Copyright (c), 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Galeratest |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
5 rows in Set (0.00 sec)

mysql> use galeratest;
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed

Mysql> SELECT * from test_table;
+----+-----------------------------+
| ID | msg |
+----+-----------------------------+
| 1 | Hello my dear cluster. |
| 4 | Hello, again, cluster dear. |
+----+-----------------------------+
2 rows in Set (0.00 sec)

![](http://i2.51cto.com/images/blog/201804/24/52ee1a95b756059ec79e0fda5ec78e1a.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)#### 三节点Galera集群故障模拟1.模拟单个mysqld进程的崩溃，在node-14上kill掉mysqld进程

[[Email protected] mysql]# service MySQL status
success! MySQL Running (72075)
[Email protected] mysql]# killall-9 mysqld
-bash:killall:command not found

安装killall命令

[email protected] mysql]# Yum install psmisc-y
[Email protected] mysql]# killall-9 mysqld
[Email protected]]#/usr/bin/mysqld_safe:line 183:72075 killed Nohup/usr/sbin/mysqld--basedir=/usr--datadir=/var/ Lib/mysql--plugin-dir=/usr/lib64/mysql/plugin--user=mysql--log-error=node-14.err--pid-file=/var/lib/mysql/ Node-14.pid--socket=/var/lib/mysql/mysql.sock--wsrep_start_position=00000000-0000-0000-0000-000000000000:-1 </dev/null >/dev/null 2>&1

![](http://i2.51cto.com/images/blog/201804/24/759be41283f0a7de0e34db84917ca364.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)此时到node-12上查看集群状态，显示有2个在线节点![](http://i2.51cto.com/images/blog/201804/24/c59e5a05427fbaaddbaf71c968e1edc3.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)把故障节点加入galera 集群![](http://i2.51cto.com/images/blog/201804/24/1f807aaa58d5479be986270f09f7a2dd.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)

MariaDB Galera Cluster Deployment practices