MySQL major version upgrade from 5.7 to 8.0.30


MySQL major version upgrade from 5.7 to 8.0.30

In the previous blog, we saw MySQL minor version upgrade from 8.0.22 to 8.0.30  Click here to read more In this blog, we are going to perform Major Version upgrade from 5.7 to 8.0.30 using Yum Repository method.


MySQL 8.0 have many important performance and security improvements and also lots of mysql parameters and features are deprecated and removed so spend some time in testing to avoid any issues after migrating to MySQL 8. And the end of support for MySQL 5.7 is 2023. Please refer to blog “Moving from MySQL 5.7 to MySQL 8.0 – What You Should Know” from Severalnines for more information on MySql 8 new features and deprecated parameters.

 

Please note downgrade from MySQL 8.0 to MySQL 5.7, or from a MySQL 8.0 release to a previous MySQL 8.0 release, is not supported. The only supported alternative is to restore a backup taken before upgrading. It is therefore imperative that you backup your data before starting the upgrade process.


Follow the below steps to perform a major version upgrade

1. Take the Full backup of the MySQL server
2. Check the current MySQL version
3. Check for the latest available MySQL version
4. Install mysql-shell utility
5. Run MySQL Upgrade Checker
6. Remove MySQL 5 Yum Repository and Install MySql 8 Yum Repository
7. Stop MySQL Server
8. Run YUM update command to update new binaries
9. Start MySQL server and check Version


Step 1. Take the Full backup of the MySQL server : We are using Percona Xtrabackup to perform a physical level Full MySQL server backup. Click here to read more about Percona Xtrabackup.

[mysql@test-machine01 mysql_bkp]$ xtrabackup --backup --target-dir=/u01/mysql_bkp/full_bkp/ -uroot -p
xtrabackup: recognized client arguments: --backup=1 --target-dir=/u01/mysql_bkp/full_bkp/ --user=root --password
Enter password:
xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82)
211117 11:48:27  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at - line 1535.
211117 11:48:27 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 8.0.22
211117 11:48:27 Executing LOCK INSTANCE FOR BACKUP...
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
Number of pools: 1
xtrabackup: inititialize_service_handles suceeded
211117 11:48:27 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
xtrabackup: Redo Log Archiving is not set up.
211117 11:48:27 >> log scanned up to (1061140266)
xtrabackup: Generating a list of tablespaces
xtrabackup: Generating a list of tablespaces
Scanning './'
Directory '/var/lib/mysql/.cache' will not be scanned because it is a hidden directory.
Directory '/var/lib/mysql/.config' will not be scanned because it is a hidden directory.
Directory '/var/lib/mysql/.bash_history' will not be scanned because it is a hidden directory.
Directory '/var/lib/mysql/.bash_profile' will not be scanned because it is a hidden directory.
Scanning '/u01/mysql/'
Completed space ID check of 2 files.
Allocated tablespace ID 74 for grouprepl_test/t1, old maximum was 0
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
211117 11:48:27 [01] Copying ./ibdata1 to /u01/mysql_bkp/full_bkp/ibdata1
211117 11:48:27 [01]        ...done
211117 11:48:27 [01] Copying ./sys/sys_config.ibd to /u01/mysql_bkp/full_bkp/sys/sys_config.ibd
211117 11:48:27 [01]        ...done
211117 11:48:27 [01] Copying ./grouprepl_test/t1.ibd to /u01/mysql_bkp/full_bkp/grouprepl_test/t1.ibd
211117 11:48:27 [01]        ...done
211117 11:48:27 [01] Copying ./mysql_innodb_cluster_metadata/clusters.ibd to /u01/mysql_bkp/full_bkp/mysql_innodb_cluster_metadata/clusters.ibd
211117 11:48:27 [01]        ...done
  
