Skip to main content

​
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

​
Install and Configure the Xata CLI

Install the Xata CLI: 
curl -fsSL https://xata.io/install.sh | bash
Authenticate with your Xata account: 
xata auth login

​
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:
  1. Connect to Xata Branch: 
    xata branch url
  2. 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;
  3. 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));

​
Next Steps

⌘I