Phased Delta Migration with Failback Replication from MySQL

A Phased Delta Migration with Failback Replication involves migrating data to CockroachDB in several phases. Data can be sliced per tenant, per service, per region, or per table to suit the needs of the migration. For each given migration phase, you use MOLT Fetch to perform an initial bulk load of the data, you use MOLT Replicator to update the target database via forward replication and to activate failback replication, and then you cut over application traffic to CockroachDB after schema finalization and data verification. This process is repeated for each phase of data.

Data is migrated to the target in phases.
This approach utilizes continuous replication.
Rollback is achieved via failback replication.

This approach is comparable to the Delta Migration, but dividing the data into multiple phases allows each downtime window to be shorter, and it allows each phase of the migration to be less complex. Depending on how you divide the data, it also may allow your downtime windows to affect only a subset of users. For example, dividing the data per region could mean that, when migrating the data from Region A, application usage in Region B may remain unaffected. This approach may increase overall migration complexity: its duration is longer, you will need to do the work of partitioning the data, and you will have a longer period when you run both the source and the target database concurrently. Failback replication keeps the source database up to date with changes that occur in the target database once the target database begins receiving write traffic. Failback replication ensures that, if something goes wrong during the migration process, traffic can easily be returned to the source database without data loss. Like forward replication, in this approach, failback replication is run on a per-phase basis. It can persist indefinitely, until you’re comfortable maintaining the target database as your sole data store. This approach is best for databases that are too large to migrate all at once, and in business cases where downtime must be minimal. It’s also suitable for risk-averse situations in which a safe rollback path must be ensured. It can only be performed if your team can handle the complexity of this approach, and if your source database can easily be divided into the phases you need. This page describes an example scenario. While the commands provided can be copy-and-pasted, they may need to be altered or reconsidered to suit the needs of your specific environment.

Example scenario

You have a moderately-sized (500GB) database that provides the data store for a web application. You want to migrate the entirety of this database to a new CockroachDB cluster. You will divide this migration into four geographic regions (A, B, C, and D). The application runs on a Kubernetes cluster with an NGINX Ingress Controller. Your business could not accommodate major performance issues that could arise after the migration. Therefore, you want to enable failback replication so that you can easily return to using your original database with minimal interruption. Estimated system downtime: 3-5 minutes per region.

Before the migration

Install the MOLT (Migrate Off Legacy Technology) tools.
Review the MOLT Fetch and MOLT Replicator documentation.
Develop a migration plan and prepare for the migration.
Recommended: Perform a dry run of this full set of instructions in a development environment that closely resembles your production environment. This can help you get a realistic sense of the time and complexity it requires.
Understand the prerequisites and limitations of the MOLT tools:

Limitations

MOLT Fetch limitations

Only tables with primary key types of INT, FLOAT, or UUID can be sharded with --export-concurrency.
GEOMETRY and GEOGRAPHY types are not supported.

MOLT Replicator limitations

Replication modes require connection to the primary instance (PostgreSQL primary, MySQL primary/master, or Oracle primary). MOLT cannot obtain replication checkpoints or transaction metadata from replicas.
Running DDL on the source or target while replication is in progress can cause replication failures.
TRUNCATE operations on the source are not captured. Only INSERT, UPDATE, UPSERT, and DELETE events are replicated.
Changes to virtual columns are not replicated automatically. To migrate these columns, you must define them explicitly with transformation rules.
MySQL replication is supported only with GTID-based configurations. Binlog-based features that do not use GTID are not supported.

MOLT Verify limitations

MOLT Verify compares 20,000 rows at a time by default, and row values can change between batches, potentially resulting in temporary inconsistencies in data. To configure the row batch size, use the --row_batch_size flag.
MOLT Verify checks for collation mismatches on primary key columns. This may cause validation to fail when a STRING is used as a primary key and the source and target databases are using different collations.
MOLT Verify might give an error in case of schema changes on either the source or target database.
Geospatial types cannot yet be compared.
MOLT Verify only supports comparing one MySQL database to a whole CockroachDB schema (which is assumed to be public).

Step 1: Prepare the source database

In this step, you will:

Create a dedicated migration user on your source database.
Configure the source database for replication.

Create migration user on source database

Create a dedicated migration user (for example, MIGRATION_USER) on the source database. This user is responsible for reading data from source tables during the migration. You will pass this username in the source connection string.

CREATE USER 'migration_user'@'%' IDENTIFIED BY 'password';

Grant the user privileges to select the tables you migrate and access GTID information for snapshot consistency:

GRANT SELECT ON migration_db.* TO 'migration_user'@'%';
GRANT SELECT ON mysql.gtid_executed TO 'migration_user'@'%';
FLUSH PRIVILEGES;

For replication, grant additional privileges for binlog access:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'migration_user'@'%';
FLUSH PRIVILEGES;

Configure source database for replication

Connect to the primary instance (PostgreSQL primary, MySQL primary/master, or Oracle primary), not a replica. Replicas cannot provide the necessary replication checkpoints and transaction metadata required for ongoing replication.

Enable global transaction identifiers (GTID) and configure binary logging. Set binlog-row-metadata or binlog-row-image to full to provide complete metadata for replication. Configure binlog retention to ensure GTIDs remain available throughout the migration:

MySQL 8.0.1+: Set binlog_expire_logs_seconds (default: 2592000 = 30 days) based on your migration timeline.
MySQL < 8.0: Set expire_logs_days, or manually manage retention by setting max_binlog_size and using PURGE BINARY LOGS BEFORE NOW() - INTERVAL 1 HOUR (adjusting the interval as needed). Force binlog rotation with FLUSH BINARY LOGS if needed.
Managed services: Refer to provider-specific configuration for Amazon RDS or Google Cloud SQL.

GTID replication sends all database changes to Replicator. To limit replication to specific tables or schemas, apply a userscript when you run Replicator. Refer to the Filter multiple tables cookbook example.

