Replication Updates

October 4, 2023

11 min read

Dolt supports three types of replication:

Remote-based replication launched October 2021
Standby replication launched October 2022
Replicate from MySQL to Dolt launched February 2023

Hosted Dolt launched read replicas in May 2023 using the Standby replication approach. While useful, the feature was limited to the database itself, and did not include any of the permissions or users, nor did it persist database drops. Both of these were operationally problematic. In this blog I'll be going over new replication features that have been added.

Replicated Permissions

The first feature we added was replicated permissions. This means that when you create and add database users, those users will be replicated to the replicas, and so will their permissions. This means that you can create a replica and give your users access to it, without having to manually create the users on each replica. Additionally, it means that if you need to fail over to one of your replicas, the users and permissions that were set on your previous primary server will be available on the new primary.

Replicated Permissions Demonstration

To demonstrate this I will create a hosted Dolt deployment with replication enabled. I will then create a database and two tables t and t2 and seed them with some data. I will then create a user treader and grant them SELECT access on table t only. Then I will log in as treader to the primary and a replica showing that the user can SELECT * FROM t, but SELECT * FROM t2 fails.

The following was executed from a connection made to the primary server as the root user.

MySQL [(none)]> CREATE DATABASE db;
Query OK, 1 row affected (0.076 sec)

MySQL [(none)]> USE db;
Database changed

MySQL [db]> CREATE TABLE t (pk int primary key, c1 varchar(8));
Query OK, 0 rows affected (0.046 sec)

MySQL [db]> CREATE TABLE t2 (pk int primary key, c1 varchar(8));
Query OK, 0 rows affected (0.046 sec)

MySQL [db]> INSERT INTO t VALUES (0,'zero'),(1,'one'),(2,'two');
Query OK, 3 rows affected (0.051 sec)

MySQL [db]> INSERT INTO t2 VALUES (3,'three'),(4,'four'),(5,'five');
Query OK, 3 rows affected (0.051 sec)

MySQL [db]> CREATE USER 'treader'@'%' IDENTIFIED BY 'pass1234';
Query OK, 0 rows affected (0.042 sec)

MySQL [db]> GRANT SELECT ON db.t TO 'treader'@'%';
Query OK, 0 rows affected (0.043 sec)

MySQL [db]> SELECT * FROM t;
+------+------+
| pk   | c1   |
+------+------+
|    0 | zero |
|    1 | one  |
|    2 | two  |
+------+------+
3 rows in set (0.043 sec)

MySQL [db]> SELECT * FROM t2;
+------+-------+
| pk   | c1    |
+------+-------+
|    3 | three |
|    4 | four  |
|    5 | five  |
+------+-------+
3 rows in set (0.044 sec)

Now that I've set up our test, I will now log back into the primary as treader.

~>mysql -h"dolthub-change-prim-test-7.dbs.hosted.doltdb.com" -u"treader" -p"pass1234" -Ddb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.9-Vitess Dolt

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [db]> select * from t;
+------+------+
| pk   | c1   |
+------+------+
|    0 | zero |
|    1 | one  |
|    2 | two  |
+------+------+
3 rows in set (0.046 sec)

MySQL [db]> select * from t2;
ERROR 1105 (HY000): Access denied for user 'treader'@'%' to table 't2'

As you can see, the user treader can access table t, but not table t2. Now I will log into the read replica as treader and show that the user can access table t, but not table t2.

~>mysql -h"r01-dolthub-change-prim-test-7.dbs.hosted.doltdb.com" -u"treader" -p"pass1234" -Ddb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9-Vitess Dolt

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [db]> SELECT * from t;
+------+------+
| pk   | c1   |
+------+------+
|    0 | zero |
|    1 | one  |
|    2 | two  |
+------+------+
3 rows in set (0.046 sec)

MySQL [db]> SELECT * from t2;
ERROR 1105 (HY000): Access denied for user 'treader'@'%' to table 't2'

Replicated Database Drops

The second feature we added was replicated database drops. Previously, if you dropped a database on your primary server, the replicas would continue to make the data available, and would require additional work to remove the database from the replicas. Now, if you drop a database on your primary server, the read replicas will also drop the database, and will no longer make the data available.

Replicated Database Drops Demonstration

To demonstrate this I will simply connect to the primary server that we set up earlier as the root user and drop our database db. Then I will log into our read replica

~>mysql -h"dolthub-change-prim-test-7.dbs.hosted.doltdb.com" -u"ef3bc5qv0ssuzrot" -p"7xM3jERq4vdElwt8jvVwKKR9rvfnO5gC"
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.9-Vitess Dolt

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db                 |
| dolt_cluster       |
| information_schema |
| mysql              |
+--------------------+
4 rows in set (0.048 sec)

