Migrating from Mysql 8.0 to MariaDB 10.5

You are viewing an old version of this question. View the current version here.

Hello people,

i am using an Ubuntu 20.04 Server and trying to perform a migration from Mysql 8 to MariaDB 10.5. Unfortunately it seems like this can't be done smoothly because just removing mysql-server-8.0 and installing mariadb-server leads to db server not loading because InnoDB has troubles reading ibdata file.

Because of that i am starting from scratch, which means backing-up the whole db

mysqldump -u USER -p --all-databases --routines --triggers > dump.sql

removing mysql 8, wiping out /etc/mysql and /var/lib/mysql, and install a clean mariadb package, which is a succesful operation up to now.

However, when i try to restore the db

mysql -u USER -p < dump.sql

mariadb is having some trouble with the database called "mysql" and it's "db" table. In fact this table also has a "Db" column inside which apparently can not be accepted, infact if i try to name the table anything else the restore is being completed. Here is the table:

CREATE TABLE `db` (

`Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',

`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',

`Select_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Insert_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Update_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Delete_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Create_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Drop_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Grant_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`References_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Index_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Alter_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Create_view_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Show_view_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Execute_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Event_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

`Trigger_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',

PRIMARY KEY (`Host`,`Db`,`User`),

KEY `User` (`User`)

) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Database privileges';

The command also works if i change ENGINE to MyIsam instead of InnoDB, but all the other tables are InnoDB and so i would like to know if this could lead to issues or if there is any other workaround of suggestion.

Many thanks in advance

Answer

I would advice to use the new mysqldump option --system=user provide in MariaDB and report if it failed on the MySQL also possible tools to dump user can be found in percona-toolkit

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.