README

Export and import MySQL database schema to/from JSON format.

Features

• Export complete MySQL database schema to JSON format
• Import schema from JSON with intelligent diff detection
• Supports:
  • Tables (with columns, primary keys, unique keys, foreign keys, indexes)
  • Views
  • Triggers
  • Functions
  • Procedures
  • Events
• Smart schema diff: uses ALTER TABLE for existing tables instead of recreating
• Dry-run mode to preview SQL without executing
• CLI tool with easy-to-use options
• Can be used as a PHP library

Requirements

• PHP >= 8.1
• PDO extension
• PDO MySQL extension
• JSON extension

Installation

Via Composer

composer require mathsgod/mysql-schema-migrate

Global Installation

composer global require mathsgod/mysql-schema-migrate

Usage

Export Command

# Basic usage
./vendor/bin/mysql-schema-migrate export -u username -d database_name

# With password
./vendor/bin/mysql-schema-migrate export -u username -p password -d database_name

# Specify host and port
./vendor/bin/mysql-schema-migrate export -H localhost -P 3306 -u username -p password -d database_name

# Output to file
./vendor/bin/mysql-schema-migrate export -u username -d database_name -o schema.json

# With custom charset
./vendor/bin/mysql-schema-migrate export -u username -d database_name -c utf8mb4

Export Options

Import Command

# Basic import
./vendor/bin/mysql-schema-migrate import -u username -d database_name schema.json

# Dry-run mode (preview SQL without executing)
./vendor/bin/mysql-schema-migrate import --dry-run -u username -d database_name schema.json

# Dry-run with SQL output to file
./vendor/bin/mysql-schema-migrate import --dry-run -o output.sql -u username -d database_name schema.json

# Allow dropping columns/tables/objects
./vendor/bin/mysql-schema-migrate import --allow-drop -u username -d database_name schema.json

# Keep original DEFINER (default: reset to CURRENT_USER)
./vendor/bin/mysql-schema-migrate import --keep-definer -u username -d database_name schema.json

# Import only specific object types
./vendor/bin/mysql-schema-migrate import --only=tables,views -u username -d database_name schema.json

Import Options

Import Behavior

• Tables: Uses ALTER TABLE for existing tables (ADD/MODIFY/DROP COLUMN, index changes)
• Views: Uses CREATE OR REPLACE VIEW
• Functions/Procedures/Triggers/Events: Uses DROP IF EXISTS + CREATE
• DEFINER: Reset to CURRENT_USER by default (use --keep-definer to preserve)
• Foreign Keys: Handled separately to avoid dependency issues

As a Library

<?php

use MysqlSchemaMigrate\Exporter;
use MysqlSchemaMigrate\Importer;

// Export schema
$exporter = new Exporter(
    host: 'localhost',
    database: 'my_database',
    username: 'root',
    password: 'password',
    port: 3306,
    charset: 'utf8mb4'
);

// Get schema as array
$schema = $exporter->export();

// Get schema as JSON string
$json = $exporter->toJson();

// Save to file
file_put_contents('schema.json', $json);

// Import schema
$importer = new Importer(
    host: 'localhost',
    database: 'target_database',
    username: 'root',
    password: 'password',
    port: 3306,
    charset: 'utf8mb4'
);

// Import from file (dry-run)
$statements = $importer->importFromFile(
    filePath: 'schema.json',
    dryRun: true,
    allowDrop: false,
    keepDefiner: false,
    only: [] // empty = all types
);

// Get formatted SQL for review
$sql = $importer->formatSqlForFile();
file_put_contents('migration.sql', $sql);

// Import and execute
$statements = $importer->importFromFile(
    filePath: 'schema.json',
    dryRun: false,
    allowDrop: true
);

Output Format

The exported JSON contains the following structure:

{
    "tables": [
        {
            "name": "users",
            "columns": [
                {
                    "name": "id",
                    "type": "int",
                    "nullable": false,
                    "default": null,
                    "auto_increment": true,
                    "unsigned": true
                },
                {
                    "name": "email",
                    "type": "varchar",
                    "nullable": false,
                    "default": null,
                    "auto_increment": false,
                    "length": 255,
                    "charset": "utf8mb4",
                    "collation": "utf8mb4_unicode_ci"
                }
            ],
            "primary_key": ["id"],
            "unique_keys": [
                {
                    "name": "users_email_unique",
                    "columns": ["email"]
                }
            ],
            "foreign_keys": [],
            "indexes": [],
            "engine": "InnoDB",
            "charset": "utf8mb4",
            "collation": "utf8mb4_unicode_ci",
            "comment": null
        }
    ],
    "views": [],
    "triggers": [],
    "functions": [],
    "procedures": [],
    "events": []
}

License

MIT License. See LICENSE for more information.

Author

Raymond Chong (mathsgod@yahoo.com)