High availability practices for MySQL InnoDB Cluster-Elastic Compute Service(ECS)-阿里云帮助中心

A single MySQL database service has performance limitations and is vulnerable to a single point of failure, which may cause your business system to become unavailable. You can deploy a highly available MySQL InnoDB Cluster across multiple ECS instances and use a proxy service to enable flexible scheduling and read/write splitting, improving database availability and fault tolerance.

Architecture overview

Deploying your cluster across ECS servers in multiple zones reduces the risk of business downtime if machines in a single zone fail, enhancing system stability.

Proxy node: Handles client requests and forwards them to appropriate database nodes. These proxy nodes can also be deployed across multiple zones to further improve high availability.
Primary node: Serves as the core data processing node in the cluster, handling all write operations and synchronizing data to other nodes.
Secondary node: Maintains consistency by replicating data from the primary node and supports read-only operations only. If the primary node fails, secondary nodes automatically elect a new primary to keep the cluster running.

Environment preparation

Create four ECS instances. This example uses Alibaba Cloud Linux 3 as the operating system.

Note	Instance	Public network access	VPC	Virtual switch (vSwitch)	IP address
Proxy node	ECS A	Enable public network access. After installation, disable public network access to improve security.	192.168.0.0/16	192.168.0.0/24	192.168.0.1
Primary node (read/write)	ECS B			192.168.1.0/24	192.168.1.1
Secondary node (read-only)	ECS C			192.168.2.0/24	192.168.2.1
Secondary node (read-only)	ECS D			192.168.2.0/24	192.168.2.2

Install MySQL on ECS B, ECS C, and ECS D. Ensure all instances run the same MySQL version. This example uses MySQL 8.4. If you need help installing MySQL, see Manually deploy a MySQL database (Linux).

Procedure

Step 1: Configure cluster nodes

Remotely connect to ECS B, ECS C, and ECS D. For details, see Log on to a Linux instance using Workbench.
Create a MySQL user for cluster communication on ECS B, ECS C, and ECS D. Use the same username and password on all nodes.
Important
- Replace <username> in the code with the MySQL username you create.
- Replace <password> in the code with the password for the MySQL user. The password must follow this policy: include at least one uppercase letter, one lowercase letter, one digit, and one special character, and be at least 8 characters long.
```
# You will be prompted for the root password
sudo mysql -uroot -p \
-e "CREATE USER '<username>'@'%' IDENTIFIED BY '<password>';" \
-e "GRANT ALL PRIVILEGES ON *.* TO '<username>'@'%' WITH GRANT OPTION;" \
-e "FLUSH PRIVILEGES;"
```

Add the following content to the MySQL configuration file /etc/my.cnf on ECS B, ECS C, and ECS D.

# Set the hostname to the node's IP address
report_host=192.168.x.x
# Enable GTID strong consistency
enforce_gtid_consistency=ON
gtid_mode=ON
# Set a unique positive integer server ID for each node
server_id=1
# Set the maximum connections per node; ensure this value is consistent across all nodes
max_connections=1024
# Disable storage engines other than InnoDB
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

Restart the MySQL service on ECS B, ECS C, and ECS D to apply the configuration changes.
```
sudo systemctl restart mysqld
```

Step 2: Create the cluster

Remotely connect to ECS A. For details, see Log on to a Linux instance using Workbench.

Install mysql-shell and mysql-router on ECS A.

# Add the official MySQL repository
sudo rpm -Uvh https://repo.mysql.com/mysql84-community-release-el8-1.noarch.rpm
# Install mysql-shell, mysql-router, and mysql-community-client
sudo dnf install -y mysql-shell mysql-router mysql-community-client

Connect to the primary node using MySQL Shell. Replace <username> with your MySQL username and <IP> with the primary node’s IP address. You will be prompted for the password and asked whether to save it. If saved, you won’t need to enter it again for future connections.
```
mysqlsh --js <username>@<IP>:3306
```
After a successful connection, MySQL Shell displays a prompt like this:
```
 MySQL  xxx  3306  ssl  JS >
```
Create the cluster. Replace <cluster_name> with your desired cluster name.
```
var cluster = dba.createCluster('<cluster_name>');
```
Add secondary nodes to the cluster. Replace <username> with your MySQL username and <IP> with the secondary node’s IP address. Run this command for each secondary node.

