Announcing Hosted MySQL with Dolt Replica

August 9, 2024

8 min read

We are excited to announce that Hosted Dolt now supports creating clusters with a MySQL primary and a Dolt replica. This feature allows you to create a MySQL server that is replicated to a Dolt database. In this blog I'll walk you through how to create a cluster with a MySQL primary and a Dolt replica, and we'll talk about some of the ways in which this is better than running a traditional MySQL cluster.

Creating a Mysql Cluster with a Dolt Replica

To create a hosted MySQL cluster with a Dolt replica, you must start by creating an account on https://hosted.doltdb.com. Once you have an account, you can click "Launch a database" from the Hosted Dolt home page or the "Create Deployment" button on the deployments page. The create deployment workflow will guide you through creating a deployment. The initial page allows you to select a "trial" or "standard" cluster type. Trial clusters are run on small instances and are limited to 50GB of storage, and are $50 per month per instance. Selecting a "Standard" cluster will allow you to select the exact instance type you want as well as the amount of storage you need. The pricing for standard clusters is based on the instance type and storage you select. You'll need to give your deployment a name, and then select "MySQL with Dolt Replica" from the "cluster" dropdown.

Assuming you've selected a standard deployment the next step in the workflow allows you to select a cloud provider, instance type, storage size, and region. Once you've selected the options you want, click "Next" to proceed to the next step.

The next step in the workflow allows you to select advanced options. You will see that "Enable replication" is disabled, as all clusters of this type are replicated with a single Dolt instance. You can also select "Use Web PKI Certificate", which will then allow you to select "Expose remotesapi endpoint" and at that point you will be able to clone, fetch, and pull from the Dolt replica using the Dolt CLI.

Finally, you will be able to review your selections and click "Create Deployment" to create your cluster.

Once your deployment is created, you will be able to see the status of the deployment on the deployments page. The deployment will be in the "Starting" state until it is ready, at which point it will be in the "Started" state. At this point you can connect to the MySQL primary using the credentials provided on the deployment page. You can also connect to the dolt replica by prepending the host name with "r01-" and using the same credentials.

Uses

There are a number of cases when having a Dolt replica for your MySQL database can be useful. An example is recovering data that was lost due to a mistake. If you accidentally ran a query with a bad where clause and deleted or modified data you didn't mean to, you can use the Dolt replica to revert the changes quickly.

For this example I'm going to create a database "db" and then a table named "Users" with the following create statement: Next I'll seed the database with some users:

$>mysql -h"dolthub-dolt-replicated-mysql.dbs.hosted.doltdb.com" -u"kp09c690ggvlocrz" -p"KZfLQmaOImxEudl8r0yYyQ98MxEL1XG9"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.39-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE TABLE Users (
    ->     username VARCHAR(64) PRIMARY KEY,
    ->     first_name VARCHAR(64) NOT NULL,
    ->     last_name VARCHAR(64) NOT NULL,
    ->     email VARCHAR(256) NOT NULL,
    ->     age INT
    -> );
ERROR 1046 (3D000): No database selected
mysql> CREATE DATABASE db;
Query OK, 1 row affected (0.05 sec)

