无法使用预览版Azure PaaS，如何实现Azure VM上MySQL Community Edition高可用与容错？

Got it, let's break down exactly how to set up a highly available, fault-tolerant MySQL Community Edition deployment on Azure VMs—since you can't leverage the preview PaaS option. I've implemented this setup for several production workloads, so here's a practical, step-by-step guide:

First, get your infrastructure right to avoid single points of failure:

• VM Count & Placement: Use at least 3 VMs (MySQL Group Replication needs an odd number for quorum). Deploy them in either an Availability Set (for hardware-level fault tolerance within a data center) or across Availability Zones (for cross-data-center resilience, ideal for critical workloads).
• VM Configuration: Pick Standard_DS3_v2 or higher for enough CPU/memory, and use Premium SSD storage (Premium_LRS) to ensure low-latency I/O for database operations.
• Networking: Keep all VMs in the same VNet. Set up an Internal Load Balancer (ILB) as the frontend entry point for your app. Lock down NSGs to only allow traffic on MySQL's 3306 port and SSH (22) for admin access.

Stick to MySQL 8.0+ (Group Replication is far more stable here) and use a Linux distro with solid MySQL support, like Ubuntu 22.04 or RHEL 8:

• For Ubuntu, install via the official repo:

  sudo apt update
  sudo apt install mysql-server -y
  

• After installation, set a strong root password, then verify the service is running with sudo systemctl status mysql.

MySQL Group Replication is the native HA solution for Community Edition—it handles automatic failover and data consistency out of the box.

3.1 Base Configuration for All Nodes #

Edit your MySQL config file (/etc/mysql/mysql.conf.d/mysqld.cnf for Ubuntu, /etc/my.cnf for RHEL) to add these settings (adjust values for each node):

# Unique server ID for each node (e.g., 1, 2, 3)
server-id = 1
datadir = /var/lib/mysql

# Replication prerequisites
binlog_format = ROW
log_bin = /var/log/mysql/mysql-bin.log
relay_log = /var/log/mysql/relay-bin.log
log_slave_updates = ON
gtid_mode = ON
enforce_gtid_consistency = ON

# Group Replication settings
plugin_load_add = group_replication.so
# Generate a unique UUID with `uuidgen` command
group_replication_group_name = "a1b2c3d4-5678-90ef-ghij-klmnopqrstuv"
group_replication_start_on_boot = OFF
# Use the VM's private IP + 33061 port
group_replication_local_address = "10.0.0.4:33061"
# List all nodes' private IP:port
group_replication_group_seeds = "10.0.0.4:33061,10.0.0.5:33061,10.0.0.6:33061"
group_replication_bootstrap_group = OFF
# Use single-primary mode for most use cases (one write node, others read-only)
group_replication_single_primary_mode = ON
# Restrict to your VNet subnet
group_replication_ip_whitelist = "10.0.0.0/24"

Restart MySQL after updating: sudo systemctl restart mysql

3.2 Bootstrap the Cluster #

Pick one node as the initial primary, then run these SQL commands (log into MySQL with mysql -u root -p):

-- Disable binary logging temporarily to create the replication user
SET SQL_LOG_BIN=0;
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'your-strong-repl-password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

-- Configure recovery channel
CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='your-strong-repl-password' FOR CHANNEL 'group_replication_recovery';

-- Bootstrap the cluster
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

Verify the cluster is up with:

SELECT * FROM performance_schema.replication_group_members;

You should see this node listed as ONLINE.

3.3 Add Remaining Nodes to the Cluster #

On every other node, log into MySQL and run:

SET SQL_LOG_BIN=0;
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'your-strong-repl-password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='your-strong-repl-password' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

Run the replication_group_members query again—all nodes should show ONLINE.

Use MySQL Router to abstract the cluster from your app—it automatically detects the primary node and routes traffic appropriately:

• Install MySQL Router on a dedicated VM (or colocate it with your app tier):

  # Ubuntu example
  wget https://dev.mysql.com/get/mysql-apt-config_0.8.26-1_all.deb
  sudo dpkg -i mysql-apt-config_0.8.26-1_all.deb
  sudo apt update
  sudo apt install mysql-router -y
  

• Bootstrap the router with your cluster:

  mysqlrouter --bootstrap root@10.0.0.4:3306 --user=mysqlrouter
  

• Start the router service: sudo systemctl start mysqlrouter

The router will expose two key ports:

• 6446: Write traffic, routed exclusively to the primary node
• 6447: Read traffic, distributed across all secondary nodes

Point your Azure ILB to these ports, so your app only needs to connect to the ILB's static IP.

• Backup Strategy: Schedule regular backups with mysqldump or Percona XtraBackup, then upload them to Azure Blob Storage for offsite retention:

  mysqldump -u root -p --all-databases | gzip > mysql_backup_$(date +%Y%m%d).sql.gz
  az storage blob upload --account-name your-storage-account --container-name mysql-backups --file mysql_backup_$(date +%Y%m%d).sql.gz --name mysql_backup_$(date +%Y%m%d).sql.gz
  

• Monitoring: Use Azure Monitor to track VM CPU, memory, and disk usage. For MySQL-specific metrics, set up alerts on performance_schema tables (e.g., replication lag, node status).
• Disaster Recovery: For cross-region resilience, set up a read replica in a secondary Azure region, or replicate backups to a geo-redundant storage account.

Validate your setup by manually taking down the primary node:

sudo systemctl stop mysql

Wait 1-2 minutes, then check the cluster status on any secondary node. A new primary should be automatically elected. Test writing to the router's 6446 port—your app should continue working without manual intervention.

内容的提问来源于stack exchange，提问作者Himanshu Mohan