211117 11:48:28 [01]        ...done
211117 11:48:28 [01] Copying performance_schema/clone_progress_507.sdi to /u01/mysql_bkp/full_bkp/performance_schema/clone_progress_507.sdi
211117 11:48:28 [01]        ...done
211117 11:48:28 [00] Writing /u01/mysql_bkp/full_bkp/test/db.opt
211117 11:48:28 [00]        ...done
211117 11:48:28 Finished backing up non-InnoDB tables and files
211117 11:48:28 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
211117 11:48:28 Selecting LSN and binary log position from p_s.log_status
211117 11:48:28 [00] Copying /var/lib/mysql/binlog.000019 to /u01/mysql_bkp/full_bkp/binlog.000019 up to position 156
211117 11:48:28 [00]        ...done
211117 11:48:28 [00] Writing /u01/mysql_bkp/full_bkp/binlog.index
211117 11:48:28 [00]        ...done
211117 11:48:28 [00] Writing /u01/mysql_bkp/full_bkp/xtrabackup_binlog_info
211117 11:48:28 [00]        ...done
211117 11:48:28 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1061140266'
xtrabackup: Stopping log copying thread at LSN 1061140266.
Starting to parse redo log at lsn = 1061140014
211117 11:48:28 Executing UNLOCK INSTANCE
211117 11:48:28 All tables unlocked
211117 11:48:28 [00] Copying ib_buffer_pool to /u01/mysql_bkp/full_bkp/ib_buffer_pool
211117 11:48:28 [00]        ...done
211117 11:48:28 Backup created in directory '/u01/mysql_bkp/full_bkp/'
MySQL binlog position: filename 'binlog.000019', position '156', GTID of the last change '13ecba9c-444a-11eb-a397-000c29f9d9e6:1,4cb6a75d-9ac5-11eb-98d9-000c293ab022:1-4871:1000012-1000187,81646864-9069-11eb-bb04-000c293ab022:1-98'
211117 11:48:28 [00] Writing /u01/mysql_bkp/full_bkp/backup-my.cnf
211117 11:48:28 [00]        ...done
211117 11:48:28 [00] Writing /u01/mysql_bkp/full_bkp/xtrabackup_info
211117 11:48:28 [00]        ...done
xtrabackup: Transaction log of lsn (1061140266) to (1061140276) was copied.
211117 11:48:30 completed OK!
[mysql@test-machine01 mysql_bkp]$
  
[mysql@test-machine01 mysql_bkp]$
[mysql@test-machine01 mysql_bkp]$ cd full_bkp
[mysql@test-machine01 full_bkp]$ ls -ltr
total 61508
-rw-r-----. 1 mysql mysql 12582912 Nov 17 11:48 ibdata1
drwxr-x---. 2 mysql mysql       28 Nov 17 11:48 sys
drwxr-x---. 2 mysql mysql       20 Nov 17 11:48 grouprepl_test
drwxr-x---. 2 mysql mysql     4096 Nov 17 11:48 mysql_innodb_cluster_metadata
drwxr-x---. 2 mysql mysql     4096 Nov 17 11:48 employees
drwxr-x---. 2 mysql mysql     4096 Nov 17 11:48 repl_test
drwxr-x---. 2 mysql mysql       93 Nov 17 11:48 repl_test1
-rw-r-----. 1 mysql mysql 25165824 Nov 17 11:48 mysql.ibd
-rw-r-----. 1 mysql mysql 12582912 Nov 17 11:48 undo_002
-rw-r-----. 1 mysql mysql 12582912 Nov 17 11:48 undo_001
drwxr-x---. 2 mysql mysql     4096 Nov 17 11:48 mysql
drwxr-x---. 2 mysql mysql     8192 Nov 17 11:48 performance_schema
drwxr-x---. 2 mysql mysql       20 Nov 17 11:48 test
-rw-r-----. 1 mysql mysql      156 Nov 17 11:48 binlog.000019
-rw-r-----. 1 mysql mysql       29 Nov 17 11:48 binlog.index
-rw-r-----. 1 mysql mysql      159 Nov 17 11:48 xtrabackup_binlog_info
-rw-r-----. 1 mysql mysql     2560 Nov 17 11:48 xtrabackup_logfile
-rw-r-----. 1 mysql mysql      108 Nov 17 11:48 xtrabackup_checkpoints
-rw-r-----. 1 mysql mysql     4275 Nov 17 11:48 ib_buffer_pool
-rw-r-----. 1 mysql mysql      475 Nov 17 11:48 backup-my.cnf
-rw-r-----. 1 mysql mysql      138 Nov 17 11:48 xtrabackup_tablespaces
-rw-r-----. 1 mysql mysql      643 Nov 17 11:48 xtrabackup_info
[mysql@test-machine01 full_bkp]$ 


Step 2. Check the current MySQL version : Login to the MySQL server and check the current version. We can use commands like \s, status or @@version to get the current version.

[root@test-machine01 log]#
[root@test-machine01 log]#  mysql -hlocalhost -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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> status
--------------
mysql  Ver 14.14 Distrib 5.7.38, for Linux (x86_64) using  EditLine wrapper

Connection id:          3
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.38 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 4 min 26 sec

Threads: 1  Questions: 9  Slow queries: 0  Opens: 106  Flush tables: 1  Open tables: 99  Queries per second avg: 0.033
--------------


Step 3. Check for the latest available MySql version: To review a list of the package updates that are available for your distribution use OS Command yum check-update. And yum list command will show installed and available MySQL packages.

