Comparison of mysql 8.0.26 to 8.0.39

• 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.
  

  MySQL

  [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)

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

  [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.
  

  MySQL

  [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!

  1 2 3 4 5 6 7 8 9 10 11 12 13 14

  [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 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.
  

  MySQL

  [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

  1 2 3 4 5 6 7 8 9

  [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: Make sure to Flush the buffer pool and dirty pages before stopping the MySQL server.
  

  MySQL

  [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)

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

  [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: Stop MySQL server using systemctl commands make sure disable mysql before update.
  

  MySQL

  [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!

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

  [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: Installing mysql available Latest Release mysql-community-server-8.0.39
  

  Shell

  [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!

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

  [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.

   

  Shell

  [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.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

  [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: Verifying the mysql Version in system and checking all the details.
  

  Shell

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

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

  MySQL

  [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

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

  [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.