Migrate from MySQL to Xata

Learn how to migrate your MySQL database to Xata using mysqldump and schema conversion. Step-by-step instructions for migrating from MySQL to PostgreSQL.

Prerequisites

MySQL database (5.7 or 8.0)
Access to MySQL server with sufficient privileges
Xata account and project setup
Network access to your MySQL server

Important Note

Xata is built on PostgreSQL, so migrating from MySQL requires:

Schema conversion from MySQL to PostgreSQL
Data export/import using mysqldump
Manual verification of data types and constraints

Create Migration User

Connect to your MySQL database and create a dedicated user for migration:

-- Create migration user
CREATE USER 'xata_migration'@'%' IDENTIFIED BY 'your_secure_password';

-- Grant necessary privileges
GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'xata_migration'@'%';

-- For specific database
GRANT SELECT, LOCK TABLES, SHOW VIEW ON your_database.* TO 'xata_migration'@'%';

-- Flush privileges
FLUSH PRIVILEGES;

Export MySQL Schema

First, export your MySQL schema to understand the structure:

# Export schema only
mysqldump --no-data --routines --triggers \
  -h your-mysql-host \
  -u xata_migration \
  -p your_database > schema.sql

# Export with data
mysqldump --routines --triggers \
  -h your-mysql-host \
  -u xata_migration \
  -p your_database > full_backup.sql

Schema Conversion

Common MySQL to PostgreSQL Conversions

You'll need to convert your MySQL schema to PostgreSQL format:

Data Types

-- MySQL -> PostgreSQL
INT -> INTEGER
BIGINT -> BIGINT
VARCHAR(255) -> VARCHAR(255)
TEXT -> TEXT
DATETIME -> TIMESTAMP
TIMESTAMP -> TIMESTAMP
BOOLEAN -> BOOLEAN
JSON -> JSONB

Auto Increment

-- MySQL
id INT AUTO_INCREMENT PRIMARY KEY

-- PostgreSQL
id SERIAL PRIMARY KEY
-- or
id BIGSERIAL PRIMARY KEY

Indexes

-- MySQL
CREATE INDEX idx_name ON table_name(column_name);

-- PostgreSQL (same syntax)
CREATE INDEX idx_name ON table_name(column_name);

Foreign Keys

-- MySQL
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

-- PostgreSQL (same syntax)
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

Export Data

Export your MySQL data in a PostgreSQL-compatible format:

# Export data in INSERT format
mysqldump --no-create-info --complete-insert \
  -h your-mysql-host \
  -u xata_migration \
  -p your_database > data.sql

# Export specific tables
mysqldump --no-create-info --complete-insert \
  -h your-mysql-host \
  -u xata_migration \
  -p your_database table1 table2 > specific_tables.sql

Initialize Xata Project

Set up your Xata project configuration:

xata init

Create PostgreSQL Schema

Create the converted schema in Xata:

# Get your Xata connection string
xata branch url

# Connect to Xata and create tables
psql "your-xata-connection-string" -f converted_schema.sql

Import Data

Import your converted data into Xata:

# Import data
psql "your-xata-connection-string" -f converted_data.sql

# Or import specific tables
psql "your-xata-connection-string" -f specific_tables_data.sql

Alternative: Using pgloader

For more automated conversion, you can use pgloader:

# Install pgloader
# Ubuntu/Debian
sudo apt-get install pgloader

# macOS
brew install pgloader

# Create a conversion file (convert.load)
LOAD DATABASE
  FROM mysql://xata_migration:password@your-mysql-host/your_database
  INTO postgresql://your-xata-connection-string

WITH include drop, create tables, create indexes, reset sequences,
     workers = 8, concurrency = 1,
     multiple readers per thread, rows per range = 50000

SET MySQL PARAMETERS
  net_read_timeout = '600',
  net_write_timeout = '600'

SET PostgreSQL PARAMETERS
  maintenance_work_mem to '128 MB',
  work_mem to '12 MB',
  search_path to 'public'

CAST
  type int with extra auto_increment to serial,
  type int with extra auto_increment to bigserial when (> precision 10),
  -- Add more type conversions as needed
  default value null for drop typemod keep default keep not null;

# Run the conversion
pgloader convert.load

Verification

After migration, verify your data:

Connect to Xata Branch:
```
xata branch url
```

Check Data Integrity:

-- Compare row counts
SELECT COUNT(*) FROM your_table;

-- Check sample data
SELECT * FROM your_table LIMIT 10;

-- Check foreign key relationships
SELECT COUNT(*) FROM table1 t1 
JOIN table2 t2 ON t1.id = t2.table1_id;

Test Application: Verify your application works with the new PostgreSQL schema

Common Conversion Issues

1. Date/Time Handling

MySQL and PostgreSQL handle dates differently:

-- MySQL
SELECT NOW();

-- PostgreSQL
SELECT NOW();
-- or
SELECT CURRENT_TIMESTAMP;

2. String Functions

Some string functions differ:

-- MySQL
CONCAT(str1, str2)
SUBSTRING(str, pos, len)

-- PostgreSQL
str1 || str2
SUBSTRING(str FROM pos FOR len)

3. JSON Handling

-- MySQL
JSON_EXTRACT(column, '$.key')

-- PostgreSQL
column->>'key'

4. Auto Increment Sequences

After importing, reset sequences:

-- For each table with auto increment
SELECT setval('table_name_id_seq', (SELECT MAX(id) FROM table_name));

Troubleshooting

Common Issues

Connection Issues:
- Verify MySQL server is accessible
- Check user privileges
- Ensure network connectivity
Schema Conversion Errors:
- Review MySQL-specific features
- Convert data types manually
- Handle MySQL-specific functions
Data Import Errors:
- Check for encoding issues
- Verify data type compatibility
- Handle NULL values appropriately
Performance Issues:
- Import data in batches
- Disable indexes during import
- Rebuild indexes after import

Using mysqldump with Options

# Export with specific options
mysqldump \
  --single-transaction \
  --routines \
  --triggers \
  --set-gtid-purged=OFF \
  --default-character-set=utf8mb4 \
  -h your-mysql-host \
  -u xata_migration \
  -p your_database > backup.sql

Migration Strategies

Strategy 1: Full Migration

Export MySQL schema and data
Convert schema to PostgreSQL
Import into Xata
Update application to use Xata
Decommission MySQL

Strategy 2: Gradual Migration

Migrate read-only data first
Keep MySQL for writes during transition
Gradually migrate write operations
Eventually consolidate to Xata

Strategy 3: Parallel Systems

Keep MySQL for existing features
Use Xata for new features
Sync data between systems
Gradually migrate features

Next Steps

Explore Xata branching for development workflows
Learn about schema changes with zero downtime
Set up continuous sync for ongoing replication
Consider deployment options for your Xata instance

Heroku Postgres

Neon