!!BEFORE DOING BELOW, SHORTEN THE USERID TO MAX 16 CHARS, AS MYSQL ONLY SUPPORT MAX 16 CHARACTER FOR MYSQL.USER TABLE!!
You can do this with relative ease, using cPanel and CloudLinux’s internal scripts.
Step one, set the version of MySQL you want installed using cPanel’s RPM version script. In our example we’re installing MySQL 5.5.
/scripts/update_local_rpm_versions --edit target_settings.MySQL51 uninstalled
/scripts/update_local_rpm_versions --edit target_settings.MySQL55 installed
/scripts/update_local_rpm_versions --edit target_settings.MySQL56 uninstalled
/usr/share/lve/dbgovernor/db-select-mysql --mysql-version=mysql55
Then, if you run the cPanel RPM script asking it to look for problems, you’ll see something like this:
/scripts/check_cpanel_rpms --long-list --list-only
[20141227.195928]
[20141227.195928] Problems were detected with cPanel-provided files which are RPM controlled.
[20141227.195928] If you did not make these changes intentionally, you can correct them by running:
[20141227.195928]
[20141227.195928] > /usr/local/cpanel/scripts/check_cpanel_rpms --fix
[20141227.195928] The following RPMs are missing from your system:
[20141227.195928] MySQL55-client-5.5.40-1.cp1136
[20141227.195928] MySQL55-devel-5.5.40-1.cp1136
[20141227.195928] MySQL55-server-5.5.40-1.cp1136
[20141227.195928] MySQL55-shared-5.5.40-1.cp1136
[20141227.195928] MySQL55-test-5.5.40-1.cp1136
[20141227.195928] compat-MySQL50-shared-5.0.96-4.cp1136
[20141227.195928] compat-MySQL51-shared-5.1.73-4.cp1136
Before you go any further, I recommend making an archive backup of all of your existing MySQL data. Assuming your /var/ partition can hold your backup, you can use this command:
cp -a /var/lib/mysql /var/lib/mysql.bak
The next step is to remove the MariaDB rpms. Use this command to view all installed RPMs that use MariaDB in the name:
rpm -qa | grep MariaDB
If you feel confident that all of the RPMs listed are the ones you want to remove, you can use this command to quickly remove them:
rpm -qa | grep MariaDB | xargs rpm -e
Then confirm they were removed with this command again:
rpm -qa | grep MariaDB
Then, ask cPanel to install your chosen version of MySQL with this command:
/scripts/check_cpanel_rpms --fix
Once that completes, you shouldn’t see any other problems! If you do see any problems related to the version of MySQL that the server has installed, try running a cPanel update with this command:
/scripts/upcp
That should take care of any lingering errors you might encounter.
Also remove MariaDB* from /etc/yum.repos.d before executing the above.
Then run easyapache again
taken from: https://encylia.com/2015/01/02/switching-from-mariadb-to-mysql-on-cpanel-and-cloudlinux/
cpanel support replies :
First I know you were made aware that the MariaDB version 10.1 code base is slightly different from the MySQL code base which is why we do not support downgrades. MariaDB is a drop-in replacement for MySQL, that means it is 100% compatible with MySQL. However it also has additional features that MySQL does NOT have. So that's why downgrades are not supported because once you upgrade from MySQL to MariaDB, the table structure of the mysql database itself is changed. So we cannot in good conscience recommend this action and if you decide to move forward on your own, we wouldn't be able to assist with problems if they arise.
Additionally, in our opinion is 10 times better than MySQL. It handles database corruption much better (IE: we see less InnoDB corruption with MariaDB then we do with MySQL), which is why we offer it and highly recommend it.
All of that being said, I have as a one time courtesy fixed the issues that did arise. First and foremost, the removal of MariaDB and re-installation of MySQL 5.6 was done correctly. Congratulations on that. The first problem I noticed was that cPanel still believed you had MariaDB, within the /var/cpanel/cpanel.config file was this line:
mysql-version=10.1
I changed this to
mysql-version=5.6
Please be very very careful making any changes to the /var/cpanel/config file. Any other changes and you could prevent cPanel services from starting. This change will also prevent the nightly updates (upcp) from trying to re-install MariaDB 10.1
Next, I checked the /var/lib/mysql/server.kh689.com.err log file, and noticed this error:
2016-08-05 22:04:29 4742 [ERROR] Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have type char(77), found type char(141).
2016-08-05 22:04:29 4742 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
I fixed that for you by following these steps:
1) Create a backup of the current mysql database. (in /root/cptechs)
[23:21:58 server root@7618845 ~/cptechs]cPs# mysqldump mysql > mysql.sql
2) Load mysql with the mysql database
[23:22:08 server root@7618845 ~/cptechs]cPs# mysql mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2149
Server version: 5.6.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
3) Ran the following query to alter the table (specifically the definer column).
mysql> ALTER TABLE mysql.event MODIFY definer CHAR(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
Finally, to address the issue with the length of the characters for the user names.
MariaDB has a 64 character limit while MySQL has a 16 character limit.
First thing I found out was that the mysql user table actually had the correct values.
mysql> select Host,User from user where user like 'goodcoid%';
+------------------+------------------+
| Host | User |
+------------------+------------------+
| 119.235.250.125 | goodcoid |
| 119.235.250.125 | goodcoid_gtsuks3 |
| localhost | goodcoid |
| localhost | goodcoid_gtsuks3 |
| server.kh689.com | goodcoid |
| server.kh689.com | goodcoid_gtsuks3 |
+------------------+------------------+
6 rows in set (0.01 sec)
mysql>
Those user names are all 16 characters. So the only other place to check is where cPanel keeps the MySQL database information and grant tables, which are in /var/cpanel/databases
First let's check and see if my hunch is correct. If I grep for the 17 character user name in /var/cpanel/databases/*
[22:56:15 server root@7618845 /var/cpanel/databases]cPs# grep goodcoid_gtsuks3s *
I get quite a few hits. So the next step is to first backup the /var/cpanel/databases/ directory.
[22:57:20 server root@7618845 /var/cpanel]cPs# cp -rfpd databases databases.cp7618845
[22:57:32 server root@7618845 /var/cpanel]cPs# cd databases
Then I run the replace command and replace the 17 character user name with the 16 character user name.
[22:57:43 server root@7618845 /var/cpanel/databases]cPs# replace 'goodcoid_gtsuks3s' 'goodcoid_gtsuks3' -- *
goodcoid.json converted
grants_goodcoid.cache converted
grants_goodcoid.yaml converted
Another search (grep) now shows nothing returned (which is good).
[22:58:01 server root@7618845 /var/cpanel/databases]cPs# grep goodcoid_gtsuks3s *
[22:58:22 server root@7618845 /var/cpanel/databases]cPs#
I had to do the same for the ind0au70 account FYI.
I no longer see any errors in the log file and I see no errors in any of the cPanel users accounts when accessing MySQL, and I ran a /scripts/check_cpanel_rpms which all came back clean.
This should solve the issue, but I would take backups again just in case.