Comparison of mysql versions 8.0.26 to 8.0.39 for Minor Version Upgrade

Learn how to perform a MySQL Minor Version Upgrade from 8.0.26 to 8.0.39 to apply security updates, improve performance, and ensure stable online MySQL database operations.

MySQL 8.0.26
- - improves audit logging to include statement digests.
  - Performance Enhancements: Various tweaks to boost overall performance.
  - Bug fixes: Fixed several problems and stability issues.

MySQL 8.0.39

- - Security updates: Improved security with patches for vulnerabilities discovered since 8.0.26.
  - Performance enhancements include improved indexing, query execution, and InnoDB storage engine performance.
  - New features: Improved JSON support, Enhanced replication capabilities, New system and status variables have been introduced to improve monitoring. Backup enhancements are made incrementally.A minor upgrade is required to install the new version using the package manager like yum or distribution type. Installing the new version package requires the MySQL service to be stopped.

Steps involved in Mysql Minor Version Upgrade are as follows:

Step 01: Checking mysql current version before upgrade.

[root@centosstream8 test_db-master]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 12
Server version: 8.0.26 Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> SELECT @@hostname AS host, @@version;
+-----------------------------------+-----------+
| host                              | @@version |
+-----------------------------------+-----------+
| centosstream8.linuxvmimages.local | 8.0.26    |
+-----------------------------------+-----------+
1 row in set (0.01 sec)

Step 02: Before upgrading mysql make sure to take backup of the mysql directory in our case I have taken backup of directory through percona xtrabackup utility with the compress option lz4.

[root@centosstream8 full_backup]# xtrabackup --backup --compress --compress-threads=4 --compress=lz4 --no-server-version-check --target-dir=/root/mysql_bkp_061024/full_backup -u root -p
2024-10-06T04:30:36.348215-04:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql
Enter password:
xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7)
241006 04:30:43  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
241006 04:30:43  version_check Connected to MySQL server
241006 04:30:43  version_check Executing a version check against the server...
………
………
………
………
2024-10-06T04:30:44.401501-04:00 0 [Note] [MY-011825] [Xtrabackup] using 
Transaction log of lsn (317222811) to (317222831) was copied.
2024-10-06T04:30:57.245036-04:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

Step 03: Looking for the available mysql versions to review a list of the packages that are available for our distribution using yum check-update. And yum list command to see installed and available packages in centos 08.

[root@centosstream8 full_backup]# yum repolist enabled | grep "mysql.*-community.*"
mysql-connectors-community MySQL Connectors Community
mysql-tools-community      MySQL Tools Community
mysql80-community          MySQL 8.0 Community Server

[root@centosstream8 full_backup]# yum list mysql-community-server*
Last metadata expiration check: 0:11:32 ago on Sun 06 Oct 2024 04:26:45 AM EDT.
Available Packages
mysql-community-server-debug.x86_64                                               8.0.39-1.el8                                               mysql80-community

Step 04: (MySQL Minor Version Upgrade) Make sure to Flush the buffer pool and dirty pages before stopping the MySQL server.

[root@centosstream8 full_backup]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 24
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show global status like 'Innodb_buffer_pool_dump_status';
+--------------------------------+------------------------------------+
| Variable_name                  | Value                              |
+--------------------------------+------------------------------------+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
+--------------------------------+------------------------------------+
1 row in set (0.57 sec)

mysql> set global innodb_buffer_pool_dump_at_shutdown=OFF, innodb_buffer_pool_dump_now=ON;
Query OK, 0 rows affected (0.03 sec)

mysql> show global status like 'Innodb_buffer_pool_dump_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Buffer pool(s) dump completed at 241006  5:23:01 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.01 sec)

mysql> set global innodb_max_dirty_pages_pct=0;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> show global status like 'Innodb_buffer%dirty';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_dirty | 0     |
| Innodb_buffer_pool_bytes_dirty | 0     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

Step 05: (MySQL Minor Version Upgrade) Stop MySQL server using systemctl commands make sure disable mysql before update.

[root@centosstream8 full_backup]# systemctl stop mysqld
[root@centosstream8 full_backup]# systemctl status mysqld
● mysqld.service - MySQL 8.0 database server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Sun 2024-10-06 05:24:32 EDT; 13s ago
  Process: 80657 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS)
  Process: 46561 ExecStart=/usr/libexec/mysqld --basedir=/usr (code=exited, status=0/SUCCESS)
 Main PID: 46561 (code=exited, status=0/SUCCESS)
   Status: "Server shutdown complete"

