📖 Overview

Database migration means moving schema (tables, views, indexes, stored procedures) and data from one relational database system to another. Each RDBMS has its own SQL dialect, data types, and features — so migration is rarely a simple copy-paste.

🗺 Migration Phases

🛠 Tools

1️⃣ Step 1 — Assessment & Planning

Before touching a single byte, understand what you have and what needs special handling.

1. Inventory your database objects

   Count tables, rows, views, stored procedures, triggers, indexes, and foreign keys. Some objects (like stored procedures) require manual rewriting.

   SQL Server — inventory query

   SELECT TABLE_NAME, COUNT(*) AS row_count
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'
   ORDER BY TABLE_NAME;
   
   -- Count stored procedures
   SELECT COUNT(*) FROM sys.procedures;
   
   -- Count triggers
   SELECT COUNT(*) FROM sys.triggers;

2. Identify incompatible data types

   Each engine has unique types. Flag columns using NTEXT, IMAGE, UNIQUEIDENTIFIER (SQL Server), NUMBER, CLOB (Oracle), SERIAL (PostgreSQL) — all need mapping.

3. Estimate data volume and migration window

   Larger databases need more time and a longer maintenance window. Plan for: small (<1 GB) = minutes, medium (1–50 GB) = hours, large (>50 GB) = overnight or phased.

4. Take a full backup of source database

   SQL Server backup

   BACKUP DATABASE [YourDB]
   TO DISK = 'C:\Backup\YourDB_pre_migration.bak'
   WITH FORMAT, COMPRESSION;

   MySQL backup

   mysqldump -u root -p --all-databases --routines --triggers \
     > backup_pre_migration.sql

   PostgreSQL backup

   pg_dump -U postgres -F c -f backup_pre_migration.dump your_db

5. Set up and test the target database server

   Install MySQL (or target RDBMS), create the destination database, and confirm you can connect from the migration machine before starting.

   MySQL — create destination database

   CREATE DATABASE migration_target
     CHARACTER SET utf8mb4
     COLLATE utf8mb4_unicode_ci;

2️⃣ Step 2 — Schema Conversion

Convert the DDL (CREATE TABLE statements, indexes, constraints) from the source dialect to the target dialect. This is usually the most manual-effort step.

1. Export DDL from the source database

   SQL Server — script all tables via SSMS

   -- In SSMS: right-click database → Tasks → Generate Scripts
   -- Or use sqlcmd:
   sqlcmd -S localhost -d YourDB -Q "SELECT definition FROM sys.objects o JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.type = 'U'"

   PostgreSQL — export schema only

   pg_dump -U postgres --schema-only -f schema.sql your_db

2. Convert data types

   See the Data Type Mapping table below. The most common conversions when targeting MySQL:

   ⬛ Source (SQL Server)

   CREATE TABLE users (
     id         INT IDENTITY(1,1) PRIMARY KEY,
     guid       UNIQUEIDENTIFIER,
     name       NVARCHAR(100),
     bio        NTEXT,
     avatar     IMAGE,
     salary     MONEY,
     created_at DATETIME,
     is_active  BIT
   );

   🐬 Target (MySQL)

   CREATE TABLE users (
     id         INT AUTO_INCREMENT PRIMARY KEY,
     guid       CHAR(36),
     name       VARCHAR(100),
     bio        LONGTEXT,
     avatar     LONGBLOB,
     salary     DECIMAL(19,4),
     created_at DATETIME,
     is_active  TINYINT(1)
   );

3. Convert indexes and constraints

   Syntax is mostly compatible, but watch for CLUSTERED indexes (SQL Server only), INCLUDE columns (not in MySQL), and partial indexes (PostgreSQL only).

   ⬛ SQL Server

   CREATE CLUSTERED INDEX ix_users_id
     ON users(id);
   
   CREATE NONCLUSTERED INDEX ix_name
     ON users(name)
     INCLUDE (email);

   🐬 MySQL

   -- CLUSTERED = PRIMARY KEY in MySQL (implicit)
   
   CREATE INDEX ix_name
     ON users(name, email);
   -- INCLUDE not supported; add col to index

4. Rewrite stored procedures & views

   Procedural SQL is the hardest part. SQL Server T-SQL, Oracle PL/SQL, and PostgreSQL PL/pgSQL all differ significantly from MySQL's stored procedure syntax.

   ⬛ T-SQL (SQL Server)

   CREATE PROCEDURE GetUser
     @UserId INT
   AS BEGIN
     SELECT * FROM users
     WHERE id = @UserId;
   END

   🐬 MySQL

   CREATE PROCEDURE GetUser(
     IN p_UserId INT
   )
   BEGIN
     SELECT * FROM users
     WHERE id = p_UserId;
   END

