Setup MariaDB Galera Cluster with Data-at-Rest and Data-in-Transit Encryption on CentOS 7
MariaDB Galera Cluster is a virtually synchronous multi-master cluster for MariaDB.
Features
- Virtually synchronous replication
- Active-active multi-master topology
- Read and write to any cluster node
- Automatic membership control, failed nodes drop from the cluster
- Automatic node joining
- True parallel replication, on row level
- Direct client connections, native MariaDB look & feel
Benefits
The above features yield several benefits for a DBMS clustering solution, including:
- No slave lag
- No lost transactions
- Read scalability
- Smaller client latencies
1. Architecture Diagram
2. System Requirements
2.1. HAProxy Load Balancers
| Component | Description |
|---|---|
| Number of VMs | 2 |
| CPU | 2 Cores |
| Memory | 2 GB |
| Disk Size | 20 GB SSD |
| Operating System | CentOS 7 x64 |
| File System | XFS |
| Privileges | ROOT access prefered |
2.2. MariaDB Galera Nodes
| Component | Description |
|---|---|
| Number of VMs | 3 |
| CPU | 4 Cores |
| Memory | 8 GB |
| Disk Size | 100 GB SSD |
| Operating System | CentOS 7 x64 |
| File System | XFS |
| Privileges | ROOT access prefered |
2.3. IP Allocation
| Component | Description |
|---|---|
| MariaDB Galera VM IPs | 192.168.15.101 - 192.168.15.103 |
| HAProxy VM IPs | 192.168.15.104 - 192.168.15.105 |
| Virtual IP (Floating IP) | 192.168.15.100 |
2.4. DNS Entries
| IP | Hostname | FQDN |
|---|---|---|
| 192.168.15.100 | db | db.example.local |
| 192.168.15.101 | db-1 | db-1.example.local |
| 192.168.15.102 | db-2 | db-2.example.local |
| 192.168.15.103 | db-3 | db-3.example.local |
| 192.168.15.104 | lb-1 | lb-1.example.local |
| 192.168.15.105 | lb-2 | lb-2.example.local |
3. Install and Configure a MariaDB Galera Cluster
3.1. Common configurations on ALL database nodes
3.1.1. Set server hostname.
# Example:
# sudo hostnamectl set-hostname db1-1.example.local
sudo hostnamectl set-hostname <hostname>
3.1.2. Install prerequisites.
# Clean YUM repository cache
sudo yum clean all
# Update packages
sudo yum update -y
# Install prerequisites
sudo yum install -y vim ntp chrony net-tools yum-utils policycoreutils-python
3.1.3. Synchronize server time with default NTP servers. If you have your own NTP servers, please make sure to update the /etc/chrony.conf
# Set timezone to Asia/Colombo
sudo timedatectl set-timezone Asia/Colombo
# Enable NTP time synchronization
sudo timedatectl set-ntp true
3.1.4. Start and enable chronyd service.
# Start and enable chronyd service
sudo systemctl enable --now chronyd
# Verify if the service is started
sudo systemctl status chronyd
3.1.5. Display time synchronization status.
# Verify synchronisation state
sudo ntpstat
# Check Chrony Source Statistics
sudo chronyc sourcestats -v
3.1.6. Disable File Access Time Logging and enable Combat Fragmentation to enhance XFS file system performance. Add noatime,nodiratime,allocsize=64m to all XFS volumes under /etc/fstab.
# Edit /etc/fstab
sudo vim /etc/fstab
# Modify XFS volume entries as follows
# Example:
UUID="03c97344-9b3d-45e2-9140-cbbd57b6f085" / xfs defaults,noatime,nodiratime,allocsize=64m 0 0
3.1.7. Turn off swap permanently.
# Permanently disable swapping
sudo sed -e '/swap/ s/^#*/#/g' -i /etc/fstab
# Disable all existing swaps from /proc/swaps
sudo swapoff -a
3.1.8. Disable IPv6 on ens192 interface.
# Disable IPv6 on ens192 interface
sudo nmcli connection modify ens192 ipv6.method ignore
3.1.9. Tweaking the system for high concurrency and security.
cat <<"EOF" | sudo tee /etc/sysctl.d/00-sysctl.conf > /dev/null
#############################################################################################
# Tweak virtual memory
#############################################################################################
# Default: 30
# 0 - Never swap under any circumstances.
# 1 - Do not swap unless there is an out-of-memory (OOM) condition.
vm.swappiness = 1
# vm.dirty_background_ratio is used to adjust how the kernel handles dirty pages that must be flushed to disk.
# Default value is 10.
# The value is a percentage of the total amount of system memory, and setting this value to 5 is appropriate in many situations.
# This setting should not be set to zero.
vm.dirty_background_ratio = 5
# The total number of dirty pages that are allowed before the kernel forces synchronous operations to flush them to disk
# can also be increased by changing the value of vm.dirty_ratio, increasing it to above the default of 30 (also a percentage of total system memory)
# vm.dirty_ratio value in-between 60 and 80 is a reasonable number.
vm.dirty_ratio = 60
# vm.max_map_count will calculate the current number of memory-mapped files.
# The minimum value for mmap limit (vm.max_map_count) is the number of open files ulimit (cat /proc/sys/fs/file-max).
# map_count should be around 1 per 128 KB of system memory. Therefore, max_map_count will be 262144 on a 32 GB system.
# Reference: https://docs.confluent.io/current/kafka/deployment.html
# Default: 65530
vm.max_map_count = 2097152
#############################################################################################
# Tweak file descriptors
#############################################################################################
# Increases the size of file descriptors and inode cache and restricts core dumps.
fs.file-max = 2097152
fs.suid_dumpable = 0
#############################################################################################
# Tweak network settings
#############################################################################################
# Default amount of memory allocated for the send and receive buffers for each socket.
# This will significantly increase performance for large transfers.
net.core.wmem_default = 25165824
net.core.rmem_default = 25165824
# Maximum amount of memory allocated for the send and receive buffers for each socket.
# This will significantly increase performance for large transfers.
net.core.wmem_max = 25165824
net.core.rmem_max = 25165824
# In addition to the socket settings, the send and receive buffer sizes for
# TCP sockets must be set separately using the net.ipv4.tcp_wmem and net.ipv4.tcp_rmem parameters.
# These are set using three space-separated integers that specify the minimum, default, and maximum sizes, respectively.
# The maximum size cannot be larger than the values specified for all sockets using net.core.wmem_max and net.core.rmem_max.
# A reasonable setting is a 4 KiB minimum, 64 KiB default, and 2 MiB maximum buffer.
net.ipv4.tcp_wmem = 20480 12582912 25165824
net.ipv4.tcp_rmem = 20480 12582912 25165824
# Increase the maximum total buffer-space allocatable
# This is measured in units of pages (4096 bytes)
net.ipv4.tcp_mem = 65536 25165824 262144
net.ipv4.udp_mem = 65536 25165824 262144
# Minimum amount of memory allocated for the send and receive buffers for each socket.
net.ipv4.udp_wmem_min = 16384
net.ipv4.udp_rmem_min = 16384
# Enabling TCP window scaling by setting net.ipv4.tcp_window_scaling to 1 will allow
# clients to transfer data more efficiently, and allow that data to be buffered on the broker side.
net.ipv4.tcp_window_scaling = 1
# Increasing the value of net.ipv4.tcp_max_syn_backlog above the default of 1024 will allow
# a greater number of simultaneous connections to be accepted.
net.ipv4.tcp_max_syn_backlog = 10240
# Increasing the value of net.core.netdev_max_backlog to greater than the default of 1000
# can assist with bursts of network traffic, specifically when using multigigabit network connection speeds,
# by allowing more packets to be queued for the kernel to process them.
net.core.netdev_max_backlog = 65536
# Increase the maximum amount of option memory buffers
net.core.optmem_max = 25165824
# Number of times SYNACKs for passive TCP connection.
net.ipv4.tcp_synack_retries = 2
# Allowed local port range.
net.ipv4.ip_local_port_range = 2048 65535
# Protect Against TCP Time-Wait
# Default: net.ipv4.tcp_rfc1337 = 0
net.ipv4.tcp_rfc1337 = 1
# Decrease the time default value for tcp_fin_timeout connection
net.ipv4.tcp_fin_timeout = 15
# The maximum number of backlogged sockets.
# Default is 128.
net.core.somaxconn = 4096
# Turn on syncookies for SYN flood attack protection.
net.ipv4.tcp_syncookies = 1
# Avoid a smurf attack
net.ipv4.icmp_echo_ignore_broadcasts = 1
# Turn on protection for bad icmp error messages
net.ipv4.icmp_ignore_bogus_error_responses = 1
# Enable automatic window scaling.
# This will allow the TCP buffer to grow beyond its usual maximum of 64K if the latency justifies it.
net.ipv4.tcp_window_scaling = 1
# Turn on and log spoofed, source routed, and redirect packets
net.ipv4.conf.all.log_martians = 1
net.ipv4.conf.default.log_martians = 1
# Tells the kernel how many TCP sockets that are not attached to any
# user file handle to maintain. In case this number is exceeded,
# orphaned connections are immediately reset and a warning is printed.
# Default: net.ipv4.tcp_max_orphans = 65536
net.ipv4.tcp_max_orphans = 65536
# Do not cache metrics on closing connections
net.ipv4.tcp_no_metrics_save = 1
# Enable timestamps as defined in RFC1323:
# Default: net.ipv4.tcp_timestamps = 1
net.ipv4.tcp_timestamps = 1
# Enable select acknowledgments.
# Default: net.ipv4.tcp_sack = 1
net.ipv4.tcp_sack = 1
# Increase the tcp-time-wait buckets pool size to prevent simple DOS attacks.
# net.ipv4.tcp_tw_recycle has been removed from Linux 4.12. Use net.ipv4.tcp_tw_reuse instead.
net.ipv4.tcp_max_tw_buckets = 1440000
net.ipv4.tcp_tw_reuse = 1
# The accept_source_route option causes network interfaces to accept packets with the Strict Source Route (SSR) or Loose Source Routing (LSR) option set.
# The following setting will drop packets with the SSR or LSR option set.
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.default.accept_source_route = 0
# Turn on reverse path filtering
net.ipv4.conf.all.rp_filter = 1
net.ipv4.conf.default.rp_filter = 1
# Disable ICMP redirect acceptance
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.default.accept_redirects = 0
net.ipv4.conf.all.secure_redirects = 0
net.ipv4.conf.default.secure_redirects = 0
# Disables sending of all IPv4 ICMP redirected packets.
net.ipv4.conf.all.send_redirects = 0
net.ipv4.conf.default.send_redirects = 0
# Disable IP forwarding.
# IP forwarding is the ability for an operating system to accept incoming network packets on one interface,
# recognize that it is not meant for the system itself, but that it should be passed on to another network, and then forwards it accordingly.
net.ipv4.ip_forward = 0
# Disable IPv6
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
#############################################################################################
# Tweak kernel parameters
#############################################################################################
# Address Space Layout Randomization (ASLR) is a memory-protection process for operating systems that guards against buffer-overflow attacks.
# It helps to ensure that the memory addresses associated with running processes on systems are not predictable,
# thus flaws or vulnerabilities associated with these processes will be more difficult to exploit.
# Accepted values: 0 = Disabled, 1 = Conservative Randomization, 2 = Full Randomization
kernel.randomize_va_space = 2
# Allow for more PIDs (to reduce rollover problems)
kernel.pid_max = 65536
EOF
3.1.10. Reload all sysctl variables without rebooting the server.
sudo sysctl -p /etc/sysctl.d/00-sysctl.conf
3.1.11. Create Local DNS records.
cat <<"EOF" | sudo tee /etc/hosts > /dev/null
# localhost
127.0.0.1 localhost localhost.localdomain
# When DNS records are updated in the DNS server, remove these entries.
192.168.15.101 db-1 db-1.example.local
192.168.15.102 db-2 db-2.example.local
192.168.15.103 db-3 db-3.example.local
EOF
3.1.12. The servers need to be restarted before continuing further.
sudo reboot
3.2. MariaDB Galera common configurations on ALL database nodes
3.2.1. Configure YUM repository for MariaDB.
# Configure YUM repository
cat <<"EOF" | sudo tee /etc/yum.repos.d/MariaDB.repo > /dev/null
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.5/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
3.2.2. Install MariaDB.
# Install MariaDB
sudo yum install -y MariaDB-server MariaDB-client MariaDB-backup galera-4
3.2.3. Increase number of max_open_files.
# Create mariadb.service.d directory if not exist
sudo mkdir -p /etc/systemd/system/mariadb.service.d
# Increase number of max_open_files
cat <<"EOF" | sudo tee /etc/systemd/system/mariadb.service.d/override.conf > /dev/null
[Service]
LimitNOFILE=infinity
EOF
# Restore SELinux context
sudo restorecon -RvF /etc/systemd/system/mariadb.service.d
# Reload systemd unit files
sudo systemctl daemon-reload
3.2.4. Copy the correct SSL certificate, SSL key and the CA certificate files under /etc/ssl/galera. This is used for Data-in-Transit encryption.
tip
To generate a custom CA and CA signed SSL certificates, please follow this guide.
# Create the directory structure
sudo mkdir -p /etc/ssl/galera
# Copy the correct CA certificate, TLS certificate and TLS key
/etc/ssl/galera/ca.pem
/etc/ssl/galera/tls.crt
/etc/ssl/galera/tls.key
# Set correct permissions
sudo chown -R mysql:mysql /etc/ssl/galera
sudo chmod 0600 /etc/ssl/galera/*
# Restore SELinux context
sudo restorecon -RvF /etc/ssl/galera
# If you are using a self-signed certificate, make sure to configure it as a trusted root certificate
sudo cp /etc/ssl/galera/ca.pem /etc/pki/ca-trust/source/anchors/ca.pem
sudo update-ca-trust
3.2.5. Open necessary firewall ports.
sudo firewall-cmd --permanent --add-port={3306,4444,4567,4568}/tcp
sudo firewall-cmd --permanent --add-port=4567/udp
sudo firewall-cmd --reload
3.2.6. Configure SELinux TCP/UDP port context for MariaDB Galera.
# Configure SELinux TCP/UDP port context for MariaDB Galera
sudo semanage port -a -t mysqld_port_t -p tcp 3306
sudo semanage port -a -t mysqld_port_t -p tcp 4567
sudo semanage port -a -t mysqld_port_t -p tcp 4568
sudo semanage port -a -t mysqld_port_t -p tcp 4444
sudo semanage port -a -t mysqld_port_t -p udp 4567
3.3. Configurations on DB-1 node.
info
This will be treated as the bootstrap node
3.3.1. Create encryption keys for File Key Management Encryption Plugin. This is used for Data-at-Rest encryption.
info
- Make sure to BACKUP /etc/mysql/encryption directory in a secure location. If you lost access to these encryption keys, you won't be able to decrypt MySQL data.
- All database servers MUST have the same encryption keys. Therefore, generate it on the bootstrap server and copy it to others.
# Create directory structure
sudo mkdir -p /etc/mysql/encryption
# Create a plain text keyfile
for i in {1..5}; do echo "$i;$(sudo openssl rand -hex 32)"; done | sudo tee /etc/mysql/encryption/keyfile > /dev/null
# Create an encryption key for keyfile
openssl rand -hex 128 | sudo tee /etc/mysql/encryption/.keyfile.key > /dev/null
# Encrypt keyfile using encryption key
sudo openssl enc -aes-256-cbc -md sha1 \
-pass file:/etc/mysql/encryption/.keyfile.key \
-in /etc/mysql/encryption/keyfile \
-out /etc/mysql/encryption/keyfile.enc
# Remove plain text keyfile
sudo rm -f /etc/mysql/encryption/keyfile
3.3.2. Set encryption key permissions.
# Set permissions
sudo chmod 0600 /etc/mysql/encryption/*
sudo chown -R mysql:mysql /etc/mysql/encryption
# Restore SELinux context
sudo restorecon -RvF /etc/mysql/encryption
3.3.3. Copy the generated encryption keys to DB-2 and DB-3 nodes.
# Copy the generated encryption keys to other servers
scp -r /etc/mysql/encryption [email protected]:/etc/mysql/encryption
scp -r /etc/mysql/encryption [email protected]:/etc/mysql/encryption
3.3.4. Secure the initial MariaDB setup
# Start mariadb service
sudo systemctl start mariadb
# Improve the security of your MariaDB installation
mysql_secure_installation
Please answer the questions as shown below.
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.
You already have your root account protected, so you can safely answer 'n'.
Switch to unix_socket authentication [Y/n] Y
Enabled successfully!
Reloading privilege tables..
... Success!
You already have your root account protected, so you can safely answer 'n'.
Change the root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] Y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] Y
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] Y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
3.3.5. Create a database user with unix_socket authentication. This is used for galera State Snapshot Transfers (SST).
tip
In a State Snapshot Transfer (SST), the cluster provisions nodes by transferring a full data copy from one node to another. When a new node joins the cluster, the new node initiates a State Snapshot Transfer to synchronize its data with a node that is already part of the cluster.
# Switch to Linux root user
sudo su - root
# Login to MySQL CLI.
# You won't be prompted for credentials as MySQL root user uses unix_socket authentication by default
mysql
# Create 'mysql' user with unix_socket authentication
mysql> CREATE USER IF NOT EXISTS 'mysql'@'localhost' IDENTIFIED VIA unix_socket;
mysql> GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mysql'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;
3.3.6. Create a database user for load balancer health checks.
# Switch to Linux root user
sudo su - root
# Login to MySQL CLI.
# You won't be prompted for credentials as MySQL root user uses unix_socket authentication by default
mysql
# Create 'clustercheck' user with password authentication
mysql> CREATE USER IF NOT EXISTS 'clustercheck'@'localhost' IDENTIFIED BY 'REvA9xYEEXhRY0NN0oLQiGH9sTuUj2Q9RenXrNRC';
mysql> GRANT PROCESS ON *.* TO 'clustercheck'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;
3.3.7. Create a database user with root privileges. This is used for database administrative tasks.
danger
Please make sure to store admin user credentials securely. This is a HIGH RISK user account and creating this account is optional.
# Switch to Linux root user
sudo su - root
# Login to MySQL CLI.
# You won't be prompted for credentials as MySQL root user uses unix_socket authentication by default
mysql
# Create 'admin' user with password authentication
mysql> CREATE USER IF NOT EXISTS 'admin'@'%' IDENTIFIED BY 'H=*+DBp:8mJRD^i8T12>hVV-1+aC09gv' REQUIRE SSL;
mysql> GRANT ALL ON *.* TO 'admin'@'%';
mysql> GRANT GRANT OPTION ON *.* TO 'admin'@'%';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;
3.3.8. Stop MariaDB service for bootstraping.
# Stop MariaDB service
sudo systemctl stop mariadb
3.3.9. Add the galera configurations under /etc/my.cnf.d/galera.cnf.
cat <<"EOF" | sudo tee /etc/my.cnf.d/galera.cnf > /dev/null
[mariadb]
# Server Configuration
server-id = 1
# Allow server to accept connections on all interfaces
bind-address = 0.0.0.0
log_error = /var/lib/mysql/mysqld.log
innodb_buffer_pool_size = 1G
# max_connections determines the maximum number of concurrent connections to the database server
max_connections = 2000
# Set default character set to UTF-8. Without this you won't be able to store unicode characters
character-set-server = utf8
# MySQL Reverse DNS lookups are often the cause of long delay and slow MySQL performance when running certain SQL queries
skip_name_resolve
# Enable data-in-transit encryption
require_secure_transport = ON
tls_version = TLSv1.2,TLSv1.3
ssl_ca = /etc/ssl/galera/ca.pem
ssl_key = /etc/ssl/galera/tls.key
ssl_cert = /etc/ssl/galera/tls.crt
# Enable data-at-rest encryption
encrypt_binlog = ON
encrypt_tmp_disk_tables = ON
encrypt_tmp_files = ON
innodb_encrypt_log = ON
innodb_encrypt_tables = ON
innodb_encrypt_temporary_tables = ON
master_verify_checksum = ON
log_slave_updates = ON
log_bin = DB-1
binlog_checksum = CRC32
plugin_load_add = file_key_management
file_key_management_encryption_algorithm = aes_cbc
file_key_management_filename = /etc/mysql/encryption/keyfile.enc
file_key_management_filekey = FILE:/etc/mysql/encryption/.keyfile.key
[galera]
# Cluster Configuration
wsrep_provider = /usr/lib64/galera-4/libgalera_smm.so
wsrep_on = ON
wsrep_cluster_name = Galera
wsrep_node_name = DB-1
wsrep_node_address = 192.168.15.101
wsrep_cluster_address = gcomm://db-1.example.local,db-2.example.local,db-3.example.local
binlog_format = ROW
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
# Enable replication traffic encryption
wsrep_provider_options = "socket.ssl_key=/etc/ssl/galera/tls.key;socket.ssl_cert=/etc/ssl/galera/tls.crt;socket.ssl_ca=/etc/ssl/galera/ca.pem"
# State snapshot transfer method
wsrep_sst_method = mariabackup
wsrep_sst_auth = mysql
EOF
3.3.10. Set config file permissions.
# Set permissions
sudo chmod 0600 /etc/my.cnf.d/galera.cnf
sudo chown -R mysql:mysql /etc/my.cnf.d
# Restore SELinux context
sudo restorecon -RvF /etc/my.cnf.d
3.3.11. Bootstrap the DB-1 node.
# Bootstrap the DB-1 node
sudo galera_new_cluster
3.3.12. Enable mariadb service.
# Enable mariadb service
sudo systemctl enable mariadb
3.3.13. If there are any errors, please check logs.
# Check error messages in journald
sudo journalctl -f -b --no-pager -u mariadb
# Check MariaDB logs
sudo tail -f /var/lib/mysql/mysqld.log
3.4. Configurations on DB-2 node.
3.4.1. Add the galera configurations under /etc/my.cnf.d/galera.cnf.
cat <<"EOF" | sudo tee /etc/my.cnf.d/galera.cnf > /dev/null
[mariadb]
# Server Configuration
server-id = 2
# Allow server to accept connections on all interfaces
bind-address = 0.0.0.0
log_error = /var/lib/mysql/mysqld.log
innodb_buffer_pool_size = 1G
# max_connections determines the maximum number of concurrent connections to the database server
max_connections = 2000
# Set default character set to UTF-8. Without this you won't be able to store unicode characters
character-set-server = utf8
# MySQL Reverse DNS lookups are often the cause of long delay and slow MySQL performance when running certain SQL queries
skip_name_resolve
# Enable data-in-transit encryption
require_secure_transport = ON
tls_version = TLSv1.2,TLSv1.3
ssl_ca = /etc/ssl/galera/ca.pem
ssl_key = /etc/ssl/galera/tls.key
ssl_cert = /etc/ssl/galera/tls.crt
# Enable data-at-rest encryption
encrypt_binlog = ON
encrypt_tmp_disk_tables = ON
encrypt_tmp_files = ON
innodb_encrypt_log = ON
innodb_encrypt_tables = ON
innodb_encrypt_temporary_tables = ON
master_verify_checksum = ON
log_slave_updates = ON
log_bin = DB-2
binlog_checksum = CRC32
plugin_load_add = file_key_management
file_key_management_encryption_algorithm = aes_cbc
file_key_management_filename = /etc/mysql/encryption/keyfile.enc
file_key_management_filekey = FILE:/etc/mysql/encryption/.keyfile.key
[galera]
# Cluster Configuration
wsrep_provider = /usr/lib64/galera-4/libgalera_smm.so
wsrep_on = ON
wsrep_cluster_name = Galera
wsrep_node_name = DB-2
wsrep_node_address = 192.168.15.102
wsrep_cluster_address = gcomm://db-1.example.local,db-2.example.local,db-3.example.local
binlog_format = ROW
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
# Enable replication traffic encryption
wsrep_provider_options = "socket.ssl_key=/etc/ssl/galera/tls.key;socket.ssl_cert=/etc/ssl/galera/tls.crt;socket.ssl_ca=/etc/ssl/galera/ca.pem"
# State snapshot transfer method
wsrep_sst_method = mariabackup
wsrep_sst_auth = mysql
EOF
3.4.2. Set config file permissions.
# Set permissions
sudo chmod 0600 /etc/my.cnf.d/galera.cnf
sudo chown -R mysql:mysql /etc/my.cnf.d
# Restore SELinux context
sudo restorecon -RvF /etc/my.cnf.d
3.4.3. Set encryption key permissions.
# Set permissions
sudo chmod 0600 /etc/mysql/encryption/*
sudo chown -R mysql:mysql /etc/mysql/encryption
# Restore SELinux context
sudo restorecon -RvF /etc/mysql/encryption
3.4.4. Start and enable mariadb.service
# Start and enable mariadb.service
sudo systemctl enable --now mariadb.service
3.4.5. If there are any errors, please check logs.
# Check error messages in journald
sudo journalctl -f -b --no-pager -u mariadb
# Check MariaDB logs
sudo tail -f /var/lib/mysql/mysqld.log
3.5. Configurations on DB-3 node.
3.5.1. Add the galera configurations under /etc/my.cnf.d/galera.cnf.
cat <<"EOF" | sudo tee /etc/my.cnf.d/galera.cnf > /dev/null
[mariadb]
# Server Configuration
server-id = 3
# Allow server to accept connections on all interfaces
bind-address = 0.0.0.0
log_error = /var/lib/mysql/mysqld.log
innodb_buffer_pool_size = 1G
# max_connections determines the maximum number of concurrent connections to the database server
max_connections = 2000
# Set default character set to UTF-8. Without this you won't be able to store unicode characters
character-set-server = utf8
# MySQL Reverse DNS lookups are often the cause of long delay and slow MySQL performance when running certain SQL queries
skip_name_resolve
# Enable data-in-transit encryption
require_secure_transport = ON
tls_version = TLSv1.2,TLSv1.3
ssl_ca = /etc/ssl/galera/ca.pem
ssl_key = /etc/ssl/galera/tls.key
ssl_cert = /etc/ssl/galera/tls.crt
# Enable data-at-rest encryption
encrypt_binlog = ON
encrypt_tmp_disk_tables = ON
encrypt_tmp_files = ON
innodb_encrypt_log = ON
innodb_encrypt_tables = ON
innodb_encrypt_temporary_tables = ON
master_verify_checksum = ON
log_slave_updates = ON
log_bin = DB-3
binlog_checksum = CRC32
plugin_load_add = file_key_management
file_key_management_encryption_algorithm = aes_cbc
file_key_management_filename = /etc/mysql/encryption/keyfile.enc
file_key_management_filekey = FILE:/etc/mysql/encryption/.keyfile.key
[galera]
# Cluster Configuration
wsrep_provider = /usr/lib64/galera-4/libgalera_smm.so
wsrep_on = ON
wsrep_cluster_name = Galera
wsrep_node_name = DB-3
wsrep_node_address = 192.168.15.103
wsrep_cluster_address = gcomm://db-1.example.local,db-2.example.local,db-3.example.local
binlog_format = ROW
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
# Enable replication traffic encryption
wsrep_provider_options = "socket.ssl_key=/etc/ssl/galera/tls.key;socket.ssl_cert=/etc/ssl/galera/tls.crt;socket.ssl_ca=/etc/ssl/galera/ca.pem"
# State snapshot transfer method
wsrep_sst_method = mariabackup
wsrep_sst_auth = mysql
EOF
3.5.2. Set config file permissions.
# Set permissions
sudo chmod 0600 /etc/my.cnf.d/galera.cnf
sudo chown -R mysql:mysql /etc/my.cnf.d
# Restore SELinux context
sudo restorecon -RvF /etc/my.cnf.d
3.5.3. Set encryption key permissions.
# Set permissions
sudo chmod 0600 /etc/mysql/encryption/*
sudo chown -R mysql:mysql /etc/mysql/encryption
# Restore SELinux context
sudo restorecon -RvF /etc/mysql/encryption
3.5.4. Start and enable mariadb.service
# Start and enable mariadb.service
sudo systemctl enable --now mariadb.service
3.5.5. If there are any errors, please check logs.
# Check error messages in journald
sudo journalctl -f -b --no-pager -u mariadb
# Check MariaDB logs
sudo tail -f /var/lib/mysql/mysqld.log
4. Configure MariaDB Health Check Endpoints for Load Balancers
4.1. Common configurations on ALL database nodes
4.1.1. Install the xinetd package.
# Install xinetd package
sudo yum install -y xinetd
tip
The Extended Internet Service Daemon (xinetd) service listens for incoming requests over a network and launches the appropriate service for that request. Requests are made using port numbers as identifiers and xinetd usually launches another daemon to handle the request. It can be used to start services with both privileged and non-privileged port numbers.
4.1.2. Create a mariadb health check script under /usr/libexec/xinetd/mariadb-health.
# Create the directory structure
sudo mkdir -p /usr/libexec/xinetd
# Create the mariadb-health script
cat <<"EOF" | sudo tee /usr/libexec/xinetd/mariadb-health > /dev/null
#!/bin/bash
#
# Script to make a proxy (i.e. HAProxy) capable of monitoring MariaDB Cluster nodes properly
# Based on the original script from Unai Rodriguez and Olaf (https://github.com/olafz/percona-clustercheck)
#
# Grant privileges required:
# CREATE USER IF NOT EXISTS 'clustercheck'@'localhost' IDENTIFIED BY 'REvA9xYEEXhRY0NN0oLQiGH9sTuUj2Q9RenXrNRC';
# GRANT PROCESS ON *.* TO 'clustercheck'@'localhost';
# FLUSH PRIVILEGES;
if [[ $1 == '-h' || $1 == '--help' ]];then
echo "Usage: $0 <user> <pass> <available_when_donor=0|1> <log_file> <available_when_readonly=0|1> <defaults_extra_file>"
exit
fi
# If you have different credentials, please update it here
MYSQL_USERNAME="${1-clustercheck}"
MYSQL_PASSWORD="${2-REvA9xYEEXhRY0NN0oLQiGH9sTuUj2Q9RenXrNRC}"
AVAILABLE_WHEN_DONOR=${3:-0}
ERR_FILE="${4:-/dev/null}"
AVAILABLE_WHEN_READONLY=${5:-1}
DEFAULTS_EXTRA_FILE=${6:-/etc/my.cnf}
#Timeout exists for instances where mysqld may be hung
TIMEOUT=10
EXTRA_ARGS=""
if [[ -n "$MYSQL_USERNAME" ]]; then
EXTRA_ARGS="$EXTRA_ARGS --user=${MYSQL_USERNAME}"
fi
if [[ -n "$MYSQL_PASSWORD" ]]; then
EXTRA_ARGS="$EXTRA_ARGS --password=${MYSQL_PASSWORD}"
fi
if [[ -r $DEFAULTS_EXTRA_FILE ]];then
MYSQL_CMDLINE="mysql --defaults-extra-file=$DEFAULTS_EXTRA_FILE -nNE --connect-timeout=$TIMEOUT \
${EXTRA_ARGS}"
else
MYSQL_CMDLINE="mysql -nNE --connect-timeout=$TIMEOUT ${EXTRA_ARGS}"
fi
#
# Perform the query to check the wsrep_local_state
#
WSREP_STATUS=($($MYSQL_CMDLINE -e "SHOW GLOBAL STATUS LIKE 'wsrep_%';" \
2>${ERR_FILE} | grep -A 1 -E 'wsrep_local_state$|wsrep_cluster_status$' \
| sed -n -e '2p' -e '5p' | tr '\n' ' '))
if [[ ${WSREP_STATUS[1]} == 'Primary' && ( ${WSREP_STATUS[0]} -eq 4 || \
( ${WSREP_STATUS[0]} -eq 2 && $AVAILABLE_WHEN_DONOR -eq 1 ) ) ]]
then
# Check only when set to 0 to avoid latency in response.
if [[ $AVAILABLE_WHEN_READONLY -eq 0 ]];then
READ_ONLY=$($MYSQL_CMDLINE -e "SHOW GLOBAL VARIABLES LIKE 'read_only';" \
2>${ERR_FILE} | tail -1 2>>${ERR_FILE})
if [[ "${READ_ONLY}" == "ON" ]];then
# MariaDB Cluster node local state is 'Synced', but it is in
# read-only mode. The variable AVAILABLE_WHEN_READONLY is set to 0.
# => return HTTP 503
# Shell return-code is 1
echo -en "HTTP/1.1 503 Service Unavailable\r\n"
echo -en "Content-Type: text/plain\r\n"
echo -en "Connection: close\r\n"
echo -en "Content-Length: 36\r\n"
echo -en "\r\n"
echo -en "MariaDB Cluster Node is read-only.\r\n"
sleep 0.1
exit 1
fi
fi
# MariaDB Cluster node local state is 'Synced' => return HTTP 200
# Shell return-code is 0
echo -en "HTTP/1.1 200 OK\r\n"
echo -en "Content-Type: text/plain\r\n"
echo -en "Connection: close\r\n"
echo -en "Content-Length: 33\r\n"
echo -en "\r\n"
echo -en "MariaDB Cluster Node is synced.\r\n"
sleep 0.1
exit 0
else
# MariaDB Cluster node local state is not 'Synced' => return HTTP 503
# Shell return-code is 1
echo -en "HTTP/1.1 503 Service Unavailable\r\n"
echo -en "Content-Type: text/plain\r\n"
echo -en "Connection: close\r\n"
echo -en "Content-Length: 50\r\n"
echo -en "\r\n"
echo -en "MariaDB Cluster Node is not synced or non-PRIM. \r\n"
sleep 0.1
exit 1
fi
EOF
4.1.3. Provide necessary permissions.
# Grant execution permissions
sudo chmod +x /usr/libexec/xinetd/mariadb-health
# Restore SELinux context
sudo restorecon -RvF /usr/libexec/xinetd
4.1.4. Create the mariadb-health xinetd configuration file under /etc/xinetd.d.
cat <<"EOF" | sudo tee /etc/xinetd.d/mariadb-health > /dev/null
# default: on
# description: mariadb-health
service mariadb-health
{
disable = no
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /usr/libexec/xinetd/mariadb-health
log_on_failure += USERID
only_from = 0.0.0.0/0
per_source = UNLIMITED
}
EOF
4.1.5. The mariadb-health xinetd service needs to be added to services list. Comment out the existing services which use port 9200 under /etc/services and add mariadb-health service instead.
# Comment out the existing services which use port 9200 and add mariadb-health service as shown below
sudo vim /etc/services
# wap-wsp 9200/tcp # WAP connectionless session service
# wap-wsp 9200/udp # WAP connectionless session service
mariadb-health 9200/tcp # MariaDB health check service
4.1.6. Open necessary firewall ports.
sudo firewall-cmd --permanent --add-port=9200/tcp
sudo firewall-cmd --reload
4.1.7. Start and enable xinetd service.
# Start and enable xinetd service
sudo systemctl start --now xinetd
4.1.8. Verify if the health check endpoint is working.
curl http://localhost:9200
If it is working, you should get the following output
MariaDB Cluster Node is synced.
5. Configure HAProxy Load Balancers
5.1. Install and configure prerequisites on BOTH nodes.
5.1.1. Set server hostname.
# Example:
# sudo hostnamectl set-hostname lb-1.example.local
sudo hostnamectl set-hostname <hostname>
5.1.2. Install prerequisites.
# Clean YUM repository cache
sudo yum clean all
# Update packages
sudo yum update -y
# Install prerequisites
sudo yum install -y vim ntp chrony net-tools yum-utils policycoreutils-python
5.1.3. Synchronize server time with default NTP servers. If you have your own NTP servers, please make sure to update the /etc/chrony.conf
# Set timezone to Asia/Colombo
sudo timedatectl set-timezone Asia/Colombo
# Enable NTP time synchronization
sudo timedatectl set-ntp true
5.1.4. Start and enable chronyd service.
# Start and enable chronyd service
sudo systemctl enable --now chronyd
# Verify if the service is started
sudo systemctl status chronyd
5.1.5. Display time synchronization status.
# Verify synchronisation state
sudo ntpstat
# Check Chrony Source Statistics
sudo chronyc sourcestats -v
5.1.6. Disable File Access Time Logging and enable Combat Fragmentation to enhance XFS file system performance. Add noatime,nodiratime,allocsize=64m to all XFS volumes under /etc/fstab.
# Edit /etc/fstab
sudo vim /etc/fstab
# Modify XFS volume entries as follows
# Example:
UUID="03c97344-9b3d-45e2-9140-cbbd57b6f085" / xfs defaults,noatime,nodiratime,allocsize=64m 0 0
5.1.7. Turn off swap permanently.
# Permanently disable swapping
sudo sed -e '/swap/ s/^#*/#/g' -i /etc/fstab
# Disable all existing swaps from /proc/swaps
sudo swapoff -a
5.1.8. Disable IPv6 on ens192 interface.
# Disable IPv6 on ens192 interface
sudo nmcli connection modify ens192 ipv6.method ignore
5.1.9. Tweaking the system for high concurrency and security.
cat <<"EOF" | sudo tee /etc/sysctl.d/00-sysctl.conf > /dev/null
#############################################################################################
# Tweak virtual memory
#############################################################################################
# Default: 30
# 0 - Never swap under any circumstances.
# 1 - Do not swap unless there is an out-of-memory (OOM) condition.
vm.swappiness = 10
# vm.dirty_background_ratio is used to adjust how the kernel handles dirty pages that must be flushed to disk.
# Default value is 10.
# The value is a percentage of the total amount of system memory, and setting this value to 5 is appropriate in many situations.
# This setting should not be set to zero.
vm.dirty_background_ratio = 5
# The total number of dirty pages that are allowed before the kernel forces synchronous operations to flush them to disk
# can also be increased by changing the value of vm.dirty_ratio, increasing it to above the default of 30 (also a percentage of total system memory)
# vm.dirty_ratio value in-between 60 and 80 is a reasonable number.
vm.dirty_ratio = 60
# vm.max_map_count will calculate the current number of memory-mapped files.
# The minimum value for mmap limit (vm.max_map_count) is the number of open files ulimit (cat /proc/sys/fs/file-max).
# map_count should be around 1 per 128 KB of system memory. Therefore, max_map_count will be 262144 on a 32 GB system.
# Reference: https://docs.confluent.io/current/kafka/deployment.html
# Default: 65530
vm.max_map_count = 2097152
#############################################################################################
# Tweak file descriptors
#############################################################################################
# Increases the size of file descriptors and inode cache and restricts core dumps.
fs.file-max = 2097152
fs.suid_dumpable = 0
#############################################################################################
# Tweak network settings
#############################################################################################
# Default amount of memory allocated for the send and receive buffers for each socket.
# This will significantly increase performance for large transfers.
net.core.wmem_default = 25165824
net.core.rmem_default = 25165824
# Maximum amount of memory allocated for the send and receive buffers for each socket.
# This will significantly increase performance for large transfers.
net.core.wmem_max = 25165824
net.core.rmem_max = 25165824
# In addition to the socket settings, the send and receive buffer sizes for
# TCP sockets must be set separately using the net.ipv4.tcp_wmem and net.ipv4.tcp_rmem parameters.
# These are set using three space-separated integers that specify the minimum, default, and maximum sizes, respectively.
# The maximum size cannot be larger than the values specified for all sockets using net.core.wmem_max and net.core.rmem_max.
# A reasonable setting is a 4 KiB minimum, 64 KiB default, and 2 MiB maximum buffer.
net.ipv4.tcp_wmem = 20480 12582912 25165824
net.ipv4.tcp_rmem = 20480 12582912 25165824
# Increase the maximum total buffer-space allocatable
# This is measured in units of pages (4096 bytes)
net.ipv4.tcp_mem = 65536 25165824 262144
net.ipv4.udp_mem = 65536 25165824 262144
# Minimum amount of memory allocated for the send and receive buffers for each socket.
net.ipv4.udp_wmem_min = 16384
net.ipv4.udp_rmem_min = 16384
# Enabling TCP window scaling by setting net.ipv4.tcp_window_scaling to 1 will allow
# clients to transfer data more efficiently, and allow that data to be buffered on the broker side.
net.ipv4.tcp_window_scaling = 1
# Increasing the value of net.ipv4.tcp_max_syn_backlog above the default of 1024 will allow
# a greater number of simultaneous connections to be accepted.
net.ipv4.tcp_max_syn_backlog = 10240
# Increasing the value of net.core.netdev_max_backlog to greater than the default of 1000
# can assist with bursts of network traffic, specifically when using multigigabit network connection speeds,
# by allowing more packets to be queued for the kernel to process them.
net.core.netdev_max_backlog = 65536
# Increase the maximum amount of option memory buffers
net.core.optmem_max = 25165824
# Number of times SYNACKs for passive TCP connection.
net.ipv4.tcp_synack_retries = 2
# Allowed local port range.
net.ipv4.ip_local_port_range = 2048 65535
# Protect Against TCP Time-Wait
# Default: net.ipv4.tcp_rfc1337 = 0
net.ipv4.tcp_rfc1337 = 1
# Decrease the time default value for tcp_fin_timeout connection
net.ipv4.tcp_fin_timeout = 15
# The maximum number of backlogged sockets.
# Default is 128.
net.core.somaxconn = 4096
# Turn on syncookies for SYN flood attack protection.
net.ipv4.tcp_syncookies = 1
# Avoid a smurf attack
net.ipv4.icmp_echo_ignore_broadcasts = 1
# Turn on protection for bad icmp error messages
net.ipv4.icmp_ignore_bogus_error_responses = 1
# Enable automatic window scaling.
# This will allow the TCP buffer to grow beyond its usual maximum of 64K if the latency justifies it.
net.ipv4.tcp_window_scaling = 1
# Turn on and log spoofed, source routed, and redirect packets
net.ipv4.conf.all.log_martians = 1
net.ipv4.conf.default.log_martians = 1
# Tells the kernel how many TCP sockets that are not attached to any
# user file handle to maintain. In case this number is exceeded,
# orphaned connections are immediately reset and a warning is printed.
# Default: net.ipv4.tcp_max_orphans = 65536
net.ipv4.tcp_max_orphans = 65536
# Do not cache metrics on closing connections
net.ipv4.tcp_no_metrics_save = 1
# Enable timestamps as defined in RFC1323:
# Default: net.ipv4.tcp_timestamps = 1
net.ipv4.tcp_timestamps = 1
# Enable select acknowledgments.
# Default: net.ipv4.tcp_sack = 1
net.ipv4.tcp_sack = 1
# Increase the tcp-time-wait buckets pool size to prevent simple DOS attacks.
# net.ipv4.tcp_tw_recycle has been removed from Linux 4.12. Use net.ipv4.tcp_tw_reuse instead.
net.ipv4.tcp_max_tw_buckets = 1440000
net.ipv4.tcp_tw_reuse = 1
# The accept_source_route option causes network interfaces to accept packets with the Strict Source Route (SSR) or Loose Source Routing (LSR) option set.
# The following setting will drop packets with the SSR or LSR option set.
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.default.accept_source_route = 0
# Turn on reverse path filtering
net.ipv4.conf.all.rp_filter = 1
net.ipv4.conf.default.rp_filter = 1
# Disable ICMP redirect acceptance
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.default.accept_redirects = 0
net.ipv4.conf.all.secure_redirects = 0
net.ipv4.conf.default.secure_redirects = 0
# Disables sending of all IPv4 ICMP redirected packets.
net.ipv4.conf.all.send_redirects = 0
net.ipv4.conf.default.send_redirects = 0
# Disable IP forwarding.
# IP forwarding is the ability for an operating system to accept incoming network packets on one interface,
# recognize that it is not meant for the system itself, but that it should be passed on to another network, and then forwards it accordingly.
net.ipv4.ip_forward = 0
# Disable IPv6
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
#############################################################################################
# Tweak kernel parameters
#############################################################################################
# Address Space Layout Randomization (ASLR) is a memory-protection process for operating systems that guards against buffer-overflow attacks.
# It helps to ensure that the memory addresses associated with running processes on systems are not predictable,
# thus flaws or vulnerabilities associated with these processes will be more difficult to exploit.
# Accepted values: 0 = Disabled, 1 = Conservative Randomization, 2 = Full Randomization
kernel.randomize_va_space = 2
# Allow for more PIDs (to reduce rollover problems)
kernel.pid_max = 65536
EOF
5.1.10. Reload all sysctl variables without rebooting the server.
sudo sysctl -p /etc/sysctl.d/00-sysctl.conf
5.1.11. Create Local DNS records.
cat <<"EOF" | sudo tee /etc/hosts > /dev/null
# localhost
127.0.0.1 localhost localhost.localdomain
# When DNS records are updated in the DNS server, remove these entries.
192.168.15.101 db-1 db-1.example.local
192.168.15.102 db-2 db-2.example.local
192.168.15.103 db-3 db-3.example.local
EOF
5.1.12. The servers need to be restarted before continuing further.
sudo reboot
5.1.13. Install the HAProxy package.
# Install the HAProxy package
sudo yum install -y haproxy
5.1.14. Configure HAProxy.
cat <<"EOF" | sudo tee /etc/haproxy/haproxy.cfg > /dev/null
global
log 127.0.0.1 local2
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon
# turn on stats unix socket
stats socket /var/lib/haproxy/stats
#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
mode http
log global
option httplog
option dontlognull
option http-server-close
option forwardfor except 127.0.0.0/8
option redispatch
retries 3
timeout http-request 10s
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout http-keep-alive 10s
timeout check 10s
maxconn 3000
#---------------------------------------------------------------------
# haproxy monitor
#---------------------------------------------------------------------
listen monitor
bind 0.0.0.0:80
mode http
monitor-uri /health
option httplog
# enable statistics
stats enable
stats uri /stats
stats refresh 15s
stats show-legends
stats show-node
#---------------------------------------------------------------------
# leastconn balancing between mariadb backends
#---------------------------------------------------------------------
listen mariadb-cluster
bind 0.0.0.0:3306
mode tcp
balance leastconn
timeout client 5m
timeout server 5m
option httpchk
option tcplog
server db-1 db-1.example.local:3306 check port 9200 inter 5000 fastinter 2000 rise 2 fall 2
server db-2 db-2.example.local:3306 check port 9200 inter 5000 fastinter 2000 rise 2 fall 2
server db-3 db-3.example.local:3306 check port 9200 inter 5000 fastinter 2000 rise 2 fall 2
EOF
5.1.15. Start and enable haproxy service.
# Start and enable haproxy service
sudo systemctl enable --now haproxy
# Verify if the service is started
sudo systemctl status haproxy
5.1.16. Open necessary firewall ports.
sudo firewall-cmd --permanent --add-port={80,3306}/tcp
sudo firewall-cmd --reload
5.1.17. Install Keepalived package.
# Install Keepalived package
sudo yum install -y keepalived
5.1.18. Allow VRRP traffic on the firewall.
# Allow VRRP traffic on the firewall
sudo firewall-cmd --permanent --add-rich-rule='rule protocol value="vrrp" accept'
sudo firewall-cmd --reload
5.1.19. Create the health check script under /usr/libexec/keepalived
# Create the health check script
cat <<"EOF" | sudo tee /usr/libexec/keepalived/haproxy-health > /dev/null
#!/bin/bash
if [ $# -ne 1 ];then
echo "WARNING: You must provide health check URL"
exit 1
else
CHECK_URL=$1
CMD=$(/usr/bin/curl -k -I ${CHECK_URL} 2>/dev/null | grep "200 OK" | wc -l)
if [ ${CMD} -eq 1 ];then
exit 0
else
exit 1
fi
fi
EOF
5.1.20. Provide necessary permissions to the health check script.
# Make the health check script executable
sudo chmod +x /usr/libexec/keepalived/haproxy-health
# Restore SELinux context
sudo restorecon -RvF /usr/libexec/keepalived
5.2. Configure Keepalived on LB-1 node.
5.2.1. Configure keepalived on LB-1 node.
cat <<"EOF" | sudo tee /etc/keepalived/keepalived.conf > /dev/null
# Global definitions configuration block
global_defs {
router_id LVS_LB
}
vrrp_script check_haproxy_health {
script "/usr/libexec/keepalived/haproxy-health http://localhost/health"
interval 3
}
vrrp_instance VI_1 {
# Can be the same on both instances, whichever starts first will be the master, or choose MASTER/BACKUP
state MASTER
# Needs to be the same on both instances, and needs to be unique if using multicast, does not matter with unicast
virtual_router_id 100
# Can be the same on both instances unless using MASTER/BACKUP then the bigger number is for the master
priority 100
# Transit from the MASTER to the BACKUP is triggered when the BACKUP server doesn't receive the VRRP advertisement
# from the MASTER for 3x the period defined in the "advert_int"
advert_int 1
# Don't failback
nopreempt
# Please make sure to verify interface name using 'nmcli connection show'
interface ens192
# Unicast specific option, this is the IP of the interface keepalived listens on
unicast_src_ip 192.168.15.104
# Unicast specific option, this is the IP of the peer instance
unicast_peer {
192.168.15.105
}
virtual_ipaddress {
# Floating IP
192.168.15.100
}
track_interface {
# Please make sure to verify interface name using 'nmcli connection show'
ens192
}
track_script {
check_haproxy_health
}
}
EOF
5.2.2. Start and enable keepalived.service.
# Start and enable vault.service
sudo systemctl enable --now keepalived.service
# Verify if the service is started
sudo systemctl status keepalived.service
5.2.3. Check if the floating IP is assigned to a node.
# Run this command on ALL nodes. One node should show it is assigned
sudo ip addr | grep 192.168.15.100
5.3. Configure Keepalived on LB-2 node.
5.3.1. Configure keepalived on LB-2 node.
cat <<"EOF" | sudo tee /etc/keepalived/keepalived.conf > /dev/null
# Global definitions configuration block
global_defs {
router_id LVS_LB
}
vrrp_script check_haproxy_health {
script "/usr/libexec/keepalived/haproxy-health http://localhost/health"
interval 3
}
vrrp_instance VI_1 {
# Can be the same on both instances, whichever starts first will be the master, or choose MASTER/BACKUP
state MASTER
# Needs to be the same on both instances, and needs to be unique if using multicast, does not matter with unicast
virtual_router_id 100
# Can be the same on both instances unless using MASTER/BACKUP then the bigger number is for the master
priority 100
# Transit from the MASTER to the BACKUP is triggered when the BACKUP server doesn't receive the VRRP advertisement
# from the MASTER for 3x the period defined in the "advert_int"
advert_int 1
# Don't failback
nopreempt
# Please make sure to verify interface name using 'nmcli connection show'
interface ens192
# Unicast specific option, this is the IP of the interface keepalived listens on
unicast_src_ip 192.168.15.105
# Unicast specific option, this is the IP of the peer instance
unicast_peer {
192.168.15.104
}
virtual_ipaddress {
# Floating IP
192.168.15.100
}
track_interface {
# Please make sure to verify interface name using 'nmcli connection show'
ens192
}
track_script {
check_haproxy_health
}
}
EOF
5.3.2. Start and enable keepalived.service.
# Start and enable vault.service
sudo systemctl enable --now keepalived.service
# Verify if the service is started
sudo systemctl status keepalived.service
5.3.3. Check if the floating IP is assigned to a node.
# Run this command on ALL nodes. One node should show it is assigned
sudo ip addr | grep 192.168.15.100
6. References
- Deploy a Galera Cluster Node with MariaDB Community Server 10.5
- File Key Management Encryption Plugin
- Maria Backup SST Method
- MariaDB Tables Encryption
- Full MariaDB Encryption At-Rest and In-Transit for Maximum Data Protection - Part One
- Full MariaDB Encryption At-Rest and In-Transit for Maximum Data Protection - Part Two
- MariaDB Firewall