MySQL to MariaDB Migration Script

As of late it seems as if everyone is jumping on the MariaDB Database bandwagon. In this article I will provide a bash automation script as well as detail it’s usage in order to ease the MySQL Server to MariaDB Server migration. The script is broken down into four stages. Below I will break down each section and explain what it is doing.

#!/bin/bash

# Declare Variables
mysql="192.168.1.100"
mariadb="127.0.0.1"
mysql_user="root"
mysql_pass="My$ql"
mariadb_user="root"
mariadb_pass="M@r1a"

# Migrate Function
function migrate
{
	echo #####################
	echo Dumping Database - $1
	mysqldump --routines -h $mysql -u $mysql_user -p$mysql_pass $1 > $1.dump
	echo Converting Tables - $1
	cat $1.dump | sed -e 's/MyISAM/InnoDB/g' > $1.sql
	echo Creating Database - $1
	mysql -h $mariadb -u $mariadb_user -p$mariadb_pass mysql -e "CREATE DATABASE $1"
	echo Migrating Database - $1
	mysql -h $mariadb -u $mariadb_user -p$mariadb_pass $1 < $1.sql
	echo Migration Complete - $1
} 

# Migrate Databases
migrate "openfire"
migrate "postfix"
migrate "projects"
migrate "pureftp"
migrate "security"
migrate "statistics"
migrate "webmail"

# Database Optimization
echo ####################
echo Optimizing Databases
mysqlcheck -o -s --all-databases -h $mariadb -u $mariadb_user -p$mariadb_pass > optimize.txt
echo Optimization Complete
echo ####################

Declare Variables

In this section it is pretty obvious that we are declaring the variables to be passed to the rest of the script. This section will need to be edited for your environment.

Migrate Function

This is the meat and potatoes of the entire script. In this function, once again we have four sub components stacked to run in a specific order.

1. Export the database from the MySQL Server to a .dump file.
2. Convert all tables from MyISAM to InnoDB and export to a .sql file.
3. Create the database on the MariaDB Server using command line.
4. Import the database .sql export into the MaraiaDB Server.

Migrate Databases

Again, this section is pretty self-explanatory in that it simply calls the migrate function with the database you would like to migrate. Note that you will need one statement for each database that will be migrated as seen in the example script.

Database Optimization

Lastly, we are calling mysqlcheck to optimize the newly created databases. Note, the output from the command is being redirected to a file called optimize.txt.

Migration Script Download