MySQL 5.7 vs MySQL 8 - Do you need to upgrade to MySQL 8

John D K -

In this post:

Update April 2018: In mid 2018 the stable version of MySQL is 5.7.21 and it's too early to switch to MySQL 8

You may want to check this post - it contains summary of useful tips and tutorials for MySQL (the list will grow with time):

MySQL useful tips and reference project

Update June 2018: MySQL 8 was released on 2018-04-19 as general availability with version - 8.0.11. Since MySQL 5.7 will be supported until October 2023 you have enough time to prepare migration to MySQL 8. Many linux distros still use MySQL 5.7 as latest release and you will need to install MySQL 8 manually. If you want to check how to install MySQL 8:

How to install MySQL 8 on Ubuntu

MySQL 5.7 vs MySQL 8

MySQL is known as the most popular and common RDBMS with last significant release - MySQL 5.7 more than 5 years ago. If you think where are the other version MySQL 6, 7 ( I was having the some doubts) then the answer is 5.7 will be continued by 8 (maybe it's coming from 5.8 ).

The development release was launched and is available for download on the MySQL repos. For example: MySQL development release for Windows: MySQL Installer 8.0.3 rc and the Ubuntu versions: Download MySQL APT Repository

There are some differences which will have impact on your work with MySQL if you move from 5.7 to 8. For example if you want to change your root password in MySQL 8 you will need to do different steps in comparison to 5.7:

MySQL 8 set or reset user password

Another big change that can impact you is the storage engine MyISAM which will be available with some restrictions. MySQL meta tables are moved to InnoDB. This has to be taken into account when you want to upgrade.

What's new in MySQL 8 that need your attention

Character Sets

utf8mb4 collations — The idea is to change the default character set from latin1 to utf8mb4, and the default collation from latin1_swedish_ci to utf8mb4_800_ci_ai. UTF8 is the dominating character encoding for the web(supporting 4-Byte UTF-8 characters / emoji into your Database), and this move will make life easier for the vast majority of MySQL users. These are great news and one step ahead towards standardized multilingual support in data driven applications.

Roles

Roles has been a highly desired feature for a long time. In general it's a representation for a collection of privileges. Now you can define a role for user and there's no need to remember whether you should GRANT INSERT, UPDATE, etc for these users. Just create a role and you're good to go.

Invisible indexes.

This feature will help you when you need to check if an index can be dropped or not. In order to test the performance without this index you can make it invisible. If you suspect that an index is being used and the performance is affected then you can restore the invisible index. The optimizer will not see the invisible indexes and you can monitor query performance before dropping index.

System table moved to InnoDB

The first storage engine for MySQL - MyISAM and was available in MySQL up to now. MySQL 5.7 still uses MyISAM for the MySQL privilege tables in the MySQL schema but in MySQL they are moved to InnoDB

In MySQL 8.0 the MyISAM would be available but with some constraints:

  • After introducing the new data dictionary, the MyISAM tables are not part from the system schema.
  • You can create a table engine=MyISAM, and it will work as before
  • Coping MyISAM table into a running MySQL server will not work because it will not be discovered.

SET PERSIST for global variables

Another great new feature is persistent runtime configuration changes. Since version 8 is allowed to be used

SET PERSIST innodb_buffer_pool_size = X;

instead of

SET GLOBAL innodb_buffer_pool_size = X;

for the runtime changes to persist during a restart.

UUID Enhancements

UUIDs are intended to generate unique ids in tables. Since version 8 MySQL can store these values in a VARBINARY(16) column instead of CHAR(36). New functions are introduced to work with these UUID values: BIN_TO_UUID(), UUID_TO_BIN(), IS_UUID(). Better storage usage, performance improvement more flexible way to work with unique ids.

Descending Indexes

Descending indexes are now available. It will be possible to scan then in reverse order without any performance impact. In the past the the desc or asc was ignored. This feature is extremely helpful when you need to get the newly created records vs the older ones.

Official statement:

MySQL supports descending indexes: DESC in an index definition is no longer ignored but causes storage of key values in descending order. Previously, indexes could be scanned in reverse order but at a performance penalty. A descending index can be scanned in forward order, which is more efficient. Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others.

Huge number of defect fixed

You can check them here:

Changes in MySQL 8.0.0 (2016-09-12, Development Milestone)

Do you need upgrade to MySQL 8

If you find any of these changes suitable for you then you can move to the new version. Have in mind that it'll take some time before this version become more stable and it's the new mainline version for the most users. So you can used it at your own risk. Personally I'll stay on the version 5.7 at least for next year - 2019.

Here you can see information about end of support for latest releases:

  • MySQL 5.6 - February 2021
  • MySQL 5.7 - October 2023

and release date of MySQL 8

  • MySQL 8 - April 2018

Reference