Migrate to MariaDB Server from MySQL 5.7 using a single command

Posted on September 19, 2023 by

MySQL 5.7 Extended Support from Oracle ends in October 2023 (source 1, source 2). MariaDB offers a compelling alternative to MySQL 8.0, and the proprietary paths from MySQL 8.0 to Oracle Heatwave/Oracle Cloud. This companion blog simplifies the 11 commands, in-place migration in the original post from MySQL 5.7.43 to MariaDB Community Server 10.6.14. In this post, we introduce new bash scripts created by our talented customer engineers.  They simplify the upgrade steps into a single command to automate an in-place migration with, and without, partitioning. These scripted migration steps were tested using a non-trivial, example MySQL database with 6 tables, views, primary/foreign key relationships, 167MB of data, one primary and two replicas – all setup on a remote virtual machine.

For developers DBAs and/or architects approaching a MySQL to MariaDB migration for the first time, it’s important to consider what scripts and tools like these accomplish in the context of an overall migration process. Such migrations typically include activities outside the scope of what database migration tools typically provide:

Migrate from MySQL to MariaDB

 

Migration to MariaDB from MySQL with no partitioned tables

These Bash scripts are commented to provide clarity for developers who may wish to make modifications. Please refer to the original post for more information about the virtual machine used for hosting the MySQL test_db instance used. All non-SQL commands are for BASH/CentOS, and it assumes the script is run with the sudo command:

$ sudo ./migrate-mysql-57-mrdb-106.sh
 
#!/bin/bash
​
mariadb_version=$1
​
# Take a backup of your database. You want to be able to revert back
# in case anything goes wrong during this process. Be sure to copy
# the my.cnf file to ensure custom configuration is preserved and
# can be applied to the MariaDB server. If you have put in custom
# values to the my.cnf file, this should automatically be archived
# to a my.cnf.rpmsave. This script also backs up the .cnf file to
# /tmp/my_sql_upgrade.

mkdir /tmp/mysql_upgrade
cp -r /etc/my.cnf /tmp/mysql_upgrade/backup_my.cnf
​
# Stop the mysql database service
systemctl stop mysqld
​
# Remove the installed MySQL packages
yum remove mysql-* -y
​
# Add EPEL to satisfy galera dependency for CentOS/other Fedora/RHEL
# based linux distros yum install epel-release
yum install epel-release

# Install wget if needed, download the MariaDB Repo Setup tool
if ! command -v wget &> /dev/null; then
echo "wget is not installed. Installing wget..."
yum install -y wget
fi
wget https://r.mariadb.com/downloads/mariadb_repo_setup

# Make the downloaded file executable
chmod +x mariadb_repo_setup

# Setup the MariaDB repo for your server
./mariadb_repo_setup --skip-tools --skip-maxscale --mariadb-server-version="10.6"
​
# Install MariaDB server package via yum
yum makecache fast
yum install MariaDB-server -y
​
# Copy the existing configuration for use on startup. When
# troubleshooting, check this file for invalid entries
cat /tmp/mysql_upgrade/backup_my.cnf >> /etc/my.cnf

# Ensure permissions for PID directory
mkdir /var/run/mysqld
chown -R mysql:mysql /var/run/mysqld

# Start the MariaDB service
systemctl start mariadb
​
#Run mariadb-upgrade process, prompting for a password
mariadb-upgrade
​
#restart to ensure upgrade success
systemctl restart mariadb
​
#remove errors from the error log (login as root)
mysql <<EOF
DELETE FROM mysql.plugin WHERE name = 'validate_password';
OPTIMIZE TABLE mysql.innodb_table_stats;
OPTIMIZE TABLE mysql.innodb_index_stats;
EOF
​
#restart the service to ensure all changes are successful
systemctl restart mariadb

NOTE: For replicated architectures simply start by upgrading the replicas to MariaDB Server in sequence, verifying that replication is working after each one. When complete, upgrade the primary database and you’re done.

 

How to handle partitioned tables for in-place migration

Since the way MySQL and MariaDB handle partitions are different, special consideration is needed when dealing with these tables. This script was written to facilitate the partition exchange scenario.  Again, please refer to the original post for more information about the virtual machine used for hosting the mySQL test_db instance used. All non-SQL commands are for BASH/CentOS, and it assumes script is run with the sudo command:

$ sudo ./migrate-mysql-57-mrdb-106-partitioned.sh
 
