MySQL High Availability and Read Replicas

New MySQL databases run as a MySQL InnoDB Cluster. MySQL Group Replication keeps a group of servers in step, MySQL Router sends each connection to the right server, and optional read replicas take read traffic. This page covers how these databases behave and what they expect from your application.

MySQL databases created before InnoDB Cluster became the default keep their original setup, described in MySQL.

At a Glance

Every server, in the group or a read replica, keeps its own copy of the data and is billed at the database's plan rate.

Supported Versions

• MySQL 8.4 (LTS): the long-term support release, and the default.
• MySQL 9.7 (Innovation): newer features, with a shorter support window than an LTS release.

You choose the version when you create the database.

Connecting

The Connectivity tab shows the endpoints and credentials.

• Writer endpoint: always reaches the current primary, including after a failover. Use it for writes, and for reads that must see the latest data.
• Reader endpoint: appears once the database has read replicas. It spreads connections across the read replicas only, never the primary or the standbys. If no read replica is running, it refuses connections rather than sending your reads to the primary. It is an internal hostname (…svc.cluster.local) with no public equivalent.

Encryption

Connections are encrypted when your client asks for it. Use --ssl-mode=REQUIRED, or your driver's equivalent, so that a connection is never made without encryption:

mysql -h <writer-host> -P <port> -u <user> -p --ssl-mode=REQUIRED

The servers present self-signed certificates, so certificate verification (VERIFY_CA, VERIFY_IDENTITY) is not available.

Table Requirements

Group Replication runs on every one of these databases, single-server ones included. It only replicates tables that have a primary key and use the InnoDB storage engine. The database enforces both for you:

• A table created without a primary key gets an invisible one. MySQL adds a my_row_id column as its primary key. SELECT * and your application never see it; SHOW CREATE TABLE shows it.

• MyISAM, BLACKHOLE, FEDERATED and ARCHIVE tables cannot be created:

  textCopy

  ERROR 3161 (HY000): Storage engine MyISAM is disabled (Table creation is disallowed).
  

  Create the table without an ENGINE clause, or with ENGINE=InnoDB.

• MEMORY tables: temporary ones (CREATE TEMPORARY TABLE ... ENGINE=MEMORY) work. A permanent MEMORY table can be created, but writes to it fail:

  textCopy

  ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.
  

  Use InnoDB for permanent tables.

Before you import a dump, move MyISAM tables in your source database to InnoDB, or the import stops at their CREATE TABLE:

-- Tables in your source database that are not on InnoDB
SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND engine <> 'InnoDB'
  AND table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema');

-- Move one to InnoDB
ALTER TABLE legacy_table ENGINE = InnoDB;

To replace a table's invisible key with a primary key of your own, drop my_row_id in the same statement:

-- An existing column becomes the primary key
ALTER TABLE events DROP COLUMN my_row_id, ADD PRIMARY KEY (event_id);

-- Or a new surrogate key
ALTER TABLE events DROP COLUMN my_row_id, ADD COLUMN id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;

Transaction Size

Every transaction is sent to the whole group before it commits, so the changes a single transaction makes are capped. The cap is 2.5% of the plan's memory, between 16 MB and 150 MB: about 26 MB with 1 GB of memory, 51 MB with 2 GB, 102 MB with 4 GB, and 150 MB from 6 GB up. A larger transaction is rolled back with:

ERROR 3100 (HY000): Error on observer while running replication hook 'before_commit'.

Split large imports, UPDATEs and DELETEs into batches that commit separately. The mysql client commits each statement of a dump on its own, unless the dump turns autocommit off (as mysqldump --no-autocommit does).

High Availability

With high availability, the group has three servers, each on a different host: a primary that takes writes and two standbys that follow it. A transaction commits only once a majority of the group has it, so a committed write survives the loss of any one server.

Failover

If the primary or its host fails, the standbys elect a new primary and the writer endpoint follows it automatically. When the primary leaves cleanly, for example during a restart, writes pause for about a second. Connections to the old primary are dropped, and a transaction in flight at that moment may not have committed. Have your application reconnect and retry after a lost connection, as it would after any network interruption.

Turning High Availability On or Off

Open the database's Nodes tab and choose Turn on high availability or Turn off high availability. The database must be running, and the change takes a few minutes.

• Turning it on adds two standbys, each on its own host. They copy the data from the primary before they join, so a larger database takes longer. Each standby is billed at the same rate as the primary.
• Turning it off removes both standbys and leaves one server without automatic failover. If the primary is one of the servers leaving, writes pause for about a second while another member takes over.

Read replicas and data are not affected either way. The Nodes tab lists the standbys as Standby. They exist for failover, not for reads: to spread reads, add read replicas.

Read Replicas

Read replicas are asynchronous copies of the data for read-heavy workloads. You add and remove them on the Nodes tab or through the API, with or without high availability.

• Eventually consistent. A read replica applies each change shortly after the primary commits it, and can fall behind under heavy writes. Read your own writes from the writer endpoint.
• Lag is visible. The Nodes tab and the API show each read replica's replication status and how many seconds it is behind the primary.
• Never promoted. A read replica never becomes the primary; failover happens only within the group.
• Removing a read replica deletes its data.

Plan and Storage Changes

• Plan changes (CPU and memory) restart the servers one at a time. With high availability the group keeps serving, and writes pause briefly when the primary restarts. A single-server database is unavailable while its server restarts.
• Storage grows on every server without a restart, and storage autoscaling works as it does for other databases.

Backups and Restore

• Automated backups are logical dumps made with MySQL Shell, once a day between 00:00 and 05:00 UTC. A backup policy can set their time, weekdays and retention. Without one they are kept for 30 days, and the newest completed backup is always kept.
• Manual backups can be taken at any time from the Backups tab.
• Restore creates a new database from a backup and leaves the original as it is. Your own accounts and their grants are restored; the platform's internal accounts are created fresh.
• Tables that break the table requirements are fixed in the restored copy: a table on any other engine becomes InnoDB, and a table without a primary key gets an invisible my_row_id key. The restored database therefore accepts writes to them.
• Point-in-time recovery is not available for MySQL.

Configuration

On the Parameters tab, these settings change without a restart and apply to every server at once, including servers added later: connect_timeout, wait_timeout, interactive_timeout, long_query_time and slow_query_log. Settings that need a restart cannot be changed on these databases yet.

Memory settings are sized from the plan. Settings that Group Replication depends on are managed by the platform: server identity, GTIDs, binary logging, TLS and every group_replication_* variable. A parameter group cannot override either kind.

Limits

• Names are at most 22 characters and cannot end in -ro, -readers or -instances. A database cannot be renamed.
• Up to 5 read replicas per database by default; see Account Limits.
• Stopping a database stops every server in it; starting it brings them all back.
• Version updates from the Updates tab are not available yet: a database keeps the version it was created with.

Next Steps

• MySQL
• Database Backups
• Backups & Recovery
• Parameter Groups