Best Practices for Updating MySQL from 8.0 to 8.4

Image Source: unsplash

You should use best practices to keep your data safe when performing a MySQL Update from 8.0 to 8.4. Always look at the official MySQL manual for upgrade steps and version needs. Some things will change, like the default_authentication_plugin setting being removed. The mysql_native_password will also be turned off, which can change how you log in. New reserved words and stricter foreign key rules might cause errors if your database uses them. MySQL 8.4 removes old features fast, so run checks before you upgrade and make backups to stop problems.

Tip: If you drop spatial indexes before the MySQL Update, you can avoid failures and make the process easier.

Prepare

Image Source: unsplash

Check Docs

Start by reading the official MySQL documentation. You need to know what versions you can upgrade from. Oracle says you must check the lowest version allowed for a direct upgrade. For MySQL 8.4, you need at least MySQL 8.0.21. If you have an older version, upgrade to 8.0.21 first.

Here is a table that shows some common upgrade paths and ways to upgrade:

Tip: Always look at the official manual for the newest upgrade steps and rules.

Confirm Version

Check which MySQL version you have now. You can use this command in your MySQL shell:

1

SELECT VERSION();

If your version is lower than 8.0.21, upgrade to at least 8.0.21 before you start the MySQL Update to 8.4. This step helps you avoid problems and keeps your data safe.

Review Changes

Look at all the changes and features that are gone in MySQL 8.4. You should check for removed settings, new reserved words, and old functions that are not there anymore. Some changes might break your apps or scripts.

• The default_authentication_plugin setting is gone. Now MySQL uses caching_sha2_password by default.

• The mysql_native_password plugin is turned off. Check your users and change how they log in if needed.

• New reserved words are MANUAL, PARALLEL, QUALIFY, and TABLESAMPLE.

• The PASSWORD() function is gone. Use CREATE USER or ALTER USER instead.

• The tx_isolation name is gone. Use transaction_isolation.

• The expire_logs_days setting is now called binlog_expire_logs_seconds.

• The FLUSH HOSTS command will be removed. Take it out of your scripts.

• The FLOAT(M,D) and DOUBLE(M,D) types are not allowed. Use FLOAT or DOUBLE.

Note: Make a list of these changes. Check your database, users, and scripts to make sure they will work before you upgrade.

Backup & Precheck

Image Source: unsplash

Full Backup

You must make a backup before you update MySQL. There are different backup types you can use. Each type has good and bad points. The table below shows the backup types and what they do:

Keep at least one full backup in a safe spot. Automated backups help stop mistakes.

Test Restore

After you make a backup, you should test it. Testing helps you know you can get your data back. Do these steps:

• Restore your backup on another server.

• Start MySQL with the restored data.

• Run SHOW DATABASES; and SHOW TABLES; to check your data and tables.

Testing your backup helps you feel ready for the upgrade.

Run Upgrade Checker

Use the Upgrade Checker Utility before you update MySQL. This tool looks for problems that can stop the upgrade. Some common problems are:

• Tables with old data types or old functions.

• Problems with reserved words.

• System tables with names that are now used by MySQL.

• Table partitions in shared InnoDB tablespaces.

• Foreign key names that are too long.

Fix these problems before you start. The Upgrade Checker helps you avoid errors and makes the upgrade easier.

MySQL Update Steps

Drop Spatial Indexes

You need to drop all spatial indexes before you start the MySQL Update. This step helps stop upgrade problems. MySQL 8.4 has new rules for spatial indexes. You must be careful with them. Here is what you should do:

1. Look at each table that has a spatial index.

2. Check that each indexed column uses just one SRID.

3. Write down the SRID for every index. You will need this later.

4. Use this command to drop each spatial index:

   1 2	ALTER TABLE your_table_name DROP INDEX your_spatial_index_name;

5. Do this for every spatial index in your database.

Tip: Keep a list of the indexes you dropped. This will help you add them back after the MySQL Update.

Perform Upgrade

Now you can start the MySQL Update. Always test the upgrade in a safe place first. This helps you find problems early and keeps your data safe.

Step-by-step instructions:

1. Make a test environment like your main server.

