Schemas

A schema is a description of a storage data structure. For a database, it is the description of its collection of tables.

Source Schema

Use --src-schemas <schemas_file> to specify a source schema. Rather than retrieving the schemas from the source, the provided schemas file is used as the source of truth. This functionality is mainly intended for specialized use cases, and it is assumed that the provided file is compatible with the actual state of the source database.

When source schemas are retrieved and combined with the user-provided schemas, the user-provided schemas take precedence.

Run Replicate with fetch-schemas to obtain the source schemas_file.

Destination Schema

Use --dst-schemas <schemas_file> to specify a destination schema.

This parameter is typically used more frequently. MemSQL’s schemas can be fine-tuned, such as changing column data types and table constraints, by fetching schemas (via fetch-schemas) or inferring schemas (via infer-schemas) and modifying the generated schemas_file before using it with MemSQL (via --dst-schemas).

Definitions provided via --dst-schemas override all information inferred from the source for all tables contained in the schemas_file.

Using --dst-schemas has no effect for existing tables if either --truncate-existing or --append-existing write modes are used.

Constraints

When creating tables on the destination, Replicate will also create all constraints according to the ones present on the source. This behavior can be fine-tuned using --dst-schemas.

Primary Keys

By default, if there’s a primary key present on the source table, an equivalent primary key will be created on the destination. On MemSQL, primary keys are supported only for row-store tables as column-store tables use sort keys.

Unique Keys

By default, if there’s a unique key present on the source table, an equivalent unique key will be created on the destination.

Shard Keys

The only columns that can be included in the shard key are those that are already included in the primary key. In cases where columns of the source table are included in the shard key but not in the primary key, those columns will automatically be added to the primary key created on the destination.

If the source is Oracle, the source primary key will be used as the shard key on the destination.

Sort Keys

Currently, sort keys are only supported for MemSQL column-store tables, in which case they are required for the tables to be created successfully.

If the source is Oracle, the source partitioning key will be used as a sort key on the destination.

Was this article useful?