Introduction:

Ever wondered what’s new in MySQL? Get ready to explore MySQL 8.0, the latest version that brings exciting features and improvements. In this blog post, we’ll take you on a journey from your familiar MySQL 5.7 to the fresh and powerful world of MySQL 8.0. We’ll uncover the differences between the two versions, share stories about the migration process, and offer tips to make your transition smooth. Whether you’re a tech enthusiast or a business owner, this is your chance to discover how MySQL 8.0 can elevate your database experience.

Section 1: Key Differences Between MySQL 5.7 and MySQL 8.0 ??

Authentication Method:

• MySQL 5.7 uses the mysql_native_password authentication method by default.
• MySQL 8.0 uses the more secure caching_sha2_password authentication method by default, enhancing database security.

Connection Pooling and Java Code:

• MySQL 8.0 introduces improvements in connection pooling for better resource management and performance.
• Code for connecting to the database and utilizing connection pools may need updates when migrating from MySQL 5.7 to 8.0.

Storage Engine Enhancements:

• MySQL 8.0 enhances the InnoDB storage engine with features like improved table compression, instant ADD COLUMN, and InnoDB Replica Set for high availability.

SQL Commands and Syntax:

• MySQL 8.0 introduces Common Table Expressions (CTEs), window functions, and recursive queries, enabling more advanced and readable SQL queries.

System Variables and Configuration:

• MySQL 8.0 introduces new system variables, such as innodb_dedicated_server for optimized memory usage, and the Query Rewrite Plugin for query optimization.

Replication and High Availability:

• MySQL 8.0 introduces enhancements in replication, including InnoDB Replica Set for improved high availability and read scalability.

Security and Compliance:

• MySQL 8.0 offers improved password policies and validation, enhancing overall database security.
• Roles can be used to manage sets of privileges, allowing more fine-grained control over access.

Performance and Scalability:

• MySQL 8.0 includes various performance improvements and optimizations, contributing to better overall database performance.

JSON Support:

• MySQL 8.0 enhances JSON support with new functions and capabilities, making it easier to work with JSON data.

Query Rewrite Plugin:

• The Query Rewrite Plugin in MySQL 8.0 allows dynamic modification of queries before execution, providing greater flexibility in query optimization.

Section 2: Challenges we can face while migrating from 5.7 to 8.0 ??

Migrating from MySQL 5.7 to MySQL 8.0 can bring several challenges, as there have been significant changes and improvements in MySQL 8.0. Here are some potential challenges you might face during the migration process:

• Incompatibility: MySQL 8.0 introduced several changes to SQL syntax, data types, and behavior. Queries and applications that rely on features or syntax specific to MySQL 5.7 might not work correctly in MySQL 8.0 without modifications.


• Authentication Method: MySQL 8.0 uses the caching_sha2_password authentication plugin by default, which might require updating client libraries and connection settings. This can be a challenge if your existing applications and tools are not compatible with the new authentication method.


• Default SQL Mode: MySQL 8.0 has a more strict default SQL mode, which might lead to errors or unexpected behavior if your application relies on lenient modes in MySQL 5.7. You might need to adjust your queries and data to comply with the new mode.


• Character Set and Collation Changes: MySQL 8.0 has improved support for Unicode characters and introduced changes to default character sets and collations. This can affect string comparisons and sorting in your applications.


• Data Type Changes: MySQL 8.0 introduced new data types and enhancements to existing ones. You may need to review and update your schema and data types to take advantage of these changes or ensure compatibility.


• Deprecation and Removal of Features: MySQL 8.0 deprecated and removed several features and options that were present in MySQL 5.7. You’ll need to identify and replace deprecated features with their recommended alternatives.


• JSON Handling: While both versions have JSON support, MySQL 8.0 introduced additional JSON functions and enhancements. You may need to review and modify JSON-related queries and code.


• Upgrading Stored Routines and Triggers: Stored procedures, functions, and triggers may need to be reviewed and potentially modified to work with MySQL 8.0. Certain behaviors and syntax might have changed.


• Upgrade Process: The actual process of upgrading the MySQL server involves careful planning and execution to avoid data loss or downtime. You should test the upgrade process thoroughly in a controlled environment before attempting it in production.