[root@test-machine01 ~]# yum check-update mysql*
Loaded plugins: langpacks, ulninfo
mysql-connectors-community                                                                                                                                             | 2.6 kB  00:00:00
mysql-tools-community                                                                                                                                                  | 2.6 kB  00:00:00
mysql80-community                                                                                                                                                      | 2.6 kB  00:00:00
mysql-community-client.x86_64                                                                 8.0.30-1.el7                                                          mysql80-community
mysql-community-client-plugins.x86_64                                                         8.0.30-1.el7                                                          mysql80-community
mysql-community-common.x86_64                                                                 8.0.30-1.el7                                                          mysql80-community
mysql-community-libs.x86_64                                                                   8.0.30-1.el7                                                          mysql80-community
mysql-community-libs-compat.x86_64                                                            8.0.30-1.el7                                                          mysql80-community
mysql-community-server.x86_64                                                                 8.0.30-1.el7                                                          mysql80-community
mysql-connector-odbc.x86_64                                                                   8.0.30-1.el7                                                          mysql-connectors-community
mysql-router-community.x86_64                                                                 8.0.30-1.el7                                                          mysql-tools-community
mysql-shell.x86_64                                                                            8.0.30-1.el7                                                          mysql-tools-community
mysql80-community-release.noarch                                                              el7-9                                                                mysql80-community
[root@test-machine01 ~]#

[root@test-machine01 ~]#
[root@test-machine01 ~]# yum list mysql-community-server*
Loaded plugins: langpacks, ulninfo
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
Installed Packages
mysql-community-server.x86_64         															 5.7.38-1.el7                 											   @mysql57-community
Available Packages
mysql-community-server.x86_64                                                                    8.0.30-1.el7                                                               mysql80-community
mysql-community-server-debug.x86_64                                                              8.0.30-1.el7                                                               mysql80-community
[root@test-machine01 ~]#


Step 4. Install mysql-shell utility: Install mysql-shell utility as it will be required to run the upgrade checker. Use the command yum install mysql-shell to install it.

[root@test-machine01 yum.repos.d]#
[root@test-machine01 yum.repos.d]# yum install mysql-shell
Loaded plugins: langpacks, ulninfo
mysql-connectors-community                                                                                                                                             | 2.6 kB  00:00:00
mysql-tools-community                                                                                                                                                  | 2.6 kB  00:00:00
mysql57-community                                                                                                                                                      | 2.6 kB  00:00:00
Resolving Dependencies
--> Running transaction check
---> Package mysql-shell.x86_64 0:8.0.23-1.el7 will be updated
---> Package mysql-shell.x86_64 0:8.0.30-1.el7 will be an update
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================
 Package                                     Arch                                   Version                                       Repository                                             Size
==============================================================================================================================================================================================
Updating:
 mysql-shell                                 x86_64                                 8.0.30-1.el7                                  mysql-tools-community                                  34 M

Transaction Summary
==============================================================================================================================================================================================
Upgrade  1 Package

Total size: 34 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Updating   : mysql-shell-8.0.30-1.el7.x86_64                                                                                                                                            1/2
  Cleanup    : mysql-shell-8.0.23-1.el7.x86_64                                                                                                                                            2/2
  Verifying  : mysql-shell-8.0.30-1.el7.x86_64                                                                                                                                            1/2
  Verifying  : mysql-shell-8.0.23-1.el7.x86_64                                                                                                                                            2/2

Updated:
  mysql-shell.x86_64 0:8.0.30-1.el7

Complete!
[root@test-machine01 yum.repos.d]#


Step 5. Run MySql Upgrade Checker: Login to mysqlshell and connect to MySQL server using \connect command. You can get the list of util commands by typing util. and press TAB button twice this will give you a full list of available util commands. Use command util.checkForServerUpgrade and pass MySQL Server connection details and my.cnf parameter file location. This command will connect to MySql instance and perform the pre-check before upgradeing and report for potential errors that can cause the upgrade to fail. In our case at the bottom in the Note section, we have no fatal error.

[root@test-machine01 ~]#
[root@test-machine01 ~]# mysqlsh
MySQL Shell 8.0.30

Copyright (c) 2016, 2022, 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 '\?' for help; '\quit' to exit.
 MySQL  JS >
 MySQL  JS > 
 MySQL  JS > \connect root@localhost
Creating a session to 'root@localhost'
Please provide the password for 'root@localhost': *********
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 3
Server version: 5.7.38 MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
 MySQL  localhost  JS >
 MySQL  localhost  JS >

 MySQL  localhost  JS >
 MySQL  localhost  JS > util.[Press double TAB Button]
