Migrating MySQL Databases with MyISAM Tables to a MariaDB Galera Cluster

Home > Developers help > Server Administration > Mysql > Migrating MySQL Databases with MyISAM Tables to a MariaDB Galera Cluster

Migrating MySQL databases that use MyISAM tables to a MariaDB Galera Cluster requires careful planning, as Galera supports only InnoDB tables for synchronous replication. This guide outlines the recommended steps for a smooth migration and provides a bash script to automate parts of the process.

Prerequisites

  • Access to the source MySQL database with MyISAM tables
  • A running MariaDB Galera Cluster
  • Sufficient storage and network capacity for the data transfer
  • Familiarity with command-line tools such as mysqldump and mysql

Migration Steps

  1. Backup the original database.

    Use mysqldump to create a backup of the database. Include the --single-transaction flag for InnoDB tables. For MyISAM tables, this option does not provide consistency, so consider stopping writes during the dump.

  2. Convert MyISAM tables to InnoDB.

    Before importing the data to the Galera cluster, convert all MyISAM tables to InnoDB. You can generate the necessary ALTER statements using the following query:

    SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE engine = 'MyISAM'
  AND table_schema = 'your_database';

  3. Import the data into the Galera cluster.

    Restore the dump file to the MariaDB Galera Cluster using the mysql client.

  4. Verify the migration.

    Confirm that all tables have been converted to InnoDB and test application functionality.

Bash Script Example

The following bash script demonstrates how to automate the conversion of all MyISAM tables in a given database to InnoDB:

#!/bin/bash
DB_NAME="your_database"
MYSQL="mysql -u root -p"
for table in $($MYSQL -N -e "SELECT table_name FROM information_schema.tables WHERE engine = 'MyISAM' AND table_schema = '$DB_NAME';")
do
  echo "Converting $table..."
  $MYSQL -e "ALTER TABLE $DB_NAME.$table ENGINE=InnoDB;"
done

This script assumes you have the necessary privileges and that mysql is available on your PATH. Modify the MYSQL variable as needed for your environment.

Conclusion

Migrating from MyISAM to InnoDB in preparation for a MariaDB Galera Cluster deployment is essential for ensuring data consistency and high availability. By following the steps outlined in this guide and utilizing the provided script, you can efficiently transition your databases to take full advantage of Galera’s synchronous replication.

Always perform a test migration in a non-production environment before executing these steps on a live system.

Do not attempt to migrate MyISAM tables directly to a Galera cluster without converting them to InnoDB first. Galera does not support MyISAM tables, and data inconsistency or replication failures may occur.

Our Bash Script Full Example

#!/bin/bash
if [ -z "$1" ]
  then
    echo "No argument supplied."
    echo "Usage: transfer.sh <src_dbserver> <src_dbname> <src_username> <src_password>"
    exit 1
fi

SOURCE_DBHOST=$1
SOURCE_DBNAME=$2
SOURCE_USERNAME=$3
SOURCE_PASSWORD=$4

#export db structure
echo "Exporting structure"

mysqldump --no-data --skip-comments --skip-add-drop-table --host=$SOURCE_DBHOST --password=$SOURCE_PASSWORD --user=$SOURCE_USERNAME $SOURCE_DBNAME > $SOURCE_DBNAME.schema.sql

#replace MYISAM with INNODB engine
sed -i 's/MyISAM/INNODB/g' $SOURCE_DBNAME.schema.sql

#export data
echo "Exporting data"
mysqldump --no-create-info --no-autocommit --compact --complete-insert --no-create-db --skip-add-drop-table --host=$SOURCE_DBHOST --password=$SOURCE_PASSWORD --user=$SOURCE_USERNAME $SOURCE_DBNAME > $SOURCE_DBNAME.data.sql

#admindbuser password with rights to grant rights for other db users
DEST_ROOT_PW=adminDbPassword

#create user on each mariadb server SQL (mysql.user table is not synchronized across cluster)

echo "DROP DATABASE IF EXISTS $SOURCE_DBNAME; CREATE DATABASE $SOURCE_DBNAME;" > $SOURCE_DBNAME.db.sql
echo "GRANT ALL ON $SOURCE_DBNAME.* TO '$SOURCE_USERNAME'@'localhost' IDENTIFIED BY '$SOURCE_PASSWORD'; FLUSH PRIVILEGES;" > $SOURCE_DBNAME.user.sql

echo "create DB"
mysql --host=mariadb1.yourdomain.com --user=<admindbuser> --password=$DEST_ROOT_PW < $SOURCE_DBNAME.db.sql

echo "MDB1 create user"
mysql --host=mariadb1.yourdomain.com --user=<admindbuser> --password=$DEST_ROOT_PW < $SOURCE_DBNAME.user.sql

echo "MDB2 create user"
mysql --host=mariadb2.yourdomain.com --user=<admindbuser> --password=$DEST_ROOT_PW < $SOURCE_DBNAME.user.sql

echo "MDB3 create user"
mysql --host=mariadb3.yourdomain.com --user=<admindbuser> --password=$DEST_ROOT_PW < $SOURCE_DBNAME.user.sql

#import data
echo "Importing schema"
mysql --host=mariadb1.yourdomain.com --user=$SOURCE_USERNAME --password=$SOURCE_PASSWORD $SOURCE_DBNAME < $SOURCE_DBNAME.schema.sql

echo "Importing data"
mysql --host=mariadb1.yourdomain.com --user=$SOURCE_USERNAME --password=$SOURCE_PASSWORD $SOURCE_DBNAME < $SOURCE_DBNAME.data.sql

#cleanning
rm -f $SOURCE_DBNAME.schema.sql
rm -f $SOURCE_DBNAME.db.sql
rm -f $SOURCE_DBNAME.user.sql
rm -f $SOURCE_DBNAME.data.sql
Updated:

Other Articles

×