MySQL InnoDB Cluster (Group Replication + MySQL Router)

Builds a single-primary InnoDB Cluster from 3 (or 5–9) installed MySQL servers in the same data center: dba.configureInstance, createCluster and addInstance with clone through the MySQL Shell AdminAPI; MySQL Router bootstrap (6446 RW / 6447 RO), cluster.status() monitoring, a failover test, and runbooks for quorum loss, complete outage and rolling restart. The result is a cluster that fails over automatically when the primary goes down, with the application connecting through Router. For a remote site or simple read scaling use classic replication; for day-to-day operation of an existing cluster use the InnoDB Cluster operations guide.

Topology: 3–9 MySQL nodes + 1 Router server

Before you start

• MySQL … is installed on all nodes at the same minor version (DBAtlas MySQL single-server installation guide), GTID is enabled, and each node has a different server_id. The operating system root can connect with mysql without a password.
• Nodes 2..… hold no data that must be kept: when they join the cluster they receive a clone of node 1 (…) and their existing data is deleted.
• All tables on node 1 are InnoDB and have a primary key (a Group Replication requirement). The check query must return no rows.
• The nodes reach each other on ports 3306 (MySQL and Group Replication communication), 33060 (X Protocol) and 33061; the Router server reaches the nodes on 3306/33060. Latency is low (same data center; < 1–2 ms).
• Clocks are synchronized (chrony).
• The node count is odd (…): … nodes tolerate the loss of at most … node(s). An even number adds no tolerance.
• A strong password is ready for the cluster administrator account (…) (at least 8 characters, upper/lower case, digits, special characters). MySQL Shell and Router prompt for the password in the steps without echoing it; Shell is told not to store it.
• The Router server can reach repo.mysql.com (the MySQL repository is added in this guide).

Phases of the guide

• Preparation (all MySQL nodes) — Open the cluster ports in the firewall · Register the Group Replication port with SELinux · Add the cluster settings (…) · Restart MySQL · Install MySQL Shell
• Instance configuration (dba.configureInstance) — Create the cluster administrator account (…) · Check the instances remotely
• Create the cluster — Create the cluster · Add the node to the cluster (…) · Check the cluster status
• MySQL Router — Add the MySQL repository (Router server) · Install Router and Shell · Bootstrap Router · Start the Router service · Open the Router ports in the firewall · Test the connection through Router
• Verification and monitoring — Detailed status · Monitor the members with SQL
• Failover test — Failover: stop the primary · Failover: stop the primary · Failover: see the new primary · Failover: see the new primary · Failover: is Router routing to the new primary · Failover: is Router routing to the new primary …
• Loss of quorum and complete outage — Quorum: diagnose the state · Quorum: diagnose the state · Quorum: force quorum with the surviving partition · Quorum: force quorum with the surviving partition · Quorum: stop the old group on the returning node (…) · Quorum: stop the old group on the returning node (…) …
• Rolling restart (maintenance) — Rolling: restart a secondary (…) · Rolling: restart a secondary (…) · Rolling: hand over the primary role · Rolling: hand over the primary role · Rolling: restart the old primary · Rolling: restart the old primary …