Failover

When the upstream database fails and a replica is promoted to primary, Readyset can be redirected to the new source without a full restart. The failover procedure depends on the database type and the replication mode in use.

Automatic failover with server lists

For MySQL with GTID replication, Readyset can fail over by itself when you give it more than one server. --upstream-db-url and --cdc-db-url accept a list of URLs. Repeat the option or separate the URLs with commas, and add an optional |<weight> suffix from 1 to 100 (default 50) to each one.

--cdc-db-url 'mysql://user:pass@replica-a:3306/db|60,mysql://user:pass@replica-b:3306/db|50,mysql://user:pass@replica-c:3306/db'

Replication connects to the highest-weight server. When that server fails, Readyset retries it up to --upstream-connect-retries times, then moves to the next server by weight. GTIDs name the same position on every server, so no rows are lost or duplicated. Replication will use the new server until that server fails or you change the list.

Client connections are spread across the servers in the --upstream-db-url list that are up.

You can edit the list while replication runs, for example from a tool that adds and removes replicas:

ALTER READYSET CHANGE CDC TO 'mysql://user:pass@replica-a:3306/db|60,mysql://user:pass@replica-d:3306/db';

Readyset checks new servers before it accepts the list. If replication's current server is still in the new list, replication keeps using it. If you removed it, Readyset restarts replication, using a server from the new list.

To see the list, each server's health, which server replication is on, and each server's connection count, run:

SELECT * FROM readyset.upstream_servers;

Use the manual steps below with a single server, to move to a server that isn't in the list, or with PostgreSQL.

MySQL

GTID replication

When Readyset is replicating with GTIDs, failover is straightforward because GTIDs are global identifiers that are consistent across all servers in a replication topology. No position adjustment is needed. Point Readyset at the new primary:

ALTER READYSET CHANGE CDC TO 'mysql://<user>:<password>@new-primary:3306/db';

Replication restarts on the new server and resumes from the last GTID Readyset processed. If the new primary has the same GTID history, replication continues without a gap. It is not necessary to stop replication first.

Binlog file and position replication

Binlog file names and positions are local to each MySQL server. When failing over to a new primary, you must determine the correct starting position on the new server. This is the same process as failing over any MySQL replica.

1. Stop replication:

ALTER READYSET STOP REPLICATION;

2. Check the current replication position in Readyset:

SHOW READYSET STATUS;

The Minimum Replication Offset field shows the current binlog file and position (e.g., binlog.000003:154).

3. Determine the equivalent position on the new primary. This step is environment-specific and is the same procedure used when failing over any MySQL replica. Common approaches include:

   • Using SHOW BINARY LOG STATUS on the new primary if the replica was in sync
   • Consulting your replication management tool (e.g., Orchestrator, MHA)
   • Comparing GTID sets if the servers have gtid_mode=ON (consider switching to GTID replication instead)

4. Set the replication position to the new primary's binlog coordinates:

ALTER READYSET SET REPLICATION POSITION 'binlog.000001:4587';

5. Point Readyset at the new primary:

ALTER READYSET CHANGE CDC TO 'mysql://<user>:<password>@new-primary:3306/db';

6. Resume replication:

ALTER READYSET START REPLICATION;

PostgreSQL

PostgreSQL logical replication uses replication slots that are local to the primary server. When the primary fails, the slot is lost and cannot be recreated at the old position on a new server. Readyset handles this automatically by performing a full resnapshot. Point Readyset at the new primary:

ALTER READYSET CHANGE CDC TO 'postgresql://<user>:<password>@new-primary:5432/db';

Readyset restarts replication, detects that the replication slot does not exist on the new primary, creates a new slot, and performs a full resnapshot of all tables. Once the snapshot completes, replication resumes from the new slot's starting position.

Monitoring failover

Use SHOW READYSET STATUS to monitor the failover process:

• Status: Shows Replication Stopped after stopping, then transitions back to Online once replication catches up.
• CDC Server: The host:port of the upstream server that replication is connected to. It doesn't appear while replication is stopped. After a failover, it shows the new server.
• Minimum Replication Offset / Maximum Replication Offset: Show the current replication position. For MySQL GTID, this displays the GTID set. For binlog file and position, it displays the file name and offset.

With a server list, SELECT * FROM readyset.upstream_servers shows one row per server. Each row has the server's weight, its purpose (cdc, proxy, or cdc, proxy), whether replication is using it, its health, and its connection count.