Migrate MySQL Database with Myisam engine to MariaDB Galera Cluster

Recently we have been facing higher load on our mysql servers and we decided to migrate our databases to Galera cluster based on MariaDB.

One of the restriction with Galera Cluster is, that it doesn't support MyISAM tables, just InnoDB engine is supported for master-master synchronization.

So here is a easy way how to migrate Mysql database with MyISAM tables into Galera and InnoDB:

Make sure your db schema doesn't contain FULLTEXT indexes or any other constructions, which are not supported by InnoDB engine
Dump schema of your database
replace in the dump string "MYISAM" with "INNODB"
Dump data
Prepare db user in Galera Cluster (mysql.user table is not replicated across cluster, so you have to insert db user into each of your mariadb servers)
Import schema (with innodb engine)
Import data
Cleanup dump files

Bash script could looks like:

#!/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

Migrate MySQL Database with Myisam engine to MariaDB Galera Cluster

Other Articles