Oct 06 03:47:59 centosstream8.linuxvmimages.local systemd[1]: Starting MySQL 8.0 database server...
Oct 06 03:48:00 centosstream8.linuxvmimages.local mysql-prepare-db-dir[46413]: Initializing MySQL database
Oct 06 03:48:15 centosstream8.linuxvmimages.local systemd[1]: Started MySQL 8.0 database server.
Oct 06 05:24:20 centosstream8.linuxvmimages.local systemd[1]: Stopping MySQL 8.0 database server...
Oct 06 05:24:32 centosstream8.linuxvmimages.local systemd[1]: mysqld.service: Succeeded.
Oct 06 05:24:32 centosstream8.linuxvmimages.local systemd[1]: Stopped MySQL 8.0 database server.

[root@centosstream8 full_backup]# sudo yum module -y disable mysql
Last metadata expiration check: 0:08:36 ago on Sun 06 Oct 2024 05:39:52 AM EDT.
Dependencies resolved.
==============================================================================================================================================================
 Package                               Architecture                         Version                               Repository                             Size
==============================================================================================================================================================
Disabling modules:
 mysql

Transaction Summary
==============================================================================================================================================================

Complete!

Step 06: (MySQL Minor Version Upgrade) Installing mysql available Latest Release mysql-community-server-8.0.39

[root@centosstream8 full_backup]# sudo yum install mysql-community-server-8.0.39 -y
Last metadata expiration check: 0:09:01 ago on Sun 06 Oct 2024 05:39:52 AM EDT.
Dependencies resolved.
==============================================================================================================================================================
 Package                                            Architecture               Version                            Repository                             Size
==============================================================================================================================================================
Installing:
 mysql-community-server                             x86_64                     8.0.39-1.el8                       mysql80-community                      65 M
…………………………………………………………….
…………………………………………………………….
Installed:
  mysql-community-client-8.0.39-1.el8.x86_64             mysql-community-client-plugins-8.0.39-1.el8.x86_64     mysql-community-common-8.0.39-1.el8.x86_64
  mysql-community-icu-data-files-8.0.39-1.el8.x86_64     mysql-community-libs-8.0.39-1.el8.x86_64               mysql-community-server-8.0.39-1.el8.x86_64

Complete!

Step 07: Starting mysql service and enabling mysql after the completion of Installation process and verifying the process using systemctl.

[root@centosstream8 full_backup]# systemctl start mysqld
[root@centosstream8 full_backup]# sudo yum module -y enable mysql
Last metadata expiration check: 0:25:20 ago on Sun 06 Oct 2024 05:39:52 AM EDT.
Dependencies resolved.
==============================================================================================================================================================
 Package                               Architecture                         Version                               Repository                             Size
==============================================================================================================================================================
Enabling module streams:
 mysql                                                                      8.0

Transaction Summary
==============================================================================================================================================================

Complete!
[root@centosstream8 full_backup]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: active (running) since Sun 2024-10-06 06:01:41 EDT; 3min 54s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 94081 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 94186 (mysqld)
   Status: "Server is operational"
    Tasks: 37 (limit: 10936)
   Memory: 551.8M
   CGroup: /system.slice/mysqld.service
           └─94186 /usr/sbin/mysqld

Oct 06 06:00:40 centosstream8.linuxvmimages.local systemd[1]: Starting MySQL Server...
Oct 06 06:01:41 centosstream8.linuxvmimages.local systemd[1]: Started MySQL Server.

Step 08: (MySQL Minor Version Upgrade) Verifying the mysql Version in system and checking all the details.

[root@centosstream8 full_backup]# mysql --version
mysql  Ver 8.0.39 for Linux on x86_64 (MySQL Community Server - GPL)

[root@centosstream8 full_backup]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 10
Server version: 8.0.39 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> s
--------------
mysql  Ver 8.0.39 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          10
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.39 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/lib/mysql/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 5 min 37 sec

Threads: 2  Questions: 11  Slow queries: 0  Opens: 588  Flush tables: 4  Open tables: 35  Queries per second avg: 0.032

Minor Version Upgrade of mysql is completed to latest from 8.0.26 to 8.0.39

Conclusion:

- In conclusion, upgrading from MySQL 8.0.26 to MySQL 8.0.39 is a safe and recommended minor version upgrade that brings clear benefits without major complexity. MySQL 8.0.39 strengthens database administration security and upgrade with the latest patches, improves overall performance through better indexing, query execution, and InnoDB enhancements, and adds useful features such as improved JSON support and enhanced replication. Since minor upgrades can be performed using standard package managers with minimal downtime, moving to MySQL 8.0.39 helps ensure a more secure, stable, and efficient MySQL database administration environment while staying aligned with best practices.

See also our blog on the MySQL Major Version Upgrade: Enhance Performance – Upgrade MySQL 8.0.40 to 8.4.4 on CentOS 8

MySQL Minor Version upgrade from 8.0.26 to 8.0.39