2. Run the Upgrade Checker Utility to look for problems. This tool checks for old temporal types, reserved word issues, and removed system variables. Here is a table with some checks it does:

   Check Type	Description
   Use of old temporal types	Finds deprecated temporal types in your database.
   Conflicts with reserved words	Looks for objects that use new reserved keywords.
   Removed system variables	Detects system variables that are no longer supported.
   Total Checks	The utility performs about 21 checks in total.

3. Read the report from the Upgrade Checker Utility. Fix any errors or warnings before you go on.

4. Use tools like pt-upgrade from the Percona Toolkit to help with the upgrade.

5. Start the MySQL Update on your test server. Watch for errors.

6. Check important logs during the upgrade. You should look at these logs:

   1. General Query Log – shows all SQL statements from clients.

   2. Slow Query Log – shows slow queries that may cause problems.

   3. Error Log – records big errors and server events.

   4. Binary Log – tracks changes for replication and recovery.

   5. Relay Log – stores events for replica servers.

Note: If you see errors in the logs, stop and fix them before you update your main server.

7. After testing, do the upgrade steps on your main server. Always use the same process to avoid mistakes and reduce downtime.

Recreate Indexes

After the MySQL Update, you need to add back the spatial indexes you dropped. MySQL 8.4 needs you to give the SRID when you make a spatial index. Do these steps:

1. For each table, check the SRID you wrote down before.

2. Use this command to add the spatial index back:

   1 2	ALTER TABLE your_table_name ADD SPATIAL INDEX your_spatial_index_name (your_column_name) /*!80000 SRID your_srid_number */;

   Change your_table_name, your_spatial_index_name, your_column_name, and your_srid_number to your real values.

3. Make sure each spatial index uses only one SRID.

4. Test your app to make sure the indexes work right.

Tip: Always check that your data and indexes work after the MySQL Update. This helps you find problems early.

Post-Upgrade Tasks

Validate Data

When you finish the upgrade, check your data. Make sure your data is safe and correct. Look at your tables and records for problems. Follow these steps to check your data:

• Look at your tables for missing records.

• Check for extra records in your tables.

• Make sure data types are what you expect.

• See if values fit the right range or format.

• Fix any null values with the right rules.

• Remove records that are the same to keep data clean.

• Use transactions when you change related data.

• This keeps everything consistent.

• Set up error handling and logging to find issues fast.

• Make a backup again to protect your changes.

• Teach your team how to manage data well.

Tip: Checking your data often helps you find problems early. This keeps your database healthy.

Update Configs

After the upgrade, look at your MySQL settings. Some settings are new or changed. Use the table below to see old and new defaults:

Check your my.cnf or my.ini file. Change these settings if you need to. Also remember:

• Drop and add spatial indexes if you have not done this.

• Use WAIT_FOR_EXECUTED_GTID_SET() instead of WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS().

• If you use FIDO plugins, check if they still work.

• Do not set server variables to SQL NULL on the command line.

Note: Always restart MySQL after you change settings. This makes new settings work.

Troubleshoot

You might see warnings after the upgrade. Look at the MySQL error log for messages. These messages can be about old features or changed settings. Common problems are:

• Warnings about using single dollar signs in names.

• Notices about the SET_USER_ID privilege being gone for users like 'root'@'localhost'.

• You may see about 20 warnings and 1 notice after this upgrade.

If you find a problem:

1. Read the warning or error message.

2. Look in the MySQL docs for help.

3. Fix the problem by changing your settings or scripts.

4. Test your changes to make sure the problem is fixed.

🛠️ Watch for new warnings after the upgrade. Fixing them now stops bigger problems later.

You can make your MySQL upgrade go well if you follow good steps. Get your system ready, check for problems, and save backups before you begin. After the upgrade, check your data and change your settings if needed. Watch your system often so you can find problems early.

• Some good things about upgrading are:

  • Your database works better

  • Security and speed get stronger

  • It works well with new tech

Keep learning by reading MySQL guides, joining online classes, and using tools like Percona Monitoring and Management or Prometheus.

Recommended reading

• SQL Optimization Course
• SQL optimization knowledge
• Database industry knowledge

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.

How to use SQLFlash in a database?

• SQLFlash + MySQL

• SQLFlash + PostgreSQL