Version	Configuration
MySQL 5.6	`--gtid-mode=on` `--enforce-gtid-consistency=on` `--server-id={unique_id}` `--log-bin=mysql-binlog` `--binlog-format=row` `--binlog-row-image=full` `--log-slave-updates=ON`
MySQL 5.7	`--gtid-mode=on` `--enforce-gtid-consistency=on` `--binlog-row-image=full` `--server-id={unique_id}` `--log-bin=log-bin`
MySQL 8.0+	`--gtid-mode=on` `--enforce-gtid-consistency=on` `--binlog-row-metadata=full`
MariaDB	`--log-bin` `--server_id={unique_id}` `--log-basename=master1` `--binlog-format=row` `--binlog-row-metadata=full`

Step 2: Prepare the target database

Define the target tables

Convert the source table definitions into CockroachDB-compatible equivalents. CockroachDB supports the PostgreSQL wire protocol and is largely compatible with PostgreSQL syntax.

The source and target table definitions must match. Review Type mapping to understand which source types can be mapped to CockroachDB types. MySQL tables belong directly to the database specified in the connection string. A MySQL source table defined as CREATE TABLE tbl (id INT PRIMARY KEY); should map to CockroachDB’s default public schema:
CREATE TABLE tbl (id INT PRIMARY KEY);
- MOLT Fetch can automatically define matching CockroachDB tables using the drop-on-target-and-recreate option.
- If you define the target tables manually, review how MOLT Fetch handles type mismatches. You can use the MOLT Schema Conversion Tool to create matching table definitions.
Every table must have an explicit primary key.
Avoid using sequential keys. To learn more about the performance issues that can result from their use, refer to the guidance on indexing with sequential keys. If a sequential key is necessary in your CockroachDB table, you must create it manually, after using MOLT Fetch to load and replicate the data.
Review Transformations to understand how computed columns and partitioned tables can be mapped to the target, and how target tables can be renamed.
By default on CockroachDB, INT is an alias for INT8, which creates 64-bit signed integers. PostgreSQL and MySQL default to 32-bit integers. Depending on your source database or application requirements, you may need to change the integer size to 4. For more information, refer to Considerations for 64-bit signed integers.

Schema Conversion Tool

The MOLT Schema Conversion Tool (SCT) converts source table definitions to CockroachDB-compatible syntax. It requires a free CockroachDB Cloud account.

Upload a source .sql file to convert the syntax and identify unimplemented features and syntax incompatibilities in the table definitions.
Import the converted table definitions to a CockroachDB cluster:
- When migrating to CockroachDB Cloud, the Schema Conversion Tool automatically applies the converted table definitions to a new Cloud database.
- When migrating to a self-hosted CockroachDB cluster, export a converted DDL file and pipe the data definition language (DDL) directly into cockroach sql.

Syntax that cannot automatically be converted will be displayed in the Summary Report. These may include the following:

String case sensitivity

Strings are case-insensitive in MySQL and case-sensitive in CockroachDB. You may need to edit your MySQL data to get the results you expect from CockroachDB. For example, you may have been doing string comparisons in MySQL that will need to be changed to work with CockroachDB. For more information about the case sensitivity of strings in MySQL, refer to Case Sensitivity in String Searches from the MySQL documentation. For more information about CockroachDB strings, refer to STRING.

Identifier case sensitivity

Identifiers are case-sensitive in MySQL and case-insensitive in CockroachDB. When using the Schema Conversion Tool, you can either keep case sensitivity by enclosing identifiers in double quotes, or make identifiers case-insensitive by converting them to lowercase.

`AUTO_INCREMENT` attribute

The MySQL AUTO_INCREMENT attribute, which creates sequential column values, is not supported in CockroachDB. When using the Schema Conversion Tool, columns with AUTO_INCREMENT can be converted to use sequences, UUID values with gen_random_uuid(), or unique INT8 values using unique_rowid(). Cockroach Labs does not recommend using a sequence to define a primary key column. For more information, refer to Unique ID best practices.

Changing a column type during table definition conversion will cause MOLT Verify to identify a type mismatch during data validation. This is expected behavior.

`ENUM` type

MySQL ENUM types are defined in table columns. On CockroachDB, ENUM is a standalone type. When using the Schema Conversion Tool, you can either deduplicate the ENUM definitions or create a separate type for each column.

`TINYINT` type

TINYINT data types are not supported in CockroachDB. The Schema Conversion Tool automatically converts TINYINT columns to INT2 (SMALLINT).

Geospatial types

MySQL geometry types are not converted to CockroachDB geospatial types by the Schema Conversion Tool. They should be manually converted to the corresponding types in CockroachDB.

`FIELD` function

The MYSQL FIELD function is not supported in CockroachDB. Instead, you can use the array_position function, which returns the index of the first occurrence of element in the array. Example usage:

SELECT array_position(ARRAY[4,1,3,2],1);

  array_position
------------------
               2
(1 row)

While MySQL returns 0 when the element is not found, CockroachDB returns NULL. So if you are using the ORDER BY clause in a statement with the array_position function, the caveat is that sort is applied even when the element is not found. As a workaround, you can use the COALESCE operator.

SELECT * FROM table_a ORDER BY COALESCE(array_position(ARRAY[4,1,3,2],5),999);

Drop constraints and indexes

To optimize data load performance, drop all non-PRIMARY KEY constraints and indexes on the target CockroachDB database before migrating:

FOREIGN KEY
UNIQUE
Secondary indexes
CHECK
DEFAULT
NOT NULL (you do not need to drop this constraint when using drop-on-target-and-recreate for table handling)

Do not drop PRIMARY KEY constraints.

You can recreate the constraints and indexes after loading the data.

Create the SQL user

Create a SQL user in the CockroachDB cluster that has the necessary privileges. To create a user crdb_user in the default database (you will pass this username in the target connection string):

CREATE USER crdb_user WITH PASSWORD 'password';

Grant database-level privileges for schema creation within the target database:

GRANT ALL ON DATABASE defaultdb TO crdb_user;

Grant permission to modify cluster settings:

GRANT SYSTEM MODIFYCLUSTERSETTING TO crdb_user;

Grant usage on schemas being migrated:

GRANT USAGE ON SCHEMA migration_schema TO crdb_user;
GRANT USAGE ON SCHEMA public TO crdb_user;

Grant user privileges to create tables in the migration_schema schema and internal MOLT tables like _molt_fetch_exceptions in the public CockroachDB schema:

