CL6 with MariaDB 10.3 migrating to CL8 with mySQL 8.

[bloatedstoat]

Hello,

I'm prepping for a migration from our trusty CL6 server to a shiny new Almalinux/CL8 server.

The cPanel versions are identical, however the database on the source server is MariaDB 10.3 and the out of the box install provisioned to us has mySQL8. I've read on here about removing mySQL8 and replacing it with MDB10.3 but that operation is considered to be a downgrade and isn't supported - although we'd likely update it once everything had been moved. I mention this because ideally it would be nice to have near as identical software on the destination server to that on the source, one less thing to be concerned with.

We do not have any websites that use exotic SQL statements, they're all fairly common or garden INSERT, SELECT, UPDATE, DELETE statements with some VIEWS.

Is there anything to catch us out when moving accounts from MariaDB to mySQL?

We'd like to try the new Live Transfer tool, but wanted to clarify the database situation and any pitfalls others may have experienced before diving in.

Thanks in advance, cheers.

 

[mtindor]

I've migrated hundreds of accounts from a CL6 ELS MariaDB 10.3 server to a CL8 MySQL 8.0 server. I think I only ran into an issue one time, and it had to do with a character set. I forget how I resolve it, but it was a one-off thing. I would suggest that during the time you are doing the transfers from the MariaDB 10.3 server to the MySQL 8 server, that you try to make sure you have some sane sql_mode= settings on the MySQL 8.0 server . I think by default MySQL might have STRICT_TRANS_TABLES as one of the defaults in the sql_mode variable. If so I'd consider removing that.

For instance, if you check your running mysql server and it has something like STRICT_TRANS_TABLES enabled in the sql_mode line (like below) you probably want to remove it.

mysql mysql
mysql> show variables like 'sql_m%';
+---------------+---------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+---------------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> exit
#

I'd add a line in /etc/my.cnf (or edit an existing sql_mode line if you have one):

sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Then '/scripts/restartsrv_mysql'

A check of the sql_mode variable should no longer show the STRICT_TRANS_TABLES

That's the only thing I can think of that might bite you. But depending upon how ancient some of the SQL-driven sites might be on your server, you might run into other issues. Like ancient Joomla 1/2 installations or something. I don't have any Joomla on my servers.

 

[bloatedstoat]

@mtindor.33655, that's perfect, thanks for that mate, appreciated!

 

[ResellerWiz]

Performance wise, I would never choose MySQL over MariaDB. I would reinstall cPanel and set MariaDB as the database server:

How to customize the version of MySQL or MariaDB used when installing cPanel for the first time

Introduction It is possible to preconfigure a cPanel installation so that your desired version of MySQL is installed from the start. Procedure On a brand new server that does not have cPanel inst...

support.cpanel.net

 

[mtindor]

Performance wise, I would never choose MySQL over MariaDB. I would reinstall cPanel and set MariaDB as the database server:

How to customize the version of MySQL or MariaDB used when installing cPanel for the first time

Introduction It is possible to preconfigure a cPanel installation so that your desired version of MySQL is installed from the start. Procedure On a brand new server that does not have cPanel inst...

support.cpanel.net

Pretty sure that's a hotly debated topic. I always felt that was the case back in the MySQL 5.x days. Switched to MariaDB and was never happier. However, I have had zero issues with MySQL 8.x -- performance seems on par to me / my clients. YMMV.

 

[ResellerWiz]

Pretty sure that's a hotly debated topic. I always felt that was the case back in the MySQL 5.x days. Switched to MariaDB and was never happier. However, I have had zero issues with MySQL 8.x -- performance seems on par to me / my clients. YMMV.

I’ve tried MySQL 8 in a high volume shared hosting production environment and it couldn’t keep up with the queries no matter how much tweaking was done.

Moved the accounts to another server with same hardware and configuration with MariaDB 10.x and it handled the large amount of queries with ease, so that was proof enough for me.