Back
Outdated Version

You are viewing an older version of this section. View current production version.

REPLICATE DATABASE

Start or continue replicating a database from a remote host to a local database.

REPLICATE DATABASE db_name [ASYNC | SYNC] FROM master_user[:master_password]@master_host[:master_port][/master_db_name] [FORCE] [AUTO_REPROVISION]

Notes

  • db_name is the name of the target database on the secondary MemSQL instance. REPLICATE DATABASE will always attempt to create a new database named db_name. The database name on the secondary does not need to match the name of its corresponding remote primary database.
  • MemSQL does not support granular security (see :ref:security_accounts), so master_user, master_password must grant access to the primary database. The password is assumed to be '’ if master_password is not specified explicitly.
  • The ASYNC and SYNC keywords can be used to specify asynchronous or synchronous replication. Note that the throughput of synchronous replication depends highly on the latency between the primary and secondary MemSQL servers and disk write throughput. For more information, see Managing High Availability.
  • master_host is the host name or IPv4/IPv6 pointing to the remote database. It can be quoted to allow special characters (e.g. “-", among others).
  • master_db_name is the name of the remote, primary database. If it is not specified explicitly, MemSQL attempts to replicate from db_name on the master MemSQL instance.
  • While replicating, the database is in the replicating state (see Database States).
Info

The keyword FORCE can be used to convert an existing ONLINE database db_name into a replica. It will only perform basic consistency checks to ensure that snapshots and logs of db_name and remote master_db_name match each other. The log of db_name needs to be a prefix of the log on master_db_name. You can verify this property manually by performing a binary file comparison (using tools like cmp or md5sum) between the corresponding database snapshot and log files in the primary’s and secondary’s data directories. MemSQL computes a rolling checksum of its log files and this mechanism will abort replication when using CONTINUE ... FORCE with diverged log files.

Info

The AUTO_REPROVISION keyword only applies to partition databases (within-cluster replication). When a secondary database has this option, it is automatically reprovisioned if it ever diverges from its primary database. This reprovisioning step occurs asynchronously (regardless of whether the slave is SYNC or ASYNC) and consists of the secondary database being dropped and then replicated from scratch from the primary database. By default, all replica partitions have this option, so it is recommended that you use it during manual cluster troubleshooting.

If you run CONTINUE ... FORCE AUTO_REPROVISION on a database whose log is not a prefix of the primary database log, it will be reprovisioned immediately.

Notes

  • The long form of CONTINUE REPLICATING can be used to re-point a replica from one master to another. See “Replication Failover” for more details on failover and using the CONTINUE REPLICATING command.
  • The optional ASYNC and SYNC keywords can be used to continue replicating as an asynchronous or synchronous replica, respectively.
  • This command can be run on any MemSQL node (see Node Requirements for MemSQL Commands ).
Info

This MemSQL feature is only available in MemSQL Enterprise Edition. MemSQL Enterprise Edition includes 24x7 support and several enterprise-only features for critical deployments. For more information about MemSQL Community and MemSQL Enterprise, see the MemSQL Editions page.

Example

memsql> REPLICATE DATABASE ExampleDatabase FROM root@master-host:3306;
Query OK, 1 row affected (0.32 sec)

Questions?

Ask the Community, find solutions in the Troubleshooting Overview, get Self-Paced Training or learn more about our Support Plans.