Automating MySQL Cluster Failover with Bash Script on Ubuntu
High availability is critical in modern database management systems. MySQL clustering provides fault tolerance, but automating failover is essential to ensure minimal downtime during a node failure. This tutorial walks you through creating a Bash script to automate MySQL cluster failover by detecting node failures and promoting a secondary node to primary.
Step-by-Step Guide
Step 1: Prerequisites
MySQL Cluster Setup:
A MySQL cluster with one primary and at least one secondary node.
Replication must be configured between nodes.
Install Necessary Tools:
Install mysql client:
sudo apt install mysql-client
Ensure SSH access between nodes for remote commands.
Step 2: Plan the Failover Process
Primary Node Monitoring:
Ping the primary node to ensure it’s reachable.
Secondary Node Promotion:
Promote a secondary node to primary using RESET SLAVE and RESET MASTER.
Reconfigure Replication:
Update other secondary nodes to replicate from the new primary.
Notification:
Send an alert when failover occurs.
Step 3: Create the Bash Script
Below is the Bash script for automating the failover process.
#!/bin/bash
# Configuration
PRIMARY_NODE="192.168.1.1"
SECONDARY_NODES=("192.168.1.2" "192.168.1.3")
MYSQL_USER="admin"
MYSQL_PASS="password"
ALERT_EMAIL="your_email@example.com"
# Function to check node availability
check_node() {
local node_ip=$1
ping -c 1 $node_ip > /dev/null 2>&1
if [[ $? -eq 0 ]]; then
return 0 # Node is reachable
else
return 1 # Node is unreachable
fi
}
# Function to promote a secondary node to primary
promote_secondary() {
local new_primary=$1
echo "Promoting $new_primary to primary..."
mysql -h $new_primary -u $MYSQL_USER -p$MYSQL_PASS -e "STOP SLAVE; RESET SLAVE ALL; RESET MASTER;"
if [[ $? -eq 0 ]]; then
echo "Node $new_primary promoted to primary successfully."
else
echo "Failed to promote $new_primary to primary."
exit 1
fi
}
# Function to reconfigure secondary nodes
reconfigure_secondaries() {
local new_primary=$1
echo "Reconfiguring secondary nodes..."
for node in "${SECONDARY_NODES[@]}"; do
if [[ $node != $new_primary ]]; then
mysql -h $node -u $MYSQL_USER -p$MYSQL_PASS -e "STOP SLAVE; CHANGE MASTER TO MASTER_HOST='$new_primary', MASTER_USER='$MYSQL_USER', MASTER_PASSWORD='$MYSQL_PASS'; START SLAVE;"
if [[ $? -eq 0 ]]; then
echo "Node $node configured to replicate from $new_primary."
else
echo "Failed to configure $node."
fi
fi
done
}
# Main failover process
while true; do
echo "Checking primary node ($PRIMARY_NODE)..."
if ! check_node $PRIMARY_NODE; then
echo "Primary node $PRIMARY_NODE is down. Initiating failover..."
for secondary in "${SECONDARY_NODES[@]}"; do
if check_node $secondary; then
promote_secondary $secondary
reconfigure_secondaries $secondary
echo "Failover completed. New primary is $secondary."
# Send alert
echo -e "Subject: MySQL Failover Alert\n\nPrimary node $PRIMARY_NODE is down. Failover completed. New primary: $secondary" | sendmail $ALERT_EMAIL
# Update primary node
PRIMARY_NODE=$secondary
break
fi
done
else
echo "Primary node $PRIMARY_NODE is healthy."
fi
# Wait before the next check
sleep 30
done
Step 4: Save and Run the Script
Save the script as mysql_cluster_failover.sh.
Make it executable:
chmod +x mysql_cluster_failover.sh
Run the script:
./mysql_cluster_failover.sh
Step 5: Test the Failover
Simulate Primary Node Failure:
Stop MySQL on the primary node:
sudo systemctl stop mysql
Monitor the Script:
Verify the script detects the failure, promotes a secondary node, and reconfigures the cluster.
Check Notifications:
Ensure you receive an email alert indicating the failover process.
Step 6: Logs and Alerts
Check logs for detailed failover information.
Verify secondary nodes are replicating correctly by connecting to each node and running:
SHOW SLAVE STATUS\G
This Bash script automates MySQL cluster failover, minimizing downtime during node failures. For production environments, consider integrating this script with monitoring tools like Nagios or Prometheus for enhanced reliability. Hope this is helpful, and I apologize if there are any inaccuracies in the information provided.
Comments
Post a Comment