SOLVED convert from MysqlDB to MariaDB

ca2236

Well-Known Member
Feb 2, 2018
217
22
18
Nebraska
cPanel Access Level
DataCenter Provider
Hello everyone,

I know there are many guides that talk about converting from MysqlDB to MariaDB. However, I think I have what is probably an obvious question to someone in the mysql world or cpanel world.

These guides (unless I missed it) did not talk about restoring the data.

do I need to dump all databases (backup) and then restore the databases to MariaDB?

I think I read that MariaDB could read the raw mysql files? I'm not sure if this is true.

Thanks!
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,908
2,218
463
Hello,

Actually, there's no need to manually restore or move any data when converting to MariaDB using "WHM >> MySQL/MariaDB Upgrade". The feature takes care of configuration and RPM changes for you, and importing the MySQL data isn't required. We document this feature at:

MySQL or MariaDB Upgrade - Version 70 Documentation - cPanel Documentation

That said, note that making a backup of your databases is always recommended before upgrading.

Let us know if you have any additional questions.

Thanks!
 

ca2236

Well-Known Member
Feb 2, 2018
217
22
18
Nebraska
cPanel Access Level
DataCenter Provider
Hello,

Thanks for this link. I have two questions, based off the article you shared.
1) for this warning (last warning)

"Warning:

You must now recompile any software on your system that depended on the old version of MySQL or MariaDB so that it is compatible with the new version."

then is the software mentioned just PHP and Apache (mentioned in step 9)? Are there other packages?

2) in the opening warning, there is this line "
  • We do not support the use of MySQL's sha256_password plugin for MySQL 5.7 or MariaDB 10.2."

what password encryption strengths are used? Although, I bet I can find it on cpanel's docs for Mysql.

Thanks
 

ca2236

Well-Known Member
Feb 2, 2018
217
22
18
Nebraska
cPanel Access Level
DataCenter Provider
Additionally,

I have one more question. The reason I want to do this is do overcome the issue with usernames in cpanel, I was reading that MariaDB didn't have the limitation of the first X characters needing to be unique, that we could just set whatever username it wanted.

and this was a limitation in the mysql type?

I presume that we can't just modify the current DB as upgrades would overwrite anything (and that might cause other problems)
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,908
2,218
463
1) for this warning (last warning)

"Warning:

You must now recompile any software on your system that depended on the old version of MySQL or MariaDB so that it is compatible with the new version."

then is the software mentioned just PHP and Apache (mentioned in step 9)? Are there other packages?
Systems using EasyApache 4 do not require the rebuild or reinstallation of Apache and PHP when you upgrade MySQL or MariaDB. If you are still using EasyApache 3, then "WHM Home » Software » MySQL/MariaDB Upgrade" will initiate the EasyApache 3 build of Apache/PHP (depending on the method you choose):

  • Unattended Upgrade — This option automatically rebuilds Apache and PHP with the last saved defaults. It also automatically updates Ruby Gems and rebuilds Apache and PHP.

  • Partially-Interactive Upgrade — This option automatically updates Ruby Gems, but it does not rebuild Apache and PHP until you choose to do so.

  • Interactive Upgrade — This option steps you through the upgrade process. This process includes the Ruby Gems update, the MySQL or MariaDB upgrade, and the Apache and PHP rebuild.
You don't have to do anything else in this regard other than to initiate an EasyApache 3 build.

2) in the opening warning, there is this line "
  • We do not support the use of MySQL's sha256_password plugin for MySQL 5.7 or MariaDB 10.2."

what password encryption strengths are used? Although, I bet I can find it on cpanel's docs for Mysql.
We continue to support the use of the mysql_native_password plugin (as utilized in previous versions of MySQL/MariaDB):

MySQL :: MySQL 5.7 Reference Manual :: 6.5.1.1 Native Pluggable Authentication

This won't require any action on your part, as the existing authentication plugin utilized earlier MySQL/MariaDB versions won't change.

I have one more question. The reason I want to do this is do overcome the issue with usernames in cpanel, I was reading that MariaDB didn't have the limitation of the first X characters needing to be unique, that we could just set whatever username it wanted.

and this was a limitation in the mysql type?

I presume that we can't just modify the current DB as upgrades would overwrite anything (and that might cause other problems)
You can actually control this with the following option under the "SQL" tab in "WHM >> Tweak Settings":

Require a username prefix on names of new databases and database users

Per it's description:

When this option is enabled, the system will require that the name of each new database or database user from a cPanel session begin with the first eight characters of the system username and an underscore (_). This makes it easier to tell which user owns a given database, but it also restricts the number of characters that users can use for names of databases and database users. Note that if the username changes at a later point, the name of the database or database user does NOT change. Also, while older cPanel API calls automatically add this prefix, newer API calls expect the caller to add it.

That said, note the maximum character limits referenced on MySQL Username Limit still apply:

MySQL limits the database username to 16 characters. The system includes the database prefix (the first eight characters of the cPanel account's username and an underscore character) in the character count for the username. For example:
  • A MySQL database with the db_ prefix allows usernames that contain up to 13 characters.
  • A MySQL database with the example_ prefix allows usernames that contain up to eight characters.
MariaDB limits the database username to 47 characters. The system includes the database prefix (all of the cPanel account's username and an underscore character) in the character count for the username.

For example:
  • A MariaDB database with the db_ prefix allows usernames that contain up to 44 characters.
  • A MariaDB database with the example_ prefix allows usernames that contain up to 39 characters.

Thank you.
 

ca2236

Well-Known Member
Feb 2, 2018
217
22
18
Nebraska
cPanel Access Level
DataCenter Provider
One last question, I hope :)

in the mariadb, there is recommended for 10.1 and general availability for 10.2. Is the general availability stable? is it not recommended?
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,908
2,218
463
in the mariadb, there is recommended for 10.1 and general availability for 10.2. Is the general availability stable? is it not recommended?
Essentially, MariaDB 10.1 is based off MySQL 5.6 and MariaDB 10.2 is based off MySQL 5.7. I've not seen reports of problems related to the use of MariaDB 10.2, but you could always use MariaDB 10.1 for now and then upgrade to MariaDB 10.2 later.

One item of note is that you can't switch from MariaDB back to MySQL once you convert over.

Thank you.
 

ca2236

Well-Known Member
Feb 2, 2018
217
22
18
Nebraska
cPanel Access Level
DataCenter Provider
Hello,

Thanks, I went through the upgrade. It seemed to go smooth. One thing, and maybe this should be a feature request, is that if you can't downgrade, I wish it would not even present you the option to switch back to mysql or a lower version :)

but the warning in yellow is probably sufficient. I went with 10.2 Before closing this thread. Is there any link to see known cpanel issues that might arise (if any) with mysql, easyapache or anything else?
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,908
2,218
463
I went with 10.2 Before closing this thread. Is there any link to see known cpanel issues that might arise (if any) with mysql, easyapache or anything else?
Hello,

We don't provide a document or resource with a list of potential issues (I've not noticed anything in-particular), but you are welcome to report any issues you face here and we will provide information about any internal cases (or create a new internal case if it's not yet been reported).

Thank you.