This guide will walk you through the steps to upgrade MySQL or MariaDB on your Liquid Web Core Managed server. Performing an upgrade can provide access to new features, performance improvements, and security enhancements.

Important: Before proceeding with an upgrade, we strongly recommend you take a full server backup or ensure you have recent, restorable backups.

Pre-Flight Check

Before starting the upgrade process, it’s crucial to perform some preliminary checks. Completing these pre-flight steps can help prevent common issues during the upgrade.

1. Gathering information

The first step is to collect important details about your current MySQL/MariaDB setup. You can do this by running the following command in your server’s terminal:

echo;echo "Current MySQL Version: ";mysql -V | grep -Po "\d+.\d+.\d+";echo;echo "Deprecated MySQL Variables: ";egrep -wi 'table_cache|log_slow_queries|key_buffer|innodb_additional_mem_pool_size|safe_show_database|skip_locking|innodb_buffer_pool_instances|thread_cache|skip_symlink|secure_file_priv|master-*|log-slow-queries|innodb_additional_mem_pool_size|innodb_use_sys_malloc|storage_engine|create_old_temporals|ignore_db_dir|default-authentication-plugin|thread_concurrency|timed_mutexes|log-slow-admin-statements|log-slow-slave-statements' /etc/my.cnf;echo;echo "MySQL Datadir: ";mysql -e "show variables;" |grep datadir;echo;echo "Users with Old Style Passwords: "; if [[ $(mysql -e "SELECT Host, User, Password AS Hash FROM mysql.user WHERE Password REGEXP '^[0-9a-fA-F]{16}' ORDER BY User, Host;"|wc -l) > 0 ]];then echo "Yes";else echo "No";fi;echo

This command checks for:

Your current MySQL/MariaDB version.
Any deprecated variables in your MySQL configuration file (/etc/my.cnf). These are settings that might not be supported in newer versions.
The MySQL data directory (where your database files are stored).
Whether you have any database users with old-style passwords, which may need updating.

Example Output:

Current MySQL Version: 
10.1.12 

Deprecated MySQL Variables: 
key_buffer = 128m

MySQL Datadir: 
datadir /var/lib/mysql/ 

Users with Old Style Passwords: 
No

What to do with this information:

Note down your current MySQL version for reference.
If Deprecated MySQL Variables are listed, you may need to update your /etc/my.cnf file after the upgrade. These variables might be removed or renamed in newer MySQL/MariaDB versions. For guidance on specific variables, consult the official MySQL or MariaDB documentation for the version you are upgrading to.
Make a note of your MySQL Datadir. The typical location is /var/lib/mysql/.
If “Users with Old Style Passwords” shows “Yes,” these passwords will need to be updated for compatibility and security.

2. Checking your databases

Next, check the health of your existing databases using the following command:

mysqlcheck -Asc

This command checks all your databases for errors and performs a basic analysis.

You might see messages like “xx users didn’t properly close the table” or “this table doesn’t support repair.” These are generally safe to ignore.
If you see corruption messages related to performance_schema or information_schema, don’t worry. These system databases are typically rebuilt during the upgrade process.
Pay close attention to messages like “error : Corrupt”.
- If these errors are for MyISAM tables, you can often repair them using: mysqlcheck -r databasename tablename. Replace databasename and tablename with the actual names.
- If a table is corrupt and cannot be easily repaired, you might need to restore it from a backup. It’s important to resolve corruption issues before proceeding with the upgrade.

3. Checking disk space

It’s vital to ensure you have enough free disk space for the upgrade process, including space for backups. Running out of disk space during an upgrade can lead to data corruption.

Check your current disk usage with:

df -h

This command shows the available and used space on your server’s partitions.

As a general guideline:

If your primary partition (usually / or /var) is more than 60% full, check how much space MySQL is using: Bashdu -sh /var/lib/mysql/ (If your datadir from step 1 was different, use that path instead of /var/lib/mysql/) Example Result: 299M /var/lib/mysql/ Ensure that the total space needed for MySQL data, plus backups (which can be similar in size to your current data), will not exceed the available free space.
If the total MySQL database usage is more than half of the free space left on the partition:
- We strongly advise you to free up disk space before proceeding. Low free space increases the risk of upgrade complications.
If total disk usage on the relevant partition is over 90%:
- Do not proceed with the upgrade. Attempting an upgrade in this scenario has a high risk of data corruption, data loss, or complete upgrade failure.
- Work to reduce disk usage to 80% or less. If you need assistance, please contact our support team.

4. Identifying your MySQL/MariaDB service command

The command to manage the MySQL/MariaDB service can vary depending on your server’s operating system and MySQL/MariaDB version. You’ll need to find the correct one for stopping and starting the service during the backup and upgrade process.

Run the following commands one by one. The one that shows service information (including “active (running)” or similar) is the one you’ll use.