3️⃣ Step 3 — Export Data from Source

Extract all data from the source database into a portable format (SQL dump, CSV, or binary).

Option A — SQL dump (recommended for small/medium DBs)

-- Export each table to CSV
bcp YourDB.dbo.users out users.csv -c -t, -S localhost -T
bcp YourDB.dbo.orders out orders.csv -c -t, -S localhost -T

mysqldump -u root -p \
  --no-create-info \        # data only (schema already converted)
  --complete-insert \       # explicit column names in INSERT
  --single-transaction \    # consistent snapshot without locking
  --hex-blob \              # safe export of BLOB columns
  source_db > data_export.sql

-- Run inside psql for each table:
\COPY users TO '/tmp/users.csv' WITH (FORMAT CSV, HEADER);
\COPY orders TO '/tmp/orders.csv' WITH (FORMAT CSV, HEADER);

Option B — Direct connection (for live migrations)

Use AWS DMS or MySQL Workbench Migration Wizard to stream data directly from source to target without intermediate files — better for large databases or minimal-downtime scenarios.

4️⃣ Step 4 — Transform & Fix Data

Raw exports often contain values that don't load cleanly into the target. Fix these before importing.

Common transformations

Example: fix dates in a CSV file with Python

import csv, re
from datetime import datetime

def fix_date(val):
    try:
        return datetime.strptime(val, '%b %d %Y %I:%M%p').strftime('%Y-%m-%d %H:%M:%S')
    except:
        return val

with open('users_raw.csv') as fin, open('users_clean.csv', 'w') as fout:
    reader = csv.DictReader(fin)
    writer = csv.DictWriter(fout, fieldnames=reader.fieldnames)
    writer.writeheader()
    for row in reader:
        row['created_at'] = fix_date(row['created_at'])
        row['is_active']  = '1' if row['is_active'] == 'True' else '0'
        writer.writerow(row)

5️⃣ Step 5 — Import Data into Target

1. Disable foreign key checks and autocommit

   MySQL — run before import

   SET FOREIGN_KEY_CHECKS = 0;
   SET UNIQUE_CHECKS = 0;
   SET autocommit = 0;
   SET sql_mode = '';

2. Import from SQL dump

   MySQL — import SQL file

   mysql -u root -p migration_target < data_export.sql

   MySQL — import large file with progress

   pv data_export.sql | mysql -u root -p migration_target

3. Import from CSV (fast bulk load)

   MySQL — LOAD DATA for each table

   LOAD DATA LOCAL INFILE 'users_clean.csv'
   INTO TABLE users
   FIELDS TERMINATED BY ','
   ENCLOSED BY '"'
   LINES TERMINATED BY '\n'
   IGNORE 1 ROWS  -- skip CSV header
   (id, guid, name, bio, salary, created_at, is_active);

4. Re-enable constraints and commit

   MySQL — run after import

   COMMIT;
   SET FOREIGN_KEY_CHECKS = 1;
   SET UNIQUE_CHECKS = 1;
   SET autocommit = 1;

6️⃣ Step 6 — Validation & Testing

Never assume the migration worked. Verify row counts, spot-check data, and run your application test suite.

Row count comparison

SELECT 'users'  AS tbl, COUNT(*) AS rows FROM users
UNION ALL
SELECT 'orders' AS tbl, COUNT(*) AS rows FROM orders
UNION ALL
SELECT 'products', COUNT(*) FROM products;

Checksum / hash comparison

CHECKSUM TABLE users;

Spot-check critical rows

-- Source (SQL Server / PostgreSQL)
SELECT * FROM users WHERE id = 1;

-- Target (MySQL)
SELECT * FROM users WHERE id = 1;

Validate foreign key integrity

SELECT o.id
FROM   orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE  u.id IS NULL;
-- Must return 0 rows

Run application smoke tests

7️⃣ Step 7 — Cutover

Cutover is the final switch from source to target in production. Plan this for a low-traffic window.

1. Schedule a maintenance window

   Announce downtime. For zero-downtime migrations, use AWS DMS continuous replication up to cutover, then a brief read-only pause to sync the final delta.

2. Set source database to read-only

   MySQL source

   FLUSH TABLES WITH READ LOCK;
   SET GLOBAL read_only = 1;

   SQL Server source

   ALTER DATABASE [YourDB] SET READ_ONLY;

3. Sync final delta (changed rows since export)

   Re-export only rows modified since your last export using a WHERE updated_at > 'export_time' filter, and re-import them to the target.

4. Run final row-count validation

   Confirm source and target counts match one more time before switching.