Ensure that you are connected to the target database.

GRANT CREATE ON SCHEMA migration_schema TO crdb_user;
GRANT CREATE ON SCHEMA public TO crdb_user;

If you manually defined the target tables (which means that drop-on-target-and-recreate will not be used), grant the following privileges on the schema:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO crdb_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO crdb_user;

If you will be running Fetch with drop-on-target-and-recreate, and the target schema has pre-existing tables that were created by a different user, you may need to change table ownership to allow the migration user to drop those tables. Make the following alteration for each table:

ALTER TABLE migration_schema.table1 OWNER TO crdb_user;

Depending on the MOLT Fetch data load mode you will use, grant the necessary privileges to run either IMPORT INTO or COPY FROM on the target tables:

`IMPORT INTO` privileges

Grant SELECT, INSERT, and DROP (required because the table is taken offline during the IMPORT INTO) privileges on all tables being migrated:

GRANT SELECT, INSERT, DROP ON ALL TABLES IN SCHEMA public TO crdb_user;

If you plan to use cloud storage with implicit authentication for data load, grant the EXTERNALIOIMPLICITACCESS system-level privilege:

GRANT SYSTEM EXTERNALIOIMPLICITACCESS TO crdb_user;

`COPY FROM` privileges

Grant admin privileges to the user:

GRANT admin TO crdb_user;

Set session variable

Ensure that the statement_timeout session variable is set to 0s for the user:

ALTER ROLE crdb_user SET statement_timeout = '0s';

Replication privileges

Grant permissions to create the staging schema for replication:

ALTER USER crdb_user CREATEDB;

Configure GC TTL

Before starting the initial data load, configure the garbage collection (GC) TTL on the source CockroachDB cluster to ensure that historical data remains available when replication begins. The GC TTL must be long enough to cover the full duration of the data load. Increase the GC TTL before starting the data load. For example, to set the GC TTL to 24 hours:

ALTER DATABASE defaultdb CONFIGURE ZONE USING gc.ttlseconds = 86400;

The GC TTL duration must be higher than your expected time for the initial data load.

Once replication has started successfully (which automatically protects its own data range), you can restore the GC TTL to its original value. For example, to restore to 5 minutes:

ALTER DATABASE defaultdb CONFIGURE ZONE USING gc.ttlseconds = 300;

For details, refer to Protect Changefeed Data from Garbage Collection.

Migrate each phase

Steps 3-12 are run for each phase of the data migration. When migrating the first phase, you will run through these steps for Region A. You will repeat these steps for the other regions during each subsequent migration phase.

Step 3: Load data into CockroachDB

In this step, you will:

Configure MOLT Fetch with the flags needed for your migration.
Run MOLT Fetch.
Understand how to continue a load after an interruption.

Configure MOLT Fetch

The MOLT Fetch documentation includes detailed information about how to configure MOLT Fetch, and how to monitor MOLT Fetch metrics. When you run molt fetch, you can configure the following options for data load:

Specify source and target databases: Specify URL‑encoded source and target connections.
Select data to migrate: Specify schema and table names to migrate. Important for a phased migration.
Define intermediate file storage: Export data to cloud storage or a local file server.
Define fetch mode: Specifies whether data will only be loaded into/from intermediate storage.
Shard tables: Divide larger tables into multiple shards during data export.
Data load mode: Choose between IMPORT INTO and COPY FROM.
Table handling mode: Determine how existing target tables are initialized before load.
Define data transformations: Define any row-level transformations to apply to the data before it reaches the target.
Monitor fetch metrics: Configure metrics collection during initial data load.

Read through the documentation to understand how to configure your molt fetch command and its flags. Follow best practices, especially those related to security. At minimum, the molt fetch command should include the source, target, data path, and --ignore-replication-check flags. For a phased migration, you may also choose to include --schema-filter or --table-filter flags:

molt fetch \
--source $SOURCE \
--target $TARGET \
--table-filter '.*user.*' \
--bucket-path 's3://bucket/path' \
--ignore-replication-check

However, depending on the needs of your migration, you may have many more flags set, and you may need to prepare some accompanying .json files.

Run MOLT Fetch

Perform the initial load of the source data.

Issue the MOLT Fetch command to move the source data to CockroachDB. This example command passes the source and target connection strings as environment variables, writes intermediate files to S3 storage, and uses the truncate-if-exists table handling mode to truncate the target tables before loading data. It also limits the migration to a single schema and filters three specific tables to migrate. The data load mode defaults to IMPORT INTO.
molt fetch \ --source $SOURCE \ --target $TARGET \ --table-filter 'employees|payments|orders' \ --bucket-path 's3://migration/data/cockroach' \ --table-handling truncate-if-exists

Check the output to observe fetch progress. A starting fetch message indicates that the task has started:

{"level":"info","type":"summary","num_tables":3,"cdc_cursor":"4c658ae6-e8ad-11ef-8449-0242ac140006:1-29","time":"2025-02-10T14:28:11-05:00","message":"starting fetch"}

data extraction messages are written for each table that is exported to the location in --bucket-path:

{"level":"info","table":"public.employees","time":"2025-02-10T14:28:11-05:00","message":"data extraction phase starting"}

{"level":"info","table":"public.employees","type":"summary","num_rows":200000,"export_duration_ms":1000,"export_duration":"000h 00m 01s","time":"2025-02-10T14:28:12-05:00","message":"data extraction from source complete"}

data import messages are written for each table that is loaded into CockroachDB:

{"level":"info","table":"public.employees","time":"2025-02-10T14:28:12-05:00","message":"starting data import on target"}

{"level":"info","table":"public.employees","type":"summary","net_duration_ms":1899.748333,"net_duration":"000h 00m 01s","import_duration_ms":1160.523875,"import_duration":"000h 00m 01s","export_duration_ms":1000,"export_duration":"000h 00m 01s","num_rows":200000,"cdc_cursor":"4c658ae6-e8ad-11ef-8449-0242ac140006:1-29","time":"2025-02-10T14:28:13-05:00","message":"data import on target for table complete"}

A fetch complete message is written when the fetch task succeeds:

