Replication

Read Replication

Replicating data between servers can increase your application's read query throughput. If a read replica fails, your application is still available. If a replication source fails, that is a bigger problem that requires a failover solution.
Dolt supports simple read replication with two caveats:
  • A remote is a replication middleman.
  • Individual transactions are not replicated, only commits.
In summary, we support replicating a source database by pushing on commit, and pulling to replicas on read. The stability of the middleman is required to maintain the thread of communication between a primary server and its replicas.
Refer to the read replication blog for a walkthrough.

Configuration

Persisting system variables

Configs can be set in the CLI (limited to --local scope for now):
1
dolt config --add --local sqlserver.global.dolt_replicate_to_remote <name>
2
dolt config --add --local sqlserver.global.dolt_read_replica_remote <name>
Copied!
Configs can be set equivalently in an SQL session:
1
SET PERSIST @@GLOBAL.dolt_replicate_to_remote = '<name>'
2
SET PERSIST @@GLOBAL.dolt_read_replica_remote = '<name>'
Copied!
Note: after changing replication configuration options, the Dolt server process needs to be restarted before replication changes will take effect.

Push (on write) from sources

To push on write, a valid remote middleman must be configured:
1
dolt sql -q "SET PERSIST @@GLOBAL.dolt_replicate_to_remote = 'origin'"
Copied!
There are two ways to trigger pushing to a remote middleman: a Dolt commit, or a branch head update. A standalone COMMIT or head set will not trigger replication:
1
SELECT DOLT_COMMIT('-am', 'message')
Copied!

Pull (on read) to replica

Read replicas are instantiated with a remote:
1
dolt sql -q "SET PERSIST @@GLOBAL.dolt_read_replica_remote = 'origin'"
Copied!
A complete replication setup requires a pull spec with either 1) a set of heads, or 2) all heads (but not both):
1
dolt sql -q "SET PERSIST @@GLOBAL.dolt_replicate_heads = 'main,feature1'"
2
dolt sql -q "SET PERSIST @@GLOBAL.dolt_replicate_all_heads = 1'"
Copied!
On the replica end, pulling is triggered by an SQL START TRANSACTION. The first query in a session automatically starts a transaction. Setting autocommit = 1, which begins every query with a transaction, is encouraged on read replicas for convenience.

Auto-fetching

Dolt supports auto-fetching branches on demand for read replication in certain circumstances:
  1. 1.
    Clients that connect to a missing branch:
mysql://127.0.0.1:3306/mydb/feature-branch
  1. 1.
    USEing a missing branch:
1
USE `mydb/feature-branch`
Copied!
In either case, a read replica will pull the indicated branch from the remote middleman. If the branch is not on the replica, a new remote tracking branch, head branch, and working set will be created.
Read more about different head settings here.

Quiet Warnings

Set sqlserver.global.dolt_skip_replication_errors = true to print warnings rather than error if replication is misconfigured.
Could not load image
Read replication

Failover

If the primary database processing writes fails, queries will either need to be routed to a standby server, or queue/fail until the primary restarts. We do not have a purpose-built solution or documentation for failover recovery yet.
In the meantime, it is possible to use push/pull replication to maintain a standby server. If the primary server fails, the standby and proxy would need to walk through a series of steps to create a new primary:
  • Standby server disables read-only mode if it was used as a read replica previously.
  • Standby server recovers the most recent transactions, either from the remote middleman or a primary backup.
  • Standby sets the replication source configuration to push on write.
  • Proxy layer directs write queries to the formerly standby, now primary server.

Multi-Master

We do not have specific solutions or documentation to run Dolt as an OLTP database with multiple masters. It is possible to connect several write target with a common remote middleman, but they would need to reconcile merge conflicts in the same way an offline Dolt database does. Providing a transactional layer to enforce multi-master (to avoid merge conflicts) or a way to automatically resolve merge conflicts is necessary to run Dolt as a multi-master database effectively.