MySQL to Azure Migration -- Best Practices¶

Version compatibility checklist, parameter tuning for Azure MySQL Flexible Server, connection pooling, monitoring with slow query log and Performance Insights, and CSA-in-a-Box integration patterns using Fabric Mirroring for MySQL and ADF MySQL connector.

1. Assessment methodology¶

1.1 Discovery phase¶

Before any migration work begins, conduct a thorough MySQL estate discovery:

-- Inventory all databases and sizes
SELECT
    table_schema AS db_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb,
    COUNT(*) AS table_count,
    SUM(table_rows) AS approx_rows
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

-- Check MySQL version
SELECT VERSION();

-- Check storage engines in use
SELECT engine, COUNT(*) AS table_count,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS total_size_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
GROUP BY engine;

-- Check for stored procedures, functions, triggers, events
SELECT routine_schema, routine_type, COUNT(*)
FROM information_schema.routines
WHERE routine_schema NOT IN ('mysql', 'sys')
GROUP BY routine_schema, routine_type;

SELECT trigger_schema, COUNT(*)
FROM information_schema.triggers
WHERE trigger_schema NOT IN ('mysql', 'sys')
GROUP BY trigger_schema;

SELECT event_schema, COUNT(*)
FROM information_schema.events
GROUP BY event_schema;

-- Check character sets and collations
SELECT character_set_name, collation_name, COUNT(*) AS column_count
FROM information_schema.columns
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND character_set_name IS NOT NULL
GROUP BY character_set_name, collation_name
ORDER BY column_count DESC;

1.2 Complexity scoring¶

2. Version compatibility checklist¶

2.1 Pre-migration compatibility check¶

• MySQL version: Confirm source is 5.7 or 8.0 (required for Azure MySQL Flexible Server)
• sql_mode: Document current sql_mode and set identically on target
• lower_case_table_names: Must be set during target server creation (cannot change later)
• character_set_server: Document and match on target (recommend utf8mb4)
• collation_server: Document and match on target
• time_zone: Document and configure on target
• innodb_strict_mode: Verify and match
• explicit_defaults_for_timestamp: Verify and match
• default_authentication_plugin: caching_sha2_password on MySQL 8.0
• All tables InnoDB: Convert MyISAM, Aria, MEMORY tables before migration

2.2 Breaking changes by version¶

3. Parameter tuning for Azure MySQL Flexible Server¶

3.1 Essential parameters to configure¶

-- Buffer pool: Set to maximum available (auto-sized but verify)
-- Azure auto-sizes to 50-80% of RAM
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Connection limits: Size based on expected concurrency
-- Rule of thumb: 4x number of application server threads
SET GLOBAL max_connections = 500;  -- Adjust based on tier

-- Slow query logging: Enable for monitoring
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- Log queries > 2 seconds

-- Wait timeout: Reduce from default 28800 to prevent stale connections
SET GLOBAL wait_timeout = 600;       -- 10 minutes
SET GLOBAL interactive_timeout = 600;

-- Temporary tables: Size based on query complexity
SET GLOBAL tmp_table_size = 67108864;      -- 64 MB
SET GLOBAL max_heap_table_size = 67108864; -- 64 MB

-- Sort buffer: Increase for ORDER BY / GROUP BY heavy workloads
SET GLOBAL sort_buffer_size = 4194304;  -- 4 MB (per connection)

-- Join buffer: Increase for JOIN-heavy queries without indexes
SET GLOBAL join_buffer_size = 4194304;  -- 4 MB (per connection)

-- Thread cache: Reduce connection overhead
SET GLOBAL thread_cache_size = 50;  -- Reuse threads

3.2 Parameters by workload type¶

3.3 Parameters to avoid changing¶

4. Connection pooling¶

4.1 Why connection pooling matters on Azure MySQL¶

Each MySQL connection consumes ~10 MB of RAM and a server thread. Without pooling, applications that create/destroy connections frequently waste resources and hit max_connections limits.

4.2 Application-level pooling recommendations¶

Python (SQLAlchemy):

from sqlalchemy import create_engine

engine = create_engine(
    "mysql+pymysql://admin:password@server.mysql.database.azure.com/mydb",
    pool_size=20,           # Persistent connections
    max_overflow=10,         # Additional connections under load
    pool_timeout=30,         # Wait time for available connection
    pool_recycle=1800,       # Recycle connections every 30 min
    pool_pre_ping=True,      # Verify connection before use
    connect_args={
        "ssl": {"ca": "/path/to/DigiCertGlobalRootCA.crt.pem"}
    }
)

Java (HikariCP):

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://server.mysql.database.azure.com:3306/mydb?useSSL=true&requireSSL=true");
config.setUsername("admin");
config.setPassword("password");
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);
config.setLeakDetectionThreshold(60000);

Node.js (mysql2):

const mysql = require("mysql2");

const pool = mysql.createPool({
    host: "server.mysql.database.azure.com",
    user: "admin",
    password: "password",
    database: "mydb",
    waitForConnections: true,
    connectionLimit: 20,
    queueLimit: 0,
    ssl: { ca: fs.readFileSync("/path/to/DigiCertGlobalRootCA.crt.pem") },
});

4.3 Connection pool sizing formula¶

Pool size = (Number of app server instances) * (Threads per instance) * 0.5

Example: 4 app servers with 10 threads each = 4 _ 10 _ 0.5 = 20 connections per pool.

Total connections = 4 servers * 20 pool connections = 80 active connections.

Ensure max_connections on Azure MySQL is set higher than total expected connections (add 20% buffer).

5. Monitoring¶

5.1 Slow query log¶

Enable and configure the slow query log for ongoing performance monitoring:

