In this guide you will learn to upgrade the MySQL version on your dedicated server. For this you will need to be logged into the server command line as the root user. Make sure you follow all of the steps very carefully as your databases can easily get corrupted, if this upgrade is not done properly. Also, please do keep updated backups of your databases before upgrading.
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.