MySQL [(none)]> DROP DATABASE db;
Query OK, 1 row affected (0.052 sec)

MySQL [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| dolt_cluster       |
| information_schema |
| mysql              |
+--------------------+
3 rows in set (0.046 sec)

MySQL [(none)]> exit
Bye

Now we will connect to our read replica and verify that the database is no longer available.

~>mysql -h"r01-dolthub-change-prim-test-7.dbs.hosted.doltdb.com" -u"ef3bc5qv0ssuzrot" -p"7xM3jERq4vdElwt8jvVwKKR9rvfnO5gC"
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.9-Vitess Dolt

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| dolt_cluster       |
| information_schema |
| mysql              |
+--------------------+
3 rows in set (0.044 sec)

As you can see, the database db is no longer available on the read replica.

Dolt Failover

In September 2022, almost a year after the launch of read replicas, Dolt added support for standby replication allowing you to manually fail over from your primary server to one of your replicas. Then in June we added additional functionality to be able to change a server's role more gracefully by providing a new stored procedure dolt_cluster_transition_to_standby.

Graceful Fail Over Demonstration

Again we will create a new replicated hosted deployment, and create a database db with table t. With our table created we will attempt to insert data using the root user logged into the primary, and then the standby replica. We expect writing to the primary will succeed, and writing to the replica will fail. Then we will fail over to the replica and attempt writing to both servers again. This time we expect writing to the old primary to fail, and writing to the old replica to succeed.

~>mysql -h"dolthub-graceful-failover.dbs.hosted.doltdb.com" -u"526e1pw6avazilnw" -p"iE70AHtLiv5oP1VKAgpLK6YQ6sOQPD1K"
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.9-Vitess Dolt

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> create database db;
Query OK, 1 row affected (0.196 sec)

MySQL [(none)]> use db;
Database changed
MySQL [db]> CREATE TABLE t (pk int primary key, c1 varchar(8));
Query OK, 0 rows affected (0.162 sec)

MySQL [db]> INSERT INTO t VALUES (0,'zero'),(1,'one'),(2,'two');
Query OK, 3 rows affected (0.300 sec)

MySQL [db]> exit
Bye

As you can see we've connected to our primary server and created a database db with table t, inserted some data successfully, and disconnected. Now we will connect to our standby replica and attempt to insert data.

~>mysql -h"r01-dolthub-graceful-failover.dbs.hosted.doltdb.com" -u"526e1pw6avazilnw" -p"iE70AHtLiv5oP1VKAgpLK6YQ6sOQPD1K"
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.9-Vitess Dolt

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> use db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [db]> SELECT * FROM t;
+------+------+
| pk   | c1   |
+------+------+
|    0 | zero |
|    1 | one  |
|    2 | two  |
+------+------+
3 rows in set (0.166 sec)

MySQL [db]> INSERT INTO t VALUES (3,'three'),(4,'four'),(5,'five');
ERROR 1105 (HY000): Database db is read-only.
MySQL [db]> exit
Bye

As you can see, the insert failed because the database is read-only. Now we will fail over to the standby replica by calling the dolt_cluster_transition_to_standby() procedure on the primary server, and the dolt_cluster_assume_role() procedure on the standby replica.

~>mysql -h"dolthub-graceful-failover.dbs.hosted.doltdb.com" -u"526e1pw6avazilnw" -p"iE70AHtLiv5oP1VKAgpLK6YQ6sOQPD1K"
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.9-Vitess Dolt

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> SELECT @@global.dolt_cluster_role;
+----------------------------+ 
| @@GLOBAL.dolt_cluster_role | 
+----------------------------+ 
| primary                    | 
+----------------------------+ 
1 row in set (0.164 sec)

MySQL [(none)]> SELECT `database`, `standby_remote`, `epoch`
FROM dolt_cluster.dolt_cluster_status;
+----------+----------------+-------+ 
| database | standby_remote | epoch | 
+----------+----------------+-------+ 
| db       | server01       |     1 | 
| db       | server02       |     1 | 
+----------+----------------+-------+ 
2 rows in set (0.161 sec)

MySQL [(none)]> CALL dolt_cluster_transition_to_standby(2,2)
+-----------+----------+----------+--------------------------+ 
| caught_up | database | remote   | remote_url               | 
+-----------+----------+----------+--------------------------+ 
|         1 | db       | server01 | http://server01:50051/db | 
|         1 | db       | server02 | http://server02:50051/db | 
+-----------+----------+----------+--------------------------+ 
2 rows in set (20.164 sec)

MySQL [(none)]> SELECT @@global.dolt_cluster_role;
ERROR 1105 (HY000): this server transitioned cluster roles.
this connection can no longer be used. please reconnect.

Here we connected to our primary server using the root account and queried its current role, as well as the dolt_cluster_status table in the dolt_cluster database. We see that this server is the primary, and that there are two standby replicas. Additionally we can see the current epoch for each of these servers is 1. Next we call dolt_cluster_transition_to_standby passing an epoch that is greater than the current, and the count of the number of replicas as arguments. You can see that additional queries to this connection fail because the server has transitioned roles and we will need to reconnect.

Next we will connect to our replica, query it's state, then call dolt_cluster_assume_role with the arguments primary and the new epoch of 2 which we used in our call to dolt_cluster_transition_to_standby on the primary. After that this connection will also need to be remade.

~>mysql -h"r01-dolthub-graceful-failover.dbs.hosted.doltdb.com" -u"526e1pw6avazilnw" -p"iE70AHtLiv5oP1VKAgpLK6YQ6sOQPD1K"
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.9-Vitess Dolt

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> SELECT @@global.dolt_cluster_role;
+----------------------------+
| @@GLOBAL.dolt_cluster_role |
+----------------------------+
| standby                    |
+----------------------------+
1 row in set (0.164 sec)

MySQL [(none)]> 
SELECT `database`, 
   `standby_remote`, 
   `epoch` 
FROM dolt_cluster.dolt_cluster_status;
+----------+----------------+-------+
| database | standby_remote | epoch |
+----------+----------------+-------+
| db       | server00       |     1 |
| db       | server02       |     1 |
+----------+----------------+-------+
2 rows in set (0.163 sec)

MySQL [(none)]> call dolt_assume_cluster_role('primary', 2);
+--------+
| status |
+--------+
|      0 |
+--------+

MySQL [(none)]> SELECT @@global.dolt_cluster_role;
ERROR 1105 (HY000): this server transitioned cluster roles. 
this connection can no longer be used. please reconnect.

Now we will reconnect to the server that was previously the primary and attempt to insert data into our table t again. Since this server has transitioned roles we expect this to fail. It does.

~>mysql -h"dolthub-graceful-failover.dbs.hosted.doltdb.com" -u"526e1pw6avazilnw" -p"iE70AHtLiv5oP1VKAgpLK6YQ6sOQPD1K"
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.9-Vitess Dolt

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> select @@global.dolt_cluster_role;
+----------------------------+
| @@GLOBAL.dolt_cluster_role |
+----------------------------+
| standby                    |
+----------------------------+
1 row in set (0.156 sec)

MySQL [(none)]> USE db;
Database changed

MySQL [db]> INSERT INTO t VALUES (3,'three'),(4,'four'),(5,'five');
ERROR 1105 (HY000): Database db is read-only.

MySQL [db]> exit
Bye

Lastly we will check that we can write to our new primary server.

~>mysql -h"r01-dolthub-graceful-failover.dbs.hosted.doltdb.com" -u"526e1pw6avazilnw" -p"iE70AHtLiv5oP1VKAgpLK6YQ6sOQPD1K"
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.9-Vitess Dolt

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> SELECT @@global.dolt_cluster_role;
+----------------------------+
| @@GLOBAL.dolt_cluster_role |
+----------------------------+
| primary                    |
+----------------------------+
1 row in set (0.160 sec)

MySQL [(none)]> USE db
Database changed
MySQL [db]> INSERT INTO t VALUES (3,'three'),(4,'four'),(5,'five');
Query OK, 3 rows affected (0.165 sec)

Hosted Dolt and Failover

You have seen the process of manually failing over from a primary server to a standby replica in hosted. It is far from ideal, and there are a number of things that we can do to improve the experience. We are currently working to provide failover with the click of a button. This will allow you to fail over to a standby replica without having to manually log in to the servers and execute the stored procedures. Additionally, it will make dns changes so that the writable primary server is always available at the same address. This feature should be out in the next few weeks, and we look forward to announcing it.

Conclusion

Dolt's usage is growing, and more and more people are using it in production. We are excited to be able to provide additional features to make it easier to use Dolt in production with improved availability, reliability and performance. Replication is a key feature that we are continuing to improve, and we look forward to providing additional features. If you'd like to talk to us about Dolt, Hosted Dolt, or anything else please join us on Discord.

Blog