Delta Migration from MySQL

• 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.

​

Example scenario

​

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

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

​

Create migration user on source database

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

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

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

​

Configure source database for replication

• 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.

​

Step 2: Prepare the target database

​

Define the target tables

• 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

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.

String case sensitivity

Identifier case sensitivity

AUTO_INCREMENT attribute

ENUM type

TINYINT type

Geospatial types

FIELD function

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

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

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

​

Drop constraints and indexes

• 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)

​

Create the SQL user

CREATE USER crdb_user WITH PASSWORD 'password';

GRANT ALL ON DATABASE defaultdb TO crdb_user;

GRANT SYSTEM MODIFYCLUSTERSETTING TO crdb_user;

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

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

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;

ALTER TABLE migration_schema.table1 OWNER TO crdb_user;

​

IMPORT INTO privileges

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

GRANT SYSTEM EXTERNALIOIMPLICITACCESS TO crdb_user;

​

COPY FROM privileges

GRANT admin TO crdb_user;

​

Set session variable

ALTER ROLE crdb_user SET statement_timeout = '0s';

​

Replication privileges

ALTER USER crdb_user CREATEDB;

​

Configure GC TTL

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

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

​

Step 3: Load data into CockroachDB

• 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

• 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.

molt fetch \
--source $SOURCE \
--target $TARGET \
--bucket-path 's3://bucket/path' \
--ignore-replication-check

​

Run MOLT Fetch

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, 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
   
2. 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

{"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)"}

• 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.

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

​

Run MOLT Verify

1. 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'
   
2. 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

​

Step 6: Begin forward replication

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

​

Configure MOLT Replicator

• 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

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

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

​

Replicator flags

​

Replicator metrics

--metricsAddr :30005

​

Start MOLT Replicator

1. 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

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

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

​

Step 7: Stop application traffic

kubectl scale deployment app --replicas=0

​

Step 8: Stop forward replication

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

​

Step 10: Cut over application traffic

​

Modify application code

env:
  - name: DATABASE_URL
    value: postgres://root@localhost:26257/defaultdb?sslmode=verify-full

​

Resume application traffic

kubectl scale deployment app --replicas=3

​

See also

• Migration Overview
• Migration Considerations
• Phased Bulk Load Migration
• MOLT Fetch
• MOLT Verify
• MOLT Fetch Troubleshooting for MySQL
• MOLT Replicator Troubleshooting for MySQL