How to Migrate Data to MySQL from Other Databases

Every migration I’ve ever done has taught me the same lesson: the actual data transfer is rarely the hard part. The hard part is everything around it — data type mismatches, character encoding surprises, foreign key ordering, and the one column that used a database-specific feature MySQL doesn’t support the same way. I once migrated a PostgreSQL database that used arrays as a column type, and figuring out the cleanest way to represent that in MySQL took longer than moving the other 40 tables combined.

This guide walks through a structured, professional approach to migrating data into MySQL, whether you’re coming from PostgreSQL, SQL Server, Oracle, MongoDB, or a legacy CSV-based system.

Migration Architecture Overview

graph TD
    A[Source Database] --> B[Schema Analysis & Mapping]
    B --> C[Data Type Conversion Rules]
    C --> D[Export/Extract Data]
    D --> E[Transform - Encoding, Format, Constraints]
    E --> F[Load into MySQL Staging Tables]
    F --> G[Validation & Reconciliation]
    G --> H[Cutover to Production MySQL]

A migration that skips the validation step is a migration waiting to produce a support ticket three weeks later when someone notices a column full of silently truncated data.

Step 1: Assess and Map the Schema

Before moving a single row, document the source schema and how each type maps to MySQL.

Step 2: Choose Your Migration Method

Migrating from PostgreSQL

Export schema and data using pg_dump in a portable format:

pg_dump -h localhost -U postgres -d source_db --data-only --column-inserts > data.sql

Because PostgreSQL and MySQL SQL dialects differ (quoting, sequences, data types), a raw pg_dump file usually needs cleanup rather than a direct import. For anything beyond a trivial schema, I use MySQL Workbench’s Migration Wizard, which connects directly to the source PostgreSQL instance, reads its catalog, proposes type mappings, and generates the MySQL DDL automatically.

Example transformation for a boolean column:

-- PostgreSQL source
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    is_active BOOLEAN DEFAULT TRUE
);

-- MySQL target
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    is_active TINYINT(1) DEFAULT 1
);

Migrating from SQL Server

# Export to CSV using bcp utility
bcp "SELECT * FROM dbo.customers" queryout customers.csv -c -t, -S server_name -U username -P password

Then load into MySQL:

LOAD DATA LOCAL INFILE 'customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(customer_id, customer_name, email, created_date);

Watch out for SQL Server’s DATETIME2 fractional precision and NVARCHAR Unicode handling — always set your MySQL connection and table character set to utf8mb4 before loading to avoid silent character corruption.

Migrating from MongoDB (Document to Relational)

This is the most involved case because you’re changing data models, not just syntax.

// Sample MongoDB document
{
  "_id": "6512abc",
  "name": "Acme Corp",
  "contacts": [
    { "type": "email", "value": "sales@acme.com" },
    { "type": "phone", "value": "555-0110" }
  ]
}

This needs to be normalized into two related MySQL tables:

CREATE TABLE companies (
    company_id VARCHAR(24) PRIMARY KEY,
    name VARCHAR(200)
);

CREATE TABLE company_contacts (
    contact_id INT AUTO_INCREMENT PRIMARY KEY,
    company_id VARCHAR(24),
    contact_type VARCHAR(20),
    contact_value VARCHAR(200),
    FOREIGN KEY (company_id) REFERENCES companies(company_id)
);

A typical approach: write a script (Python with pymongo and mysql-connector-python) that iterates documents, inserts the parent row, then inserts each embedded array item into the child table.

import pymongo
import mysql.connector

mongo_client = pymongo.MongoClient("mongodb://localhost:27017")
mysql_conn = mysql.connector.connect(host="localhost", user="root", password="pass", database="target_db")
cursor = mysql_conn.cursor()

for doc in mongo_client.crm.companies.find():
    cursor.execute(
        "INSERT INTO companies (company_id, name) VALUES (%s, %s)",
        (str(doc["_id"]), doc["name"])
    )
    for contact in doc.get("contacts", []):
        cursor.execute(
            "INSERT INTO company_contacts (company_id, contact_type, contact_value) VALUES (%s, %s, %s)",
            (str(doc["_id"]), contact["type"], contact["value"])
        )

mysql_conn.commit()

Bulk Loading CSV Data Efficiently

For large flat-file migrations, LOAD DATA INFILE is dramatically faster than row-by-row INSERT statements:

LOAD DATA INFILE '/var/lib/mysql-files/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(order_id, customer_id, order_date, total_amount);

Sample output:

Query OK, 1450233 rows affected (12.44 sec)
Records: 1450233  Deleted: 0  Skipped: 0  Warnings: 0

Handling Foreign Keys During Migration

Load parent tables before child tables, or temporarily disable checks and validate afterward:

SET FOREIGN_KEY_CHECKS = 0;
-- run your bulk loads in any order
SET FOREIGN_KEY_CHECKS = 1;

Always re-enable checks and run a validation query afterward to catch orphaned rows that would otherwise violate referential integrity:

SELECT o.order_id 
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Validation and Reconciliation