{"level":"info","type":"summary","fetch_id":"f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c","num_tables":3,"tables":["public.employees","public.payments","public.payments"],"skipped_unmigratable_tables":[],"cdc_cursor":"4c658ae6-e8ad-11ef-8449-0242ac140006:1-29","net_duration_ms":6752.847625,"net_duration":"000h 00m 06s","time":"2024-03-18T12:30:37-04:00","message":"fetch complete"}

This message includes a cdc_cursor value. You must set the --defaultGTIDSet replication flag to this value when starting Replicator:

--defaultGTIDSet 4c658ae6-e8ad-11ef-8449-0242ac140006:1-29

Continue MOLT Fetch after an interruption

If MOLT Fetch fails while loading data into CockroachDB from intermediate files, it exits with an error message, fetch ID, and continuation token for each table that failed to load on the target database.

{"level":"info","table":"public.employees","file_name":"shard_01_part_00000001.csv.gz","message":"creating or updating token for duplicate key value violates unique constraint \"employees_pkey\"; Key (id)=(1) already exists."}
{"level":"info","table":"public.employees","continuation_token":"a1b2c3d4-e5f6-7890-abcd-ef1234567890","message":"created continuation token"}
{"level":"info","fetch_id":"f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c","message":"continue from this fetch ID"}
{"level":"error","message":"Error: error from fetching table for public.employees: error importing data: duplicate key value violates unique
  constraint \"employees_pkey\" (SQLSTATE 23505)"}

You can use this information to continue the task from the continuation point where it was interrupted. Continuation is only possible under the following conditions:

All data has been exported from the source database into intermediate files on cloud or local storage.
The initial load of source data into the target CockroachDB database is incomplete.
The load uses IMPORT INTO rather than COPY FROM.

Only one fetch ID and set of continuation tokens, each token corresponding to a table, are active at any time. See List active continuation tokens.

The following command reattempts the data load starting from a specific continuation file, but you can also use individual continuation tokens to reattempt the data load for individual tables.

molt fetch \
--source $SOURCE \
--target $TARGET \
--table-filter 'employees|payments|orders' \
--bucket-path 's3://migration/data/cockroach' \
--table-handling truncate-if-exists \
--ignore-replication-check \
--fetch-id f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c \
--continuation-file-name shard_01_part_00000001.csv.gz

Step 4: Verify the initial data load

In this step, you will use MOLT Verify to confirm that the source and target data is consistent. This ensures that the data load was successful. Use MOLT Verify’s --schema-filter or --table-filter to select only the tables that are relevant for the given phase.

Run MOLT Verify

Run the MOLT Verify command, specifying the source and target connection strings and the tables to validate.

molt verify \
--source $SOURCE \
--target $TARGET \
--table-filter 'employees|payments|orders'

Check the output to observe verify progress. A verification in progress indicates that the task has started:

{"level":"info","time":"2025-02-10T15:35:04-05:00","message":"verification in progress"}

starting verify messages are written for each specified table:

{"level":"info","time":"2025-02-10T15:35:04-05:00","message":"starting verify on public.employees, shard 1/1"}

A finished row verification message is written after each table is compared. If num_success equals num_truth_rows and the error counters (num_missing, num_mismatch, num_extraneous, and num_column_mismatch) are all 0, the table verified successfully. Any non-zero values in the error counters indicate data discrepancies that need investigation. For details on each field, refer to the MOLT Verify page.

{"level":"info","type":"summary","table_schema":"public","table_name":"employees","num_truth_rows":200004,"num_success":200004,"num_conditional_success":0,"num_missing":0,"num_mismatch":0,"num_extraneous":0,"num_live_retry":0,"num_column_mismatch":0,"time":"2025-02-10T15:35:05-05:00","message":"finished row verification on public.employees (shard 1/1)"}

A verification complete message is written when the verify task succeeds:

{"level":"info","net_duration_ms":699.804875,"net_duration":"000h 00m 00s","time":"2025-02-10T15:35:05-05:00","message":"verification complete"}

Step 5: Finalize the target schema

Add constraints and indexes

Add any constraints or indexes that you previously removed from the CockroachDB schema to facilitate data load.

If you used the --table-handling drop-on-target-and-recreate option for data load, only PRIMARY KEY and NOT NULL constraints are preserved. You must manually recreate all other constraints and indexes.

For the appropriate SQL syntax, refer to ALTER TABLE ... ADD CONSTRAINT and CREATE INDEX. Review the best practices for creating secondary indexes on CockroachDB.

Step 6: Begin forward replication

In this step, you will:

Configure MOLT Replicator with the flags needed for your migration.
Start MOLT Replicator.
Understand how to continue replication after an interruption.

Configure MOLT Replicator (forward replication)

When you run replicator, you can configure the following options for replication:

Replication connection strings: Specify URL-encoded source and target database connections.
Replicator flags: Specify required and optional flags to configure replicator behavior.
Replicator metrics: Monitor replication progress and performance.

Replication connection strings

MOLT Replicator uses --sourceConn and --targetConn to specify the source and target database connections. --sourceConn specifies the connection string of the source database:

--sourceConn 'mysql://{username}:{password}@{protocol}({host}:{port})/{database}'

--targetConn specifies the target CockroachDB connection string:

--targetConn 'postgresql://{username}:{password}@{host}:{port}/{database}'

Follow best practices for securing connection strings. Refer to Secure connections.

Replicator flags

Configure the following MOLT Replicator flags for continuous replication. For details on all available flags, refer to Replicator Flags.

Flag	Description
`--targetSchema`	Required. Target schema name on CockroachDB where tables will be replicated. Schema name must be fully qualified in the format `database.schema`.
`--defaultGTIDSet`	Required. Default GTID set for changefeed.
`--stagingSchema`	Required. Staging schema name on CockroachDB for replication metadata and checkpoints. Schema name must be fully qualified in the format `database.schema`.
`--stagingCreateSchema`	Automatically create the staging schema if it does not exist. Include this flag when starting replication for the first time.
`--fetchMetadata`	Explicitly fetch column metadata for MySQL versions that do not support `binlog_row_metadata`. Requires `SELECT` permissions on the source database or `PROCESS` privileges.
`--metricsAddr`	Enable Prometheus metrics at a specified `{host}:{port}`. Metrics are served at `http://{host}:{port}/_/varz`.
`--userscript`	Path to a userscript that enables data filtering, routing, or transformations. For examples, refer to Userscript Cookbook.