• Third-Party Compatibility: Some third-party tools, libraries, and applications might not yet fully support MySQL 8.0, which could cause compatibility issues.


• Performance Considerations: While MySQL 8.0 introduces various performance improvements, there might be changes in query execution plans and optimizer behavior. You should thoroughly test your application’s performance in the new version.


• InnoDB Changes: InnoDB storage engine improvements in MySQL 8.0 might impact your database’s behavior, especially if you rely on specific InnoDB settings or features.

To mitigate these challenges, it’s recommended to thoroughly test the migration process in a staging environment, ensure that your applications are compatible, and consult the official MySQL documentation and relevant resources to understand the changes and best practices for migrating to MySQL 8.0.

Section 3: New important variables added in to 8.0 ??

MySQL 8.0 introduced several new system variables and configuration options that provide greater control and customization over various aspects of the database server’s behavior. Here are some important variables that were added or modified in MySQL 8.0:

• innodb_dedicated_server: This variable is a boolean that, when enabled, configures InnoDB to optimize for dedicated database server instances with large amounts of memory.


• innodb_page_cleaners: Specifies the number of background page cleaner threads for the InnoDB storage engine. Helps improve write-intensive workloads.


• innodb_buffer_pool_chunk_size: Controls the size of individual memory chunks within the InnoDB buffer pool, allowing better allocation of memory resources.


• innodb_flush_neighbors: Determines whether InnoDB flushes pages in the buffer pool in a random order or in a sequential pattern.


• innodb_monitor_enable_all: Enables or disables all InnoDB monitoring.


• binlog_expire_logs_seconds: Specifies the number of seconds after which binary log files are automatically purged.


• binlog_row_image: Specifies how row changes are stored in the binary log, affecting replication efficiency and storage.


• max_execution_time: Defines the maximum execution time for SQL statements, preventing long-running queries from consuming excessive resources.


• default_authentication_plugin: Sets the default authentication plugin used for creating new user accounts.


• default_password_lifetime: Specifies the default password lifetime in days for new accounts.


• delayed_insert_limit: Controls the maximum number of rows that can be inserted into a delayed-insert table.


• enforce_storage_engine: Prevents creation of tables or indexes with a storage engine other than the one specified.


• log_slow_verbosity: Determines the level of detail for the slow query log, providing more insight into slow query performance.


• validate_password_policy: Specifies the password policy to be enforced for new user passwords.


• sql_mode: MySQL 8.0 introduced several changes and stricter default SQL modes. You can customize the SQL mode to control how queries and data behave.


• query_cache_type: The query cache was deprecated and removed in MySQL 8.0, so related variables are no longer applicable.

Section 4: Different Methods of Upgrade MYSQL5.7 to 8.0 ??

In-Place Upgrade:

• In this method, you directly upgrade the existing MySQL installation on the same server.
• Back up your MySQL 5.7 data, configuration files, and user accounts.
• Download and install MySQL 8.0 on the same server, ensuring compatibility with your operating system and hardware.
• Start the MySQL 8.0 server and perform the upgrade process, which includes running the upgrade scripts and migrating data.
• Adjust configuration settings, test your applications thoroughly, and update any necessary code or queries.

Dump and Restore:

• This method involves exporting data from MySQL 5.7, installing a new MySQL 8.0 instance, and then importing the data.
• Back up your MySQL 5.7 data using tools like mysqldump or a graphical tool.
• Install MySQL 8.0 on a new server or a different machine.
• Create the necessary databases and tables in MySQL 8.0.
• Restore the data from the backup using mysql command or a similar tool.
• Review and update your application code, queries, and configurations to ensure compatibility with MySQL 8.0.

Replication-Based Upgrade:

• This method involves setting up replication between MySQL 5.7 and MySQL 8.0, allowing you to gradually migrate data.
• Set up a new MySQL 8.0 instance as a slave to your existing MySQL 5.7 master. This allows data replication between the two versions.
• Monitor and verify replication to ensure data consistency.
• Once the replication is stable, promote the MySQL 8.0 slave to become the new master.
• Update your application to point to the new master server (MySQL 8.0).
• Decommission the old MySQL 5.7 server.

