How to Upgrade Your MySQL
Migrating from MySQL 5.5 to a newer version is a major upgrade that must be done with some consideration in mind. Returning to MySQL 5.5, in case anything goes wrong, is not possible, so a solid backup is essential before you can proceed with the upgrade. For better stability, we recommend a systematic upgrading approach. Here is the step-by-step procedure for upgrading your MySQL:
1 – Log into WHM, go to “SQL Services“, and click on “MySQL/MariaDB Upgrade“.
2 – Confirm your current MySQL version, select the new version you want to upgrade to, and then click “Next“. Upgrade one version at a time.
3 – On the next interface, check the boxes for each warning to acknowledge that you are aware of the potential consequences of the upgrade, then click “Continue“. It’s important to know that downgrading from MariaDB is not supported by cPanel & WHM or switching back to MySQL. It’s also not recommended to try doing so. This must be considered when upgrading from MySQL to MariaDB.
4 – Choose the upgrade type you wish to use:
Unattended Upgrade: (Recommended) — Automatically rebuilds Apache and PHP with the last saved defaults. It also automatically updates Ruby Gems and rebuilds Apache and PHP.
Partially-Interactive Upgrade: (On some cPanel & WHM versions only) — This option automatically updates Ruby Gems, but it does not automatically rebuild Apache and PHP, until you choose to do so.
Interactive Upgrade: (Advanced users only) — This option takes you through the upgrade process step by step. The process includes the Ruby Gems update, the MySQL or MariaDB upgrade, and the Apache and PHP rebuild.
Once you have selected the upgrade type, click “Continue” to start the upgrade process.
After the MySQL Upgrade
Once the upgrade is complete, we strongly suggest that you check your website databases just to be sure that things are working properly. Check the MySQL error log for any errors. The best way to check your websites for errors is by testing them in a browser. In some cases, you may need to update the user password for a specific database, due to an incompatibility issue. Updating the password is a quick fix.
If your error log is not customized, your configuration file is usually named $hostname.err in /var/lib/mysql/. If your hostname, for instance, is ‘host.example.com’, the file will be named host.example.com.err. To interact with this file, you could use commands such as cat and tail: tail /var/lib/mysql/`hostname`.err. The file should not be edited.
Once you confirm that all your sites are working properly, the migration is complete.
Source : https://www.globo.tech/learning-center/upgrading-from-mysql-5-5-using-whm-cpanel/