Upgrading MySQL in WHM

Keeping your database server software up to date is crucial for security, performance, and access to the latest features. cPanel & WHM makes it straightforward to upgrade MariaDB or MySQL. However, before you begin, it’s essential to take certain precautions to ensure a smooth process and prevent data loss.

This article will guide you through the recommended precautions and the upgrade process itself.

Important precautions before upgrading

Before initiating the MariaDB or MySQL upgrade via WHM, please carefully review and perform the following steps. Crucially, understand that downgrading MariaDB or MySQL is not supported after an upgrade.

1. Create comprehensive backups

Backups are your safety net. If anything unexpected occurs during the upgrade, having reliable backups will allow you to restore your databases.

The easiest way to create backups is via SSH.

• Raw datadir backup: Create a full copy of your MySQL data directory. The default location is typically /var/lib/mysql. You can copy this directory to a separate backup location. Ensure the partition where you store this backup has sufficient free space. To copy the directory, you can use a command like:
  
  cp -Rp /var/lib/mysql /path/to/your/backup_location/mysql_datadir_backup_YYYYMMDD
  
  Replace /path/to/your/backup_location/ with your actual backup path and YYYYMMDD with the current date. It’s best to stop the MySQL service before copying these files to ensure data consistency.
• Individual .sql database dumps: It’s also highly recommended to create SQL dump files (.sql) for each database. These text-based backups can be easier to restore individually if needed. You can use the mysqldump utility for this. To back up a single database:
  
  mysqldump databasename > /path/to/your/backup_location/databasename_backup_YYYYMMDD.sql
  
  To back up all databases, you can use:
  
  mysqldump --all-databases > /path/to/your/backup_location/all_databases_backup_YYYYMMDD.sql
  
  Again, ensure your backup destination has enough free disk space. SQL dumps can be quite large, especially for numerous or large databases.

2. Check for and repair crashed tables

Crashed or corrupted tables can cause issues during the upgrade process. It’s vital to check and repair any problematic tables beforehand.

You can use the mysqlcheck utility to check all databases and tables:

mysqlcheck -Aao --auto-repair

This command will analyze (-a), check all databases (-A), optimize (-o), and attempt to auto-repair (--auto-repair) any issues found. Review the output carefully and address any errors that could not be automatically repaired.

3. Review your MySQL/MariaDB configuration file (my.cnf)

Newer versions of MariaDB or MySQL may deprecate or change certain configuration variables. Before upgrading, review your current MySQL configuration file (usually located at /etc/my.cnf). Check the official MariaDB or MySQL documentation for the version you are upgrading to, specifically looking for release notes or upgrade guides that list deprecated variables. If you find any such variables in your my.cnf, you should update or remove them to prevent startup issues after the upgrade.

Performing the upgrade in WHM

Once you have completed all the precautionary steps, you can proceed with the upgrade using WHM’s interface.

1. Log in to WHM as the ‘root’ user or a reseller with equivalent privileges.
2. In the search bar, type “MySQL” or “MariaDB” and navigate to MySQL/MariaDB Upgrade (the exact wording may vary slightly depending on your WHM version).
3. You will see a list of available MariaDB or MySQL versions. Select the version you wish to upgrade to. cPanel usually indicates the recommended stable versions.
4. Read any warnings or notes provided by WHM carefully.
5. Choose your upgrade type. An “Unattended Upgrade” will proceed without further prompts. A “Interactive Upgrade” will guide you through a few steps. For most users, the unattended option is suitable after taking the precautions above.
6. Click the Continue or Start Upgrade button.
7. The upgrade process will begin. WHM will handle stopping the database service, performing the upgrade, and restarting the service. You can monitor the progress from the WHM interface.

After the upgrade is complete, it’s a good practice to re-check your websites and applications to ensure they are functioning correctly with the new database version. Also, monitor the MySQL error logs (usually found at /var/lib/mysql/hostname.err) for any unusual messages.