How to Upgrade MySQL on CentOS

This guide will go over how to upgrade the MySQL version on your dedicated server. This requires you to be logged into the server command line as the root user. Please follow all of the steps very carefully as you can easily corrupt your databases if this upgrade is not done properly. Also, please remember to keep updated backups of your databases prior to upgrading.

NOTE: Root access is required to upgrade MySQL.

Creating a Back Up & Upgrading

  1. Log into your server via SSH with the root user.
  2. Create a directory to store the backups and list the databases that have been backed up.
    mkdir /root/dbbackups; touch /root/dbbackups/list
  3. Next, backup the databases. It’s okay if you get a SELECT and LOCK error on certain tables relating 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
  4. Edit the /var/cpanel/cpanel.config file with your favorite editor (nano, vim, vi, etc). In this example, we’ll use vim
    vim /var/cpanel/cpanel.config
  5. Perform a search for mysql-version (default is around line 180) and edit it to the desired version.

  6. Save the file and run the following command for cPanel to upgrade or downgrade your MySQL version.
    /usr/local/cpanel/scripts/check_cpanel_rpms –fix
  7. Once the script is complete and there are no errors saying it failed, proceed to log into the MySQL command interface and you should see the following. In this tutorial we went from 5.6 to 5.5 in order to demonstrate
    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 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>

  8. Just to be on the safe side you may want to repair the databases as a preventative measure.
    mysqlcheck -aR
  9. Also, you should run the following command to upgrade the system tables so there are no issues.
    mysql_upgrade

If you need to restore the databases you have previously backed up then you can do so via 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 necessary due to the MySQL changes 90% of the time.

Leave a Reply