Overview of Changes from MySQL 5.7.44 to 8.0.39 Version
The upgrade from MySQL 5.7.44 to 8.0.39 includes major additions and changes which affect safety, performance, and functionality. Below are the key fields of changes
New Default Authentication Plugin
MySQL 8.0 uses caching_sha2_password as the default authentication method instead of mysql_native_password. This change enhances security but requires updates to connection strings and client applications.
Transactional Data Dictionary
MySQL 8.0 introduces a transactional data dictionary, which improves reliability by storing metadata in a single InnoDB tablespace, ensuring atomic DDL operations.
In Data Dictionary (DD) support is introduced in MySQL 8.0 for which several new DD tables are created in the mysql schema. Hence user tables with the conflicting names in the mysql schema should be dropped or renamed prior to upgrade. The DD table names are mentioned below: ‘catalogs’, ‘character_sets’, ‘collations’, ‘column_statistics’, ‘column_type_elements’, ‘columns’, ‘dd_properties’, ‘events’, ‘foreign_key_column_usage’, ‘foreign_keys’, ‘index_column_usage’, ‘index_partitions’, ‘index_stats’, ‘indexes’, ‘parameter_type_elements’, ‘parameters’, ‘resource_groups’, ‘routines’, ‘schemata’, ‘st_spatial_reference_systems’, ‘table_partition_values’, ‘table_partitions’, ‘table_stats’, ‘tables’, ‘tablespace_files’, ‘tablespaces’, ‘triggers’, ‘view_routine_usage’, ‘view_table_usage’.
Default Character Set
The default character set has shifted from latin1 in MySQL 5.7 to utf8mb4 in MySQL 8.0, which supports a wider range of characters, including emojis.
Default Collation
The default collation is now utf8mb4_0900_ai_ci, aligning with Unicode 9.0 standards, improving character comparison and sorting capabilities.
Stricter SQL Modes
The default SQL mode in MySQL 8.0 includes ONLY_FULL_GROUP_BY, which enforces stricter rules on GROUP BY clauses, potentially affecting existing queries.
Obsolete Data Types
Several data types and features have been deprecated or removed, including old-style decimal types and non-native partitioning. Users are encouraged to transition to newer alternatives like VARCHAR or TEXT for string storage.
Optimizations
MySQL 8.0 is designed for better performance, particularly in read/write workloads, with various internal optimizations.
Role-Based Access Control
MySQL 8.0 supports roles, allowing for easier management of user privileges and enhancing security2.
Common Table Expressions (CTEs) and Window Functions
These features allow for more complex queries and better performance by simplifying code and enabling advanced data manipulations.
Checklist for MySQL Upgrade
- Make sure to take backup before upgrading.
- Check with the mysql utility for ERROR before upgrading.
- Make sure there is no ERROR before going to the next step.
Following Step by Step process for Upgrade
Verify the existing MySQL version and databases
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | [root@Slave ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 17 Server version: 5.7.44 MySQL Community Server (GPL) Copyright (c) 2000, 2023, 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 databases; +--------------------+ | Database | +--------------------+ | information_schema | | employees | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.01 sec) |
Take backup before upgradation
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [root@Slave centos]# xtrabackup --backup --target-dir=/home/centos/full_bkp/ -uroot -p xtrabackup: recognized server arguments: --datadir=/var/lib/mysql xtrabackup: recognized client arguments: --backup=1 --target-dir=/home/centos/full_bkp/ --user=root --password Enter password: 241018 18:00:37 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES). 241018 18:00:37 version_check Connected to MySQL server 241018 18:00:37 version_check Executing a version check against the server... # A software update is available: 241018 18:00:44 version_check Done. 241018 18:00:44 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set ................ ................ xtrabackup: Transaction log of lsn (589277859) to (589277868) was copied. 241018 18:01:10 completed OK! |
Verify the backup on directory
| 1 2 3 4 5 6 7 8 9 10 11 12 13 | [root@Slave centos]# cd full_bkp [root@Slave full_bkp]# ls -lh total 77M -rw-r-----. 1 root root 487 Oct 18 18:01 backup-my.cnf drwxr-x---. 2 root root 4.0K Oct 18 18:01 employees -rw-r-----. 1 root root 436 Oct 18 18:01 ib_buffer_pool -rw-r-----. 1 root root 76M Oct 18 18:00 ibdata1 drwxr-x---. 2 root root 4.0K Oct 18 18:01 mysql drwxr-x---. 2 root root 8.0K Oct 18 18:01 performance_schema drwxr-x---. 2 root root 8.0K Oct 18 18:01 sys -rw-r-----. 1 root root 141 Oct 18 18:01 xtrabackup_checkpoints -rw-r-----. 1 root root 441 Oct 18 18:01 xtrabackup_info -rw-r-----. 1 root root 2.5K Oct 18 18:01 xtrabackup_logfile |
Check compatibility before upgrade
Using mysql shell and mysql shell utility checking for Server upgrade util.checkForServerUpgrade.
| 1 2 3 4 5 6 7 8 9 10 11 12 | [root@Slave full_bkp]# mysqlsh MySQL Shell 8.4.1 Copyright (c) 2016, 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 '?' for help; 'quit' to exit. MySQL SQL > connect root@localhost Creating a session to 'root@localhost' Fetching global names for auto-completion... Press ^C to stop. Your MySQL connection id is 21 Server version: 5.7.44 MySQL Community Server (GPL) No default schema selected; type use <schema> to set one. |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | MySQL localhost:3306 ssl JS > util.checkForServerUpgrade('root@localhost', {configPath: '/etc/my.cnf', targetVersion: '8.0'}); The MySQL server at localhost:3306, version 5.7.44 - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.38. 1) Usage of old temporal type (oldTemporal) No issues found 2) MySQL syntax check for routine-like objects (routineSyntax) No issues found ......................... ......................... Errors: 0 Warnings: 32 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:3306 ssl JS > q Bye! |
Install package “mysql80-community-release-el8-3.noarch” using Yum repository.
| 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@Slave centos]# yum install mysql80-community-release-el8-3.noarch.rpm Last metadata expiration check: 0:28:34 ago on Fri 18 Oct 2024 05:57:27 PM EDT. Dependencies resolved. ============================================================================================================================================================== Package Architecture Version Repository Size ============================================================================================================================================================== Installing: mysql80-community-release noarch el8-3 @commandline 14 k Transaction Summary ============================================================================================================================================================== Install 1 Package Total size: 14 k Installed size: 7.5 k Is this ok [y/N]: y Downloading Packages: Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : mysql80-community-release-el8-3.noarch 1/1 Verifying : mysql80-community-release-el8-3.noarch 1/1 Installed: Mysql80-community-release-el8-3.noarch Complete! |
| 1 2 3 4 5 6 7 8 | [root@Slave centos]# yum list installed |grep -i mysql mysql-community-client.x86_64 5.7.44-1.el7 @mysql57-community mysql-community-common.x86_64 5.7.44-1.el7 @mysql57-community mysql-community-libs.x86_64 5.7.44-1.el7 @mysql57-community mysql-community-server.x86_64 5.7.44-1.el7 @mysql57-community mysql-shell.x86_64 8.4.1-1.el8 @mysql-tools-8.4-lts-community mysql80-community-release.noarch el8-3 @@commandline perl-DBD-MySQL.x86_64 4.046-3.module_el8+353+7103df35 @appstream |
Stop the MySQL Service
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [root@Slave centos]# systemctl stop mysqld.service [root@Slave centos]# systemctl status mysqld.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: inactive (dead) since Fri 2024-10-18 18:27:12 EDT; 13s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Main PID: 16549 (code=exited, status=0/SUCCESS) Oct 18 15:41:07 Slave systemd[1]: Starting MySQL Server... Oct 18 15:41:49 Slave systemd[1]: Started MySQL Server. Oct 18 18:27:09 Slave systemd[1]: Stopping MySQL Server... Oct 18 18:27:12 Slave systemd[1]: mysqld.service: Succeeded. Oct 18 18:27:12 Slave systemd[1]: Stopped MySQL Server. |
Update the mysql server
update the MySQL server using yum command, it will clear the existing mysql-community-server-5.7.44-1.el7.x86_64, mysql-community-client-5.7.44-1.el7.x86_64, mysql-community-common-5.7.44-1.el7.x86_64
| 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 42 43 44 45 46 47 48 49 50 | [root@Slave centos]# yum update mysql-server MySQL Connectors Community 555 kB/s | 143 kB 00:00 MySQL Tools Community 2.5 MB/s | 1.2 MB 00:00 Last metadata expiration check: 0:00:01 ago on Fri 18 Oct 2024 06:27:54 PM EDT. Dependencies resolved. ============================================================================================================================================================== Package Architecture Version Repository Size ============================================================================================================================================================== Upgrading: mysql-community-client x86_64 8.0.40-1.el8 mysql80-community 16 M mysql-community-common x86_64 8.0.40-1.el8 mysql80-community 668 k mysql-community-libs x86_64 8.0.40-1.el8 mysql80-community 1.5 M mysql-community-server x86_64 8.0.40-1.el8 mysql80-community 65 M Installing dependencies: mysql-community-client-plugins x86_64 8.0.40-1.el8 mysql80-community 3.6 M mysql-community-icu-data-files x86_64 8.0.40-1.el8 mysql80-community 2.2 M Transaction Summary ============================================================================================================================================================== Install 2 Packages Upgrade 4 Packages Total download size: 89 M Is this ok [y/N]: y Downloading Packages: (1/6): mysql-community-icu-data-files-8.0.40-1.el8.x86_64.rpm 1.2 MB/s | 2.2 MB 00:01 (2/6): mysql-community-common-8.0.40-1.el8.x86_64.rpm 1.0 MB/s | 668 kB 00:00 (3/6): mysql-community-client-plugins-8.0.40-1.el8.x86_64.rpm 1.0 MB/s | 3.6 MB 00:03 (4/6): mysql-community-libs-8.0.40-1.el8.x86_64.rpm 1.1 MB/s | 1.5 MB 00:01 (5/6): mysql-community-client-8.0.40-1.el8.x86_64.rpm 1.8 MB/s | 16 MB 00:09 (6/6): mysql-community-server-8.0.40-1.el8.x86_64.rpm 3.0 MB/s | 65 MB 00:21 -------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 3.5 MB/s | 89 MB 00:25 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 .......................... .......................... 10/10 Upgraded: mysql-community-client-8.0.40-1.el8.x86_64 mysql-community-common-8.0.40-1.el8.x86_64 mysql-community-libs-8.0.40-1.el8.x86_64 mysql-community-server-8.0.40-1.el8.x86_64 Installed: mysql-community-client-plugins-8.0.40-1.el8.x86_64 mysql-community-icu-data-files-8.0.40-1.el8.x86_64 Complete! |
Start MySQL service
Start the MySQL service using new binaries
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | [root@Slave centos]# systemctl start mysqld.service [root@Slave centos]# systemctl status mysqld.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Fri 2024-10-18 18:32:11 EDT; 5s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 76080 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 76171 (mysqld) Status: "Server is operational" Tasks: 38 (limit: 10938) Memory: 688.2M CGroup: /system.slice/mysqld.service └─76171 /usr/sbin/mysqld Oct 18 18:31:10 Slave systemd[1]: Starting MySQL Server... Oct 18 18:32:11 Slave systemd[1]: Started MySQL Server. |
Verify the upgradation
Verify the MySQL upgradation by connecting to the new database version
| 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 42 43 44 45 46 47 48 49 | [root@Slave centos]# mysql -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.40 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.40 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.40 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: 1 min 27 sec Threads: 2 Questions: 11 Slow queries: 0 Opens: 668 Flush tables: 4 Open tables: 35 Queries per second avg: 0.126 -------------- mysql> show databases; +--------------------+ | Database | +--------------------+ | employees | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.01 sec) mysql> q |
Verify the compatibility of the tables
Checks tables for any compatibility issues
| 1 2 3 4 5 6 | [root@Slave centos]# mysqlcheck --user=root --all-databases --check-upgrade -p Enter password: employees.departments Table is already up to date employees.dept_emp Table is already up to date .................................. .................................. |
