A database server allows multiple local or remote clients to connect to the same database. You start a database server process on a host. The database server process opens a port or socket and then clients connect with a compatible client. The database server handles authentication.
Database servers are used to allow multiple users to access the same database over a network. Database servers are often used to back applications. In that case, the application instances are the user.
Dolt behaves the same way as the MySQL database server started using mysqld. By default, Dolt starts on the same port as MySQL, 3306.
Dolt allows users to connect to multiple branches using a connection string. All users connected to the same branch see the same branch state.
dolt sql-server
Starting server with Config HP="localhost:3306"|T="28800000"|R="false"|L="info"% mysql --host 127.0.0.1 --port 3306 -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9-Vitess
Copyright (c) 2000, 2022, 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>Dolt can be used as a Relational Database Management System or RDBMS. Dolt ships with a MySQL compatible server built in, accessed via the dolt sql-server command.
Dolt supports backups. There are two options for backups: remotes or backups. Pushing to a remote only backs up committed changes. Using dolt backup backs up uncommitted changes as well. Backups are accessed via the dolt backup command or dolt_backup() procedure.
Dolt leverages Git-style remotes to facilitate replication. The master and replicas configure the same remote. On the master, you configure "push on write" and on the replicas you configure "pull on read".
A backup is a copy of your database. You can restore the state of the database as it existed at the time of the backup.
Backups are used for disaster recovery. When you create a backup it is wise to copy the backup to a different host. If you lose access to the host that houses your database, you restore the database from a backup on another host.
Backups have additional uses. Taking a backup of a database is often the easiest way to get a copy of the database. You can use this copy for development, testing, or analytics.
Dolt does not support any of the except the mysqldump method. Dolt can create a dump using the or mysqldump connected to the running Dolt server.
Dolt supports different methods of backup that leverage Dolt's git-style features.
Dolt databases contain the entire history of the database, meaning Dolt has backups built in to the running server. To restore to a previous point on a running server, you checkout the database at the commit you would like to restore to.
To replicate your database off host so you can restore if you lose the host, Dolt supports two different concepts: remotes and backups.
Using a for backup allows you to back up all committed changes. You add a remote using the . Then you push a branch to a remote using the or .
Dolt has an additional backup functionality beyond remotes. Using dolt backup backs up uncommitted changes as well. Backups are accessed via the or .
Interestingly, you can trigger backups from your application using the dolt_backup() procedure. If you hit a weird edge case you can create a backup of the state of your database for debugging.
%% dolt backup add backup1 file://../backups/backup1
/ Tree Level: 1, Percent Buffered: 0.00% Files Written: 0, Files Uploaded: 1$ dolt backup sync backup1$ dolt backup restore file://./backups/backup1 repo2
$ dolt branch -a
* main
$ dolt statusReplication is the ability for an RDBMS to synchronize a primary server with one or more read replicas. In this configuration, the primary database serves reads and writes while the replicas only serve reads.
Replication is used for disaster recovery and to increase read throughput by distributing read load.
For disaster recovery, if your primary server goes offline, your database can still serve read traffic from its replicas. Often a manual or automated process can elect and configure a replica to be the primary instance limiting downtime.
To increase read throughput, multiple replicas can be used to scale reads horizontally. If you have N replicas and your primary still takes reads, each read replica serves 1/N+1 percent of the read traffic. Note, in this set up your application must be aware of the replicas. The database does not route requests automatically.
, most based on the . Dolt supports a , where you configure a Dolt sql-server as a replica for an existing MySQL or MariaDB database. Dolt does not create binary logs and can NOT act as a primary for binlog replication.
Dolt supports two replication modes where Dolt can act as a primary and replicate to other Dolt sql-servers. The first is called . In this mode the primary and the read replicas are completely decoupled. The primary and the read replicas leverage a shared, Git-style to facilitate replication. On the primary, you configure "push on write" and on the replicas you configure "pull on read". This mode only replicates branch heads, which means that new dolt commits are required in order to replicate writes.
The second mode is called . In this mode, you configure a cluster of dolt sql-server instances to replicate all writes to each other. Each server is configured to replicate writes to all other servers in the cluster. One server is configured as the primary replica and it accepts writes. All other servers are configured as standbys and only accept read requests.
Dolt uses remotes to synchronize between primary and read replicas. Replication leverages Dolt's ability to produce differences between two versions of a database quickly.
The following example shows write replication from a primary and read replicas using a Git-style remote to rendezvous and maintain loose coupling. For more details on clustering in sql-server, see .
In this example I use a DoltHub remote to facilitate replication. I created an empty database on DoltHub and .
The changes are pushed to the remote.
To start a replica, you first need a clone.
Now, I'm going to configure my read replica to "pull on read" the main branch from origin.
Now on the primary.
And back to the replica.
$ dolt remote add origin timsehn/replication_example
$ dolt config --add --local sqlserver.global.dolt_replicate_to_remote origin
$ dolt sql -q "create table test (pk int, c1 int, primary key(pk))"
$ dolt sql -q "insert into test values (0,0)"
Query OK, 1 row affected
$ dolt add test
$ dolt sql -q "call dolt_commit('-m', 'trigger replication')"
+----------------------------------+
| hash |
+----------------------------------+
| 7on23n1h8k22062mbebbt0ejm3i7dakd |
+----------------------------------+$ dolt clone timsehn/replication_example read_replica
cloning https://doltremoteapi.dolthub.com/timsehn/replication_example
28 of 28 chunks complete. 0 chunks being downloaded currently.
dolt $ cd read_replica/$ dolt config --add --local sqlserver.global.dolt_read_replica_remote origin
Config successfully updated.
$ dolt config --add --local sqlserver.global.dolt_replicate_heads main
Config successfully updated.
$ dolt sql -q "select * from test"
+----+----+
| pk | c1 |
+----+----+
| 0 | 0 |
| 1 | 1 |
+----+----+$ dolt sql -q "insert into test values (2,2); call dolt_commit('-am', 'Inserted (2,2)');"
Query OK, 1 row affected
+----------------------------------+
| hash |
+----------------------------------+
| i97i9f1a3vrvd09pphiq0bbdeuf8riid |
+----------------------------------+$ dolt sql -q "select * from test"
+----+----+
| pk | c1 |
+----+----+
| 0 | 0 |
| 1 | 1 |
| 2 | 2 |
+----+----+
$ dolt log -n 1
commit i97i9f1a3vrvd09pphiq0bbdeuf8riid (HEAD -> main, origin/main)
Author: Tim Sehn <[email protected]>
Date: Mon Jul 11 16:48:37 -0700 2022
Inserted (2,2)