systemctl status mysql
systemctl status mysqld
systemctl status mariadb

For older systems (like CentOS 6) that don’t use systemctl, try these:

service mysql status
service mysqld status
service mariadb status

Example Output (for systemctl status mariadb):

[root@host ~]# systemctl status mariadb
Redirecting to /bin/systemctl status mariadb.service
● mariadb.service - MariaDB 10.6.12 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: active (running) since Thu 2023-03-16 17:55:16 EDT; 3 days ago

If multiple commands show output, typically the last one that successfully reports the service status is the correct one (e.g., if mysql and mariadb both show something, but mariadb is the actual running service, use mariadb). Make a note of the correct service name (mysql, mysqld, or mariadb).

5. Backing up MySQL/MariaDB data files

Before making any significant changes, it’s crucial to back up your MySQL/MariaDB data directory. This backup allows you to restore your databases if something goes wrong during the upgrade.

Create a backup directory:

mkdir -p /home/temp/mysql_datadir_backup

Now, use the appropriate command block below based on the service name you identified in the previous step and your server’s OS version. Replace your_backup_log_identifier with something unique, like pre_upgrade_backup_YYYYMMDD.

Important: The commands below will temporarily stop your MySQL/MariaDB service, making your databases unavailable. Plan for this brief downtime.

For mysql service (CentOS 7 and newer):

systemctl stop mysql
rsync -avHl --info=progress2 --no-inc-recursive --log-file=/home/temp/mysql_datadir_backup/mysql-upgrade-your_backup_log_identifier.txt /var/lib/mysql/ /home/temp/mysql_datadir_backup/
systemctl start mysql

For mysql service (CentOS 6 and older): (Note: --info=progress2 and --no-inc-recursive flags are not available)

service mysql stop
rsync -avHl --log-file=/home/temp/mysql_datadir_backup/mysql-upgrade-your_backup_log_identifier.txt /var/lib/mysql/ /home/temp/mysql_datadir_backup/
service mysql start

For mysqld service (CentOS 7 and newer):

systemctl stop mysqld
rsync -avHl --info=progress2 --no-inc-recursive --log-file=/home/temp/mysql_datadir_backup/mysql-upgrade-your_backup_log_identifier.txt /var/lib/mysql/ /home/temp/mysql_datadir_backup/
systemctl start mysqld

For mysqld service (CentOS 6 and older):

service mysqld stop
rsync -avHl --log-file=/home/temp/mysql_datadir_backup/mysql-upgrade-your_backup_log_identifier.txt /var/lib/mysql/ /home/temp/mysql_datadir_backup/
service mysqld start

For mariadb service (CentOS 7 and newer):

systemctl stop mariadb
rsync -avHl --info=progress2 --no-inc-recursive --log-file=/home/temp/mysql_datadir_backup/mysql-upgrade-your_backup_log_identifier.txt /var/lib/mysql/ /home/temp/mysql_datadir_backup/
systemctl start mariadb

For mariadb service (CentOS 6 and older):

service mariadb stop
rsync -avHl --log-file=/home/temp/mysql_datadir_backup/mysql-upgrade-your_backup_log_identifier.txt /var/lib/mysql/ /home/temp/mysql_datadir_backup/
service mariadb start

Notes:

If your MySQL data directory (found in Step 1) is not /var/lib/mysql/, replace /var/lib/mysql/ in the rsync command with your actual data directory path.
The rsync command copies all files from your MySQL data directory to the /home/temp/mysql_datadir_backup/ folder.
A log file for the backup process will be saved in the backup directory.

6. Dumping all databases (SQL backups)

In addition to the file-based backup, creating SQL dumps of all your databases provides another layer of safety and can be useful for individual database restores.

Create a directory for SQL dumps: First, check if /home/temp exists:

stat /home/temp

If it reports “No such file or directory”, create it:

mkdir /home/temp

Then create the backup directory:

mkdir /home/temp/db_sql_backup
cd /home/temp/db_sql_backup

Dump all databases:

echo "SHOW DATABASES;" | mysql -Bs | while read i ; do echo "Dumping database: $i" ; mysqldump --single-transaction "$i" | gzip -c > "$i.sql.gz" ; done

This command lists all databases and then, one by one, dumps each database into a compressed SQL file (e.g., databasename.sql.gz). The --single-transaction flag helps ensure consistency for InnoDB tables during the dump.

Verify the database dumps: Run this command to check if all databases were dumped successfully:

error='0';count='';for f in $(/bin/ls *.sql.gz); do if [[ ! $(zegrep 'Dump completed on [0-9]{4}-([0-9]{2}-?){2}' ${f}) ]]; then echo "Possible error in dump file: <span class="math-inline">\{f\}"; error\=</span>((error+1)); fi ; count=$((count+1)); done; (echo "Error Count: ${error}"; echo "Total DB Dumps: ${count}"; echo "Total DBs on Server: $(mysql -e 'SELECT COUNT(*) FROM information_schema.SCHEMATA;'|tail -n1)";)|column -t

