Set up MariaDB Galera Cluster 11.6 for multi-master replication with automatic failover

#!/usr/bin/env bash

set -euo pipefail

# Colors
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

# Default values
NODE_ADDRESS=""
NODE_NAME=""
CLUSTER_ADDRESSES=""
ROOT_PASSWORD=""

# Usage message
usage() {
    cat << EOF
Usage: $0 --node-address=IP --node-name=NAME --cluster-addresses=IP1,IP2,IP3 --root-password=PASS
  --node-address      This node's IP address
  --node-name         This node's name (e.g., galera-node-1)
  --cluster-addresses Comma-separated list of all cluster node IPs
  --root-password     MariaDB root password
EOF
    exit 1
}

# Parse arguments
for arg in "$@"; do
    case $arg in
        --node-address=*) NODE_ADDRESS="${arg#*=}" ;;
        --node-name=*) NODE_NAME="${arg#*=}" ;;
        --cluster-addresses=*) CLUSTER_ADDRESSES="${arg#*=}" ;;
        --root-password=*) ROOT_PASSWORD="${arg#*=}" ;;
        *) usage ;;
    esac
done

[[ -z "$NODE_ADDRESS" || -z "$NODE_NAME" || -z "$CLUSTER_ADDRESSES" || -z "$ROOT_PASSWORD" ]] && usage

# Error handling
cleanup() {
    echo -e "${RED}[ERROR] Installation failed. Cleaning up...${NC}"
    systemctl stop mariadb 2>/dev/null || true
    systemctl disable mariadb 2>/dev/null || true
}
trap cleanup ERR

# Check prerequisites
echo -e "${YELLOW}[0/8] Checking prerequisites...${NC}"
[[ $EUID -eq 0 ]] || { echo -e "${RED}This script must be run as root${NC}"; exit 1; }

# Detect distro
if [[ ! -f /etc/os-release ]]; then
    echo -e "${RED}Cannot detect OS distribution${NC}"
    exit 1
fi

. /etc/os-release
case "$ID" in
    ubuntu|debian)
        PKG_MGR="apt"
        PKG_UPDATE="apt update -y"
        PKG_INSTALL="apt install -y"
        FIREWALL_CMD="ufw"
        GALERA_LIB="/usr/lib/galera/libgalera_smm.so"
        ;;
    almalinux|rocky|centos|rhel|ol)
        PKG_MGR="dnf"
        PKG_UPDATE="dnf update -y"
        PKG_INSTALL="dnf install -y"
        FIREWALL_CMD="firewall-cmd"
        GALERA_LIB="/usr/lib64/galera-4/libgalera_smm.so"
        ;;
    fedora)
        PKG_MGR="dnf"
        PKG_UPDATE="dnf update -y"
        PKG_INSTALL="dnf install -y"
        FIREWALL_CMD="firewall-cmd"
        GALERA_LIB="/usr/lib64/galera/libgalera_smm.so"
        ;;
    amzn)
        PKG_MGR="yum"
        PKG_UPDATE="yum update -y"
        PKG_INSTALL="yum install -y"
        FIREWALL_CMD="firewall-cmd"
        GALERA_LIB="/usr/lib64/galera-4/libgalera_smm.so"
        ;;
    *)
        echo -e "${RED}Unsupported distribution: $ID${NC}"
        exit 1
        ;;
esac

# Step 1: Update system
echo -e "${GREEN}[1/8] Updating system packages...${NC}"
$PKG_UPDATE

# Install base dependencies
if [[ "$PKG_MGR" == "apt" ]]; then
    $PKG_INSTALL software-properties-common dirmngr apt-transport-https lsb-release ca-certificates wget curl
else
    $PKG_INSTALL wget curl epel-release which
fi

# Step 2: Add MariaDB repository
echo -e "${GREEN}[2/8] Adding MariaDB repository...${NC}"
curl -o /tmp/mariadb_repo_setup https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
echo "367ad0a16085c59d9a78c9d1ba3c8e9fac4a7b9096a93bbbc626bffbdcb3e04c /tmp/mariadb_repo_setup" | sha256sum -c -
bash /tmp/mariadb_repo_setup --mariadb-server-version="mariadb-11.6"

# Step 3: Install MariaDB Galera packages
echo -e "${GREEN}[3/8] Installing MariaDB Galera packages...${NC}"
if [[ "$PKG_MGR" == "apt" ]]; then
    apt update
    $PKG_INSTALL mariadb-server galera-4 mariadb-client mariadb-backup socat rsync
else
    $PKG_INSTALL MariaDB-server MariaDB-client galera-4 MariaDB-backup socat rsync
fi

# Step 4: Stop and configure service
echo -e "${GREEN}[4/8] Stopping MariaDB service...${NC}"
systemctl stop mariadb || true
systemctl disable mariadb

# Step 5: Configure Galera cluster
echo -e "${GREEN}[5/8] Configuring Galera cluster...${NC}"
mkdir -p /etc/mysql/conf.d

# Convert comma-separated addresses to gcomm format
GCOMM_ADDRESSES=$(echo "$CLUSTER_ADDRESSES" | sed 's/,/,/g')

