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.
[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
- MySQL major version upgrade from 5.7 to 8.0.30
- MySQL minor version upgrade from 8.0.22 to 8.0.29
- Steps to create a Database Link from Oracle to MySQL
- Multiplexing of Control Files Using RMAN, SPFILE & PFILE in Oracle
- Create MySQL Instance Replica using Clone Command