5. Update application connection string

   Change the database host/credentials in your app config or environment variable to point at the new target server.

   Example .env change

   # Before
   DB_HOST=sqlserver-prod.internal
   DB_PORT=1433
   
   # After
   DB_HOST=mysql-prod.internal
   DB_PORT=3306

6. Restart application and monitor

   Watch application logs and database slow-query logs for the first 30 minutes. Have a rollback plan ready: revert the connection string and re-enable the source database.

🔀 SQL Server → MySQL

The most common enterprise migration path. MySQL Workbench has a dedicated wizard for this.

Using MySQL Workbench Migration Wizard

1. Open Workbench → Database → Migrate

   Click "Start Migration" on the welcome screen.

2. Source Connection: choose Microsoft SQL Server

   Enter the SQL Server hostname, port (1433), username, and password. Install the FreeTDS or ODBC driver if prompted.

3. Target Connection: your MySQL server

   Enter the MySQL host, port (3306), and credentials.

4. Select databases and tables to migrate

   Check all tables you want to include. Optionally exclude system tables.

5. Review and edit the generated schema

   Workbench shows the converted MySQL DDL. Fix any flagged issues before continuing.

6. Run migration — schema first, then data

   The wizard creates tables in MySQL, then bulk-copies all rows.

Key SQL Server → MySQL differences

-- String functions
LEN(name)
SUBSTRING(name, 1, 5)
ISNULL(col, 'default')
GETDATE()
NEWID()
TOP 10 * FROM t
DATEDIFF(DAY, d1, d2)
CAST(x AS NVARCHAR)

-- Equivalent MySQL functions
CHAR_LENGTH(name)
SUBSTRING(name, 1, 5)  -- same
IFNULL(col, 'default')
NOW()
UUID()
* FROM t LIMIT 10
DATEDIFF(d2, d1) -- args reversed!
CAST(x AS CHAR)

🔀 Oracle → MySQL

Oracle is the hardest migration source due to PL/SQL, sequences, packages, and Oracle-specific types.

Key Oracle → MySQL conversions

-- Auto-increment via sequence
CREATE SEQUENCE user_seq
  START WITH 1 INCREMENT BY 1;

INSERT INTO users (id, name)
VALUES (user_seq.NEXTVAL, 'Alice');

-- Types
id     NUMBER(10)
name   VARCHAR2(100)
notes  CLOB
data   BLOB
price  NUMBER(10,2)
active NUMBER(1)

-- Outer join syntax (old)
WHERE a.id = b.id(+)

-- Auto-increment (built-in)
-- (no sequence needed)

INSERT INTO users (name)
VALUES ('Alice');
-- id AUTO_INCREMENT fills itself

-- Types
id     INT
name   VARCHAR(100)
notes  LONGTEXT
data   LONGBLOB
price  DECIMAL(10,2)
active TINYINT(1)

-- Standard LEFT JOIN
LEFT JOIN b ON a.id = b.id

Using Endrias Bridge for Oracle → MySQL

Endrias Bridge handles Oracle → MySQL (and SQL Server → Oracle) natively — no MySQL Workbench or SQLines required for schema + data transfer. It automatically:

• Remaps NUMBER, VARCHAR2, CLOB, BLOB, DATE → MySQL equivalents
• Rewrites sequences to AUTO_INCREMENT
• Defers FK constraints and issues cross-schema REFERENCES grants where needed
• Generates Schema Objects tab guidance for stored procedures, views, and temporal tables that require manual rewrite

Oracle-specific gotchas (SQL Server → Oracle or Oracle → any target)

Oracle temporal tables (Flashback Data Archive)

If the source has system-versioned temporal tables, the history data migrates as a plain table. To re-enable history tracking on Oracle, use the script generated by the Schema Objects tab:

• Oracle 23ai+: PERIOD FOR SYSTEM_TIME + ADD VERSIONING USE HISTORY TABLE
• Oracle 11g R2–21c: Flashback Data Archive (FDA) — CREATE FLASHBACK ARCHIVE + ALTER TABLE … FLASHBACK ARCHIVE

See Troubleshooting → Oracle temporal tables for full guidance.

🔀 PostgreSQL → MySQL

Both are open-source and standards-compliant, making this one of the easier migrations — but there are still syntax and type differences.

-- Auto-increment
id SERIAL PRIMARY KEY
-- or
id INT GENERATED ALWAYS AS IDENTITY

-- Types with no MySQL equivalent
tags  TEXT[]        -- arrays
data  JSONB         -- binary JSON
loc   POINT         -- geometry
flag  BOOLEAN       -- true/false

-- String concat
first_name || ' ' || last_name