cat > /etc/mysql/conf.d/galera.cnf << EOF
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_cluster_name="production_cluster"
wsrep_cluster_address="gcomm://$GCOMM_ADDRESSES"
wsrep_node_address="$NODE_ADDRESS"
wsrep_node_name="$NODE_NAME"
wsrep_provider=$GALERA_LIB

# Replication settings
wsrep_sst_method=rsync
wsrep_slave_threads=4
wsrep_certify_nonPK=1
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_retry_autocommit=1
wsrep_auto_increment_control=1

# Binary logging
log-bin=mysql-bin
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

# Network and security
bind-address=0.0.0.0
max_connections=500
max_allowed_packet=64M

# Performance optimization
innodb_buffer_pool_size=1G
innodb_log_file_size=256M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
EOF

chown mysql:mysql /etc/mysql/conf.d/galera.cnf
chmod 644 /etc/mysql/conf.d/galera.cnf

# Step 6: Configure SSL
echo -e "${GREEN}[6/8] Configuring SSL encryption...${NC}"
mkdir -p /etc/mysql/ssl
openssl genrsa -out /etc/mysql/ssl/ca-key.pem 4096
openssl req -new -x509 -nodes -days 365000 -key /etc/mysql/ssl/ca-key.pem -out /etc/mysql/ssl/ca-cert.pem -subj "/CN=galera-ca"
openssl req -newkey rsa:4096 -days 365000 -nodes -keyout /etc/mysql/ssl/server-key.pem -out /etc/mysql/ssl/server-req.pem -subj "/CN=galera-server"
openssl x509 -req -in /etc/mysql/ssl/server-req.pem -days 365000 -CA /etc/mysql/ssl/ca-cert.pem -CAkey /etc/mysql/ssl/ca-key.pem -set_serial 01 -out /etc/mysql/ssl/server-cert.pem
chown -R mysql:mysql /etc/mysql/ssl
chmod 750 /etc/mysql/ssl
chmod 640 /etc/mysql/ssl/*

# Step 7: Configure firewall
echo -e "${GREEN}[7/8] Configuring firewall...${NC}"
if [[ "$FIREWALL_CMD" == "ufw" ]]; then
    ufw allow 3306/tcp  # MariaDB
    ufw allow 4444/tcp  # SST
    ufw allow 4567/tcp  # Galera
    ufw allow 4568/tcp  # IST
elif [[ "$FIREWALL_CMD" == "firewall-cmd" ]] && systemctl is-active --quiet firewalld; then
    firewall-cmd --permanent --add-port=3306/tcp
    firewall-cmd --permanent --add-port=4444/tcp
    firewall-cmd --permanent --add-port=4567/tcp
    firewall-cmd --permanent --add-port=4568/tcp
    firewall-cmd --reload
fi

# Configure SELinux if present
if command -v getenforce >/dev/null 2>&1 && [[ "$(getenforce)" != "Disabled" ]]; then
    setsebool -P mysql_connect_any 1
    semanage port -a -t mysqld_port_t -p tcp 4444 2>/dev/null || true
    semanage port -a -t mysqld_port_t -p tcp 4567 2>/dev/null || true
    semanage port -a -t mysqld_port_t -p tcp 4568 2>/dev/null || true
fi

# Step 8: Initialize cluster
echo -e "${GREEN}[8/8] Initializing cluster...${NC}"
echo -e "${YELLOW}Starting bootstrap on first node (if this is node 1)...${NC}"

# Start with bootstrap for first node setup
galera_new_cluster || {
    echo -e "${YELLOW}Bootstrap failed, trying regular start...${NC}"
    systemctl start mariadb
}

# Secure installation
mysql -e "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('$ROOT_PASSWORD');"
mysql -e "DELETE FROM mysql.user WHERE User='';"
mysql -e "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');"
mysql -e "DROP DATABASE IF EXISTS test;"
mysql -e "DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';"
mysql -e "FLUSH PRIVILEGES;"

systemctl enable mariadb

# Verification
echo -e "${GREEN}Installation completed successfully!${NC}"
echo -e "${YELLOW}Verifying cluster status...${NC}"

if mysql -uroot -p"$ROOT_PASSWORD" -e "SHOW STATUS LIKE 'wsrep_cluster_size';" 2>/dev/null; then
    echo -e "${GREEN}✓ MariaDB Galera cluster is running${NC}"
    echo -e "${YELLOW}Cluster status:${NC}"
    mysql -uroot -p"$ROOT_PASSWORD" -e "SHOW STATUS LIKE 'wsrep_%';" | grep -E "(cluster_size|ready|connected)"
else
    echo -e "${RED}✗ Failed to verify cluster status${NC}"
    exit 1
fi

echo -e "${GREEN}MariaDB Galera Cluster installation completed!${NC}"
echo -e "${YELLOW}Next steps:${NC}"
echo "1. Install and configure this script on other cluster nodes"
echo "2. For additional nodes, use: systemctl start mariadb (not galera_new_cluster)"
echo "3. Monitor cluster with: mysql -uroot -p'$ROOT_PASSWORD' -e \"SHOW STATUS LIKE 'wsrep_cluster_size';\""