Note
For example, to add a node with IP address 192.168.2.1 and username cluster_user, run: cluster.addInstance('cluster_user@192.168.2.1:3306');
```
cluster.addInstance('<username>@<IP>:3306');
```
If successful, you’ll see output like this:
```
The instance '<username>@<IP>:3306' was successfully added to the cluster.
```

Check the cluster status.

cluster.status();

The following output indicates that the cluster has been created.

{
    "clusterName": "testCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "xxx:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "xxx:3306": {
                "address": "xxx:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.4"
            },
            "xxx:3306": {
                "address": "xxx:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.4"
            },
            "xxx:3306": {
                "address": "xxx:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.4.4"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "192.168.1.1:3306"
}

Step 3: Configure the proxy node

Remotely connect to ECS A. For details, see Log on to a Linux instance using Workbench.
Run the following command to initialize the proxy configuration. Replace <username> with your MySQL username and <IP> with the primary node’s IP address. You will be prompted for the MySQL user password.

Note
By default, this command stores the initialization files in the directory /mnt/mysqlrouter.
```
sudo mysqlrouter --bootstrap <username>@<IP>:3306 --directory /mnt/mysqlrouter \
--conf-bind-address 0.0.0.0 --user=mysqlrouter
```

Edit the mysqlrouter service file.

Note

The command uses the directory /mnt/mysqlrouter by default. If you changed the directory, update the ExecStart and ExecStop paths accordingly.

sudo tee /usr/lib/systemd/system/mysqlrouter.service <<-'EOF'
[Unit]
Description=MySQL Router Service
After=network.target
[Service]
User=mysqlrouter
Group=mysqlrouter
Type=forking
ExecStart=/mnt/mysqlrouter/start.sh
ExecStop=/mnt/mysqlrouter/stop.sh
Restart=on-failure
StandardOutput=journal
[Install]
WantedBy=multi-user.target
EOF

Run the following commands to start the service.

# Reload the service configuration
sudo systemctl daemon-reload
# Start mysqlrouter
sudo systemctl start mysqlrouter.service
# Enable mysqlrouter to start on boot
sudo systemctl enable mysqlrouter.service
# Check the service status
sudo systemctl status mysqlrouter.service

Verify the cluster

Stop the primary node (ECS B) to simulate a failure and verify that the cluster automatically switches to another node and continues serving requests.

Remotely connect to ECS A. For details, see Log on to a Linux instance using Workbench.
Use the MySQL client to connect to the proxy node (ECS A). Replace <username> with your MySQL username.
```
mysql -h127.0.0.1 -P6450 -u<username> -p
```

Run an SQL statement to check the cluster status. The result shows three online nodes, with one acting as the primary.

SELECT * FROM performance_schema.replication_group_members;

| CHANNEL_NAME              | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
| group_replication_applier | xxx       | xxx         |        3306 | ONLINE       | SECONDARY   | 8.4.4          | MySQL                      |
| group_replication_applier | xxx       | xxx         |        3306 | ONLINE       | SECONDARY   | 8.4.4          | MySQL                      |
| group_replication_applier | xxx       | xxx         |        3306 | ONLINE       | PRIMARY     | 8.4.4          | MySQL                      |

Run SQL statements to create a test database and table.

-- 1. Create a database
CREATE DATABASE test_db;
-- 2. Use the database
USE test_db;
-- 3. Create a table: test_table
CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY, -- Auto-increment primary key
    name VARCHAR(100) NOT NULL,        -- Name
    value DECIMAL(10, 2) NOT NULL
);
-- 4. Insert records into test_table
INSERT INTO test_table (name, value) VALUES
('Item A', 100.50),
('Item B', 200.75);

