> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Delta Migration from MySQL
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
A *Delta Migration* uses an initial data load, followed by continuous replication, to migrate data to CockroachDB. In this approach, you migrate most application data to the target using MOLT Fetch **before** stopping application traffic to the source database. You then use MOLT Replicator to keep the target database in sync with any changes in the source database (the migration *delta*), before finally halting traffic to the source and cutting over to the target after schema finalization and data verification.
* All source data is migrated to the target at once.
* This approach utilizes continuous replication.
* Failback replication is supported, though this example will not use it. See Phased Delta Migration with Failback Replication for an example of a migration that uses failback replication.
This approach is best for production environments that need to minimize system downtime.
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 small (300 GB) database that provides the data store for a web application. You want to migrate the entirety of this database to a new CockroachDB cluster. Business cannot accommodate a full maintenance window, but it can accommodate a brief (\<60 second) halt in traffic.
The application runs on a Kubernetes cluster.
**Estimated system downtime:** 3-5 minutes.
## 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](https://dev.mysql.com/doc/refman/8.0/en/replication-gtids.html)-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](#create-migration-user-on-source-database).
* [Configure the source database for replication](#configure-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](#source-connection-string).
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE USER 'migration_user'@'%' IDENTIFIED BY 'password';
```
Grant the user privileges to select the tables you migrate and access GTID information for snapshot consistency:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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)](https://dev.mysql.com/doc/refman/8.0/en/replication-options-gtids.html) 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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql-stored-proc-configuring.html) or [Google Cloud SQL](https://cloud.google.com/sql/docs/mysql/flags#mysql-b).
GTID replication sends all database changes to Replicator. To limit replication to specific tables or schemas, [apply a userscript](#replicator-flags) 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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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](#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.
1. Upload a source `.sql` file to convert the syntax and identify unimplemented features and syntax incompatibilities in the table definitions.
2. 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](https://dev.mysql.com/doc/refman/8.0/en/case-sensitivity.html) 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`](https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html) 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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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](#table-handling-mode))
Do **not** drop `PRIMARY KEY` constraints.
You can [recreate the constraints and indexes after loading the data](#add-constraints-and-indexes).
### 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](#target-connection-string)):
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE USER crdb_user WITH PASSWORD 'password';
```
Grant database-level privileges for schema creation within the target database:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT ALL ON DATABASE defaultdb TO crdb_user;
```
Grant permission to modify cluster settings:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT SYSTEM MODIFYCLUSTERSETTING TO crdb_user;
```
Grant usage on schemas being migrated:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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`](#table-handling-mode) will not be used), grant the following privileges on the schema:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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`](#table-handling-mode), 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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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`](#import-into-privileges) or [`COPY FROM`](#copy-from-privileges) 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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT SELECT, INSERT, DROP ON ALL TABLES IN SCHEMA public TO crdb_user;
```
If you plan to use [cloud storage with implicit authentication](#cloud-storage-authentication) for data load, grant the `EXTERNALIOIMPLICITACCESS` system-level privilege:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT SYSTEM EXTERNALIOIMPLICITACCESS TO crdb_user;
```
#### `COPY FROM` privileges
Grant `admin` privileges to the user:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
GRANT admin TO crdb_user;
```
#### Set session variable
Ensure that the `statement_timeout` session variable is set to `0s` for the user:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER ROLE crdb_user SET statement_timeout = '0s';
```
#### Replication privileges
Grant permissions to create the staging schema for replication:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER USER crdb_user CREATEDB;
```
### Configure GC TTL
Before starting the [initial data load](#run-molt-fetch), 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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE defaultdb CONFIGURE ZONE USING gc.ttlseconds = 300;
```
For details, refer to Protect Changefeed Data from Garbage Collection.
## Step 3: Load data into CockroachDB
In this step, you will:
* [Configure MOLT Fetch with the flags needed for your migration](#configure-molt-fetch).
* [Run MOLT Fetch](#run-molt-fetch).
* [Understand how to continue a load after an interruption](#continue-molt-fetch-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.
* 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:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
molt fetch \
--source $SOURCE \
--target $TARGET \
--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.
1. 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](#secure-connections), writes [intermediate files](#intermediate-file-storage) to S3 storage, and uses the `truncate-if-exists` [table handling mode](#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`.
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
molt fetch \
--source $SOURCE \
--target $TARGET \
--table-filter 'employees|payments|orders' \
--bucket-path 's3://migration/data/cockroach' \
--table-handling truncate-if-exists
```
2. Check the output to observe `fetch` progress.
A `starting fetch` message indicates that the task has started:
```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"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`:
```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"level":"info","table":"public.employees","time":"2025-02-10T14:28:11-05:00","message":"data extraction phase starting"}
```
```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"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:
```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"level":"info","table":"public.employees","time":"2025-02-10T14:28:12-05:00","message":"starting data import on target"}
```
```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"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:
```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"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](#start-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.
```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"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`](#data-load-mode).
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.
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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
Use MOLT Verify to confirm that the source and target data is consistent. This ensures that the data load was successful.
### Run MOLT Verify
1. Run the MOLT Verify command, specifying the source and target [connection strings](#connection-strings) and the tables to validate.
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
molt verify \
--source $SOURCE \
--target $TARGET \
--table-filter 'employees|payments|orders'
```
2. Check the output to observe `verify` progress.
A `verification in progress` indicates that the task has started:
```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"level":"info","time":"2025-02-10T15:35:04-05:00","message":"verification in progress"}
```
`starting verify` messages are written for each specified table:
```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"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.
```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"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:
```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"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](#drop-constraints-and-indexes) 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](#configure-molt-replicator).
* [Start MOLT Replicator](#start-molt-replicator).
* [Understand how to continue replication after an interruption](#continue-molt-replicator-after-an-interruption).
### Configure MOLT Replicator
When you run `replicator`, you can configure the following options for replication:
* [Replication connection strings](#replication-connection-strings): Specify URL-encoded source and target database connections.
* [Replicator flags](#replicator-flags): Specify required and optional flags to configure replicator behavior.
* [Replicator metrics](#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](#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](#start-fetch) 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
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.
1. Run the `replicator` command, specifying the GTID from the [checkpoint recorded during data load](#run-molt-fetch). 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](#schema-and-table-filtering), write a userscript to filter tables on replication and specify the path with `--userscript`.
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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
1. 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
Run the MOLT Replicator `mylogical` command using the same `--stagingSchema` value from your [initial replication command](#start-molt-replicator).
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.
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.
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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. Scale down the Kubernetes cluster to zero pods.
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
kubectl scale deployment app --replicas=0
```
Application downtime begins now.
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*.
1. 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`.
2. 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](#step-4-verify-the-initial-data-load) to verify the updated data.
## Step 10: Cut over application traffic
With the target cluster verified and finalized, it's time to resume application traffic.
### Modify application code
In the application back end, make sure that the application now directs traffic to the CockroachDB cluster. For example:
```yml theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
env:
- name: DATABASE_URL
value: postgres://root@localhost:26257/defaultdb?sslmode=verify-full
```
### Resume application traffic
Scale up the Kubernetes deployment to the original number of replicas:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
kubectl scale deployment app --replicas=3
```
This ends downtime.
## See also
* Migration Overview
* Migration Considerations
* Phased Bulk Load Migration
* MOLT Fetch
* MOLT Verify
* MOLT Fetch Troubleshooting for MySQL
* MOLT Replicator Troubleshooting for MySQL