Replication in Managed Service for MySQL®

Managed Service for MySQL® clusters use semi-sync replication: by default, the master waits for a transaction to be completed in at least one replica.

Managing replication proceduresManaging replication procedures

Recommended cluster configurationRecommended cluster configuration

Once you create a MySQL® cluster with multiple hosts, it contains one master host and replicas. Replicas use the master host as a replication source.

Here is an example of an auto replicated cluster configuration:

In this example, a master and two replicas are located in different availability zones. In which case:

• Each transaction is saved to a minimum of two hosts.
• The cluster remains available for two consecutive host failures in one of the availability zones.

If a replica and the master are located in different availability zones, the transaction commit latency cannot be less than the round-trip time (RTT) between data centers located in these availability zones. As a result, for single-thread writes with AUTOCOMMIT enabled, the cluster performance may drop considerably. For maximum performance, we recommend using multiple threads for write operations where possible, as well as disabling AUTOCOMMIT and grouping queries into transactions.

Managed Service for MySQL® clusters support automatic selection and failover to a new master. If the master host fails, one of its replicas becomes a new master.

For more information on master host selection, see Selecting a master if the primary master fails.

Manual cluster configuration managementManual cluster configuration management

With manual management, other cluster hosts may serve as replication sources for any cluster replica. Replicas that have their replication sources set manually are referred to as cascading replicas. Cascading replicas use asynchronous replication from a source host. Therefore, a cascading replica cannot become the master if the master host fails or is switched over manually.

If the source host is not responding, a cascading replica temporarily becomes the master until the source host is available again.

Here is an example of cluster configuration with cascading replication and hosts in two availability zones:

Assigning a replication source for the cluster hosts allows you to:

• Fully manage the replication process in the cluster without using automatic replication.
• Configure cascading replication for a MySQL® cluster with a tree topology, in which some replicas are managed automatically using Managed Service for MySQL® tools and others manually. This will reduce the load on the master host's network.
• Allocate some replicas for analytical load, since they will not become a master under any condition.

Selecting a master if the primary master failsSelecting a master if the primary master fails

If the master host fails, any of the cluster hosts available for replication may become a new master. To influence master selection in a MySQL® cluster, set your preferred priority values for the cluster hosts. The highest priority host will become the master. Or, in a cluster with multiple replicas of equal priority, the one lagging the least behind the master will be selected. Replicas lagging more than the value of the Mdb priority choice max lag setting (60 seconds by default) will be excluded from the selection.

You can set host priority:

• When creating a cluster with the CLI, API, or Terraform.
• When changing the host settings.

The lowest priority is 0 (default), while the highest one is 100.

You can also initiate master failover or assign this role to one of the replicas manually.

Use casesUse cases

• Delivering data to Yandex Managed Service for Apache Kafka® using Yandex Data Transfer
• Migrating data from Managed Service for MySQL® to Yandex MPP Analytics for PostgreSQL using Yandex Data Transfer
• Migrating a database from Managed Service for MySQL® to a third-party MySQL® cluster
• Migrating data from Yandex Managed Service for PostgreSQL to Managed Service for MySQL® using Yandex Data Transfer
• Migrating a database from MySQL® to ClickHouse® using Yandex Data Transfer