-- Limit syntax
LIMIT 10 OFFSET 20

-- Auto-increment
id INT AUTO_INCREMENT PRIMARY KEY


-- MySQL alternatives
tags  JSON          -- store as JSON string
data  JSON          -- MySQL 5.7+ JSON type
loc   POINT         -- MySQL spatial (same!)
flag  TINYINT(1)    -- 1=true, 0=false

-- String concat
CONCAT(first_name, ' ', last_name)

-- Limit syntax (same)
LIMIT 10 OFFSET 20

Using pgloader (PostgreSQL → MySQL)

LOAD DATABASE
  FROM    pgsql://postgres:pass@localhost/source_db
  INTO    mysql://root:pass@localhost/target_db

WITH include drop, create tables, create indexes, reset sequences

CAST
  type boolean to tinyint(1) using integer-to-string,
  type uuid    to varchar(36),
  type text[]  to text

EXCLUDING TABLES MATCHING 'schema_migrations';

pgloader pg_to_mysql.load

🔀 MySQL → PostgreSQL

Common when scaling up or moving to a cloud-hosted PostgreSQL (e.g. AWS Aurora PG, Supabase).

LOAD DATABASE
  FROM   mysql://root:pass@localhost/source_db
  INTO   pgsql://postgres:pass@localhost/target_db

WITH
  include drop,
  create tables,
  create indexes,
  reset sequences,
  foreign keys

SET
  maintenance_work_mem to '512MB',
  work_mem to '64MB'

CAST
  type tinyint(1) to boolean using tinyint-to-boolean,
  type datetime   to timestamptz;

pgloader mysql_to_pg.load

🔀 SQLite → MySQL

The easiest migration path. SQLite has minimal types, so mapping to MySQL is straightforward.

Option A — dump and fix (manual)

sqlite3 mydb.db .dump > sqlite_dump.sql

# Remove SQLite-specific lines and fix syntax
sed -i 's/BEGIN TRANSACTION;//g' sqlite_dump.sql
sed -i 's/COMMIT;//g' sqlite_dump.sql
sed -i 's/INTEGER PRIMARY KEY AUTOINCREMENT/INT AUTO_INCREMENT PRIMARY KEY/g' sqlite_dump.sql
sed -i '/^CREATE TABLE "sqlite_/d' sqlite_dump.sql

mysql -u root -p target_db < sqlite_dump.sql

Option B — DBeaver data transfer (GUI)

Connect both SQLite and MySQL in DBeaver → right-click source table → "Export Data" → choose MySQL target. DBeaver handles all type conversions automatically.

📊 Data Type Mapping Reference

SQL Server / Oracle → MySQL

PostgreSQL → MySQL

📝 SQL Syntax Differences

✅ Pre-Cutover Checklist

• Full backup taken from source database before any migration step
• Schema deployed to target and all DDL errors resolved
• Row counts match between source and target for all tables
• Spot checks passed — sample records compare correctly
• Foreign key integrity validated — no orphan rows found
• Application test suite passes against the target database
• Stored procedures / views tested and returning correct results
• Indexes verified — all required indexes exist on target
• Charset confirmed — target is utf8mb4, not latin1
• Connection string updated in staging config and verified working
• Rollback plan documented — know exactly how to revert if needed
• Maintenance window communicated to all affected teams
• Monitoring set up on target DB (slow queries, error rates)

🔧 Troubleshooting

Row counts don't match after import

Check for duplicate primary keys, failed inserts due to constraint violations, or character encoding errors that silently dropped rows. Run the import again with SHOW WARNINGS; after each table load.

Character encoding issues / garbled text

mysql -u root -p --default-character-set=utf8mb4 target_db < data.sql

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Import is very slow

SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL innodb_buffer_pool_size = 1073741824;  -- 1 GB
SET GLOBAL bulk_insert_buffer_size = 67108864;    -- 64 MB

Foreign key errors on import

Tables are being inserted in the wrong order. Either:

• Sort the import so parent tables come before child tables
• Disable FK checks: SET FOREIGN_KEY_CHECKS = 0; before import, re-enable after

Stored procedure fails after migration

MySQL's DELIMITER and BEGIN...END syntax must wrap each procedure. Also check for T-SQL features like @@ROWCOUNT, temp tables (#temp), or PRINT statements — these need rewriting.

DELIMITER //
CREATE PROCEDURE MyProc(IN p_id INT)
BEGIN
  SELECT * FROM users WHERE id = p_id;
END //
DELIMITER ;

Dates importing as 0000-00-00

MySQL strict mode rejects invalid dates. Fix by setting: SET sql_mode = ''; before import, or clean the source data first.