How I Completed a 100+ Table Migration in One Month Using Navicat Tricks

Problem Overview

A migration project required moving over 100 tables from MySQL to MySQL, MongoDB to PostgreSQL, and supporting dynamic new tables within a one‑month deadline. The source (outer) and target (inner) environments were completely network‑isolated: the outer side could only read source databases, the inner side could only write to target databases. Alibaba Cloud OSS was the only bridge between the two zones.

Key Technical Challenges

Diverse schema : each table had its own columns, types and primary keys, making static MyBatis mappers impractical.

Multiple sync strategies : full sync, company‑level conditional sync, shop‑level incremental sync and shop‑level full sync needed flexible configuration.

Special characters in data : semicolons, quotes and newlines broke naïve line‑based parsing.

Huge data volume : single tables could exceed 10 million rows, causing OOM if loaded entirely.

MongoDB‑to‑PostgreSQL type gap : ObjectId, BSON objects and arrays required custom mapping.

Network isolation : traditional ETL tools that need simultaneous source and target access could not be used.

Foreign‑key dependencies : tables such as order_items depended on orders, requiring ordered execution.

Inspiration from Navicat

Navicat’s export workflow revealed that the combination of SHOW CREATE TABLE (to obtain DDL) and SELECT * (to fetch data) can be reproduced programmatically to generate standard SQL files.

Overall Architecture

The solution is split into two zones:

Outer system : reads source schemas, streams data, generates SQL files, replaces the terminating semicolon with a unique marker ;#END#, and uploads the files to OSS.

Inner system : scans OSS, downloads files line‑by‑line, restores the original semicolon, batches statements (100‑500 per batch), executes them with autoCommit=true to avoid long transactions, and deletes the OSS file on success.

Core Implementations

Dynamic Table Structure Parsing

public TableStructure getTableStructure(DataSource ds, String tableName) {
    String sql = "SHOW CREATE TABLE `" + tableName + "`";
    try (Connection conn = ds.getConnection();
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {
        if (rs.next()) {
            String ddl = rs.getString(2); // DDL is in the second column
            List<String> columns = parseColumns(ddl); // extract column names
            String primaryKey = parsePrimaryKey(ddl); // extract PK
            return new TableStructure(columns, primaryKey);
        }
    }
    return null;
}

Parsing the DDL yields column names, types and primary‑key information without hard‑coded mappings.

Special Character Handling

Each SQL line ends with a custom delimiter ;#END# so that data‑embedded semicolons or newlines do not break parsing.

String SPECIAL_DELIMITER = ";#END#";
writer.write(sql + SPECIAL_DELIMITER + System.lineSeparator());

During execution the delimiter is replaced back to a normal semicolon.

Configurable Sync Strategies

A sync_config table stores the sync type, WHERE template and delete strategy for each table. Placeholders such as {shopId}, {companyId} and {lastTime} are replaced at runtime.

private String buildWhereCondition(String template, SyncContext ctx) {
    if (template == null) return ""; // full table sync
    return template
        .replace("{shopId}", String.valueOf(ctx.getShopId()))
        .replace("{companyId}", String.valueOf(ctx.getCompanyId()))
        .replace("{lastTime}", ctx.getLastSyncTime());
}

Streaming Large Tables

MySQL streaming is enabled with stmt.setFetchSize(Integer.MIN_VALUE), ensuring only one row is held in memory at a time.

Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
ResultSet rs = stmt.executeQuery(sql);

MongoDB uses a CloseableIterator<Document> to read documents one by one.

Type Conversion for MongoDB → PostgreSQL

private String convertValue(Object value, String typeRule) {
    if (value == null) return "NULL";
    switch (typeRule) {
        case "JSONB":
            String json = toJsonString(value);
            return "'" + escapeSql(json) + "'::jsonb";
        case "INTEGER_ARRAY":
            List<Integer> list = (List) value;
            return "ARRAY[" + String.join(",", list) + "]::INTEGER[]";
        case "OBJECTID_TO_VARCHAR":
            return "'" + value.toString() + "'";
        default:
            return convertDefault(value);
    }
}

SQL File Generation and Execution

For each table the process is:

Generate a DELETE statement (or TRUNCATE) based on the configured delete strategy.

Stream source rows, build an INSERT header using column names obtained from ResultSetMetaData.

Append values row‑by‑row, batching every N rows (e.g., 10) into a single INSERT statement.

Write each complete SQL line with the ;#END# delimiter.

// example of writing a line
writer.write(deleteStatement + ";#END#");
writer.write(System.lineSeparator());
writer.write(insertHeader + values + ";#END#");
writer.write(System.lineSeparator());

Inner side reads the file line by line, restores the delimiter, batches statements (100‑500 per batch) and executes them with auto‑commit.

StringBuilder currentSql = new StringBuilder();
String line;
while ((line = reader.readLine()) != null) {
    currentSql.append(line);
    if (currentSql.toString().endsWith(";#END#")) {
        String realSql = currentSql.toString().replace(";#END#", ";");
        sqlBatch.add(realSql);
        currentSql.setLength(0);
        if (sqlBatch.size() >= 100) {
            executeBatch(stmt, sqlBatch);
            sqlBatch.clear();
        }
    }
}
if (!sqlBatch.isEmpty()) {
    executeBatch(stmt, sqlBatch);
}
conn.setAutoCommit(true);

Results

200+ tables migrated (including future additions).

Largest table >10 million rows.

Initial full sync completed in 10‑30 minutes.

Daily incremental sync ≈30 seconds for company‑level tables and ≈1 minute for shop‑level tables.

Memory usage stayed around 200 MB; no OOM incidents over three months of stable operation.

Project finished in 25 days, five days ahead of schedule.

Key Takeaways

Leverage existing database capabilities ( SHOW CREATE TABLE, streaming queries) instead of writing per‑table mappers.

Push heavy logic to the outer zone where debugging is easy; keep the inner zone simple and robust.

Drive behavior with configuration tables and placeholders, eliminating code changes for new tables.

Use streaming reads and a custom delimiter to handle massive data safely without OOM.