Switch to MariaDB on cPanel servers

[el5olfaa.com]

Switching to MariaDB from Mysql on cPanel servers

Hello,

On most cases MariaDB is providing better performance than Mysql while standing with a good compatibility for scripts that made depending on Mysql .

Today i will learn you how to switch to MariaDB on cPanel server and try to fix compatibility issues "if happened" .

Let's follow my instructions :
First we will make a backup of our Mysql files & databases :
1) Go to your WHM panel "Home » Service Configuration » Service Manager" .

2) Uncheck "MySQL Server" to disable and take it down until we finish our backup .

3) Connect to your server shell and do the following command to make an copy of all mysql files :

cp -r /var/lib/mysql /var/lib/mysql_backup

4) Go to your WHM panel "Home » Service Configuration » Service Manager"

5) Check "MySQL Server" again and save .

6) Go back to your server shell and do this command to take dump all databases in one file :

mysqldump --all-databases --force > /var/lib/mysql_backup/all-databases.sql

Now let's go through the switching instructions :

1) Go to your WHM panel "Home » SQL Services » MySQL/MariaDB Upgrade"

2) Choose "MariaDB 10.2" from list and click on "Continue" button .

3) Check all boxes and click on "Continue" button .

4) Now you will see tow options to choose between just choose "Interactive Upgrade" to go through upgrading process for any exist database .

5) Click on "Continue" button and wait it to finish .

Remember that MariaDB 10.2.4+ strict mode is enabled by default so if you countered some compatibility issue on insert queries like this :

Field xxxx has no default value

Connect to your server shell and modify my.cnf by using the following command :

nano /etc/my.cnf

Then add the following line after "[mysqld]" section :

sql-mode = "NO_ENGINE_SUBSTITUTION"

Now restart MariaDB server to apply new configuration by using this command :

service mysql restart

That's all .

Good luck

 

[cyclonehosting]

Hi,
Thanks so much. I also noticed that mysql 5.7 is available. Have you had any issues with mariadb? Did you lose any DB's from upgrading? It also said that if I upgrade the server to mysql 5.7 it enables the strict mode. To disable strict mode no matter which route I go, I would still add that line in the my.cnf?

Thanks so much