In-Place migration of MySQL-5.7 to MariaDB-10.6

migration_mysql5.7_mariadb10.6.md

Notes for in-place migration of MySQL 5.7 to MariaDB 10.6

Removal of MySQL

apt remove mysql-apt-config
apt purge mysql-apt-config
apt remove mysql-server mysql-community-server mysql-community-client mysql-common mysql-client

Install MariaDB

You might need to add a repository to get/install a specific version of MariaDB.

For example:

cat /etc/apt/source.list.d/mariadb.list
> deb https://mirror.nextlayer.at/mariadb/repo/10.6/debian bookworm

apt install mariadb-server mariadb-client
# if you want to use it:
apt install mariadb-backup

Migration

Config

This notes will not go into what config-changes are needed.

Just configure it as MariaDB requires.

The service must be able to start-up to continue..

Else check the logs and fix your specific problems:

systemctl status mariadb.service
tail /var/log/mysql/error.log -n 100
journalctl -u mariadb.service -n 100

---

Error 1 - auth_socket

Got the following error when executing the 'mysql' command line client ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded

Single instance

rm -rf /var/lib/mysql/.gnupg
systemctl stop mariadb.service
mariadbd-safe --skip-grant-tables --skip-networking &
systemctl start mariadb.service

Multi-Instance

rm -rf {PATH-TO-INSTANCE}/.gnupg
systemctl stop mariadb@{INSTANCE}.service
mariadbd-safe --defaults-group-suffix={INSTANCE} --datadir={PATH-TO-INSTANCE} --skip-grant-tables --skip-networking &
systemctl start mariadb@{INSTANCE}.service

All

mysql
use mysql
update user set plugin='unix_socket' where plugin='auth_socket';
ps -aux | grep mariadb-safe
kill {PID}

---

Error 2 - Table rebuild required

Table rebuild required. Please do "ALTER TABLE 'my_table' FORCE" or dump/reload to fix it!

Single instance

mysql

Multi-Instance

mysql --socket /run/mysqld/mysqld-{INSTANCE}.sock

All

update mysql.innodb_index_stats set last_update=0;
alter table mysql.innodb_index_stats modify last_update INT UNSIGNED NOT NULL;
update mysql.innodb_table_stats set last_update=0;
alter table mysql.innodb_table_stats modify last_update INT UNSIGNED NOT NULL;

---

DB-Upgrade

Single instance

mariadb-upgrade

Multi-Instance

mariadb-upgrade --socket /run/mysqld/mysqld-{INSTANCE}.sock