How to Upgrade MySQL Manually on a cPanel Server

Keeping MySQL on the server up to date is essential for the efficiency  of codes . There are times when we just cannot let cPanel handle an upgrade because we may end up having our databases not working properly  after an upgrade. So, therefore we need to do upgrades of MySQL manually.

Procedure

The first step for this process is to generate the backup of all MySQL databases from ssh.

cp -pr /var/lib/mysql /var/lib/mysql.backup

Now login to WHM  Main >> Software >> MySQL Upgrade.

1. Select the version of MySQL you wish to use from the first page. Please make note of the End of life dates of different versions and select accordingly.
2. On the second page, confirm that you want to continue with the upgrade by clicking every available checkbox.
3. Select Interactive upgrade and click Continue.
4. Once the upgrade has completed, click Continue.
5. Your Ruby MySQL gem may need to be recompiled.  When it is finished, click Continue.
6. PHP for cPanel and WHM may need to be recompiled.When it is finished, click Continue.
7. Select whether you wish to rebuild Apache and PHP with the saved settings or with new settings:
   • If you select to rebuild Apache and PHP with new settings, select the appropriate options from the EasyApache Interface.
   • If you select to rebuild Apache and PHP using saved settings, the process will run automatically.
8. To finish the process, click Back.

Upgrade MySQL without using MySQLUP script.

This will be required generally when we wish to upgrade to a version you prefer and do not want cPanel to interfere. Here, the first step will be to disable MySQL updates under the Update Preference section of whm/cPanel

1. Under the Server Configuration Section click Update Preferences.

2. Under cPanel package updates click Never for MySQL.

3. Backup all the databases with the mysqldump command as above or with following scripts:

mkdir /home/alldatabasesbakup
mysqldump -add-drop-table --all-databases |
gzip > /home/alldatabasesbackup --add-dropsbakup.sql.gz
mkdir /root/sqllibs
cp /usr/lib/libmysqlclient.* /root/sqllibs

4. Use the following command to find the installed mysql packages that will be removed before upgrading:

rpm -qa | grep -i mysql-

5. Remove the rpms listed by above command using rpm -e e.g.

rpm -e MySQL-devel-4.1.21-0.glibc23

6. There may be some dependency issue while removing the rpms so remove the dependency first and then the main one later.

7. Download the MySQL version you wish to install per the os version.  The following url may help: http://httpupdate.cpanel.net/mysqlinstall/

8. Install the rpms using rpm -i for each package e.g.

rpm -i MySQL-client-5.1.30-0.glibc23.i386.rpm

9.  Now edit the /var/cpanel/cpanel.config file to add the version of MySQL installed with above command

10. Disable the automatic updates of MySQL.

touch /etc/mysqlupdisable

This command will protect from reverting the mysql version during updates

11. Restart MySQL and check the version with mysql -V

12.  Copy the lib files back and do not overwrite

mv /root/sqllibs/libmysqlclient.* /usr/lib/mysql/

13.  Execute following commands

/scripts/perlinstaller –force Bundle::DBD::mysql
/scripts/makecpphp

Make sure to backup everything before doing the upgrade.

This is are the two available ways to upgrade MySQL on the server. Since PHP is dynamically linked to MySQL libraries, you need to confirm that PHP on the server is working fine, or you may need to recompile PHP using easyapache so that it will use the newly installed MySQL libs.