MySQL/MariaDB Upgrade on Remote Server

[Michael Legg]

The cPanel "MySQL/MariaDB Upgrade" tool doesn't work if you have MySQL running on a separate server .
When I try to run it, I get the message "This system uses a remote server for MySQL/MariaDB. Perform the MySQL/MariaDB upgrade on the remote server."
Are there any pitfalls we need to be aware of if we convert from MySQL to MariaDB manually so we don't break anything?

 

[HostNoc]

Hi

There will no issue in converting from MySql to MariaDB, but please note that Switching back from MariaDB to MySQL may not be so easy.

Regards

 

[cPanelLauren]

If you're running a remote server, yes you need to update it on the remote server. @HostNoc is correct there is typically no issue with conversion from MySQL to MariaDB - best practice dictates as always to ensure that you have backups just in case though.

 

[Michael Legg]

Thank you. Can you point me to any clear step by step instructions on how to do the upgrade ?

As opposed to "Run your package manager’s update command, stop your MySQL server, and then install MariaDB with your package manager".

 

[HostNoc]

Login to WHM and Use the quick search box on the left and type in: mysql upgrade and Click the MySQL/MariaDB Upgrades link and then Select your desired version of MariaDB and click on next

Wait for the “Upgrade Warnings” to finish and click on continue button

Wait for the “Upgrade Type” page to finish and select Unattended Upgrade and click continue

Finally wait for the upgrade process to complete

NOTE: Before you start this process please make sure you have backup of your databases

 

[Michael Legg]

That would be ideal, but it doesn't work if you have MySQL running on a separate server.
Maybe cPanel will add that feature

 

[Michael Legg]

I've taken the bits and pieces of specific instructions that I could find on the web that were relevant to my situation (Upgrade MySQL Community 5.6.37 -> MariaDB 10) and distilled them down to this.

(1) Create the file /etc/yum.repos.d/MariaDB.repo and add this to it ...

# MariaDB 10.4 CentOS repository list - created 2020-03-16 05:42 UTC
# MariaDB - Setting up MariaDB Repositories - MariaDB
[mariadb]
name = MariaDB
baseurl = Index of /10.4/centos7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

------------------------------------------------

(2) Copy and run the command created by the wizard at MariaDB - Setting up MariaDB Repositories - MariaDB to install the repository signing key, and copy the repository configuration into your sources list. I have no idea what "copy the repository configuration into your sources list" means, but I ran the command successfully so yum was able to find it.

yum install MariaDB-server MariaDB-client

------------------------------------------------

(3) Run your package manager’s update command
mysql_upgrade -uroot -p

-------------------------------------------------

(4) There were an innumerable references to stopping and starting MYSQL but no two references agreed on when that should be done, and since it has to be running for mysql_upgrade to work, I did it afterwards.

--------------------------------------------------

End result after doing all of this is when I log into MySQL it says I'm still running MySQL Community 5.6.37.

I tried running the upgrade again, and this time it said "This installation of MySQL is already upgraded to 5.6.37, use --force if you still need to run mysql_upgrade"

So mysql_upgrade isn't aware that I want it to change to MariaDB (Which is understandable) so how do I tell it that's what I want? (It's also interesting that it thinks that 5.6.37 is the latest version of MySQL, I would think it would at least try to upgrade to something more recent)

So I'm back to my previous question - can anyone point me to a set of step by step instructions on upgrading MySQL to MariaDB?

 

[Michael Legg]

I found another set of instructions that says after stopping MySQL, you should start MariaDB using
# systemctl start mariadb.service
# systemctl enable mariadb.service

And then run mysql_upgrade.

This made a lot of sense and seemed to be the key piece of information missing from all of the other instructions (Including those on the MariaDB website) but alas MariaDB wont start ... I get the error "Unit not found."

 

[Michael Legg]

Okay ... I think I see the problem - MariaDB installation is failing.

There are dozens of these errors ...

file /usr/share/man/man1/mysql_waitpid.1.gz from install of MariaDB-client-10.4.12-1.el7.centos.x86_64 conflicts with file from package mysql-community-client-5.6.47-2.el7.x86_64

Apparantly, I can only go from mySQL 5.6 to Maria 10.0, so I edited the repo file to reflect this, but it still fails even with MariaDB 10.0.

 

[Michael Legg]

The whole MySQL application is completely trashed now. Fortunately, I did this on a test server before trying on a production server. I guess there's a reason why even cPanel doesn't build an "Upgrade to MariaDB on a remote server" into their GUI.

 

[Michael Legg]

i hope this might help someone else ...

Despite the oft heard claim that "MariaDB is a drop in replacement for MySQL", it would not install on my server until I removed every trace of MySQL.
I've lost track of all of the commands I ran but the final one that allowed MariaDB to install was

yum -y remove mysql-community-common-5.6.47-2.el7.x86_64

 

[normanj]

Login to WHM and Use the quick search box on the left and type in: mysql upgrade and Click the MySQL/MariaDB Upgrades link and then Select your desired version of MariaDB and click on next

Wait for the “Upgrade Warnings” to finish and click on continue button

Wait for the “Upgrade Type” page to finish and select Unattended Upgrade and click continue

Finally wait for the upgrade process to complete

NOTE: Before you start this process please make sure you have backup of your databases

Thank you so much for your help @HostNoc. Finally It's working!!