# Enable via Azure CLI
az mysql flexible-server parameter set \
  --resource-group myResourceGroup \
  --server-name myMySQLServer \
  --name slow_query_log --value ON

az mysql flexible-server parameter set \
  --resource-group myResourceGroup \
  --server-name myMySQLServer \
  --name long_query_time --value 2

az mysql flexible-server parameter set \
  --resource-group myResourceGroup \
  --server-name myMySQLServer \
  --name log_slow_admin_statements --value ON

az mysql flexible-server parameter set \
  --resource-group myResourceGroup \
  --server-name myMySQLServer \
  --name log_queries_not_using_indexes --value ON

5.2 Performance Insights¶

Azure MySQL Flexible Server provides Query Performance Insights in the Azure Portal:

5.3 Azure Monitor metrics¶

Key metrics to monitor and alert on:

# Create alert rule for high CPU
az monitor metrics alert create \
  --name "MySQL-High-CPU" \
  --resource-group myResourceGroup \
  --scopes "/subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.DBforMySQL/flexibleServers/myMySQLServer" \
  --condition "avg cpu_percent > 90" \
  --window-size 5m \
  --evaluation-frequency 1m \
  --action "/subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.Insights/actionGroups/myActionGroup"

5.4 Diagnostic logging¶

# Enable diagnostic settings
az monitor diagnostic-settings create \
  --resource "/subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.DBforMySQL/flexibleServers/myMySQLServer" \
  --name "MySQLDiagnostics" \
  --workspace "<log-analytics-workspace-id>" \
  --logs '[
    {"category": "MySqlSlowLogs", "enabled": true},
    {"category": "MySqlAuditLogs", "enabled": true}
  ]' \
  --metrics '[{"category": "AllMetrics", "enabled": true}]'

6. CSA-in-a-Box integration¶

6.1 Fabric Mirroring for Azure MySQL¶

Fabric Mirroring for Azure MySQL enables near-real-time replication of MySQL tables into Microsoft Fabric's OneLake as Delta Lake tables. This is the recommended integration pattern for analytics on MySQL data within the CSA-in-a-Box platform.

How it works:

1. Fabric Mirroring reads the MySQL binlog (similar to a replica)
2. Changes are streamed to OneLake as Delta Lake tables
3. dbt models in CSA-in-a-Box transform data through medallion layers (Bronze/Silver/Gold)
4. Power BI with Direct Lake serves analytics with zero data movement
5. Microsoft Purview catalogs and classifies the mirrored data

Setup steps:

1. Ensure binlog is enabled on Azure MySQL Flexible Server (default for HA-enabled servers)
2. In Microsoft Fabric portal, create a new Mirrored Database
3. Select "Azure Database for MySQL" as the source type
4. Enter connection details (server name, database, credentials)
5. Select tables to mirror (start with a subset, expand later)
6. Configure mirroring schedule (continuous or periodic)
7. Start mirroring and verify data appears in OneLake

Best practices for Fabric Mirroring:

• Start with the most analytically valuable tables (e.g., orders, customers, products)
• Avoid mirroring large BLOB/TEXT columns unless needed for analytics
• Monitor mirroring lag in Fabric portal
• Use dbt models to transform mirrored data (do not query raw mirrored tables directly for analytics)

6.2 Azure Data Factory MySQL connector¶

For batch data integration or when Fabric Mirroring is not suitable, ADF provides a MySQL connector:

{
    "name": "MySQLToOneLake",
    "properties": {
        "activities": [
            {
                "name": "CopyMySQLToLakehouse",
                "type": "Copy",
                "typeProperties": {
                    "source": {
                        "type": "MySqlSource",
                        "query": "SELECT * FROM orders WHERE updated_at > '@{pipeline().parameters.lastWatermark}'"
                    },
                    "sink": {
                        "type": "LakehouseTableSink",
                        "tableActionOption": "Append"
                    }
                }
            }
        ]
    }
}

When to use ADF vs Fabric Mirroring:

6.3 Microsoft Purview integration¶

After migrating MySQL to Azure and connecting to CSA-in-a-Box:

1. Register Azure MySQL Flexible Server as a data source in Microsoft Purview
2. Configure scanning to discover metadata (tables, columns, data types)
3. Apply classifications for sensitive data (PII, CUI, PHI)
4. Enable lineage tracking from MySQL through Fabric to Power BI
5. Set up access policies based on data sensitivity

# Register MySQL source in Purview (via API or Portal)
# Portal: Purview Studio > Data Map > Register > Azure Database for MySQL
# Provide: server name, database name, collection assignment

7. Operational best practices¶

7.1 Maintenance windows¶

# Set custom maintenance window (e.g., Sunday 2:00 AM - 3:00 AM UTC)
az mysql flexible-server update \
  --resource-group myResourceGroup \
  --name myMySQLServer \
  --maintenance-window "Sun:02:00"

7.2 Backup strategy¶

7.3 Scaling guidelines¶

7.4 Security hardening post-migration¶

• Remove all public firewall rules (use Private Link / VNet integration)
• Migrate all users to Entra ID authentication
• Enable customer-managed keys for encryption at rest
• Enable audit logging with at least CONNECTION and DDL/DCL events
• Enable Azure Defender for MySQL
• Review and restrict database privileges (least privilege)
• Disable local_infile unless specifically needed
• Set require_secure_transport = ON (default)
• Configure password expiration policies via Entra ID

8. Common pitfalls and how to avoid them¶

9. Post-migration optimization timeline¶

Next: Migration Playbook | Index | Benchmarks

Maintainers: csa-inabox core team Last updated: 2026-04-30