Installing Galera
Cluster with MySQL
Codership Training

Introduction
Installing Galera Cluster with MySQLIntroduction

Configuring Nodes

Starting Galera

Galera Cluster Overview

Opening PortsConclusion

Installing Software

Introductions
Codership Oy

Russell Dyer, Presenter

Creators & Developers of Galera Cluster
Employees in Multiple Countries

Galera Cluster

KB Editor, Documentation, Instructor(MySQL, MariaDB)

Writer (O'Reilly Books)

Released Initially in May 2007
Over 1.5 Million Downloads

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0Slide 3

Tutorial Outline
Galera Cluster Overview
Installing Software on Nodes
Configuring Nodes
Opening Ports
Starting Galera

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0
Slide 4Galera Cluster Overview
Installing Galera Cluster with MySQLIntroduction

Configuring Nodes

Starting Galera

Galera Cluster Overview

Opening Ports

Conclusion

Installing Software

Basic Replication Concepts
Nodes — Physical & Virtual Servers

Master

Databases — MySQL, MariaDB, XtraDB
Replication

Slave

Client Writes

Load Balancing

Slave

Client Reads

High Availability
Client Reads
Client Reads

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0
Slide 6

Galera Cluster Concepts
Virtual Synchronous Replication

Galera Cluster

True Multi-Master Solution
Conflict Detection & Resolution onCommit

Node 1
Client Writes

Easy MaintenanceNode 2

Node 3

Automatic Provisioning
Node Isolatation
Rolling Upgrades

Client Writes

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

Client Reads

library@galeracluster.com
Version 1.0
Slide 7

Galera Factors & Best Practices
Servers
Linux or Unix Operating System
Dedicated Servers with Plenty of RAMMultiple, Odd Number of Nodes
Not a Stand-Alone
Three Minimum

Equal Nodes
Equipment & Software
Configuration
Codership KB - Best Practices Articles: https://galeracluster.com/library/kb/best/index.html

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0
Slide 8

Installing Software
Installing Galera Cluster with MySQLIntroduction

Configuring Nodes

Starting Galera

Galera Cluster Overview

Opening Ports

Conclusion

Installing Software

Minimal Software
Operating System & Utilities

ssh -i '.ssh/galera-key' centos@12.127.17.75

Synchronizing Toolssh galera-1

FirewallLOGIN METHODS

Database Software
Galera Cluster

Simpler with config File
Host galera-1
HostName 12.127.17.75
User centos
IdentityFile ~/.ssh/galera-key
Host galera-2
HostName 12.127.17.89
User centos
IdentityFile ~/.ssh/galera-key
Host galera-3
HostName 12.127.17.124
User centos
IdentityFile ~/.ssh/galera-key

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0
Slide 10

Node Provisioning Tool
State Transfers for New Nodes

Galera Cluster

State Snapshot Transfer (SST)Node 1

Incremental State Transfers (IST)

Methods for State Transfers

STATE TRANSFER
Node 2Joiner
NodeLogical — mysqldump
Physical — rsync

ASKS TO JOIN

Node 3sudo yum -y install rsync
Executed from Command-Line on Each Node.
Documentation on Node Provisioning: https://galeracluster.com/library/documentation/node-provisioning.html
Documentation on State Transfers: https://galeracluster.com/library/documentation/state-transfer.html

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0Slide 11

Installing MySQL with Galera
Repository File(http://releases.galeracluster.com/)

Install MySQL & Galera
Secure Installation (e.g., Password)[galera]name = Galera
baseurl = http://REPO-SUB-DOMAIN/VER/DIST/RELEASE/ARCH
gpgkey = http://REPO-SUB-DOMAIN/GPG-KEY-galeracluster.com
gpgcheck = 1[mysql-wsrep]
name = MySQL-wsrep
baseurl = http://REPO-SUB-DOMAIN/mysql-wsrep-VER/DIST/RELEASE/ARCH
gpgkey = http://REPO-SUB-DOMAIN/GPG-KEY-galeracluster.com
gpgcheck = 1
Galera Repo File Format — /etc/yum.repos.d/galera.repo

yum -y install mysql-wsrep-5.7 galera-3
systemctl start mysqld
grep 'temporary password' /var/log/mysqld.log
Installation Doc: https://galeracluster.com/library/documentation/install.html
Galera Repository: https://releases.galeracluster.com/

mysql_secure_installation
Executed from Command-Line

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0
Slide 13

Configuring Nodes
Installing Galera Cluster with MySQLIntroduction

Configuring Nodes

Starting Galera

Galera Cluster Overview

Opening Ports

Conclusion

Installing Software

Database Configuration
Edit MySQL Configuration File (/etc/my.cnf.d/server.cnf)
bind-address — Not Local Hostdefault_storage_engine
binlog_format
log-error[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
bind-address=0.0.0.0
user=mysql
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=128M
binlog_format=ROW
log-error=/var/log/mysqld.log
Excerpt from MySQL Configuration File.

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0
Slide 16

Galera Configuration — Initializing
wsrep_on Enables Galera
wsrep_provider is Name &Path of Galera Libraries[mysqld]…
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so…
Excerpt from Database Configuration File.

May Need to Adjust File Path
MORE SETTINGSUNDER THE [MYSQLD] HEADERDocumentation on wsrep_on: https://galeracluster.com/library/documentation/mysql-wsrep-options.html#wsrep-on
Documentation on wsrep_provider: https://galeracluster.com/library/documentation/mysql-wsrep-options.html#wsrep-provider

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0
Slide 17

Galera Configuration — Node & Cluster
wsrep_node_name
Unique for Each Node (e.g., galera1, galera2)
Corresponds to AWS Instances[mysqld]...
wsrep_node_name='galera-1'
wsrep_node_address="172.31.19.208"
wsrep_cluster_name='galera-training'
wsrep_cluster_address="gcomm://172.31.19.208,
172.31.26.197,
172.31.15.54"

wsrep_node_address
Node's IP Address
Use Internal IP Address for AWS

Excerpt from Database Configuration File.

Documentation on wsrep_node_name: https://galeracluster.com/library/documentation/mysql-wsrep-options.html#wsrep-node-name
Documentation on wsrep_node_address: https://galeracluster.com/library/documentation/mysql-wsrep-options.html#wsrep-node-address
Documentation on wsrep_cluster_name: https://galeracluster.com/library/documentation/mysql-wsrep-options.html#wsrep-cluster-name
Documentation on wsrep_cluster_address: https://galeracluster.com/library/documentation/mysql-wsrep-options.html#wsrep-cluster-address

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0
Slide 18

Galera Configuration — More Options
Set Galera Cache withwsrep_provider_options[mysqld]...
wsrep_provider_options="gcache.size=300M;
gcache.page_size=300M"
wsrep_slave_threads=4
wsrep_sst_method=rsync

Set the Number of Threads for Galerawith wsrep_slave_threads
Excerpt from Database Configuration File.
Set wsrep_sst_method to eitherrsync or mysqldump for Provisioning
New Nodes

Documentation on wsrep_provider_options: https://galeracluster.com/library/documentation/mysql-wsrep-options.html#wsrep-provider-options
Documentation on wsrep_slave_threads: https://galeracluster.com/library/documentation/mysql-wsrep-options.html#wsrep-slave-threads
Documentation on wsrep_sst_method: https://galeracluster.com/library/documentation/mysql-wsrep-options.html#wsrep-sst-method

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0
Slide 19

Opening Ports
Installing Galera Cluster with MySQLIntroduction

Configuring Nodes

Starting Galera

Galera Cluster Overview

Opening Ports

Conclusion

Installing Software

Galera Ports
MySQL Default Traﬃc — TCP 3306
Galera Cluster Communications — TCP & UDP 4567
Incremental State Transfers — TCP 4444
State Snapshot Transfers — TCP 4568

Firewall Settings: https://galeracluster.com/library/documentation/firewall-settings.html

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0Slide 21

SELinux
Kernel Module for Linux Security

setenforce 0
Disables SELinux

Open Port for MySQLOpen Ports for Galera
semanage port -a -t mysqld_port_t -p tcp 3306
semanage port -a -t mysqld_port_t -p tcp 4444
semanage port -a -t mysqld_port_t -p tcp 4567
semanage port -a -t mysqld_port_t -p udp 4567
semanage port -a -t mysqld_port_t -p tcp 4568
Open Ports on SELinuxsemanage permissive -a mysqld_t

SELinux Configuration: https://galeracluster.com/library/documentation/selinux.html

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0
Slide 22

Firewall — firewalld
Firewall Daemon for
Restricting Network
Traﬃc & Services
Enable & Start Firewall
Open Port for MySQLOpen Ports for Galera

systemctl enable firewalld
systemctl start firewalld
firewall-cmd --zone=public --add-service=mysql --permanent
firewall-cmd --zone=public --add-port=3306/tcp —permanent
firewall-cmd --zone=public --add-port=4444/tcp --permanent
firewall-cmd --zone=public --add-port=4567/tcp --permanent
firewall-cmd --zone=public --add-port=4567/udp --permanent
firewall-cmd --zone=public --add-port=4568/tcp --permanent
firewall-cmd --reload

Reload Firewall
Configuring firewalld: https://galeracluster.com/library/documentation/firewalld.html

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0
Slide 23

Starting Galera
Installing Galera Cluster with MySQLIntroduction

Configuring Nodes

Starting Galera

Galera Cluster Overview

Opening Ports

Conclusion

Installing Software

Caveats of Starting a Cluster
A Cluster is made of Multiple Nodes— Not Stand-Alone

New Nodes Unassuming — Look forPrimary Component

Tell First Node it's the PrimaryComponent

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0Slide 25

Starting Nodes
Bootstrap Database & Galera on
Seed Node

Starting
MySQL Nodes

MySQL — mysqld_bootstrap

Seed Node

mysqld_bootstrap
Additional Nodes

systemctl start mysqld

Start Database & Galera Normallyon Additional Nodes

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0Slide 26

Video DemonstrationInstalling Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0

Conclusion
Installing Galera Cluster with MySQLIntroduction

Configuring Nodes

Starting Galera

Galera Cluster Overview

Opening Ports

Conclusion

Installing SoftwareAdditional Resources
Codership Library (galeracluster.com/library)
Documentation (/library/documentation)
Knowledge Base (/library/kb)
FAQ (/library/faq)
Training (/library/training)Videos (/library/training/videos)Tutorials (/library/training/tutorials)

Tutorial Article on Installing with MySQL: https://galeracluster.com/library/training/tutorials/galera-mysql-installing.html

Installing Galera Cluster with MySQL
Copyright Codership Oy 2019. All Rights Reserved.

library@galeracluster.com
Version 1.0
Slide 29