Move from MySQL 5.7 to MariaDB 10.0.34 manually?

[planetjoin]

Hello

I´m updating all my cpanel servers to new ones. (from centOS 6X to centOS 7 because Cloudlinux 6X will discontinue soon).
At now i have installed MariaDB 10.0.34 and all sites are working fine and perfect.

2 days ago i built one of the new ones (by default with mySQL 5.7) and i made the WHM upgrade to MariaDB (unattended). all was well but the system installed me the last stable MariaDB : 10.2.16

I had these problems :

1 - CL mySQL governor is prepared to work on MariaDB 10.2.15 (not 10.2.16) and can´t install it. i get a terrorific warning from CL when i try to install it!
I not installed mysql governor in this box. no problem

2 - worst problem : MANY but MANY of my clients had LOT of problems in his sites, they must to loose lot time to addapt his sites to 10.2.16 because many things changed in this version.

Briefly : I Need to update few more servers and i not want this nighmare again.

Is any way to upgrade from mysql 5.7 to MariaDB 10.0.34 from SSH, to avoid WHM install the 10.2.16?

So, i keep all my new servers with the same database configuration?

Thanks in advance for any help
Fabian

 

[24x7server]

Hi,

As per the below documentation link, MariaDB 10.0, 10.1, and 10.2 are available. You can check this in the MySQL upgrade section.
MySQL or MariaDB Upgrade - Version 74 Documentation - cPanel Documentation

 

[planetjoin]

Thanks!

Yes i saw that link too. but when i tried to upgrade to MariaDB from mySQL 5.7 WHM only display the option to upgrade to

MariaDB 10.2
recommended

• Improved connection performance.
  

I not had any option to select MariaDB 10.0 or 10.1 to make the upgrade.
Apparently the new WHM versions not allow to choose other MariaDB version than 10.2.16

This not only cause conflicts with many script using other versions, but also not allow to install well the Cloudlinux mysql Governor, because it is made for 10.2.15 (not 10.2.16)

Edit : Apparently Cloudlinux have a beta update for mysql governor for mariaDB 10.2.16 : Beta: MariaDB for MySQL Governor updated

I will try in the next server migration...

Regards
Fabian

 

[cPanelMichael]

Hello Fabian,

It's by design that MariaDB 10.1 is not an upgrade option if your system is currently using MySQL version 5.7. MySQL version 5.7 is equivalent to MariaDB 10.2 and thus converting from 5.7 to MariaDB 10.1 is considered a version downgrade (something that's not supported).

If you want to use MariaDB 10.1, you'd need to create the /root/cpanel_profile/cpanel.config file prior to installing cPanel and add the following line to it:

mysql-version=10.1

Regarding the script errors with MariaDB 10.2, most of the reports I've seen on this topic relate to use of strict_mode (MariaDB 10.2 enables it by default). After the initial upgrade to MariaDB 10.2, you can try manually adding the following value under the [mysqld] section in the /etc/my.cnf file if you want to disable strict_mode to see if it solves the issue:

sql_mode=NO_ENGINE_SUBSTITUTION

Then, restart MariaDB to apply the changes.

Thank you.

 

[planetjoin]

Regarding the script errors with MariaDB 10.2, most of the reports I've seen on this topic relate to use of strict_mode (MariaDB 10.2 enables it by default). After the initial upgrade to MariaDB 10.2, you can try manually adding the following value under the [mysqld] section in the /etc/my.cnf file if you want to disable strict_mode to see if it solves the issue:

sql_mode=NO_ENGINE_SUBSTITUTION

Then, restart MariaDB to apply the changes.

Thanks Michael!!

I just updated a new one with MariaDB 10.2.17
But the /etc/my.cnf file is very different at normal file i know ;(

This is the new my.cnf file :

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

I assume i can add lines under :

!includedir /etc/my.cnf.d

for example :

!includedir /etc/my.cnf.d
sql_mode=NO_ENGINE_SUBSTITUTION

I´m right?

Thanks
Fabian

 

[cPanelMichael]

for example :

!includedir /etc/my.cnf.d
sql_mode=NO_ENGINE_SUBSTITUTION

Hello @planetjoin,

This is close, but the example is missing the [mysqld] entry. Here's an example of how the file should look:

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION

Thank you.

 

[planetjoin]

Thanks Michael!!!

I tried this in a new server and worked perfect!

In fact i added this :

sql_mode=NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO

Regards
Fabian

Hello @planetjoin,

This is close, but the example is missing the [mysqld] entry. Here's an example of how the file should look:

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION

Thank you.

 

[cPanelMichael]

Hi Fabian,

I'm glad to see it worked out! Thanks for sharing the outcome