L

Tutorial How to install MariaDB 10.3 in favor of MySQL 8.0

It's not officially possible to migrate from MySQL 8.0 to MariaDB 10.3 in cPanel, since the two versions on a storage level are by no means compatible.

If you however require to change from MySQL 8.0 to MariaDB 10.3, this guide explains how to do it.

Note: Please keep in mind, this is by no means supported by cPanel. Attempting this is at your own risk.

Since the underlying file format differs between MySQL 8.0 and MariaDB 10.3, it requires that you back up all your databases, since they have to be restored after the MariaDB installation.

1: Perform a backup of your databases

To perform a backup of your databases, however, we want to skip a few databases, namely mysql, sys, information_schema and performance_schema, and then we dump it. We'll use a script from stackoverflow below (slightly modified):

Bash:
mysql -ANe "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','information_schema','performance_schema', 'sys')" > databases.txt

DBLIST=""
for DB in `cat databases.txt` ; do DBLIST="${DBLIST} ${DB}" ; done

MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > all-dbs.sql
Your databases are now stored in all-dbs.sql

2: Uninstall MySQL packages

Next we'll uninstall MySQL 8.0 packages, it can be done quite simply with yum:

Bash:
yum remove mysql-community-*
Simply press y to confirm the uninstall.

Now we also remove the /var/lib/mysql:

Bash:
rm -rf /var/lib/mysql
3: Change the mysql-version in cpanel.config

We now want to change from MySQL 8.0 to MariaDB 10.3 in the cpanel.config file.

Bash:
sed -i 's/mysql-version=8.0/mysql-version=10.3/g' /var/cpanel/cpanel.config
4: Install MariaDB 10.3

We can simply use whmapi1 to start an "upgrade":

Bash:
whmapi1 start_background_mysql_upgrade version=10.3
It will return an upgrade_id, such as mysql_upgrade.20200731-204729

We can use this to check the progress:

Bash:
tail -f /var/cpanel/logs/mysql_upgrade.20200731-204729/unattended_background_upgrade.log
You'll eventually see:
Code:
mysql restarted successfully.
MariaDB upgrade completed successfully
While you can execute mysql and it will give you the MariaDB prompt, there's a few functions that doesn't work, we'll do that by resetting the password.

5: Reset the MySQL root password

Some functions will not really work correctly without resetting the MySQL root password, I don't know the exact reason, but resetting the password makes all functions work again:

Bash:
whmapi1 set_local_mysql_root_password password=superSecurePassword
6: Restore databases

Restoring the databases will be as simple as running:

Bash:
mysql < all-dbs.sql
However, this will only restore the databases, not the users and the grants for the users. We'll do this next!

7: Restore user grants

cPanel keeps users (including password hashes) and grants in some map files, we can use these files to restore the grants for all our users:

Bash:
for user in $(ls /var/cpanel/users); do echo $user; /usr/local/cpanel/bin/restoregrants --db=mysql --cpuser=$user --all; done
And we're done :)
  • Like
Reactions: IndicHosts.net
Author
LucasRolff
Views
253
First release
Last update
Rating
5.00 star(s) 1 ratings