Phased Delta Migration with Failback Replication from MySQL

• Data is migrated to the target .
• This approach utilizes .
• is achieved via failback replication.

​

Example scenario

​

Before the migration

• Install the tools.
• Review the and documentation.
• and .
• 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 types of , , or can be sharded with .
• 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 .
• 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 .
• MOLT Verify checks for collation mismatches on columns. This may cause validation to fail when a is used as a primary key and the source and target databases are using different .
• MOLT Verify might give an error in case of schema changes on either the source or target database.
• 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 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 option.
  • If you define the target tables manually, review how MOLT Fetch handles . 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 . If a sequential key is necessary in your CockroachDB table, you must create it manually, after using to load and replicate the data.
• Review 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 .

​

Schema Conversion Tool

1. Upload a source .sql file to convert the syntax and identify in the table definitions.
2. Import the converted table definitions to a CockroachDB cluster:
   • When migrating to CockroachDB Cloud, the Schema Conversion Tool automatically .
   • When migrating to a self-hosted CockroachDB cluster, and pipe the directly into .

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

• (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;

​

Migrate each phase

​

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

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

​

Run MOLT Fetch

1. Issue the 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 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 or .
• 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 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 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 (forward 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

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

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

​

Replicator flags

​

Replicator metrics

--metricsAddr :30005

​

Start MOLT Replicator (forward replication)

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

​

Check that replication is working

[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

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

​

Continue MOLT Replicator after an interruption (forward replication)

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 deploy/app-eu --replicas=0

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

​

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 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 , , and .
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: Begin failback replication

• 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

SET CLUSTER SETTING kv.rangefeed.enabled = true;

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

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

SET CLUSTER SETTING kv.rangefeed.concurrent_catchup_iterators = 64;

​

Grant target database user permissions

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

​

Create a CockroachDB changefeed

1. Get the current logical timestamp from CockroachDB, after ensuring that forward replication has fully drained:
   SELECT cluster_logical_timestamp();
   
       cluster_logical_timestamp
   ----------------------------------
     1759246920563173000.0000000000
   
2. 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 .
   Explicitly set a default 10s 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.
   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, 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.
3. 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.
4. 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)

• 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

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

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

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

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

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

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

​

Replicator flags

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

​

Replicator metrics

--metricsAddr :30005

​

Start MOLT Replicator (failback replication)

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

​

Step 11: Cut over application traffic

​

Modify application code

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

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

kubectl scale deploy/app-eu --replicas=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

​

Step 12: Stop failback 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 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 , , and .
2. Cancel replication by entering ctrl-c to issue a SIGTERM signal. This returns an exit code 0.

​

Repeat for each phase

​

See also