Join the MemSQL Community Today
Get expert advice, develop skills, and connect with others.

Modes

Operation Modes

Replicate has five modes of operation:

  1. init
  2. snapshot
  3. full
  4. fetch-schemas
  5. infer-schemas

init

Replicate retrieves the existing source schemas and creates equivalent schemas on the MemSQL destination.

Example:

./bin/replicate init conf/conn/oracle.yaml conf/conn/memsql.yaml <additional_parameters>

snapshot

Replicate first creates the destination schemas. Once the schemas are created, all pre-existing data from the source is replicated to MemSQL. Any changes made to the data on the source (insert, update, and/or delete) after the replication has started are ignored.

After the data is replicated, a summary file is generated in either ./data/default/snapshot_summary.txt (if no instance name is specified) or ./data/<instance_name>/snapshot_summary.txt (if an instance name is specified) and Replicate exits.

Example:

./bin/replicate snapshot conf/conn/oracle.yaml conf/conn/memsql.yaml <additional_parameters>

full

Replicate first creates the destination schemas. Once the schemas are created, all pre-existing data from the source is replicated to MemSQL. Data is continually synchronized with minimal latency until Replicate is stopped via Ctrl-C (^C).

This mode is currently available only for Oracle as a source, where CDC is used. The transition between snapshot migration and continuous integration is seamless, and Replicate guarantees an “exactly-once” replication.

Example:

./bin/replicate full conf/conn/oracle.yaml conf/conn/memsql.yaml <additional_parameters>

fetch-schemas

Fetches and analyzes the contents of a source or MemSQL database and generates a schemas.yaml file in the current directory. This mode also supports additional parameters, but they may not be available for all replication modes.

Provide --output-file <file> to change the destination for the schemas.yaml file.

Provide --filter <filter_file> to define what to retrieve from the source and include in the generated schemas file.

If the --create-sql option is specified, the CREATE TABLE statement retrieved from the source table descriptions are included in the generated schemas file for each table.

Use --unknown-to-string to treat any source column as a string if its datatype is not yet supported by Replicate.

Example:

./bin/replicate fetch-schemas conf/conn/oracle.yaml <additional_parameters>

infer-schemas

Infers schemas from a source database and conforms them to a MemSQL database.

This mode supports all additional parameters as fetch-schemas and adds a few more.

Use --src-schema <schemas_file> to infer the destination schemas based on the user-provided definitions of the source schemas. If the provided schemas file is incomplete, the missing table definitions are still retrieved from the source database.

Use --map <mapper_file> to perform various schema transformations while inferring the destination schemas.

Use --create-sql when replicating from MemSQL to MemSQL to include the CREATE TABLE statements from the source in the inferred destination schemas. This option is only available for tables that were not affected by a provided mapper file.

Note: Inferring schemas is highly recommended before replicating data as it allows the involved schemas to be examined and, if required, tailored to meet data replication requirements.

Example:

./bin/replicate infer-schemas conf/conn/oracle.yaml MEMSQL <additional_parameters>

Write Modes

Replicate has three write modes:

  1. --append-existing
  2. --replace-existing
  3. --truncate-existing

If the destination contains one or more tables with the same name as any of the tables being replicated, and no write mode is specified, Replicate exits with an error in order to preserve existing tables.

A mapper file can be used to replicate a source database or schema to a different name at the destination. A write mode can be specified to achieve the same result.

--append-existing

Data from the source is appended to existing data in MemSQL.

Example:

./bin/replicate snapshot conf/conn/oracle.yaml conf/conn/memsql.yaml --append-existing <additional_parameters>

--replace-existing

Tables from the source replace conflicting tables in MemSQL. When --lazy-init is specified, the existing tables are replaced at the last possible moment. By default, the existing MemSQL tables are replaced when Replicate initializes.

Example:

./bin/replicate snapshot conf/conn/oracle.yaml conf/conn/memsql.yaml --replace-existing --lazy-init <additional_parameters>

--truncate-existing

Tables from the source replace conflicting tables in MemSQL while preserving the existing MemSQL table schemas. When --lazy-init is specified, the existing data is replaced at the last possible moment. By default, the existing data in MemSQL is deleted when Replicate initializes.

Example:

./bin/replicate snapshot conf/conn/oracle.yaml conf/conn/memsql.yaml --truncate-existing --lazy-init <additional_parameters>