You can find the starting GTID in the cdc_cursor field of the fetch complete message after the initial data load completes.

Replicator metrics

MOLT Replicator metrics are not enabled by default. Enable Replicator metrics by specifying the --metricsAddr flag with a port (or host:port) when you start Replicator. This exposes Replicator metrics at http://{host}:{port}/_/varz. For example, the following flag exposes metrics on port 30005:

--metricsAddr :30005

For guidelines on using and interpreting replication metrics, refer to Replicator Metrics.

Start MOLT Replicator (forward replication)

With initial load complete, start replication of ongoing changes on the source to CockroachDB using MOLT Replicator.

MOLT Fetch captures a consistent point-in-time checkpoint at the start of the data load (shown as cdc_cursor in the fetch output). Starting replication from this checkpoint ensures that all changes made during and after the data load are replicated to CockroachDB, preventing data loss or duplication. The following steps use the checkpoint values from the fetch output to start replication at the correct position.

Run the replicator command, specifying the GTID from the checkpoint recorded during data load. Use --stagingSchema to specify a unique name for the staging database, and include --stagingCreateSchema to have MOLT Replicator automatically create the staging database. If you filtered tables during the initial load, write a userscript to filter tables on replication and specify the path with --userscript.

replicator mylogical \
--sourceConn $SOURCE \
--targetConn $TARGET \
--targetSchema defaultdb.public \
--defaultGTIDSet 4c658ae6-e8ad-11ef-8449-0242ac140006:1-29 \
--stagingSchema defaultdb._replicator \
--stagingCreateSchema \
--metricsAddr :30005 \
--userscript table_filter.ts \
-v

For MySQL versions that do not support binlog_row_metadata, include --fetchMetadata to explicitly fetch column metadata. This requires additional permissions on the source MySQL database. Grant SELECT permissions with GRANT SELECT ON migration_db.* TO 'migration_user'@'localhost';. If that is insufficient for your deployment, use GRANT PROCESS ON *.* TO 'migration_user'@'localhost';, though this is more permissive and allows seeing processes and server status.

Check that replication is working

Verify that Replicator is processing changes successfully. To do so, check the MOLT Replicator logs. Since you enabled debug logging with -v, you should see connection and row processing messages: You should see binlog syncer connection and row processing:

[2025/08/25 15:29:09] [info] binlogsyncer.go:463 begin to sync binlog from GTID set 77263736-7899-11f0-81a5-0242ac120002:1-38
[2025/08/25 15:29:09] [info] binlogsyncer.go:409 Connected to mysql 8.0.43 server
INFO   [2025-08-25T15:29:09-05:00] connected to MySQL version 8.0.43

When rows are successfully replicated, you should see debug output like the following:

DEBUG  [2025-08-25T15:29:38-05:00] upserted rows                                 conflicts=0 duration=1.801ms proposed=1 target="\"molt\".\"public\".\"tbl1\"" upserted=1
DEBUG  [2025-08-25T15:29:38-05:00] progressed to consistent point: 77263736-7899-11f0-81a5-0242ac120002:1-39

These messages confirm successful replication. You can disable verbose logging after verifying the connection.

Continue MOLT Replicator after an interruption (forward replication)

Run the MOLT Replicator mylogical command using the same --stagingSchema value from your initial replication command. Replicator will automatically use the saved GTID (Global Transaction Identifier) from the memo table in the staging schema (in this example, defaultdb._replicator.memo) and track advancing GTID checkpoints there. To have Replicator start from a different GTID instead of resuming from the checkpoint, clear the memo table with DELETE FROM defaultdb._replicator.memo; and run the replicator command with a new --defaultGTIDSet value.

replicator mylogical \
--sourceConn $SOURCE \
--targetConn $TARGET \
--targetSchema defaultdb.public \
--stagingSchema defaultdb._replicator \
--metricsAddr :30005 \
--userscript table_filter.ts \
-v

Replication resumes from the last checkpoint without performing a fresh load. Monitor the metrics endpoint at http://localhost:30005/_/varz to track replication progress.

Step 7: Stop application traffic

Once the inital data load has been verified and the target schema has been finalized, it’s time to begin the cutover process. First, stop application traffic to the source for this particular region. If the Kubernetes cluster that deploys the application has pre-region deployments (for example, app-us, app-eu, app-apac), you can scale down only the deployment for that region.

kubectl scale deploy/app-eu --replicas=0

Or this can be handled by the NGINX Ingress Controller, by including the following to your NGINX configuration, ensuring that the conditional statement is suitable for your deployment:

apiVersion: networking.k8s.io/v1
kind: Ingress
metadata:
  name: app
  annotations:
    nginx.ingress.kubernetes.io/server-snippet: |
      if ($http_x_region = "eu") {
        return 503;
      }
spec:
  ingressClassName: nginx
  rules:
  - host: api.example.com
    http:
      paths:
      - path: /
        pathType: Prefix
        backend:
          service:
            name: app
            port:
              number: 80

Application downtime begins now, for users in the given region.It is strongly recommended that you perform a dry run of this migration in a test environment. This will allow you to practice using the MOLT tools in real time, and it will give you an accurate sense of how long application downtime might last.

Step 8: Stop forward replication

Before you can cut over traffic to the target, the changes to the source database need to finish being written to the target. Once the source is no longer receiving write traffic, MOLT Replicator will take some seconds to finish replicating the final changes. This is known as drainage.

Wait for replication to drain, which means that all transactions that occurred on the source database have been fully processed and replicated. There are several ways to determine that replication has fully drained:
- When replication is caught up, you will not see new upserted rows logs.
- If you set up the replication metrics endpoint with --metricsAddr in the preceding steps, metrics are available at:
  http://{host}:{port}/_/varz
  Use the following Prometheus alert expression to observe when the combined rate of upserts and deletes is 0 for each schema:
  sum by (schema) (rate(apply_upserts_total[$__rate_interval]) + rate(apply_deletes_total[$__rate_interval]))