checkForServerUpgrade()  dumpInstance()           dumpTables()             help()                   importTable()
debug                    dumpSchemas()            exportTable()            importJson()             loadDump()
 MySQL  localhost  JS >
 MySQL  localhost  JS > util.checkForServerUpgrade('root@localhost', {configpath: "/etc/my.cnf"})
The MySQL server at /var%2Flib%2Fmysql%2Fmysql.sock, version 5.7.38 - MySQL
Community Server (GPL), will now be checked for compatibility issues for
upgrade to MySQL 8.0.30...

1) Usage of old temporal type
  No issues found

2) Usage of db objects with names conflicting with new reserved keywords
  No issues found

3) Usage of utf8mb3 charset
  No issues found

4) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

5) Partitioned tables using engines with non native partitioning
  No issues found

6) Foreign key constraint names longer than 64 characters
  No issues found

7) Usage of obsolete MAXDB sql_mode flag
  No issues found

8) Usage of obsolete sql_mode flags
  Notice: The following DB objects have obsolete options persisted for
    sql_mode, which will be cleared during upgrade to 8.0.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals

  global system variable sql_mode - defined using obsolete NO_AUTO_CREATE_USER
    option

9) ENUM/SET column definitions containing elements longer than 255 characters
  No issues found

10) Usage of partitioned tables in shared tablespaces
  No issues found

11) Circular directory references in tablespace data file paths
  No issues found

12) Usage of removed functions
  No issues found

13) Usage of removed GROUP BY ASC/DESC syntax
  No issues found

14) Removed system variables for error logging to the system log configuration
  No issues found

15) Removed system variables
  No issues found

16) System variables with new default values
  Warning: Following system variables that are not defined in your
    configuration file will have new default values. Please review if you rely on
    their current values and if so define them before performing upgrade.
  More information:
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

  back_log - default value will change
  character_set_server - default value will change from latin1 to utf8mb4
  collation_server - default value will change from latin1_swedish_ci to
    utf8mb4_0900_ai_ci
  event_scheduler - default value will change from OFF to ON
  explicit_defaults_for_timestamp - default value will change from OFF to ON
  innodb_autoinc_lock_mode - default value will change from 1 (consecutive) to
    2 (interleaved)
  innodb_flush_method - default value will change from NULL to fsync (Unix),
    unbuffered (Windows)
  innodb_flush_neighbors - default value will change from 1 (enable) to 0
    (disable)
  innodb_max_dirty_pages_pct - default value will change from 75 (%)  90 (%)
  innodb_max_dirty_pages_pct_lwm - default value will change from_0 (%) to 10
    (%)
  innodb_undo_log_truncate - default value will change from OFF to ON
  innodb_undo_tablespaces - default value will change from 0 to 2
  log_bin - default value will change from OFF to ON
  log_error_verbosity - default value will change from 3 (Notes) to 2 (Warning)
  log_slave_updates - default value will change from OFF to ON
  master_info_repository - default value will change from FILE to TABLE
  max_allowed_packet - default value will change from 4194304 (4MB) to 67108864
    (64MB)
  max_error_count - default value will change from 64 to 1024
  optimizer_trace_max_mem_size - default value will change from 16KB to 1MB
  performance_schema_consumer_events_transactions_current - default value will
    change from OFF to ON
  performance_schema_consumer_events_transactions_history - default value will
    change from OFF to ON
  relay_log_info_repository - default value will change from FILE to TABLE
  server_id - default value will change from 0 to 1
  slave_rows_search_algorithms - default value will change from 'INDEX_SCAN,
    TABLE_SCAN' to 'INDEX_SCAN, HASH_SCAN'
  table_open_cache - default value will change from 2000 to 4000
  transaction_write_set_extraction - default value will change from OFF to
    XXHASH64

17) Zero Date, Datetime, and Timestamp values
  No issues found

18) Schema inconsistencies resulting from file removal or corruption
  No issues found

19) Tables recognized by InnoDB that belong to a different engine
  No issues found

20) Issues reported by 'check table x for upgrade' command
  No issues found

21) New default authentication plugin considerations
  Warning: The new default authentication plugin 'caching_sha2_password' offers
    more secure password hashing than previously used 'mysql_native_password'
    (and consequent improved client connection authentication). However, it also
    has compatibility implications that may affect existing MySQL installations.
    If your MySQL installation must serve pre-8.0 clients and you encounter
    compatibility issues after upgrading, the simplest way to address those
    issues is to reconfigure the server to revert to the previous default
    authentication plugin (mysql_native_password). For example, use these lines
    in the server option file:

    [mysqld]
    default_authentication_plugin=mysql_native_password

    However, the setting should be viewed as temporary, not as a long term or
    permanent solution, because it causes new accounts created with the setting
    in effect to forego the improved authentication security.
    If you are using replication please take time to understand how the
    authentication plugin changes may impact you.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication

Errors:   0
Warnings: 27
Notices:  1

NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
 MySQL  localhost  JS >


Step 6. Remove MySql 5 Yum Repository and Install MySql 8 Yum Repository: In this step, we need to remove MySql 5 repository and install MySql 8 repository as both repositories cannot coexist same time. If you tried to install mysql 8 repositories without removing mysql 5 repositories you will get the error: “Failed dependencies: mysql57-community-release conflicts with (installed) mysql80-community-release-el7-6.noarch“. Please note with the command yum remove mysql57-community-release.noarch we are removing only the repository, not packages. You can verify what are mysql packages installed using the command yum list installed | grep -i mysql. To get Mysql 8.0 repository download link go to below MySql community download and click on highlighted Download button and copy link address.

Go to URL : https://dev.mysql.com/downloads/repo/yum/
[root@test-machine01 ~]#
[root@test-machine01 ~]# yum list installed |grep -i mysql
mysql-community-client.x86_64         5.7.38-1.el7                 @mysql57-community
mysql-community-common.x86_64         5.7.38-1.el7                 @mysql57-community
mysql-community-icu-data-files.x86_64 8.0.29-1.el7                 @mysql80-community
mysql-community-libs.x86_64           5.7.38-1.el7                 @mysql57-community
mysql-community-server.x86_64         5.7.38-1.el7                 @mysql57-community
mysql-connector-odbc.x86_64           8.0.26-1.el7                 @mysql-connectors-community
mysql-router-community.x86_64         8.0.22-1.el7                 @mysql-tools-community
mysql-shell.x86_64                    8.0.30-1.el7                 @mysql-tools-community
mysql57-community-release.noarch      el7-9                        installed
[root@test-machine01 ~]#

[root@test-machine01 ~]#
[root@test-machine01 ~]# yum remove mysql57-community-release.noarch
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package mysql57-community-release.noarch 0:el7-9 will be erased
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================
 Package                                                    Arch                                    Version                                  Repository                                  Size
==============================================================================================================================================================================================
Removing:
 mysql57-community-release                                  noarch                                  el7-9                                    installed                                  8.6 k

Transaction Summary
==============================================================================================================================================================================================
Remove  1 Package

