MySQL to HeliosDB Migration Guide

Version: 1.0 Last Updated: 2025-11-30

---

Pre-Migration Checklist

• [ ] Backup MySQL database
• [ ] Review schema for unsupported features
• [ ] Estimate data volume
• [ ] Plan downtime (if any)
• [ ] Test on development environment

---

Step 1: Export MySQL Schema

# Export schema without data
mysqldump -h localhost -u root -p database_name \
  --no-data \
  --routines \
  --triggers \
  > schema.sql

# Review for compatibility
grep -E "ENGINE=|AUTO_INCREMENT|COLLATE" schema.sql

---

Step 2: Migrate Schema

-- Import MySQL schema into HeliosDB
-- Note: Some SQL syntax may need adjustment

-- Example adjustments:
-- MySQL: `column_name` → PostgreSQL: "column_name"
-- MySQL: INT AUTO_INCREMENT → PostgreSQL: SERIAL
-- MySQL: VARCHAR(255) → PostgreSQL: VARCHAR or TEXT

---

Step 3: Export and Import Data

# Export from MySQL as CSV
mysql -h localhost -u root -p database_name \
  -e "SELECT * FROM table_name" \
  | sed 's/\t/,/g' > table_name.csv

# Import into HeliosDB using COPY
psql -h heliosdb.host -U user -d database_name \
  -c "COPY table_name FROM STDIN WITH (FORMAT csv)" \
  < table_name.csv

---

Step 4: Validate Data

-- Check row counts
SELECT COUNT(*) FROM table_name;

-- Verify data integrity
SELECT * FROM table_name LIMIT 100;

-- Check for NULL values unexpectedly introduced
SELECT * FROM table_name WHERE column_name IS NULL;

---

Step 5: Create Indexes

-- Recreate indexes
CREATE INDEX idx_name ON table_name(column_name);

-- Analyze for query optimization
ANALYZE table_name;

---

Common Compatibility Issues

MySQL	HeliosDB Solution
ENUM type	Use VARCHAR with CHECK constraint
UNSIGNED INT	Use INTEGER or BIGINT
JSON	Use JSONB type
FULL TEXT	Use HeliosDB full-text search
SPATIAL	Use PostGIS-compatible geospatial

---

Zero-Downtime Migration

# 1. Set up replication
mysql> CHANGE MASTER TO MASTER_HOST='heliosdb.host', ...
mysql> START SLAVE;

# 2. Verify sync
mysql> SHOW SLAVE STATUS;

# 3. Switch applications
# Point connection strings to HeliosDB

# 4. Clean up MySQL
mysql> STOP SLAVE;

---

Rollback Plan

# Keep MySQL running in read-only mode
mysql> SET GLOBAL read_only = ON;

# If rollback needed
mysql> SET GLOBAL read_only = OFF;
# Point applications back to MySQL

---

Performance Tuning

-- After migration, optimize indexes
ANALYZE;
VACUUM;

-- Enable query cache for hot tables
ALTER TABLE frequently_accessed ENABLE INTELLIGENT_CACHING;

---

Post-Migration

• [ ] Verify all data migrated
• [ ] Test application queries
• [ ] Validate performance
• [ ] Monitor error logs
• [ ] Clean up old MySQL database (after verification)

---

Related Documentation: - MSSQL Migration Guide - SQLite Migration Guide - Multi-Protocol Compatibility Guide