Learn to Upgrade MySQL on CentOS

You will discover how to update the MySQL version on your dedicated server by following this instruction. You must be logged in as the root user to the server command line in order to do this. If this upgrade is not done correctly, your databases might easily become damaged, therefore make sure you carefully follow every step. Before updating, kindly maintain current backups of your databases.

NOTE: You will require root access to upgrade MySQL.

With the root user, log into your server via SSH.

Then, create a directory for storing the backups and list the databases are already backed up.

mkdir /root/dbbackups; touch /root/dbbackups/list

Next, take the backup of the databases. Don’t panic if you get a SELECT and LOCK error on certain tables related to the schema databases.

for db in $( mysql -e ‘show databases’ | grep -v “Database\|information_schema” | awk ‘{print $1}’ ) ; 
do mysqldump –add-drop-table $db > /root/dbbackups/$db.sql && echo $db >> /root/dbbackups/list; done

Edit the /var/cpanel/cpanel.config file with your preferred editor (nano, vim, vi, etc). In this example, we are using vim as below:

vim /var/cpanel/cpanel.config

Search for mysql-version (default is around line 180) and change it to the desired version.

Save the file and execute the below command for cPanel to upgrade or downgrade your MySQL version.

/usr/local/cpanel/scripts/check_cpanel_rpms –fix

Once the script is complete and there are no errors indicating it failed. Then log into the MySQL command interface and you will see the following. In this tutorial, we have downgraded from 5.6 to 5.5:

mysql -u root

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.49-cll MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle Corporation and/or its affiliates have trademarked Oracle. Respective owners might have trademarked other names.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

For bring on the safe side, you might need to repair the databases as a preventative measure.

mysqlcheck -aR

Also, ensure that you run the following command to upgrade the system tables so there are no issues.

mysql_upgrade

For restoring the databases that you have previously taken the backup of, you can use the following command.

for db in `cat /root/dbbackups/list` ; do mysql $db < /root/dbbackups/$db.sql ; done

You can use EasyApache within WHM to recompile PHP, since it is usually important due to the MySQL changes 90% of the time.

In this way, you can upgrade MySQL on CentOS.