- You can also check Prometheus metrics associated with replication lag, including target_apply_transaction_lag_seconds, core_source_lag_seconds, and source_commit_to_apply_lag_seconds.
Cancel replication by entering ctrl-c to issue a SIGTERM signal. This returns an exit code 0.

Step 9: Verify the replicated data

Repeat Step 4 to verify the updated data.

Step 10: Begin failback replication

In this step, you will:

Prepare both databases for failback replication
Configure MOLT Replicator with the flags needed for your migration.
Start MOLT Replicator.
Understand how to continue replication after an interruption.

Prepare your source and target databases for failback replication

Prepare the CockroachDB cluster

For details on enabling CockroachDB changefeeds, refer to Create and Configure Changefeeds.

If you are migrating to a CockroachDB self-hosted cluster, enable rangefeeds on the cluster:

SET CLUSTER SETTING kv.rangefeed.enabled = true;

Use the following optional settings to increase changefeed throughput.

The following settings can impact source cluster performance and stability, especially SQL foreground latency during writes. For details, refer to Advanced Changefeed Configuration.

To lower changefeed emission latency, but increase SQL foreground latency:

SET CLUSTER SETTING kv.rangefeed.closed_timestamp_refresh_interval = '250ms';

To lower the closed timestamp lag duration:

SET CLUSTER SETTING kv.closed_timestamp.target_duration = '1s';

To improve catchup speeds but increase cluster CPU usage:

SET CLUSTER SETTING kv.rangefeed.concurrent_catchup_iterators = 64;

Grant target database user permissions

You should have already created a migration user on the target database (your original source database) with the necessary privileges. Refer to Create migration user on source database. For failback replication, grant the user additional privileges to write data back to the target database:

-- Grant SELECT, INSERT, UPDATE, and DELETE on tables to fail back to
GRANT SELECT, INSERT, UPDATE, DELETE ON migration_db.* TO 'migration_user'@'%';
FLUSH PRIVILEGES;

Create a CockroachDB changefeed

On the target cluster, create a CockroachDB changefeed to send changes to MOLT Replicator.

Get the current logical timestamp from CockroachDB, after ensuring that forward replication has fully drained:

SELECT cluster_logical_timestamp();

    cluster_logical_timestamp
----------------------------------
  1759246920563173000.0000000000

Create the CockroachDB changefeed pointing to the MOLT Replicator webhook endpoint. Use cursor to specify the logical timestamp from the preceding step. For details on the webhook sink URI, refer to Webhook sink.
Explicitly set a default 10s webhook_client_timeout value in the CREATE CHANGEFEED statement. This value ensures that the webhook can report failures in inconsistent networking situations and make crash loops more visible.

If any of these tables use row-level TTL, the changefeed emits a DELETE message each time a TTL job expires a row, and MOLT Replicator applies those deletes to the source database. Because TTL jobs delete rows in large batches, this can emit a high volume of changefeed messages and degrade replication performance. To prevent TTL deletes from being replicated back to the source, set the ttl_disable_changefeed_replication table storage parameter (or the sql.ttl.changefeed_replication.disabled cluster setting) before creating the changefeed. For details, refer to Prevent changefeeds from emitting row-level TTL deletes.

Create the changefeed with the diff option so it emits before-images (the row values prior to each change). MOLT Replicator uses before-images to determine a safe apply order for source transactions that change both sides of a foreign-key constraint, such as a transaction that repoints or clears a child row’s foreign-key reference while the referenced row’s UNIQUE value changes. Without diff, MOLT Replicator applies such transactions in table-dependency order, which can stall replication with a foreign-key violation (SQLSTATE 23503) retry loop.
MySQL tables belong directly to the database, not to a separate schema. The webhook URL path specifies the database name on the target MySQL database. For example, /migration_db routes changes to the migration_db database.
CREATE CHANGEFEED FOR TABLE employees, payments, orders \ INTO 'webhook-https://replicator-host:30004/migration_db?client_cert={base64_encoded_cert}&client_key={base64_encoded_key}&ca_cert={base64_encoded_ca}' \ WITH updated, diff, resolved = '250ms', min_checkpoint_frequency = '250ms', initial_scan = 'no', cursor = '1759246920563173000.0000000000', webhook_sink_config = '{"Flush":{"Bytes":1048576,"Frequency":"1s"}}', webhook_client_timeout = '10s';
The output shows the job ID:
job_id ----------------------- 1101234051444375553
Ensure that only one changefeed points to MOLT Replicator at a time to avoid mixing streams of incoming data.
Monitor the changefeed status, specifying the job ID:
SHOW CHANGEFEED JOB 1101234051444375553;
job_id | ... | status | running_status | ... ----------------------+-----+---------+-------------------------------------------+---- 1101234051444375553 | ... | running | running: resolved=1759246920563173000,0 | ...
To confirm the changefeed is active and replicating changes to the target database, check that status is running and running_status shows running: resolved={timestamp}.
running: resolved may be reported even if data isn’t being sent properly. This typically indicates incorrect host/port configuration or network connectivity issues.
Verify that Replicator is reporting incoming HTTP requests from the changefeed. To do so, check the MOLT Replicator logs. Since you enabled debug logging with -v, you should see periodic HTTP request successes:
DEBUG [2025-08-25T11:52:47-05:00] httpRequest="&{0x14000b068c0 45 200 3 9.770958ms false false}" DEBUG [2025-08-25T11:52:48-05:00] httpRequest="&{0x14000d1a000 45 200 3 13.438125ms false false}"
These debug messages confirm successful changefeed connections to MOLT Replicator. You can disable verbose logging after verifying the connection.

Configure MOLT Replicator (failback replication)

When you run replicator, you can configure the following options for replication:

Connection strings: Specify URL‑encoded source and target connections.
TLS certificate and key: Configure secure TLS connections.
Replicator flags: Specify required and optional flags to configure replicator behavior.
Replicator metrics: Monitor failback replication performance.

Replication connection strings

MOLT Replicator uses --sourceConn and --targetConn to specify the source and target database connections.