mysql> use db;
Database changed
mysql> CREATE TABLE Users (
    ->     username VARCHAR(64) PRIMARY KEY,
    ->     first_name VARCHAR(64) NOT NULL,
    ->     last_name VARCHAR(64) NOT NULL,
    ->     email VARCHAR(256) NOT NULL,
    ->     age INT
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO Users (username, first_name, last_name, email, age) VALUES
    -> ('alice', 'Alice', 'Smith', 'as@domain.com', 15),
    -> ('bob', 'Bob', 'Jones', 'bj@domain.com', 30),
    -> ('charlie', 'Charlie', 'Brown', 'cb@domain.com', 18),
    -> ('dave', 'Dave', 'Johnson', 'dj@domain.com', 49),
    -> ('evie', 'Evelyn', 'Hendriks', 'ev@domain.com', 8),
    -> ('frank', 'Frank', 'Williams', 'fw@domain.com', 22),
    -> ('gina', 'Gina', 'Martinez', 'gm@domain.com', 33),
    -> ('harry', 'Harry', 'Davis', 'hd@domain.com', 27),
    -> ('irene', 'Irene', 'Rodriguez', 'ir@domain.com', 65),
    -> ('joe', 'Joe', 'Garcia', 'jg@domain.com', 52),
    -> ('kate', 'Kate', 'Martinez', 'km@domain.com', 44);
Query OK, 11 rows affected (0.05 sec)
Records: 11  Duplicates: 0  Warnings: 0

For this example, I'm going to delete all users under the age of 30:

mysql> DELETE FROM Users  WHERE age >= 30;
Query OK, 6 rows affected (0.04 sec)

Now that we have setup our example I will log out of the MySQL primary and log into the Dolt replica. Once on the Dolt replica I will look for the commit that caused the problem.

$>mysql -h"r01-dolthub-dolt-replicated-mysql.dbs.hosted.doltdb.com" -u"kp09c690ggvlocrz" -p"KZfLQmaOImxEudl8r0yYyQ98MxEL1XG9"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 322
Server version: 8.0.33 Dolt

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> use db;
Database changed
mysql> SELECT from_commit, to_commit, count(*) delete_count
    -> FROM dolt_diff_Users
    -> WHERE diff_type = 'removed'
    -> GROUP BY from_commit,to_commit;
+----------------------------------+----------------------------------+--------------+
| from_commit                      | to_commit                        | delete_count |
+----------------------------------+----------------------------------+--------------+
| kdnfbskmnmridlo75ibji2q4t69n4lm0 | divfio0pm2a2dhafvhuric1as6st84gh |            6 |
+----------------------------------+----------------------------------+--------------+
1 row in set (0.04 sec)

mysql> SELECT *
    -> FROM dolt_patch('divfio0pm2a2dhafvhuric1as6st84gh','kdnfbskmnmridlo75ibji2q4t69n4lm0');
+-----------------+----------------------------------+----------------------------------+------------+-----------+----------------------------------------------------------------------------------------------------------------------------------+
| statement_order | from_commit_hash                 | to_commit_hash                   | table_name | diff_type | statement                                                                                                                        |
+-----------------+----------------------------------+----------------------------------+------------+-----------+----------------------------------------------------------------------------------------------------------------------------------+
|               1 | divfio0pm2a2dhafvhuric1as6st84gh | kdnfbskmnmridlo75ibji2q4t69n4lm0 | Users      | data      | INSERT INTO `Users` (`username`,`first_name`,`last_name`,`email`,`age`) VALUES ('bob','Bob','Jones','bj@domain.com',30);         |
|               2 | divfio0pm2a2dhafvhuric1as6st84gh | kdnfbskmnmridlo75ibji2q4t69n4lm0 | Users      | data      | INSERT INTO `Users` (`username`,`first_name`,`last_name`,`email`,`age`) VALUES ('dave','Dave','Johnson','dj@domain.com',49);     |
|               3 | divfio0pm2a2dhafvhuric1as6st84gh | kdnfbskmnmridlo75ibji2q4t69n4lm0 | Users      | data      | INSERT INTO `Users` (`username`,`first_name`,`last_name`,`email`,`age`) VALUES ('gina','Gina','Martinez','gm@domain.com',33);    |
|               4 | divfio0pm2a2dhafvhuric1as6st84gh | kdnfbskmnmridlo75ibji2q4t69n4lm0 | Users      | data      | INSERT INTO `Users` (`username`,`first_name`,`last_name`,`email`,`age`) VALUES ('irene','Irene','Rodriguez','ir@domain.com',65); |
|               5 | divfio0pm2a2dhafvhuric1as6st84gh | kdnfbskmnmridlo75ibji2q4t69n4lm0 | Users      | data      | INSERT INTO `Users` (`username`,`first_name`,`last_name`,`email`,`age`) VALUES ('joe','Joe','Garcia','jg@domain.com',52);        |
|               6 | divfio0pm2a2dhafvhuric1as6st84gh | kdnfbskmnmridlo75ibji2q4t69n4lm0 | Users      | data      | INSERT INTO `Users` (`username`,`first_name`,`last_name`,`email`,`age`) VALUES ('kate','Kate','Martinez','km@domain.com',44);    |
+-----------------+----------------------------------+----------------------------------+------------+-----------+----------------------------------------------------------------------------------------------------------------------------------+

As you can see I change the MySQL client command to connect to host r01-dolthub-dolt-replicated-mysql.dbs.hosted.doltdb.com instead of dolthub-dolt-replicated-mysql.dbs.hosted.doltdb.com because the r01- prefix is used to connect to the Dolt replica. I then selected the db database and queried the dolt_diff_Users table to find the commit that deleted the users. In our example we can see commit divfio0pm2a2dhafvhuric1as6st84gh with parent commit kdnfbskmnmridlo75ibji2q4t69n4lm0 deleted 6 users.

Now that we know the commit that caused the problem we can use the dolt_patch function to generate the SQL statements to revert the changes. We will pass the to_commit and from_commit to the dolt_patch function in reverse order to get the SQL statements to revert the changes. As you can see this gives us a table which includes the SQL statements to revert the changes, and the order in which they should be run. This is great, but I don't really want to copy and paste them into the MySQL client one by one, so I'll exit and run the dolt_patch command with the -e flag and redirect the results to a file.

$>mysql -h"r01-dolthub-dolt-replicated-mysql.dbs.hosted.doltdb.com" \
>-u"kp09c690ggvlocrz" \
>-p"KZfLQmaOImxEudl8r0yYyQ98MxEL1XG9" \
>-D db \
>-e "SELECT statement FROM dolt_patch('divfio0pm2a2dhafvhuric1as6st84gh','kdnfbskmnmridlo75ibji2q4t69n4lm0') ORDER BY statement_order;" \
>-sN >patch.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

$>cat patch.sql
INSERT INTO `Users` (`username`,`first_name`,`last_name`,`email`,`age`) VALUES ('bob','Bob','Jones','bj@domain.com',30);
INSERT INTO `Users` (`username`,`first_name`,`last_name`,`email`,`age`) VALUES ('dave','Dave','Johnson','dj@domain.com',49);
INSERT INTO `Users` (`username`,`first_name`,`last_name`,`email`,`age`) VALUES ('gina','Gina','Martinez','gm@domain.com',33);
INSERT INTO `Users` (`username`,`first_name`,`last_name`,`email`,`age`) VALUES ('irene','Irene','Rodriguez','ir@domain.com',65);
INSERT INTO `Users` (`username`,`first_name`,`last_name`,`email`,`age`) VALUES ('joe','Joe','Garcia','jg@domain.com',52);
INSERT INTO `Users` (`username`,`first_name`,`last_name`,`email`,`age`) VALUES ('kate','Kate','Martinez','km@domain.com',44);

A few things of note are:

-e allows us to specify our query on the command line. The MySQL client will connect, run the query, and then exit.
The -sN flag is used to suppress the column headers and the row count.
The ORDER BY statement_order clause is used to ensure the statements are in the correct order.
We use -D db to specify the database we want to connect to.
The > operator is used to redirect the output to a file.

As you can see when we cat patch.sql we have the SQL statements to revert the changes. We can now pipe this file into the MySQL client to revert the changes against the MySQL primary.

$>mysql -h"dolthub-dolt-replicated-mysql.dbs.hosted.doltdb.com" -u"kp09c690ggvlocrz" -p"KZfLQmaOImxEudl8r0yYyQ98MxEL1XG9" -D db <patch.sql

Conclusion

There may be reasons why you have been slow to adopt Dolt as your primary database, but with the introduction of Hosted MySQL with a Dolt Replica, you can now have the best of both worlds. You can run MySQL, a tried and true database with known performance characteristics, and have a Dolt replica that allows you to quickly recover from mistakes, see how data has changed over time, clone the database locally and any of the numerous benefits that a version controlled database can provide. Give it a try at https://hosted.doltdb.com and let us know what you think.

Blog

Creating a Mysql Cluster with a Dolt Replica

Uses

Conclusion

Get started with Dolt