Upgrading MySQL or MariaDB on a VPS Without Breaking Your Site

You ran the upgrade, MySQL came back up fine, and then your WordPress site threw a white screen or your app started spitting SQL syntax errors it never had before. This is one of the most common ways a routine VPS maintenance task turns into a Saturday-night incident — and almost always because the upgrade itself went fine, but nothing was checked against it first.

Symptom: What Actually Breaks

A MySQL or MariaDB major-version upgrade rarely fails loudly. The service starts, systemctl status mysql shows green, and then twenty minutes later you're looking at one of these:

• WordPress shows "Error establishing a database connection" even though the credentials haven't changed.
• A plugin or custom query throws Expression #1 of SELECT list is not in GROUP BY clause — a query that worked yesterday.
• Your app can connect from PHP but a script using an older MySQL client library can't authenticate at all.
• Replication between a primary and replica VPS stops, with the replica's error log showing an unknown event format.
• Scheduled dumps or cron jobs using mysqldump fail with a version-mismatch warning.

Cause: Why a "Simple" Upgrade Breaks Things

MySQL and MariaDB upgrades aren't just a version bump — several defaults change between major versions, and your application was quietly relying on the old ones:

• Authentication plugin changes. MySQL 8 defaults to caching_sha2_password instead of mysql_native_password. Older PHP mysqlnd or client libraries that predate this can't complete the handshake.
• Stricter SQL modes. ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES are on by default in newer versions. Queries that used to silently pick an arbitrary row from a GROUP BY now get rejected outright.
• Removed or renamed features. Things like the old utf8 alias, certain deprecated functions, or old-style partitioning syntax get dropped between majors.
• Config file incompatibilities. A directive in my.cnf that was valid in 5.7 can cause the new version to refuse to start, or worse, start while silently ignoring it.
• Skipped version jumps. Going straight from MySQL 5.7 to 8.0, or MariaDB 10.3 to 10.11, without running the upgrade in between isn't officially supported and can leave system tables in a half-migrated state.

None of this means the upgrade itself is risky — it means an unplanned one is.

Fix: A Checklist That Actually Prevents Downtime

1. Back up before you do anything else

Take a VPS snapshot if your provider offers one — on Getwebup VPS plans this is a one-click restore point. Then take a logical backup too, since a snapshot alone won't help if you need to restore just the database on a live server:

mysqldump --all-databases --single-transaction --routines --triggers -u root -p > /root/pre-upgrade-backup.sql

Verify the file isn't empty and check its size against what you'd expect before moving on.

2. Know exactly what you're running and what you're jumping to

mysql --version
cat /etc/mysql/my.cnf
apt list --installed | grep -i -E 'mysql|mariadb'

Check the official upgrade notes for your target version — both MySQL and MariaDB publish a "changes since previous version" page. Skim it for anything your app touches: strict SQL modes, removed syntax, or config directives that were deprecated.

3. Test on a clone first, not on production

If you took a VPS snapshot, spin up a temporary instance from it (or clone the droplet/VM) and run the upgrade there first. This is the single biggest difference between a boring upgrade and a 2 a.m. incident — you find the broken query or the auth plugin issue on a machine nobody's using.

4. Run the actual upgrade

The upgrade command patches system tables and privilege structures — skipping it is exactly how you end up with a server that starts but behaves inconsistently.

5. Check the error log before you touch anything else

sudo tail -100 /var/log/mysql/error.log

If it's clean, connect and confirm the version and a few real queries actually run:

mysql -u root -p -e "SELECT VERSION(); SHOW VARIABLES LIKE 'sql_mode';"

6. If a query breaks on strict mode

Don't just disable ONLY_FULL_GROUP_BY globally as a permanent fix — that hides bugs instead of fixing them. Fix the query to explicitly name the columns it's grouping by, or wrap the offending column in ANY_VALUE() if the ambiguity is genuinely harmless. Reserve a temporary sql_mode override in my.cnf only as a stopgap while you patch the actual query.

7. If authentication breaks after upgrading to MySQL 8

Switch the affected user back to the legacy plugin until every client library is updated:

ALTER USER 'wpuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'the_existing_password';
FLUSH PRIVILEGES;

If It Goes Wrong Mid-Upgrade

Downgrading MySQL or MariaDB in place isn't supported — system tables get migrated forward and there's no clean path back. If the upgrade leaves the server in a bad state, restore the VPS snapshot you took in step 1 rather than trying to fix it live. That's the whole reason the snapshot comes before anything else on this list.

Prevention: Make the Next One Boring

• Keep a staging copy of the site on a second small VPS or a snapshot clone, and run every database upgrade there first as a matter of routine.
• Pin the package version with apt-mark hold mysql-server (or the MariaDB equivalent) so a routine apt upgrade can't silently jump you to a new major version.
• Read the release notes for deprecated features before upgrading, not after something breaks.
• Never skip more than one major version at a time — upgrade sequentially and run mysql_upgrade at each step.
• Keep the pre-upgrade mysqldump and VPS snapshot until you've confirmed the site is stable for at least a few days.