Installed size: 8.6 k
Is this ok [y/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Erasing    : mysql57-community-release-el7-9.noarch                                                                                                                                     1/1
warning: /etc/yum.repos.d/mysql-community.repo saved as /etc/yum.repos.d/mysql-community.repo.rpmsave
  Verifying  : mysql57-community-release-el7-9.noarch                                                                                                                                     1/1

Removed:
  mysql57-community-release.noarch 0:el7-9

Complete!
[root@test-machine01 ~]#

[root@test-machine01 ~]#
[root@test-machine01 ~]# yum list installed |grep -i mysql
mysql-community-client.x86_64         5.7.38-1.el7                 @mysql57-community
mysql-community-common.x86_64         5.7.38-1.el7                 @mysql57-community
mysql-community-icu-data-files.x86_64 8.0.29-1.el7                 @mysql80-community
mysql-community-libs.x86_64           5.7.38-1.el7                 @mysql57-community
mysql-community-server.x86_64         5.7.38-1.el7                 @mysql57-community
mysql-connector-odbc.x86_64           8.0.26-1.el7                 @mysql-connectors-community
mysql-router-community.x86_64         8.0.22-1.el7                 @mysql-tools-community
mysql-shell.x86_64                    8.0.30-1.el7                 @mysql-tools-community
[root@test-machine01 ~]#

[root@test-machine01 ~]#
[root@test-machine01 ~]# cat /etc/oracle-release
Oracle Linux Server release 7.9
[root@test-machine01 ~]#

[root@test-machine01 ~]#
[root@test-machine01 ~]# yum install https://dev.mysql.com/get/mysql80-community-release-el7-6.noarch.rpm
Loaded plugins: langpacks, ulninfo
mysql80-community-release-el7-6.noarch.rpm                                                                                                                             |  11 kB  00:00:00
Examining /var/tmp/yum-root-n6zmz_/mysql80-community-release-el7-6.noarch.rpm: mysql80-community-release-el7-6.noarch
Marking /var/tmp/yum-root-n6zmz_/mysql80-community-release-el7-6.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql80-community-release.noarch 0:el7-6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================
 Package                                             Arch                             Version                         Repository                                                         Size
==============================================================================================================================================================================================
Installing:
 mysql80-community-release                           noarch                           el7-6                           /mysql80-community-release-el7-6.noarch                            10 k

Transaction Summary
==============================================================================================================================================================================================
Install  1 Package

Total size: 10 k
Installed size: 10 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql80-community-release-el7-6.noarch                                                                                                                                     1/1
  Verifying  : mysql80-community-release-el7-6.noarch                                                                                                                                     1/1

Installed:
  mysql80-community-release.noarch 0:el7-6

Complete!
[root@test-machine01 tmp]#

[root@test-machine01 tmp]#
[root@test-machine01 tmp]# yum list installed |grep -i mysql
mysql-community-client.x86_64         5.7.38-1.el7                 @mysql57-community
mysql-community-common.x86_64         5.7.38-1.el7                 @mysql57-community
mysql-community-icu-data-files.x86_64 8.0.29-1.el7                 @mysql80-community
mysql-community-libs.x86_64           5.7.38-1.el7                 @mysql57-community
mysql-community-server.x86_64         5.7.38-1.el7                 @mysql57-community
mysql-connector-odbc.x86_64           8.0.26-1.el7                 @mysql-connectors-community
mysql-router-community.x86_64         8.0.22-1.el7                 @mysql-tools-community
mysql-shell.x86_64                    8.0.30-1.el7                 @mysql-tools-community
mysql80-community-release.noarch      el7-6                        @/mysql80-community-release-el7-6.noarch
[root@test-machine01 tmp]#


Step 7. Stop MySql Server: Stop mysql server before proceeding with the upgrade.

[root@test-machine01 ~]#
[root@test-machine01 ~]# systemctl stop mysqld.service
[root@test-machine01 ~]#
[root@test-machine01 ~]# systemctl status mysqld.service
â mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Tue 2022-08-09 13:47:07 +03; 4s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 8824 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 8801 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 8827 (code=exited, status=0/SUCCESS)

Aug 09 13:46:30 test-machine01 systemd[1]: Starting MySQL Server...
Aug 09 13:46:59 test-machine01 systemd[1]: Started MySQL Server.
Aug 09 13:47:06 test-machine01 systemd[1]: Stopping MySQL Server...
Aug 09 13:47:07 test-machine01 systemd[1]: Stopped MySQL Server.
[root@test-machine01 ~]#


Step 8. Run YUM update command to update new binaries: Use OS command yum update to update existing MySQL binaries.

[root@test-machine01 ~]#
[root@test-machine01 ~]# yum update mysql-server
Loaded plugins: langpacks, ulninfo
mysql-connectors-community                                                                                                                                             | 2.6 kB  00:00:00
mysql-tools-community                                                                                                                                                  | 2.6 kB  00:00:00
mysql80-community                                                                                                                                                      | 2.6 kB  00:00:00
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.x86_64 0:5.7.38-1.el7 will be updated
---> Package mysql-community-server.x86_64 0:8.0.30-1.el7 will be an update
--> Processing Dependency: mysql-community-common(x86-64) = 8.0.30-1.el7 for package: mysql-community-server-8.0.30-1.el7.x86_64
--> Processing Dependency: mysql-community-icu-data-files = 8.0.30-1.el7 for package: mysql-community-server-8.0.30-1.el7.x86_64
--> Processing Dependency: mysql-community-client(x86-64) >= 8.0.11 for package: mysql-community-server-8.0.30-1.el7.x86_64
--> Running transaction check
---> Package mysql-community-client.x86_64 0:5.7.38-1.el7 will be updated
---> Package mysql-community-client.x86_64 0:8.0.30-1.el7 will be an update
--> Processing Dependency: mysql-community-client-plugins = 8.0.30-1.el7 for package: mysql-community-client-8.0.30-1.el7.x86_64
--> Processing Dependency: mysql-community-libs(x86-64) >= 8.0.11 for package: mysql-community-client-8.0.30-1.el7.x86_64
---> Package mysql-community-common.x86_64 0:5.7.38-1.el7 will be updated
---> Package mysql-community-common.x86_64 0:8.0.30-1.el7 will be an update
---> Package mysql-community-icu-data-files.x86_64 0:8.0.29-1.el7 will be updated
---> Package mysql-community-icu-data-files.x86_64 0:8.0.30-1.el7 will be an update
--> Running transaction check
---> Package mysql-community-client-plugins.x86_64 0:8.0.30-1.el7 will be installed
---> Package mysql-community-libs.x86_64 0:5.7.38-1.el7 will be updated
---> Package mysql-community-libs.x86_64 0:8.0.30-1.el7 will be an update
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================
 Package                                                    Arch                               Version                                    Repository                                     Size
==============================================================================================================================================================================================
Updating:
 mysql-community-server                                     x86_64                             8.0.30-1.el7                               mysql80-community                              54 M
Installing for dependencies:
 mysql-community-client-plugins                             x86_64                             8.0.30-1.el7                               mysql80-community                             2.5 M
Updating for dependencies:
 mysql-community-client                                     x86_64                             8.0.30-1.el7                               mysql80-community                              14 M
 mysql-community-common                                     x86_64                             8.0.30-1.el7                               mysql80-community                             645 k
 mysql-community-icu-data-files                             x86_64                             8.0.30-1.el7                               mysql80-community                             2.1 M
 mysql-community-libs                                       x86_64                             8.0.30-1.el7                               mysql80-community                             1.5 M

Transaction Summary
==============================================================================================================================================================================================
Install             ( 1 Dependent package)
Upgrade  1 Package  (+4 Dependent packages)

Total size: 74 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-community-client-plugins-8.0.30-1.el7.x86_64                                                                                                                        1/11
  Updating   : mysql-community-common-8.0.30-1.el7.x86_64                                                                                                                                2/11
  Updating   : mysql-community-libs-8.0.30-1.el7.x86_64                                                                                                                                  3/11
  Updating   : mysql-community-client-8.0.30-1.el7.x86_64                                                                                                                                4/11
  Updating   : mysql-community-icu-data-files-8.0.30-1.el7.x86_64                                                                                                                        5/11
  Updating   : mysql-community-server-8.0.30-1.el7.x86_64                                                                                                                                6/11
  Cleanup    : mysql-community-icu-data-files-8.0.29-1.el7.x86_64                                                                                                                        7/11
  Cleanup    : mysql-community-server-5.7.38-1.el7.x86_64                                                                                                                                8/11
  Cleanup    : mysql-community-client-5.7.38-1.el7.x86_64                                                                                                                                9/11
  Cleanup    : mysql-community-libs-5.7.38-1.el7.x86_64                                                                                                                                 10/11
  Cleanup    : mysql-community-common-5.7.38-1.el7.x86_64                                                                                                                               11/11
  Verifying  : mysql-community-common-8.0.30-1.el7.x86_64                                                                                                                                1/11
  Verifying  : mysql-community-client-plugins-8.0.30-1.el7.x86_64                                                                                                                        2/11
  Verifying  : mysql-community-icu-data-files-8.0.30-1.el7.x86_64                                                                                                                        3/11
  Verifying  : mysql-community-libs-8.0.30-1.el7.x86_64                                                                                                                                  4/11
  Verifying  : mysql-community-client-8.0.30-1.el7.x86_64                                                                                                                                5/11
  Verifying  : mysql-community-server-8.0.30-1.el7.x86_64                                                                                                                                6/11
  Verifying  : mysql-community-icu-data-files-8.0.29-1.el7.x86_64                                                                                                                        7/11
  Verifying  : mysql-community-client-5.7.38-1.el7.x86_64                                                                                                                                8/11
  Verifying  : mysql-community-server-5.7.38-1.el7.x86_64                                                                                                                                9/11
  Verifying  : mysql-community-common-5.7.38-1.el7.x86_64                                                                                                                               10/11
  Verifying  : mysql-community-libs-5.7.38-1.el7.x86_64                                                                                                                                 11/11

Dependency Installed:
  mysql-community-client-plugins.x86_64 0:8.0.30-1.el7

Updated:
  mysql-community-server.x86_64 0:8.0.30-1.el7

Dependency Updated:
  mysql-community-client.x86_64 0:8.0.30-1.el7 mysql-community-common.x86_64 0:8.0.30-1.el7 mysql-community-icu-data-files.x86_64 0:8.0.30-1.el7 mysql-community-libs.x86_64 0:8.0.30-1.el7

Complete!
[root@test-machine01 ~]#


Step 9. Start MySQL server and check Version: Start the MySql server using the command systemctl start and check version. You will notice the is upgraded version from 5.7.38 to 8.0.30. Also, note that the mysql_upgrade option is deprecated. The upgrade process automatically starts by running a new MySQL binary with an older data directory. In data_directory new InnoDB, files are created.

[root@test-machine01 ~]#
[root@test-machine01 ~]# systemctl status mysqld.service
â mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Tue 2022-08-09 13:47:07 +03; 16min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 8827 (code=exited, status=0/SUCCESS)

Aug 09 13:46:30 test-machine01 systemd[1]: Starting MySQL Server...
Aug 09 13:46:59 test-machine01 systemd[1]: Started MySQL Server.
Aug 09 13:47:06 test-machine01 systemd[1]: Stopping MySQL Server...
Aug 09 13:47:07 test-machine01 systemd[1]: Stopped MySQL Server.
[root@test-machine01 yum.repos.d]#
[root@test-machine01 yum.repos.d]# systemctl start mysqld.service
[root@test-machine01 yum.repos.d]#
[root@test-machine01 yum.repos.d]# systemctl status mysqld.service
â mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2022-08-09 14:04:21 +03; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 11333 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 11362 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           ââ11362 /usr/sbin/mysqld

Aug 09 14:04:10 test-machine01 systemd[1]: Starting MySQL Server...
Aug 09 14:04:21 test-machine01 systemd[1]: Started MySQL Server.
[root@test-machine01 ~]#

[root@test-machine01 ~]#
[root@test-machine01 ~]# mysql -hlocalhost -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.30 MySQL Community Server - GPL

Copyright (c) 2000, 2022, 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.30 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          11
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.30 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:                 31 min 43 sec

Threads: 2  Questions: 11  Slow queries: 0  Opens: 666  Flush tables: 4  Open tables: 35  Queries per second avg: 0.005
--------------

mysql>


[root@test-machine01 ~]#
[root@test-machine01 ~]# mysql_upgrade
The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.
To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade.
The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand.
It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.
[root@test-machine01 ~]#
[root@test-machine01 ~]#

[root@test-machine01 ~]#
[root@test-machine01 ~]# cd /var/lib/mysql
[root@test-machine01 mysql]# ls -ltr
total 94676
-rw-r-----. 1 mysql mysql       56 Jun 28 12:49 auto.cnf
-rw-------. 1 mysql mysql     1676 Jun 28 12:49 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 28 12:49 ca.pem
-rw-------. 1 mysql mysql     1680 Jun 28 12:49 server-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 28 12:49 server-cert.pem
-rw-------. 1 mysql mysql     1676 Jun 28 12:49 client-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jun 28 12:49 client-cert.pem
-rw-r--r--. 1 mysql mysql      452 Jun 28 12:49 public_key.pem
-rw-------. 1 mysql mysql     1680 Jun 28 12:49 private_key.pem
-rw-r-----. 1 mysql mysql      295 Aug  9 13:47 ib_buffer_pool
drwxr-x---. 2 mysql mysql     4096 Aug  9 14:04 #innodb_temp
drwxr-x---. 2 mysql mysql       28 Aug  9 14:04 sys
-rw-r-----. 1 mysql mysql 12582912 Aug  9 14:04 ibtmp1
drwxr-x---. 2 mysql mysql     8192 Aug  9 14:04 performance_schema
drwxr-x---. 2 mysql mysql     4096 Aug  9 14:04 mysql
-rw-r-----. 1 mysql mysql  8585216 Aug  9 14:04 #ib_16384_1.dblwr
-rw-r-----. 1 mysql mysql        6 Aug  9 14:04 mysql_upgrade_info
-rw-r-----. 1 mysql mysql       16 Aug  9 14:04 binlog.index
-rw-r-----. 1 mysql mysql      157 Aug  9 14:04 binlog.000001
-rw-------. 1 mysql mysql        6 Aug  9 14:04 mysql.sock.lock
srwxrwxrwx. 1 mysql mysql        0 Aug  9 14:04 mysql.sock
drwxr-x---. 2 mysql mysql     4096 Aug  9 14:04 #innodb_redo
-rw-r-----. 1 mysql mysql 12582912 Aug  9 14:04 ibdata1
-rw-r-----. 1 mysql mysql 29360128 Aug  9 14:04 mysql.ibd
-rw-r-----. 1 mysql mysql 16777216 Aug  9 14:06 undo_001
-rw-r-----. 1 mysql mysql 16777216 Aug  9 14:06 undo_002
-rw-r-----. 1 mysql mysql   196608 Aug  9 14:06 #ib_16384_0.dblwr
[root@test-machine01 mysql]#

[root@test-machine01 ~]#
[root@test-machine01 ~]# cd /var/log
[root@test-machine01 log]#
[root@test-machine01 log]# ls -ltr *.log
[root@test-machine01 log]# vi mysqld.log
2022-08-09T11:04:11.742601Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.30) starting as process 11362
2022-08-09T11:04:11.874156Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2022-08-09T11:04:11.874202Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-08-09T11:04:14.113840Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-08-09T11:04:14.648764Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead
2022-08-09T11:04:15.726977Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2022-08-09T11:04:16.579208Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80030' started.
2022-08-09T11:04:21.047005Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80030' completed.
2022-08-09T11:04:21.401091Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-08-09T11:04:21.401179Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-08-09T11:04:21.416810Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.30'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2022-08-09T11:04:21.416944Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[root@test-machine01 log]#


This document is only for learning purposes and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. 
Click here to understand more about our pursuit.


Related Articles


 154 Total Views,  4 Views Today

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Leave a Reply

Your email address will not be published.

18 − sixteen =