Update MySQL version cPanel/WHM using API / command line / Terminal

There a few ways to update MySQL or MariaDB and one of them is doing this via the command line / terminal and I would like to share the process with you.

Introduction

cPanel is one of the most popular Linux-based control panels for web hosting accounts. It’s been available for a long time and the installed software can often be outdated or become deprecated. cPanel is really picky when it comes to updating software and you will always be aware that issues may occur if you update any part of the software in a manner that cPanel will not acknowledge and you might end up corrupting the cPanel’s core files.

Requirements

If you’re running an outdated MySQL version or just want to run the latest MySQL version you will need a root access in order to update MySQL or MariaDB via the GUI - WHM or using the command line.

If you have root level access to your server / droplet than you’re good to go with the update process.

Updating MySQL / MariaDB

cPanel/WHM have a lot of available tools that you can use in order to update parts of your software. The build-in whmapi offers a lot of functionality and you can use in order to update software or change settings of the server or individual accounts hosted on the server.

You can use the following command in order to see the available versions to which you can update your current MySQL / MariaDB installation:

whmapi1 installable_mysql_versions

An example output of the command will be:

[root@sammy ~]# whmapi1 installable_mysql_versions
--- 
data: 
  versions: 
    - 
      server: mariadb
      version: '10.2'
    - 
      server: mariadb
      version: '10.3'
metadata: 
  command: installable_mysql_versions
  reason: OK
  result: 1
  version: 1

You can find more info about this option of the whmapi using this link - https://documentation.cpanel.net/display/DD/WHM+API+1+Functions+-+installable_mysql_versions

Now if you want to update MariaDB to 10.2 you can use the following command:

whmapi1 start_background_mysql_upgrade version=10.2

You can find more info about this option of the whmapi using this link - https://documentation.cpanel.net/display/DD/WHM+API+1+Functions+-+start_background_mysql_upgrade

The command output will look like this:

{
  "data": {
    "upgrade_id":"mysql_upgrade.20200202-172923"
  },
  "metadata": {
    "version": 1,
    "reason": "OK",
    "result": "1",
    "command": "start_background_mysql_upgrade"
  }
}

Note that there is an upgrade_id in the output, we will use it in order to track down the progress on the update.

In order to see the progress on the task you can do many things like using strace and monitor the process id of the upgrade process, but cPanel has another really useful api tool in order to monitor the upgrade.

whmapi1 background_mysql_upgrade_status upgrade_id=mysql_upgrade.20200202-172923

You can find more info about this option of the whmapi using this link - https://documentation.cpanel.net/display/DD/WHM+API+1+Functions+-+background_mysql_upgrade_status

The out put will look like this:

{
    "data": {
        "error": 0,
        "log": "Starting process with log file at /var/cpanel/logs/mysql_upgrade.20141108-172923/unattended_background_upgrade.log\n Beginning 'MariaDB 10.0' upgrade...\nObtained version information from system.\nEnsuring the 'MariaDB100' repository is available and working.\ncheckyum version 22.3\nEnsuring that the package 'MariaDB-client' with version matching '10.0' is available.\nEnsuring that the package 'MariaDB-common' with version matching '10.0' is available.\nEnsuring that the package 'MariaDB-devel' with version matching '10.0' is available.\nEnsuring that the package 'MariaDB-server' with version matching '10.0' is available.\nEnsuring that the package 'MariaDB-shared' with version matching '10.0' is available.\nEnsuring that the package 'coreutils' is available.\nEnsuring that the package 'grep' is available.\nEnsuring that the package 'perl-DBI' is available.\n your MariaDB server version for the right syntax to use near '.`netcopya0I5KfqYTfHqJr` FOR UPGRADE' at line 1 when executing 'CHECK TABLE ... FOR UPGRADE'\nFATAL ERROR: Upgrade failed\nDone building configuration.\nHooks system enabled.\nChecking for and running RPM::Versions 'post' hooks for any RPMs about to be installed\nAll required 'post' hooks have been run\nRunning: /usr/local/cpanel/scripts/check_cpanel_rpms--targets=MySQL41,MySQL50,MySQL51,MySQL55,MySQL56,MariaDB100,MariaDB101 --fix\nRestarting mysql service.\nWaiting for 'mysql' to restart............................................................waiting for 'mysql' toinitialize .........finished.\n\u001b[1;32mMariaDB upgrade completed successfully\u001b[0m\n------------------------------------\nChecking for Ruby update...\nNo Ruby update required.\n------------------------------------\n",
        "error_log": "",
        "state": "success"
    },
    "metadata": {
        "version": 1,
        "reason": "OK",
        "result": "1",
        "command": "background_mysql_upgrade_status"
    }
}

However I will recommend you to keep the progress by making the output more pretty and track the status until it’s done.

whmapi1 background_mysql_upgrade_status upgrade_id=mysql_upgrade.20200202-172923 | grep -v log

This will print the most valuable part of the process which is the state. For the time the process is running the state will be inprogress and once it’s done it will be sucsess

If there are any errors it will finish with an failed state and then you can again check the status without using the grep command in order to see the exact issue with the update process.

Conclusion

This is pretty much it. The process might take up some time depending on the MySQL / MariaDB configuration and the size of the databases on the server the configuration and etc.

Other than that the process is really simply and it the worst case it should take 30-40 minutes for a busy server to complete (keep in mind that the process length will also depend form the version update as well, the 5.5 to 5.6 update will take more time than 5.6 to 5.7 for example)

Hope this helps!

If you have any questions or ideas please feel free to share them down in the answers/comments section.

Regards, Alex