MySQL InnoDB to NDBCluster migration script (ignores non-compatible foreign keys)

innodb_to_ndbcluster_migration.sh

#!/bin/bash

# Written by Mahmoud Eskandari @ BoomerangApp - 2024

# Warning: Backup your innoDB before run!
# ****** Caution ******
# If you mistakenly swap the source and destination,
# the source database will be deleted. This script will also delete and recreate the destination database.
# Please be careful.
#

# Set the necessary variables
DB_NAME="_DB_"

# Source Server
REMOTE_SERVER_IP="192.168.1.1"
REMOTE_SERVER_PORT="3306"
REMOTE_DB_USER="root"
REMOTE_DB_PASS="***"

# Destination Server
TARGET_SERVER_IP="192.168.1.2"
TARGET_SERVER_PORT="3306"
TARGET_DB_USER="root"
TARGET_DB_PASS="***"


DUMP_FILE="innodb_database_dump.sql"

# Remove old files
rm *.sql

# Get list of InnoDB tables
TABLES=$(mysql -h $REMOTE_SERVER_IP -u $REMOTE_DB_USER -p$REMOTE_DB_PASS -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$DB_NAME' AND ENGINE='InnoDB'" -s -N)

echo "Recreating Target Database!"
mysql --user=$TARGET_DB_USER --password=$TARGET_DB_PASS --host=$TARGET_SERVER_IP --port=$TARGET_SERVER_PORT -e "DROP DATABASE IF EXISTS $DB_NAME;CREATE DATABASE $DB_NAME;"

# Loop through tables and convert them
for TABLE in $TABLES
do
    # Export table structure
    mysqldump -h $REMOTE_SERVER_IP -u $REMOTE_DB_USER -p$REMOTE_DB_PASS --skip-triggers --no-data --compact $DB_NAME $TABLE > ${TABLE}_structure.sql

    # Modify table engine to NDBCluster and remove ROW_FORMAT
    sed -i 's/ENGINE=InnoDB/ENGINE=NDBCLUSTER/' ${TABLE}_structure.sql

    # Remove FOREIGN KEY constraints and adjust commas
    sed -i '/CONSTRAINT.*FOREIGN KEY/d' ${TABLE}_structure.sql
    sed -i 's/ROW_FORMAT=COMPACT//' ${TABLE}_structure.sql
    sed -i 's/ZEROFILL//' ${TABLE}_structure.sql
    sed -i '/SET character_set_client = @saved_cs_client/d' ${TABLE}_structure.sql

    # Replace ",\n)" with ")"
    sed -i ':a;N;$!ba;s/,\n[[:space:]]*)/)/g; s/,[[:space:]]*\n[[:space:]]*)/)/g' ${TABLE}_structure.sql

    # import structure
    mysql -h $TARGET_SERVER_IP -u $TARGET_DB_USER -p$TARGET_DB_PASS $DB_NAME < ${TABLE}_structure.sql
done

echo "Dumping Data..."
# Dump the InnoDB database from the remote server
mysqldump --user=$REMOTE_DB_USER --password=$REMOTE_DB_PASS --host=$REMOTE_SERVER_IP --port=$REMOTE_SERVER_PORT --single-transaction --routines --events --triggers --databases $DB_NAME --no-create-info --skip-add-locks > $DUMP_FILE

echo "Importing Data..."
# Import data to the target
mysql --user=$TARGET_DB_USER --password=$TARGET_DB_PASS --host=$TARGET_SERVER_IP --port=$TARGET_SERVER_PORT $DB_NAME < $DUMP_FILE



echo "Importing Compatible Foreign Keys (non-nested FKs)..."
FKs=$(mysql -h $REMOTE_SERVER_IP -u $REMOTE_DB_USER -p$REMOTE_DB_PASS -e " SELECT CONCAT(
    'ALTER!TABLE!', fks.quotedSourceTableName, '!ADD!CONSTRAINT!', fks.quotedConstraintName, '!FOREIGN!KEY!(', GROUP_CONCAT(fks.quotedSourceColumnName), ')!',
    'REFERENCES!', fks.quotedTargetTableName, '!(',
        GROUP_CONCAT(fks.quotedTargetColumnName),
        ')!ON!DELETE!RESTRICT!ON!UPDATE!RESTRICT; ') as c 
    FROM (
    SELECT
          kcu.CONSTRAINT_NAME  AS quotedConstraintName,
         kcu.TABLE_NAME  AS quotedSourceTableName,
         kcu.COLUMN_NAME AS quotedSourceColumnName,
         kcu.REFERENCED_TABLE_NAME AS quotedTargetTableName,
         kcu.REFERENCED_COLUMN_NAME AS quotedTargetColumnName,
        rc.DELETE_RULE AS deleteRule,
        rc.UPDATE_RULE AS updateRule
    FROM information_schema.KEY_COLUMN_USAGE AS kcu
    INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS AS rc ON
        kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME AND
        kcu.TABLE_SCHEMA = rc.CONSTRAINT_SCHEMA
    WHERE
        kcu.TABLE_SCHEMA = '$DB_NAME' AND
        kcu.REFERENCED_COLUMN_NAME IS NOT NULL
    ORDER BY 
        kcu.ORDINAL_POSITION DESC
) AS fks GROUP BY
    fks.quotedConstraintName,
    fks.quotedSourceTableName,
    fks.quotedTargetTableName,
    fks.deleteRule,
    fks.updateRule;
")
 
# Loop through foreign keys
for FK in $FKs
do
    Q=$(sed "s/!/ /g" <<< "$FK")
    echo $Q
    mysql -h $TARGET_SERVER_IP -u $TARGET_DB_USER -p$TARGET_DB_PASS $DB_NAME -e "$Q"
done

echo "Migration complete!"

Install mysql client tools before execute.
sudo apt-get install mysql-client

Execute:

chmod +x innodb_to_ndbcluster_migration.sh
./innodb_to_ndbcluster_migration.sh