For MOLT Replicator, the source is always the replication source, while the target is always the replication target. This is distinct from the migration source and target. In the case of this example migration, the new CockroachDB cluster is the migration target, but because failback replication moves data from the migration target back to the migration source, the replication target is the original source database. In essence, the --sourceConn and --targetConn strings should be reversed for failback replication.

--sourceConn specifies the connection string of the CockroachDB cluster:

--sourceConn 'postgresql://{username}:{password}@{host}:{port}/{database}'

--targetConn specifies the original source database:

--targetConn 'mysql://{username}:{password}@{protocol}({host}:{port})/{database}'

Follow best practices for securing connection strings. Refer to Secure connections.

Secure connections

To keep your database credentials out of shell history and logs, follow these best practices when specifying your source and target connection strings:
- Avoid plaintext connection strings.
- Provide your connection strings as environment variables. For example:
  export SOURCE="postgres://migration_user:a%2452%26@localhost:5432/migration_db?sslmode=verify-full" export TARGET="postgres://root@localhost:26257/defaultdb?sslmode=verify-full"
  Afterward, reference the environment variables in MOLT commands:
  --source $SOURCE --target $TARGET
- If possible, use an external secrets manager to load the environment variables from stored secrets.
Use TLS-enabled connection strings to encrypt data in transit from MOLT to the database. When using TLS certificates, ensure certificate files are accessible to the MOLT binary on the same machine. For example, a PostgreSQL connection string with TLS certificates:
postgresql://migration_user@db.example.com:5432/appdb?sslmode=verify-full&sslrootcert=/etc/migration_db/certs/ca.pem&sslcert=/etc/migration_db/certs/client.crt&sslkey=/etc/migration_db/certs/client.key
URL-encode connection strings for the source database and CockroachDB so special characters in passwords are handled correctly.
- Given a password a$52&, pass it to the molt escape-password command with single quotes:
  molt escape-password --password 'a$52&'
  Use the encoded password in your connection string. For example:
  postgres://migration_user:a%2452%26@localhost:5432/migration_db
Remove sslmode=disable from production connection strings.

TLS certificate and key

Always use secure TLS connections for failback replication to protect data in transit. Do not use insecure configurations in production: avoid the --disableAuthentication and --tlsSelfSigned Replicator flags and insecure_tls_skip_verify=true query parameter in the changefeed webhook URI. Generate self-signed TLS certificates or certificates from an external CA. Ensure the TLS server certificate and key are accessible on the MOLT Replicator host machine via a relative or absolute file path. When you start failback with Replicator, specify the paths with --tlsCertificate and --tlsPrivateKey. For example:

replicator start \
... \
--tlsCertificate ./certs/server.crt \
--tlsPrivateKey ./certs/server.key

The client certificates defined in the changefeed webhook URI must correspond to the server certificates specified in the replicator command. This ensures proper TLS handshake between the changefeed and MOLT Replicator. To include client certificates in the changefeed webhook URL, encode them with base64 and then URL-encode the output with jq:

base64 -i ./client.crt | jq -R -r '@uri'
base64 -i ./client.key | jq -R -r '@uri'
base64 -i ./ca.crt | jq -R -r '@uri'

When you create the changefeed, pass the encoded certificates in the changefeed URL, where client_cert, client_key, and ca_cert are webhook sink parameters:

CREATE CHANGEFEED FOR TABLE table1, table2
INTO 'webhook-https://host:port/database/schema?client_cert={base64_and_url_encoded_cert}&client_key={base64_and_url_encoded_key}&ca_cert={base64_and_url_encoded_ca}'
WITH ...;

For additional details on the webhook sink URI, refer to Webhook sink.

Replicator flags

Flag	Description
`--stagingSchema`	Required. Staging schema name on CockroachDB for the changefeed checkpoint table. Schema name must be fully qualified in the format `database.schema`.
`--bindAddr`	Required. Network address to bind the webhook sink for the changefeed. For example, `:30004`.
`--tlsCertificate`	Path to the server TLS certificate for the webhook sink. Refer to TLS certificate and key.
`--tlsPrivateKey`	Path to the server TLS private key for the webhook sink. Refer to TLS certificate and key.Q
`--metricsAddr`	Enable Prometheus metrics at a specified `{host}:{port}`. Metrics are served at `http://{host}:{port}/_/varz`.
`--userscript`	Path to a userscript that enables data filtering, routing, or transformations. For examples, refer to Userscript Cookbook.

The staging schema is first created during initial replication setup with --stagingCreateSchema.
When configuring a secure changefeed for failback, you must include --tlsCertificate and --tlsPrivateKey, which specify the paths to the server certificate and private key for the webhook sink connection.

Replicator metrics

--metricsAddr :30005

For guidelines on using and interpreting replication metrics, refer to Replicator Metrics.

Start MOLT Replicator (failback replication)

With initial load complete, start replication of ongoing changes on the source to CockroachDB using MOLT Replicator.

Run the MOLT Replicator start command to begin failback replication from CockroachDB to your source database. In this example, --metricsAddr :30005 enables a Prometheus endpoint for monitoring replication metrics, and --bindAddr :30004 sets up the webhook endpoint for the changefeed. --stagingSchema specifies the staging database name (defaultdb._replicator in this example) used for replication checkpoints and metadata. This staging database was created during initial forward replication when you first ran MOLT Replicator with --stagingCreateSchema.

replicator start \
--targetConn $TARGET \
--stagingConn $STAGING \
--stagingSchema defaultdb._replicator \
--metricsAddr :30005 \
--bindAddr :30004 \
--tlsCertificate ./certs/server.crt \
--tlsPrivateKey ./certs/server.key \
-v

Continue MOLT Replicator after an interruption (failback replication)

replicator mylogical \
--sourceConn $SOURCE \
--targetConn $TARGET \
--targetSchema defaultdb.public \
--stagingSchema defaultdb._replicator \
--metricsAddr :30005 \
--userscript table_filter.ts \
-v

Replication resumes from the last checkpoint without performing a fresh load. Monitor the metrics endpoint at http://localhost:30005/_/varz to track replication progress.

Step 11: Cut over application traffic

With the target cluster verified and finalized, it’s time to resume application traffic for the current migration phase.

Modify application code

In the application back end, update the application to route traffic for this migration phase to the CockroachDB cluster. A simple example:

env:
  - name: DATABASE_URL_US_EAST
    value: postgres://root@cockroachdb.us-east:26257/defaultdb?sslmode=verify-full
  - name: DATABASE_URL_US_WEST
    value: postgres://legacy-db.us-west:5432/defaultdb  # Still on source

In your application code, route database connections based on the user’s region:

def get_db_connection(user_region):
    if user_region == "us-east":
        return os.getenv("DATABASE_URL_US_EAST")  # CockroachDB
    else:
        return os.getenv("DATABASE_URL_US_WEST")  # Source database

Resume application traffic

If you halted traffic by scaling down a regional Kubernetes deployment, scale it back up.

kubectl scale deploy/app-eu --replicas=3

Or if this was handled by the NGINX Controller, remove the 503 block that was written in step 3:

apiVersion: networking.k8s.io/v1
kind: Ingress
metadata:
  name: app
#   annotations:
#     nginx.ingress.kubernetes.io/server-snippet: |
#       if ($http_x_region = "eu") {
#         return 503;
#       }
spec:
  ingressClassName: nginx
  rules:
  - host: api.example.com
    http:
      paths:
      - path: /
        pathType: Prefix
        backend:
          service:
            name: app
            port:
              number: 80

This ends downtime for the current migration phase.

Step 12: Stop failback replication

After traffic has been cut over to the target, you can maintain failback replication indefinitely. Once you decide that you want to use the CockroachDB cluster as your sole data store going forward, you can end failback replication with the following steps.

Wait for replication to drain, which means that all transactions that occurred on the source database have been fully processed and replicated. There are several ways to determine that replication has fully drained:
- When replication is caught up, you will not see new upserted rows logs.
- If you set up the replication metrics endpoint with --metricsAddr in the preceding steps, metrics are available at:
  http://{host}:{port}/_/varz
  Use the following Prometheus alert expression to observe when the combined rate of upserts and deletes is 0 for each schema:
  sum by (schema) (rate(apply_upserts_total[$__rate_interval]) + rate(apply_deletes_total[$__rate_interval]))
- You can also check Prometheus metrics associated with replication lag, including target_apply_transaction_lag_seconds, core_source_lag_seconds, and source_commit_to_apply_lag_seconds.
Cancel replication by entering ctrl-c to issue a SIGTERM signal. This returns an exit code 0.

Repeat for each phase

During the next scheduled migration phase, return to step 3 to migrate the next phase of data. Repeat steps 3-12 for each phase of data, until every region’s data has been migrated and all application traffic has been cut over to the target.

​Example scenario

​Before the migration

​Limitations

​MOLT Fetch limitations

​MOLT Replicator limitations

​MOLT Verify limitations

​Step 1: Prepare the source database

​Create migration user on source database

​Configure source database for replication

​Step 2: Prepare the target database

​Define the target tables

​Schema Conversion Tool

String case sensitivity

Identifier case sensitivity

AUTO_INCREMENT attribute

ENUM type

TINYINT type

Geospatial types

FIELD function

​Drop constraints and indexes

​Create the SQL user

​IMPORT INTO privileges

​COPY FROM privileges

​Set session variable

​Replication privileges

​Configure GC TTL

​Migrate each phase

​Step 3: Load data into CockroachDB

​Configure MOLT Fetch

​Run MOLT Fetch

​Continue MOLT Fetch after an interruption

​Step 4: Verify the initial data load

​Run MOLT Verify

​Step 5: Finalize the target schema

​Add constraints and indexes

​Step 6: Begin forward replication

​Configure MOLT Replicator (forward replication)

​Replication connection strings

​Replicator flags

​Replicator metrics

​Start MOLT Replicator (forward replication)

​Check that replication is working

​Continue MOLT Replicator after an interruption (forward replication)

​Step 7: Stop application traffic

​Step 8: Stop forward replication

​Step 9: Verify the replicated data

​Step 10: Begin failback replication

​Prepare your source and target databases for failback replication

​Prepare the CockroachDB cluster

​Grant target database user permissions

​Create a CockroachDB changefeed

​Configure MOLT Replicator (failback replication)

​Replication connection strings

Secure connections

​TLS certificate and key

​Replicator flags

​Replicator metrics

​Start MOLT Replicator (failback replication)

​Continue MOLT Replicator after an interruption (failback replication)

​Step 11: Cut over application traffic

​Modify application code

​Resume application traffic

​Step 12: Stop failback replication

​Repeat for each phase

​See also

Example scenario

Before the migration

Limitations

MOLT Fetch limitations

MOLT Replicator limitations

MOLT Verify limitations

Step 1: Prepare the source database

Create migration user on source database

Configure source database for replication

Step 2: Prepare the target database

Define the target tables

Schema Conversion Tool

`AUTO_INCREMENT` attribute

`ENUM` type

`TINYINT` type

`FIELD` function

Drop constraints and indexes

Create the SQL user

`IMPORT INTO` privileges

`COPY FROM` privileges

Set session variable

Replication privileges

Configure GC TTL

Migrate each phase

Step 3: Load data into CockroachDB

Configure MOLT Fetch

Run MOLT Fetch

Continue MOLT Fetch after an interruption

Step 4: Verify the initial data load

Run MOLT Verify

Step 5: Finalize the target schema

Add constraints and indexes

Step 6: Begin forward replication

Configure MOLT Replicator (forward replication)

Replication connection strings

Replicator flags

Replicator metrics

Start MOLT Replicator (forward replication)

Check that replication is working

Continue MOLT Replicator after an interruption (forward replication)

Step 7: Stop application traffic

Step 8: Stop forward replication

Step 9: Verify the replicated data

Step 10: Begin failback replication

Prepare your source and target databases for failback replication

Prepare the CockroachDB cluster

Grant target database user permissions

Create a CockroachDB changefeed

Configure MOLT Replicator (failback replication)

Replication connection strings

TLS certificate and key

Replicator flags

Replicator metrics

Start MOLT Replicator (failback replication)

Continue MOLT Replicator after an interruption (failback replication)

Step 11: Cut over application traffic

Modify application code

Resume application traffic

Step 12: Stop failback replication

Repeat for each phase

See also