On this page
Starts or continues replicating a database from a remote host to the local host.
The database on the remote host is the primary database and the database on the local host is its secondary.
It is useful in maintaining database backups, high availability (HA) and disaster recovery (DR).
A replication link is configured in one direction.
REPLICATE DATABASE db_name [WITH FORCE DIFFERENTIAL] FROM master_user[:'master_password']@master_host:master_port[/master_db_name]
db_is the name of the target database on the secondary SingleStore instance.
REPLICATE DATABASEwill attempt to create a new database named
db_, unless the
WITH FORCE DIFFERENTIALclause is used.
The database name on the secondary does not need to match the name of its corresponding remote primary database.
REPLICATE DATABASEalways replicates asynchronously.
This command causes implicit commits.
See COMMIT for more information.
If you run STOP REPLICATING, replication stops and the secondary database is promoted to the primary database on the local host.
When replication stops, the promoted database will use synchronous replication, if the previous primary database used synchronous replication. Likewise, the promoted database will use asynchronous replication, if the previous primary database used asynchronous replication.
The following applies if the secondary database were to be promoted to the primary database on the local host: If the promoted database uses synchronous replication, the former primary database’s durability setting is used.
If the promoted database uses asynchronous replication, asynchronous durability is used. (In-memory database updates you make using DDL and DML are durable when they are also saved to the log on disk). For more information, see Replication and Durability Concepts.
WITH FORCE DIFFERENTIALreplicates only the contents of the primary database that are not already in the secondary database.
Most often, you should use the
WITH FORCE DIFFERENTIALclause to resume replication from the original primary database following a cluster failover.
When you use this clause, a new database is not created, because the database already exists. This option cannot be used to do a failback.
In case of multiple DR replications, for example, A -> B, A -> C, A -> D where A, B, C and D are databases, if you runSTOP REPLICATING for one replication (for example, stop A -> B), then you cannot run the
REPLICATE DATABASE WITH FORCE DIFFERENTIALcommand for the other replications.
master_should have relevant permissions.
user This user must have access to first do the resource check in the information schema in the primary database and then the initial replication setup. This user is only used to kickstart the replication, future replications will happen using internal SingleStore authentication negotiated during the REPLICATE handshake. Hence this user need not exist after the REPLICATE command completes. You can switch to a different user to do the initial setup in future replications without making any modifications to the existing replications.
master_is assumed to be blank if it is not specified explicitly.
password If you specify a password, enclose it in single quotes.
master_is the host name or IPv4/IPv6 pointing to the remote database.
host It can be quoted to allow special characters (e. g.
-, among others).
master_can be in the same cluster or in a different cluster than the local host.
master_is the name of the remote, primary database.
db_ name If it is not specified explicitly, SingleStore attempts to replicate from
db_on the master SingleStore instance.
While replicating, the database is in the
replicatingstate (see Database States).
If the primary database contains pipelines, the state of these pipelines (such as the pipeline offsets) are replicated to the secondary database.
This command cannot be run on system databases.
The replica database cannot be backed up.
Queries can be run on the replica database.
Refer to the Permission Matrix for the required permission.
The recommended minimum network bandwidth is at least 1MB/sec per partition for replication to succeed.
For example, if you have a database that has 64 partitions, this would require a network bandwidth that can handle 64MB/sec. While 1MB/sec is the bare minimum, a network configured with just 1MB/sec per partition may still encounter spurious network timeouts. SingleStore recommends setting the bandwidth reasonably higher to help prevent timeouts.
Data is always replicated across clusters asynchronously.
Databases are replicated at the leaf level, which implies that a leaf in the secondary cluster replicates data directly from a leaf in the primary cluster; therefore, when connecting a secondary cluster to the primary cluster, the leaves in the primary and secondary cluster must be able to communicate with each other. They should not be blocked by firewall or network rules.
REPLICATE DATABASE ExampleDatabase FROM root@master-host:3306;
REPLICATE DATABASE ExampleDatabase FROM master_user:'master_password'@master-host:3306/MasterExampleDatabase;
The Replicating Data Across Clusters topic provides examples of using
WITH FORCE DIFFERENTIAL to replicate only the contents of the primary database that are not already in the secondary database.
Last modified: November 16, 2023