Never trust a migration until you’ve verified row counts and spot-checked data:

-- Row count check
SELECT COUNT(*) FROM orders;

-- Checksum-style comparison for a sample of rows
SELECT SUM(total_amount), COUNT(*), MIN(order_date), MAX(order_date) FROM orders;

Compare these aggregates against the same query run on the source database before cutover.

Real-World Scenario: Zero-Downtime Cutover

For a production system that can’t tolerate extended downtime, I use a phased approach:

1. Do a full initial load into MySQL while the source system stays live.
2. Set up ongoing change data capture (CDC) — via triggers on the source, a CDC tool, or application-level dual writes — to capture changes made during the migration window.
3. Apply the captured changes to MySQL to catch up.
4. Schedule a short maintenance window, do a final delta sync, switch the application’s connection string to MySQL, and verify.

sequenceDiagram
    participant App
    participant Source DB
    participant MySQL
    App->>Source DB: Normal writes (pre-cutover)
    Note over Source DB,MySQL: Full initial load + CDC replication
    App->>Source DB: Final writes before cutover
    Source DB->>MySQL: Final delta sync
    App->>MySQL: Cutover - writes now go to MySQL

Performance and Optimization Tips

• Disable indexes and constraints during bulk load, then rebuild them afterward — this is often dramatically faster than maintaining indexes row-by-row during a huge insert.

ALTER TABLE orders DISABLE KEYS;
-- bulk load here
ALTER TABLE orders ENABLE KEYS;

• Increase innodb_buffer_pool_size and bulk_insert_buffer_size temporarily during large migrations, then return them to normal production values afterward.
• Batch inserts (a few thousand rows per statement) rather than single-row inserts when scripting a migration manually.
• Always set the character set explicitly to utf8mb4 at both the connection and table level to prevent silent data corruption on non-ASCII text.

Security Considerations

• Migration scripts often contain database credentials — store them in environment variables or a secrets manager, never hard-coded in a committed script.
• Sanitize or mask sensitive fields (PII, payment data) if migrating into a non-production/staging environment for testing.
• Confirm that MySQL’s user privileges for the migration account are scoped narrowly and removed or rotated after the migration completes.

Troubleshooting Common Issues

Garbled text after migration. Almost always a character set mismatch — confirm source encoding, target table charset (utf8mb4), and the client/connection charset all agree.

Truncated data warnings. Compare source column lengths against MySQL’s target column definitions; VARCHAR limits and TEXT vs LONGTEXT choices matter here.

Foreign key constraint failures during load. Load parent tables first, or disable checks temporarily and validate referential integrity afterward as shown above.

Auto-increment values don’t match the source’s ID sequence. Explicitly insert IDs from the source system rather than letting MySQL generate new ones, then reset the auto-increment counter:

ALTER TABLE orders AUTO_INCREMENT = 1450234;

Frequently Asked Questions

What’s the fastest way to migrate a very large table? LOAD DATA INFILE with indexes disabled during load, followed by rebuilding indexes, is typically the fastest approach for large flat-file style migrations.

Can I migrate with zero downtime? Yes, using a phased approach with an initial bulk load followed by change data capture to sync ongoing changes until a brief cutover window.

Do I need to convert stored procedures and triggers too? Yes — stored logic almost never translates automatically between database engines and typically needs to be manually rewritten in MySQL’s procedural SQL syntax.

How do I handle a source database’s array or JSON-heavy schema? Either normalize into related tables (preferred for relational integrity and indexing) or store as MySQL’s native JSON type if the data is genuinely document-like and doesn’t need to be queried relationally.

Interview Questions

1. What are the key considerations when mapping data types between two different database engines?
2. Why would you disable foreign key checks and indexes during a bulk migration, and what’s the risk if you forget to re-enable and validate them?
3. How would you design a zero-downtime migration strategy for a high-traffic production database?
4. What’s the difference between a one-time migration and an ongoing data synchronization pipeline?
5. How do you handle migrating denormalized document data (e.g., MongoDB) into a normalized relational schema?
6. What validation steps would you perform before considering a migration complete?

Summary and Key Takeaways

• A successful migration starts with careful schema and data type mapping, not the data transfer itself.
• Different source systems (PostgreSQL, SQL Server, MongoDB, flat files) each bring their own quirks that need explicit handling.
• LOAD DATA INFILE combined with temporarily disabled keys and constraints is usually the fastest reliable bulk-load approach.
• Validation — row counts, aggregate checksums, referential integrity checks — is not optional; it’s what separates a migration from a data loss incident.
• For production systems, a phased migration with change data capture allows near-zero downtime cutovers.

Migrations are unglamorous work, but they’re some of the highest-stakes projects a DBA does — get it right quietly, or get a very loud phone call later.

References

• MySQL 8.0 Reference Manual: LOAD DATA Statement
• MySQL Workbench Migration Documentation
• MySQL 8.0 Reference Manual: Character Sets and Collations
• MySQL 8.0 Reference Manual: Foreign Key Constraints