Section 5: Important commands that were introduced or modified in MySQL 8.0:

MySQL 8.0 introduced several new features, enhancements, and changes to existing commands compared to previous versions like MySQL 5.7. Here are some important commands that were introduced or modified in MySQL 8.0:

Common Table Expressions (CTEs):

• MySQL 8.0 introduced support for Common Table Expressions (CTEs) with the WITH keyword. CTEs allow you to define temporary result sets within a SQL statement, making complex queries more readable and manageable.

Window Functions:

• MySQL 8.0 added support for window functions such as ROW_NUMBER(), RANK(), DENSE_RANK(), and others. These functions enable advanced analytical operations and ranking within result sets.

Recursive Queries:

• MySQL 8.0 introduced support for recursive queries using the WITH RECURSIVE syntax, allowing you to build hierarchical and recursive queries more efficiently.

CHECK Constraints:

• MySQL 8.0 added support for CHECK constraints, allowing you to enforce data validation rules at the table level.

Instant ADD COLUMN:

• MySQL 8.0 introduced the ability to add columns to a table with the INSTANT keyword, allowing you to perform the operation without requiring a full table rebuild.

Roles:

• MySQL 8.0 introduced support for roles, enabling you to define and manage sets of privileges that can be granted to users or other roles.

DESCRIBE Improvements:

• The DESCRIBE command in MySQL 8.0 provides more detailed information about columns, indexes, and constraints, making it easier to understand table structure.

Online ALTER TABLE:

• MySQL 8.0 introduced enhancements to the online ALTER TABLE command, allowing you to perform certain table alterations without locking the table.

Atomic DDL:

• MySQL 8.0 introduced atomic Data Definition Language (DDL) statements, ensuring that certain DDL operations complete successfully or are rolled back as a single unit.

InnoDB Replica Set Management:

• MySQL 8.0 introduced SQL statements and commands for managing InnoDB replica sets, enabling more fine-grained control over replication.

MySQL Shell:

• MySQL 8.0 introduced the MySQL Shell, a command-line interface that provides enhanced capabilities for database administration and management tasks.

Resource Groups and User Resource Management:

• MySQL 8.0 introduced resource groups and user resource management, allowing you to allocate resources and control resource usage for specific users or groups of users.

SHOW CREATE ROLE:

• MySQL 8.0 added the SHOW CREATE ROLE statement, allowing you to view the SQL statement that creates a specific role.

Section 6: How to check current Mysql database is compatible to upgrade at 8.0 version ?

MySQL Shell Upgrade Checker Utility enables you to verify whether MySQL server instances are ready for upgrade. It is a script that will check your MySQL 5.7 instance for compatibility errors and issues with upgrading.

Please note it’s only a check and fixing part needs to be done separately.

Complete upgrade pre-requisites can be checked at link

Steps for same:

Install the utility:

yum install -y mysql-shell.x86_64

Now you are ready to perform the check with below command:

[ec2-user@mysql-dev ~]# mysqlsh root@localhost -e "util.checkForServerUpgrade();"
The MySQL server at /var%2Flib%2Fmysql%2Fmysql.sock, version 5.7.27-log - MySQL
Community Server (GPL), will now be checked for compatibility issues for
upgrade to MySQL 8.0.17...
 
1) Usage of old temporal type
  No issues found
 
2) Usage of db objects with names conflicting with reserved keywords in 8.0
  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
  No issues found
 
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
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging
 
15) Removed system variables
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed
 
16) System variables with new default values
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/
 
17) Schema inconsistencies resulting from file removal or corruption
  No issues found
 
18) Issues reported by 'check table x for upgrade' command
  No issues found
 
19) 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: 1
Notices:  0

There were no fatal errors but some potential issues were detected. Let us dig more with couple of additional parameters:

util.checkForServerUpgrade('root@localhost:3306', {"password":"password", "targetVersion":"8.0.11", "configPath":"/etc/my.cnf"})

It will provide couple of more warnings where the actual work to be done before upgradation.