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!

Push (on write) from sources

To push on write, a valid remote middleman must be configured:
1
dolt config --add --local sqlserver.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')
2
3
UPDATE dolt_branches SET hash = COMMIT('-m', 'message') WHERE name = 'main' AND hash = @@database_name_head
Copied!

Pull (on read) to replica

Read replicas are instantiated with a remote:
1
dolt config --add --local sqlserver.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 config --add --local sqlserver.global.dolt_replicate_heads main,feature1
2
dolt config --add --local sqlserver.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:
USE \mydb/feature-branch``
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.
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.
Last modified 1mo ago