Expected Output:

Error Count:          0
Total DB Dumps:       7
Total DBs on Server:  7

If the “Error Count” is greater than zero, or if the “Total DB Dumps” count does not match the “Total DBs on Server” count, it means one or more databases may not have dumped correctly.
Scroll up in your terminal output to see which database dump might have failed and look for any error messages.
Common reasons for dump failures include table corruption (which should have been addressed in Step 2) or specific database features that interfere with mysqldump.
If a database failed to dump and you’ve addressed any underlying corruption, you can try dumping it individually: mysqldump --single-transaction databasename | gzip -c > databasename.sql.gz (Replace databasename with the actual database name.) Then, re-run the verification command. Ensure all databases are successfully dumped before proceeding.

Upgrading MySQL/MariaDB

Now you’re ready to perform the actual upgrade.

Optional Pre-Upgrade Check: Before you start, it’s a good idea to open a few key pages of your websites in a browser. Note any pages that are already broken. This helps you distinguish between pre-existing issues and potential problems caused by the upgrade.

The following steps are a general guideline for upgrading MariaDB using yum (common on CentOS/AlmaLinux/Rocky Linux). If you are upgrading MySQL or using a different OS/package manager, the specific commands may vary. Always refer to the official documentation for the MySQL/MariaDB version you are upgrading to.

Example: Upgrading to MariaDB 10.6 (if you were on an older version like 10.3)

Add the new MariaDB repository: This command adds the software source for the MariaDB version you want to install. Adjust the --mariadb-server-version parameter to your target version (e.g., mariadb-10.5, mariadb-10.11, etc.). For specific repository URLs for MySQL, consult the official MySQL documentation. curl -sSL [https://downloads.mariadb.com/MariaDB/mariadb_repo_setup](https://downloads.mariadb.com/MariaDB/mariadb_repo_setup) | sudo bash -s -- --mariadb-server-version=mariadb-10.6
Stop the current MariaDB/MySQL service: Use the service name you identified in Step 4 (e.g., mariadb, mysqld, mysql). systemctl stop mariadb
Remove the old MariaDB/MySQL package(s): This command removes the currently installed server package. Pay attention to the output to verify it’s removing the version you expect. Sometimes, related packages like mariadb-common, mariadb-libs might also need to be removed, or they might be handled by the update process. Proceed with caution and double-check package names. yum remove mariadb-server MariaDB-server MariaDB-client MariaDB-shared MariaDB-backup MariaDB-common (Note: The exact package names can vary. yum list installed | grep -i maria or yum list installed | grep -i mysql can help identify them. Removing MariaDB-server or mysql-community-server is usually the primary one.)
Install the new MariaDB/MySQL package(s): This installs the new version from the repository you added. Verify from the yum output that it’s installing your target version. yum install mariadb-server mariadb-client mariadb-shared mariadb-backup mariadb-common (Again, package names for MySQL will differ, e.g., mysql-community-server)
Enable and start the new MariaDB/MySQL service: systemctl enable mariadb systemctl start mariadb
Upgrade system tables and data: This crucial step updates the internal MySQL/MariaDB tables and checks your user tables for compatibility with the new version. mysql_upgrade If mysql_upgrade prompts for a root password and you have one set, you’ll need to provide it: mysql_upgrade -u root -p
(Optional but Recommended) Update other packages that depend on MySQL/MariaDB libraries: yum update
Restart the MariaDB/MySQL service: A final restart ensures all changes are applied. systemctl restart mariadb
(Optional) Disable the old repository: If you had a repository configured for your previous MariaDB/MySQL version (e.g., mariadb-10.3), you might want to disable it to prevent accidental downgrades or conflicts. The name will depend on how it was originally added. Example: yum-config-manager --disable mariadb-10.3 You can list your repos with yum repolist.

Your MySQL/MariaDB upgrade should now be complete!

Follow-Up

After the upgrade process finishes:

Check Your Websites: Go back to the website pages you opened before the upgrade. Refresh them and check for any new database connection errors or other issues.
Review Logs: If you encounter problems, check the MySQL/MariaDB error log (usually located at /var/log/mysqld.log, /var/log/mysql/error.log, or /var/lib/mysql/hostname.err) for specific error messages.
Troubleshooting:
- If MySQL/MariaDB fails to start, the error log is the first place to look.
- If websites show errors, try to identify if it’s a widespread issue or specific to certain database queries or features that might have changed behavior in the new version.
- If you encounter issues that you cannot resolve, you may need to restore from the backups you created.

If you run into any difficulties or have questions during the upgrade, please don’t hesitate to reach out to our Support Team for assistance.

Was this article helpful?

Thank you for your input.

Thank you for your feedback.