#!/bin/bash
​
## Run this to get the tables with partitions
​
mkdir $PWD/partitioning_sql
​
echo "Here are the partitioned tables:"
mysql -t <<EOF
SELECT table_schema, table_name, COUNT(*) num_of_partitions
FROM information_schema.partitions
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
AND partition_name IS NOT NULL
GROUP BY table_schema, table_name
ORDER BY table_schema, table_name;
EOF
​
## Save table schemas
# use mysqldump
mysql <> $PWD/partitioning_sql/get_partitioned_tables_schemas.sh
SELECT CONCAT('mysqldump --no-data --databases ',table_schema,' --tables ', GROUP_CONCAT(table_name ORDER BY table_name ASC SEPARATOR " "), ' >> $PWD/partitioning_sql/partitioned_tables_schemas.sql;') AS stmt
FROM (
    SELECT DISTINCT table_schema, table_name
    FROM information_schema.partitions
    WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
    AND partition_name IS NOT NULL
    GROUP BY table_schema, table_name
) tbls
group by table_schema
order by table_schema;
EOF
sed -i '1d' $PWD/partitioning_sql/get_partitioned_tables_schemas.sh
​​
## Run this to create tables to store the partitions
mysql < $PWD/partitioning_sql/create_temporary_tables.sql
SELECT CONCAT('CREATE TABLE ',table_schema,'.', table_name,'_',partition_name,' LIKE ',table_schema,'.', table_name,'; ALTER TABLE ',table_schema,'.', table_name,'_',partition_name,' REMOVE PARTITIONING;') AS stmt
FROM information_schema.partitions
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
AND partition_name IS NOT NULL
ORDER BY table_schema, table_name, PARTITION_ORDINAL_POSITION;
EOF
sed -i '1d' $PWD/partitioning_sql/create_temporary_tables.sql
​
## Exchange the partitions
mysql < $PWD/partitioning_sql/exchange_partitions.sql
SELECT CONCAT('ALTER TABLE ',table_schema,'.', table_name,' EXCHANGE PARTITION ',partition_name,' WITH TABLE ',table_schema,'.', table_name,'_',partition_name,';') AS stmt
FROM information_schema.partitions
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
AND partition_name IS NOT NULL
ORDER BY table_schema, table_name, PARTITION_ORDINAL_POSITION;
EOF
sed -i '1d' $PWD/partitioning_sql/exchange_partitions.sql
​
## Drop the partitioned tables
# use mysqldump
mysql <> $PWD/partitioning_sql/drop_partitioned_tables.sql
SELECT CONCAT('DROP TABLE ',table_schema,'.',table_name,';') AS stmt
FROM information_schema.partitions
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
AND partition_name IS NOT NULL
GROUP BY table_schema, table_name
ORDER BY table_schema, table_name;
EOF
sed -i '1d' $PWD/partitioning_sql/drop_partitioned_tables.sql
​
## Run this to create tables to store the partitions
mysql < $PWD/partitioning_sql/drop_temporary_tables.sql
SELECT CONCAT('DROP TABLE ',table_schema,'.', table_name,'_',partition_name,';') AS stmt
FROM information_schema.partitions
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
AND partition_name IS NOT NULL
ORDER BY table_schema, table_name, PARTITION_ORDINAL_POSITION;
EOF
sed -i '1d' $PWD/partitioning_sql/drop_temporary_tables.sql
​
## You can use the same SQL to restore them. Neat!
​
# Use these commands to walk through it
#
# 1. Generate the partitioned table schemas
#
#       bash $PWD/partitioning_sql/get_partitioned_tables_schemas.sh
#
# 2. Create the tables:
#
#       mysql < $PWD/partitioning_sql/create_temporary_tables.sql
#
# 3. Exchange the partitions
#
#       mysql < $PWD/partitioning_sql/exchange_partitions.sql
#
# 4. Drop the partitioned tables
#
#       mysql < $PWD/partitioning_sql/drop_partitioned_tables.sql
#
# 5. Migrate to MariaDB
#
# 6. Create partitioned tables
#
#       mysql employees < $PWD/partitioning_sql/partitioned_tables_schemas.sql
#
# 7. Exchange the partitions back
#
#       mysql < $PWD/partitioning_sql/exchange_partitions.sql
#
# 8. VERIFY DATA IS CORRECT!
#
# 9. Drop temporary tables
#
#       mysql employees < $PWD/partitioning_sql/drop_temporary_tables.sql

NOTE: For replicated architectures, start by using the steps in the previous section to exchange the partitions with a set of tables on the primary database. This will push these changes down to the replicas and prepare them for the upgrade. Then upgrade each replica in turn, followed by upgrading the primary.

Getting Help

MariaDB is here to help if you need additional assistance, see our MariaDB Migration Service, or try community resources like the MariaDB Slack Community, DBA Stack Exchange, and the Community Knowledge Base.

Get Started with MariaDB Server

Go to mariadb.com/downloads for MariaDB Community Server downloads, along with connectors and everything else you need to get going with MariaDB Server! Customers also have access to MariaDB Enterprise Server which includes the advanced database proxy MariaDB MaxScale for production deployments.