Stop the primary node (ECS B) to simulate a failure.

After the primary node stops, run the SQL statement again to check the cluster status. The result shows two online nodes, with a secondary node automatically promoted to primary.

SELECT * FROM performance_schema.replication_group_members;

+----------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME               | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+----------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier  | xxx                                  | xxx         |        3306 | ONLINE       | PRIMARY     | 8.4.4          | MySQL                      |
| group_replication_applier  | xxx                                  | xxx         |        3306 | ONLINE       | SECONDARY   | 8.4.4          | MySQL                      |
+----------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+

Run an SQL statement to query the data and confirm no data loss occurred.
```
SELECT * FROM test_table;
```
Start the primary node (ECS B) to simulate recovery from failure.

Run an SQL statement to check the cluster status. The result shows three online nodes, with ECS B automatically rejoining the cluster.

SELECT * FROM performance_schema.replication_group_members;

| CHANNEL_NAME              | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
| group_replication_applier | xxx       | xxx.2       | 3306        | ONLINE       | PRIMARY     | 8.4.4          | MySQL                      |
| group_replication_applier | xxx       | xxx.1       | 3306        | ONLINE       | SECONDARY   | 8.4.4          | MySQL                      |
| group_replication_applier | xxx       | xxx.1       | 3306        | ONLINE       | SECONDARY   | 8.4.4          | MySQL                      |

Add a node to the cluster

In MySQL InnoDB Cluster, you can expand the cluster or recover from node failures by connecting to the cluster with MySQL Shell and using the addInstance command. New nodes automatically synchronize data.

Important

If more than half the cluster nodes fail, the cluster becomes unavailable. Use an odd number of nodes to ensure availability.
Before adding a node, install MySQL and complete steps 2, 3, and 4 in Step 1: Configure cluster nodes.

Remotely connect to ECS A. For details, see Log on to a Linux instance using Workbench.
Connect to any cluster node using MySQL Shell. Replace <username> with your MySQL username and <IP> with the node’s IP address. You will be prompted for the password and asked whether to save it. If saved, you won’t need to enter it again for future connections.
```
mysqlsh --js <username>@<IP>:3306
```
Get the cluster object.
```
var cluster = dba.getCluster();
```
Add a node to the cluster. Replace <username> with your MySQL username and <IP> with the node’s IP address.

Note
For example, to add a node with IP address 192.168.2.1 and username cluster_user, run: cluster.addInstance('cluster_user@192.168.2.1:3306');
```
cluster.addInstance('<username>@<IP>:3306');
```
If successful, you’ll see confirmation output.

Designate a primary node

In MySQL InnoDB Cluster, if the primary node fails, the system automatically elects a new primary from the remaining nodes. To manually designate a primary node, connect to the cluster with MySQL Shell and use the setPrimaryInstance command.

Remotely connect to ECS A. For details, see Log on to a Linux instance using Workbench.
Connect to any cluster node using MySQL Shell. Replace <username> with your MySQL username and <IP> with the node’s IP address. You will be prompted for the password and asked whether to save it. If saved, you won’t need to enter it again for future connections.
```
mysqlsh --js <username>@<IP>:3306
```
Get the cluster object.
```
var cluster = dba.getCluster();
```
Set the target primary node. Replace <username> with your MySQL username and <IP> with the node’s IP address.

Note
For example, to set a node with IP address 192.168.2.1 and username cluster_user as primary, run: cluster.setPrimaryInstance('cluster_user@192.168.2.1:3306');
```
cluster.setPrimaryInstance('<username>@<IP>:3306');
```
If successful, you’ll see output like this:
```
The instance 'xxx:3306' was successfully elected as primary.
```

References

For more information about MySQL InnoDB Cluster, see the MySQL official documentation.
We recommend using ApsaraDB RDS to simplify deployment and management. For more information, see What is RDS MySQL.
Monitor your cluster in real time for metrics such as CPU, memory, disk I/O, and network traffic to detect and resolve issues promptly. For more information, see What is Cloud Monitor.